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:

sudo apt update
sudo apt install apt-transport-https ca-certificates curl gnupg

Download the repository signing key into /etc/apt/keyrings:

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:

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:

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:

sudo systemctl enable --now clickhouse-server

Check that it is running:

sudo systemctl status clickhouse-server --no-pager

● 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:

clickhouse-client --password

Run a first query:

SELECT version();

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:

sudo nano /etc/clickhouse-server/config.d/listen.xml

<clickhouse>
    <listen_host>0.0.0.0</listen_host>
</clickhouse>

Restart the server and allow the ports only from the addresses that need them, never from the whole internet:

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:

clickhouse-client --password

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:

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);

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:

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;

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:

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:

SELECT
    event_type,
    count() AS events,
    round(avg(duration_ms)) AS avg_ms
FROM analytics.events
GROUP BY event_type
ORDER BY events DESC;

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:

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:

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:

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:

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:

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:

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:

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:

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 @-

{"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.