--- title: "How to Install ClickHouse on Ubuntu 24.04 for Analytics" description: "Install ClickHouse from the official repository on Ubuntu 24.04, create a MergeTree table, load 10 million rows, and speed up reports with a materialized view." category: "Database Advanced" language: en source: https://cubepath.com/docs/database-advanced/clickhouse-installation-for-analytics updated: 2026-09-25 --- # How to Install ClickHouse on Ubuntu 24.04 for Analytics ClickHouse is an open-source, column-oriented database built for analytical queries. It stores each column separately and compressed, so aggregations over hundreds of millions of rows that would take minutes in a row-oriented database often finish in well under a second. In this tutorial you will install ClickHouse on Ubuntu 24.04 from the official repository, design a MergeTree table for event data, load 10 million sample rows, run analytical queries and add a materialized view that keeps a daily summary up to date. ## Prerequisites To follow this tutorial, you will need: - A server running Ubuntu 24.04 LTS, for example a CubePath VPS, with at least 2 vCPUs and 4 GB of RAM. ClickHouse runs on less, but the sample load in this guide is more comfortable with this size. - A non-root user with `sudo` privileges. - An SSD or NVMe disk with at least 10 GB free. ## Step 1 - Adding the ClickHouse repository ClickHouse publishes packages for Debian and Ubuntu in its own repository. Install the tools needed to add it: ```bash sudo apt update sudo apt install apt-transport-https ca-certificates curl gnupg ``` Download the repository signing key into `/etc/apt/keyrings`: ```bash sudo install -m 0755 -d /etc/apt/keyrings curl -fsSL https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key | sudo gpg --dearmor -o /etc/apt/keyrings/clickhouse-keyring.gpg ``` The key is published under the RPM path, but the same key signs the Debian repository. Add the repository for the stable release channel: ```bash echo "deb [signed-by=/etc/apt/keyrings/clickhouse-keyring.gpg arch=$(dpkg --print-architecture)] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list ``` If you prefer releases with a longer support period, replace `stable` with `lts`. ## Step 2 - Installing ClickHouse Update the package index and install the server and the command-line client: ```bash sudo apt update sudo apt install clickhouse-server clickhouse-client ``` During the installation you are asked for a password for the `default` user. Enter a strong password: ClickHouse stores its SHA-256 hash in `/etc/clickhouse-server/users.d/default-password.xml`. Enable and start the service: ```bash sudo systemctl enable --now clickhouse-server ``` Check that it is running: ```bash sudo systemctl status clickhouse-server --no-pager ``` ```output ● clickhouse-server.service - ClickHouse Server (analytic DBMS for big data) Loaded: loaded (/usr/lib/systemd/system/clickhouse-server.service; enabled; preset: enabled) Active: active (running) ``` Connect with the client. The `--password` flag without a value makes it prompt for the password: ```bash clickhouse-client --password ``` Run a first query: ```sql SELECT version(); ``` ```output 25.x.x.x 1 row in set. Elapsed: 0.001 sec. ``` Type `exit` to leave the client. By default ClickHouse only listens on `localhost`: port 9000 for the native protocol used by `clickhouse-client` and port 8123 for the HTTP interface. ## Step 3 - Understanding the main configuration files ClickHouse keeps its default configuration in two files that the package manages: `/etc/clickhouse-server/config.xml` for server settings and `/etc/clickhouse-server/users.xml` for users and query limits. Do not edit them, because package upgrades replace them. Instead, put your changes in separate files under `/etc/clickhouse-server/config.d/` and `/etc/clickhouse-server/users.d/`, which are merged on top of the defaults. For example, if applications on other servers need to connect, make ClickHouse listen on all interfaces. Create a file: ```bash sudo nano /etc/clickhouse-server/config.d/listen.xml ``` ```xml 0.0.0.0 ``` Restart the server and allow the ports only from the addresses that need them, never from the whole internet: ```bash sudo systemctl restart clickhouse-server sudo ufw allow from your_app_server_ip proto tcp to any port 8123,9000 ``` Skip this change if you only use ClickHouse locally. Data is stored in `/var/lib/clickhouse/` and logs in `/var/log/clickhouse-server/`. ## Step 4 - Designing a MergeTree table Most ClickHouse tables use the MergeTree engine. Inserted rows are written as immutable parts sorted by the table's sorting key, and background processes merge parts into larger ones. Two choices in the table definition determine performance: - `ORDER BY` defines the sorting key and the sparse primary index. Put the columns you filter on most, from low to high cardinality, first. It is not a uniqueness constraint. - `PARTITION BY` splits data into partitions that can be dropped or moved as a unit. Monthly partitions are a sensible default for event data; too many small partitions hurt performance. Open the client again and create a database and a table for website events: ```bash clickhouse-client --password ``` ```sql CREATE DATABASE analytics; CREATE TABLE analytics.events ( event_time DateTime, user_id UInt64, event_type LowCardinality(String), country LowCardinality(String), duration_ms UInt32 ) ENGINE = MergeTree PARTITION BY toYYYYMM(event_time) ORDER BY (event_type, event_time); ``` `LowCardinality(String)` stores repeated strings as dictionary codes, which saves space and speeds up filtering on columns such as event type or country that have few distinct values. ## Step 5 - Loading sample data Generate 10 million events spread over the last 30 days with the `numbers` table function. Each call to `rand()` gets a different argument so that ClickHouse evaluates them independently: ```sql INSERT INTO analytics.events SELECT now() - toIntervalSecond(rand() % (86400 * 30)) AS event_time, rand(1) % 100000 AS user_id, ['page_view', 'click', 'signup', 'purchase'][(rand(2) % 4) + 1] AS event_type, ['ES', 'US', 'DE', 'FR', 'MX'][(rand(3) % 5) + 1] AS country, rand(4) % 5000 AS duration_ms FROM numbers(10000000); ``` ```output Ok. 0 rows in set. Elapsed: 1.523 sec. Processed 10.00 million rows, 80.00 MB (6.57 million rows/s., 52.53 MB/s.) ``` Check the row count and how well the data compresses: ```sql SELECT sum(rows) AS rows, formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed, formatReadableSize(sum(data_compressed_bytes)) AS compressed FROM system.parts WHERE database = 'analytics' AND table = 'events' AND active; ``` ```output rows uncompressed compressed 10000000 180.00 MiB 62.41 MiB 1 row in set. Elapsed: 0.004 sec. ``` Random data compresses poorly; real event data, with many repeated values, usually compresses far better. To load your own data, pipe a file into the client and name its format. For a CSV file with a header row: ```bash clickhouse-client --password --query "INSERT INTO analytics.events FORMAT CSVWithNames" < events.csv ``` ClickHouse supports many other formats, including `JSONEachRow` (one JSON object per line) and `Parquet`. ## Step 6 - Running analytical queries Count events and the average duration per type: ```sql SELECT event_type, count() AS events, round(avg(duration_ms)) AS avg_ms FROM analytics.events GROUP BY event_type ORDER BY events DESC; ``` ```output event_type events avg_ms click 2501187 2500 purchase 2500364 2499 page_view 2499553 2500 signup 2498896 2500 4 rows in set. Elapsed: 0.041 sec. Processed 10.00 million rows, 50.00 MB (243.90 million rows/s., 1.22 GB/s.) ``` ClickHouse only read the two columns the query needed. Now a query that filters on the sorting key, daily purchases and unique buyers in Spain over the last week: ```sql SELECT toDate(event_time) AS day, count() AS purchases, uniq(user_id) AS buyers FROM analytics.events WHERE event_type = 'purchase' AND country = 'ES' AND event_time >= now() - INTERVAL 7 DAY GROUP BY day ORDER BY day; ``` Because `event_type` and `event_time` are the first columns of `ORDER BY`, the primary index lets ClickHouse skip most of the table. The `Processed` line in the output shows far fewer than 10 million rows. `uniq` is an approximate distinct count that is much faster than `count(DISTINCT ...)`; use `uniqExact` when you need an exact number. To see how much of the table a query reads, prefix it with `EXPLAIN indexes = 1`, which lists the parts and granules selected by the partition key and the primary key. ## Step 7 - Building a daily summary with a materialized view A materialized view in ClickHouse works like an insert trigger: every block inserted into the source table is transformed by the view's query and written to a target table. This lets dashboards read a small summary table instead of scanning raw events. Create the target table with the SummingMergeTree engine, which adds up numeric columns for rows with the same sorting key when parts are merged: ```sql CREATE TABLE analytics.events_daily ( day Date, event_type LowCardinality(String), events UInt64, total_duration UInt64 ) ENGINE = SummingMergeTree ORDER BY (day, event_type); ``` Create the materialized view that feeds it: ```sql CREATE MATERIALIZED VIEW analytics.events_daily_mv TO analytics.events_daily AS SELECT toDate(event_time) AS day, event_type, count() AS events, sum(duration_ms) AS total_duration FROM analytics.events GROUP BY day, event_type; ``` The view only processes rows inserted after it was created. Backfill the summary with the existing data once: ```sql INSERT INTO analytics.events_daily SELECT toDate(event_time), event_type, count(), sum(duration_ms) FROM analytics.events GROUP BY toDate(event_time), event_type; ``` Merges happen in the background, so the summary table can temporarily hold several rows for the same day and type. Always aggregate again when reading it: ```sql SELECT day, event_type, sum(events) AS events FROM analytics.events_daily WHERE day >= today() - 3 GROUP BY day, event_type ORDER BY day, event_type; ``` This query reads a few hundred rows instead of millions. Insert a new event and run it again to see the counter for today increase. ## Step 8 - Expiring old data with TTL To keep the raw events table from growing forever, add a TTL expression. ClickHouse deletes expired rows during merges and drops whole parts when all their rows have expired: ```sql ALTER TABLE analytics.events MODIFY TTL event_time + INTERVAL 90 DAY; ``` The summary table has no TTL, so daily totals are kept after the raw events expire. Check the table definition with: ```sql SHOW CREATE TABLE analytics.events; ``` ## Step 9 - Querying over HTTP Applications can also query ClickHouse through its HTTP interface on port 8123, with no driver. Send the query as the request body and the credentials as basic authentication: ```bash echo "SELECT event_type, count() FROM analytics.events GROUP BY event_type FORMAT JSONEachRow" | curl -s -u default:your_password 'http://localhost:8123/' --data-binary @- ``` ```output {"event_type":"click","count()":"2501187"} {"event_type":"purchase","count()":"2500364"} {"event_type":"page_view","count()":"2499553"} {"event_type":"signup","count()":"2498896"} ``` Replace `your_password` with the `default` user's password. For anything other than local tests, put the HTTP interface behind TLS or a private network. ## Troubleshooting - **`Authentication failed: password is incorrect`**: the client is not sending the password. Use `clickhouse-client --password` so it prompts for it. - **`Code: 241. DB::Exception: Memory limit exceeded`**: the query needs more memory than allowed. Reduce the data it reads with a `WHERE` on the sorting key, or let large `GROUP BY` operations spill to disk by setting `max_bytes_before_external_group_by`. - **`Too many parts`** errors during inserts: the application sends many tiny inserts. Batch rows into inserts of thousands of rows or more, or enable asynchronous inserts with the `async_insert = 1` setting. - **The server does not start**: read `/var/log/clickhouse-server/clickhouse-server.err.log`. An XML syntax error in a file under `config.d/` is a common cause. ## Conclusion You installed ClickHouse on Ubuntu 24.04, created a MergeTree table with a sorting key that matches your queries, loaded and queried 10 million rows and kept a daily summary current with a materialized view. As next steps, create a dedicated user with limited privileges for each application instead of using `default`, connect a dashboard tool such as Grafana or Metabase, and set up backups with the `BACKUP` statement or the `clickhouse-backup` tool.