ClickHouse – Analytics at Speed

👶 What is ClickHouse?

ClickHouse is a database built for analytics. Unlike the database that powers your app (where you read and update one row at a time), ClickHouse is built for questions like: “What was our total revenue last month?”, “How many events per country in the last hour?”, “What’s the 99th percentile latency?” over huge amounts of data – billions of rows – and it answers them very fast, often in milliseconds.

Why “analytics” is different

In an analytics workload you usually add new rows (events, logs, sales) and then run queries that scan and aggregate (SUM, COUNT, AVG, GROUP BY). You rarely “update one row” or “delete one row.” ClickHouse is optimized for exactly that: lots of inserts and heavy read queries that touch many rows and many columns.

The main idea: columnar storage

Most databases (PostgreSQL, MySQL) store data row by row: each row is stored as one block (id, name, revenue, date together). So to get “sum of revenue,” the engine has to read every row and then take the revenue column. ClickHouse stores data column by column: all revenue values are stored together, all dates together, and so on. So when you ask for SUM(revenue), it only reads the revenue column – much less data from disk, so the query is much faster.

ROW STORE (e.g. PostgreSQL)       COLUMNAR STORE (ClickHouse)
Row1: [id, name, revenue, date]    Column revenue: [100, 200, 150, ...]
Row2: [id, name, revenue, date]   Column date:    [2024-01-01, ...]
Row3: [id, name, revenue, date]   Column name:   [Alice, Bob, ...]

SUM(revenue) → read every row,     SUM(revenue) → read only the
then take revenue.                 revenue column. ✓ Much faster.

Real use cases

People use ClickHouse for:

• Web and product analytics – clicks, page views, funnels (e.g. Yandex.Metrica).
• Logs and events – application logs, error tracking, user actions.
• IoT and sensors – time-series from devices (temperature, usage, telemetry).
• Business metrics – revenue by region, sales by product, dashboards.

⚡ Why Is It So Fast?

ClickHouse is built so that analytics queries over huge tables run in milliseconds instead of minutes. Here are the four main ideas (simplified from how the engine actually works). You don’t need to remember every detail – just get the intuition: read less, do more per CPU cycle, and don’t block reads with writes.

1. Columnar storage – read only what you need

We already said: ClickHouse stores by column. So SUM(revenue) only reads the revenue column from disk, not every column of every row. That means less data to read. On top of that, values in one column are often similar (e.g. many repeated dates or small integers), so they compress very well. Less data and better compression mean faster reads and lower storage cost.

2. Skip what you don’t need – pruning

ClickHouse doesn’t blindly scan every row. Data is sorted by a key (you choose it when you create the table, e.g. ORDER BY date). So when you run WHERE date >= '2024-01-01', the engine can skip whole chunks of data that only contain older dates. There are also “skipping indexes” (e.g. min/max per block): if a block’s maximum value is 10 and your query asks for clicks > 15, that block is skipped. The slogan: the fastest scan is not scanning at all.

3. Vectorized execution (SIMD)

Instead of “add two numbers, then two more, then two more,” the CPU can do the same operation on 8 or 16 numbers at once (SIMD: Single Instruction, Multiple Data). ClickHouse uses this for aggregations: summing millions of rows becomes a tight loop over chunks of 8 or 16 values. That’s why SUM(...) over a billion rows can still be very fast.

4. Parts and merges – append-friendly storage

New data is written into parts: sorted, immutable files. So inserts don’t rewrite existing data and don’t block reads. In the background, ClickHouse merges small parts into bigger ones (and can do things like “keep only the latest row per key” or “delete old data by TTL”). You get fast writes and fast reads at the same time.

📊 History & Real Numbers

Where ClickHouse came from

• 2009 – Built at Yandex for web analytics (Yandex.Metrica).
• 2016 – Open-sourced; docs in English and others.
• 2021 – ClickHouse, Inc. created; company valued at ~$2B.

OLAP vs OLTP

ClickHouse is for OLAP (analytics), not OLTP (day-to-day transactions).

OLTP (PostgreSQL, MySQL)     OLAP (ClickHouse)
Short queries, many updates   Long queries, few updates
Row storage                   Column storage
"Get one user"               "Sum revenue by region"

Benchmarks (Lund University study)

A Master’s thesis compared ClickHouse, Elasticsearch, and MinIO on real IoT-style data (billions of rows). In short:

• Ingest: ClickHouse ~21× faster than Elasticsearch.
• Storage: ClickHouse used ~3× less disk than Elasticsearch.
• Queries: ClickHouse 5–14× faster in most aggregation tests.

So “ClickHouse is fast” is backed by real experiments, not just marketing. (Source: Göransson & Wändesjö, Lund 2022.)

⚙️ Install with Docker on Mac

We use Docker on a Mac. Docker runs ClickHouse in a container so you don’t have to install it directly on your system. Follow each step and use the screenshots to confirm you’re in the right place.

docker pull clickhouse/clickhouse-server

user@MacBook-Pro ~ % docker pull clickhouse/clickhouse-server
latest: Pulling from clickhouse/clickhouse-server
a1b2c3d4e5f6: Pull complete
...
Status: Downloaded newer image for clickhouse/clickhouse-server:latest

docker run -d --name clickhouse-server \
  -p 8123:8123 -p 9000:9000 \
  clickhouse/clickhouse-server

user@MacBook-Pro ~ % docker run -d --name clickhouse-server \
  -p 8123:8123 -p 9000:9000 \
  clickhouse/clickhouse-server
a7f3b2c1d4e5968708192f3b4c5d6e7f8a9b0c1d2

docker ps

user@MacBook-Pro ~ % docker ps
CONTAINER ID   IMAGE                         STATUS         PORTS
a7f3b2c1d4e5   clickhouse/clickhouse-server   Up 2 minutes   0.0.0.0:8123->8123/tcp, 0.0.0.0:9000->9000/tcp
NAMES
clickhouse-server

curl http://localhost:8123

user@MacBook-Pro ~ % curl http://localhost:8123
Ok.

curl 'http://localhost:8123/?query=SELECT%201%2B1'

docker exec -it clickhouse-server clickhouse-client --query "SELECT 1+1"

user@MacBook-Pro ~ % curl 'http://localhost:8123/?query=SELECT%201%2B1'
2

user@MacBook-Pro ~ % docker exec -it clickhouse-server clickhouse-client --query "SELECT 1+1"
2

brew install clickhouse

clickhouse-client --host localhost

user@MacBook-Pro ~ % clickhouse-client --host localhost
ClickHouse client version 24.x.x.
Connecting to localhost:9000 as user default.
Connected to ClickHouse server.

localhost :) SELECT 1
1

localhost :) SELECT now()
2024-02-14 12:34:56

docker stop clickhouse-server

docker start clickhouse-server

📋 Tables and Data Types

In ClickHouse, all data lives in tables. Each column has a type (number, string, date, etc.). Tables are stored in a columnar way, so reading one column across many rows is very fast. This section teaches you how to create tables, choose types, and understand the options that affect performance.

Creating your first table

You need: (1) table name, (2) column names and types, (3) an engine, and (4) for MergeTree, an ORDER BY (how rows are sorted on disk). Here’s a minimal example:

CREATE TABLE events (
  time DateTime,
  name String,
  value Float64
) ENGINE = MergeTree()
ORDER BY time;

What this does: Creates a table events with three columns. ENGINE = MergeTree() is the standard engine for analytics (we’ll come back to it). ORDER BY time means data is sorted by time on disk – so queries like “last 7 days” can skip old data quickly.

Engine: MergeTree

ClickHouse has several table engines. For learning and for most analytics, you use MergeTree. MergeTree tables:

• Store data in parts (sorted by the key you give in ORDER BY).
• Merge parts in the background to keep the number of parts under control.
• Support fast inserts and fast range queries (e.g. by time).

Other engines (e.g. Log, Memory) exist for special cases; for now stick with MergeTree.

ORDER BY – the “primary key” for sorting

ORDER BY is required for MergeTree. It defines the order of rows on disk. Queries that filter or sort by a prefix of this key are very efficient (e.g. ORDER BY (date, user_id) makes WHERE date = today() and ORDER BY date, user_id fast). You can use a single column or a tuple, e.g.:

ORDER BY (toDate(time), name)
-- Good for: WHERE toDate(time) = '2024-01-15' or GROUP BY toDate(time), name

Pick columns you often filter or group by (e.g. date, region, user_id).

PARTITION BY (optional)

You can split data into partitions so that old partitions can be dropped or moved. For time-series, a common choice is by month:

CREATE TABLE events (
  time DateTime,
  name String,
  value Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(time)
ORDER BY time;

Then all rows from January 2024 go in one partition, February in another, etc. Don’t over-partition (e.g. by day for years of data can create too many parts).

Common data types

Choosing the right type saves space and speeds up queries. Here are the ones you’ll use most:

Type             Example values           Use case
─────────────────────────────────────────────────────────────────
Int8/16/32/64    42, -100                 Counts, IDs (signed)
UInt8/16/32/64    0, 65535                 Counts, quantities (non‑negative)
Float32/64        3.14, -0.5               Prices, ratios
String            'hello'                  Names, IDs, text
Date              2024-01-15               Day only (no time)
DateTime          2024-01-15 14:30:00      Date + time
Nullable(Int64)   NULL or 42               When the column can be missing

Use the smallest type that fits your data (e.g. UInt8 for age 0–255, Int32 for a 4-byte ID). Nullable(...) allows NULL in that column; use it only when you really need nulls, because it adds overhead.

Inserting data

You can insert with VALUES (a few rows) or with a format like TabSeparated for bulk load. Example:

INSERT INTO events (time, name, value) VALUES
  (now(), 'signup', 1.0),
  (now(), 'purchase', 29.99),
  (now() - INTERVAL 1 HOUR, 'click', 0.5);

Then read them back:

SELECT * FROM events ORDER BY time DESC;

┌─────────────────────┬──────────┬───────┐
│ time                │ name     │ value │
├─────────────────────┼──────────┼───────┤
│ 2024-02-14 13:00:00 │ signup   │   1   │
│ 2024-02-14 13:00:00 │ purchase │ 29.99 │
│ 2024-02-14 12:00:00 │ click    │  0.5  │
└─────────────────────┴──────────┴───────┘

Changing a table (ALTER)

You can add or drop columns without recreating the table:

ALTER TABLE events ADD COLUMN source String DEFAULT '';
ALTER TABLE events DROP COLUMN source;

Add column is lightweight; drop column is also supported. For big schema changes, check the docs (e.g. modifying column type has limitations).

🔤 SQL Basics

ClickHouse uses SQL. You write SELECT ... FROM table WHERE ... ORDER BY ... LIMIT ... and get results. This section builds from simple selects to filters, sorting, and limits so you can query your tables confidently.

SELECT and FROM

SELECT lists the columns (or expressions) you want. FROM is the table name. The simplest query returns all columns and all rows:

SELECT * FROM events;

To return only some columns and give one an alias:

SELECT time, name, value * 2 AS double_value
FROM events;

You can use expressions: value * 2, toDate(time), upper(name), etc. AS double_value names the result column.

WHERE – filtering rows

WHERE keeps only rows that satisfy a condition. Use comparison and logical operators:

SELECT * FROM events
WHERE time >= now() - INTERVAL 1 DAY
  AND name = 'purchase';

Common patterns:

• =, !=, >, <, >=, <=
• name IN ('click', 'signup') – value in a list
• name LIKE '%click%' – pattern match (% = any characters)
• AND, OR, NOT to combine conditions

Filtering by columns that appear in your ORDER BY (e.g. time) lets ClickHouse skip data efficiently.

ORDER BY and LIMIT

ORDER BY column sorts the result. Add DESC for descending:

SELECT * FROM events
ORDER BY time DESC
LIMIT 10;

LIMIT 10 returns only the first 10 rows. Very useful for “top N” or “last N” without scanning the whole table.

Example: list recent events of one type

“Show me the last 20 purchase events, newest first.”

SELECT time, name, value
FROM events
WHERE name = 'purchase'
ORDER BY time DESC
LIMIT 20;

Example: top event types by total value (last day)

“What were my top event types by total value in the last 24 hours?” This uses GROUP BY and sum() (we’ll cover aggregations next); the structure is the same:

SELECT name, sum(value) AS total
FROM events
WHERE time >= now() - INTERVAL 1 DAY
GROUP BY name
ORDER BY total DESC
LIMIT 10;

What each part does: WHERE keeps only the last day. GROUP BY name puts all rows with the same name into one group. sum(value) adds up value in each group. ORDER BY total DESC sorts by that sum, biggest first. LIMIT 10 returns the top 10.

┌──────────┬────────┐
│ name     │ total  │
├──────────┼────────┤
│ purchase │ 459.80 │
│ click    │ 120.50 │
│ signup   │  85.00 │
└──────────┴────────┘

📊 Aggregations

Aggregations turn many rows into one (or one per group): “total revenue,” “number of events,” “average value.” ClickHouse is built for this: sum, count, avg, min, max, and more run very fast over billions of rows because data is stored by column. This section teaches you how to use them with GROUP BY and HAVING.

What is aggregation?

An aggregate function takes many values and returns one: sum(revenue) → total, count() → number of rows, avg(value) → average. If you use an aggregate without GROUP BY, you get one row for the whole table. With GROUP BY column, you get one row per distinct value of column.

GROUP BY

GROUP BY splits rows into groups (e.g. by date, by product). Every column in SELECT must either be in GROUP BY or inside an aggregate function. Example:

SELECT name, count() AS cnt, sum(value) AS total
FROM events
WHERE time >= now() - INTERVAL 7 DAY
GROUP BY name
ORDER BY total DESC;

Here we get one row per name, with the count of rows and the sum of value for that name.

Basic aggregate functions

• count() – number of rows (no argument, or count(column) counts non-null values).
• sum(column) – sum of values.
• avg(column) – average.
• min(column), max(column) – smallest and largest value.

Useful ClickHouse aggregates

• uniq(column) – approximate count of distinct values (fast, very accurate).
• uniqExact(column) – exact count of distinct values (slower on huge data).
• quantile(0.5)(column) – median; quantile(0.99)(column) – 99th percentile.
• argMax(value, key) – the value that corresponds to the maximum key (e.g. “name of the user with max revenue”).

HAVING – filter after aggregation

WHERE filters rows before aggregation. HAVING filters groups after aggregation. Example: “only groups where total > 100”:

SELECT name, sum(value) AS total
FROM events
GROUP BY name
HAVING total > 100
ORDER BY total DESC;

Example: daily summary

“Per day, how many rows, total value, and average value?” (assuming you have an events table with time and value.)

SELECT
  toDate(time) AS day,
  count() AS row_count,
  sum(value) AS total_revenue,
  avg(value) AS avg_revenue
FROM events
WHERE time >= today() - 7
GROUP BY day
ORDER BY day;

Example: top 5 event types by count

“Which event types had the most rows in the last day?”

SELECT name, count() AS cnt
FROM events
WHERE time >= now() - INTERVAL 1 DAY
GROUP BY name
ORDER BY cnt DESC
LIMIT 5;

🪟 Window Functions

Window functions let you compute something per row while looking at a “window” of related rows – for example “rank within each day,” “running total so far,” or “previous row’s value.” They are powerful for rankings, trends, and time-series analysis.

What is a window?

A window is defined by:

• PARTITION BY – split rows into groups (e.g. by date, by user). The function is computed inside each group.
• ORDER BY – order of rows inside the partition (e.g. by time, by revenue).

So “for each row, consider only rows in the same partition, in this order.”

ROW_NUMBER, RANK, DENSE_RANK

These assign a number to each row within the partition:

• row_number() – 1, 2, 3, … (unique per row).
• rank() – same value for ties, then skip (1, 2, 2, 4).
• dense_rank() – same value for ties, no skip (1, 2, 2, 3).

Example: rank products by revenue within each day.

SELECT
  sale_date,
  product,
  revenue,
  row_number() OVER (PARTITION BY sale_date ORDER BY revenue DESC) AS rank
FROM sales
WHERE sale_date = today() - 1
ORDER BY sale_date, rank;

What this does: PARTITION BY sale_date means “per day.” ORDER BY revenue DESC means “best revenue first.” row_number() assigns 1 to the top product that day, 2 to the second, and so on.

LAG and LEAD

LAG(column) = value of column from the previous row (in the window order). LEAD(column) = value from the next row. Useful for “compare to previous” or “next value.”

SELECT
  time,
  value,
  LAG(value) OVER (ORDER BY time) AS prev_value,
  value - LAG(value) OVER (ORDER BY time) AS diff_from_prev
FROM events
ORDER BY time;

Here prev_value is the previous row’s value, and diff_from_prev is the change. You can use LAG(value, 2) to look two rows back.

Running sum (cumulative total)

Use sum(...) OVER (ORDER BY ...) to get a running total:

SELECT
  time,
  value,
  sum(value) OVER (ORDER BY time) AS running_total
FROM events
ORDER BY time;

Each row’s running_total is the sum of value from the first row up to that row.

🔌 Real-time and Integrations

ClickHouse can ingest data in real time (HTTP, native protocol, or Kafka) and power dashboards, APIs, and data pipelines. This section shows you the main ways to get data in and connect ClickHouse to the rest of your stack.

Inserting data via HTTP

The HTTP interface (port 8123) accepts INSERTs. You send the query in the URL and the data in the body. Any language that can do HTTP POST can insert rows. Example with curl using TabSeparated format (columns separated by tab, rows by newline):

echo -e "2024-02-14 12:00:00\tpage_view\t1.0\n2024-02-14 12:01:00\tclick\t0.5" \
  | curl -X POST 'http://localhost:8123/?query=INSERT%20INTO%20events%20FORMAT%20TabSeparated' --data-binary @-

From Python you can use the clickhouse-driver or clickhouse-connect library, or plain HTTP with requests. For very high throughput, use the native protocol (port 9000) instead of HTTP.

Using the clickhouse-client for bulk load

For loading files (CSV, Parquet, etc.), you can pipe data into the client:

clickhouse-client --host localhost --query "INSERT INTO events FORMAT TabSeparated" < data.tsv

Or from inside the Docker container:

docker exec -i clickhouse-server clickhouse-client --query "INSERT INTO events FORMAT TabSeparated" < data.tsv

Supported formats include TabSeparated, CSV, JSONEachRow, Parquet. Choose the one that matches your file.

Kafka and streaming (concept)

ClickHouse can read directly from Apache Kafka using a Kafka table engine: you define a table that reads from a Kafka topic, and optionally a materialized view that writes from that table into a MergeTree table. That way, events flowing into Kafka are continuously ingested into ClickHouse. Setting this up requires Kafka and some configuration; the docs have step-by-step examples. For learning, start with HTTP or client inserts; add Kafka once you need real-time streaming.

Materialized views (concept)

A materialized view in ClickHouse is a trigger: whenever data is inserted into a source table, a query runs and its result is written into the materialized view’s underlying table. So you can keep a “pre-aggregated” table (e.g. “daily totals per event type”) that updates automatically as new rows arrive. Useful for dashboards that need fast answers over large tables.

Dashboards and BI tools

Connect ClickHouse to your favorite tool and run SQL to build charts:

• Grafana – add ClickHouse as a data source (plugin available), write SQL in the panel editor.
• Metabase / Apache Superset – add ClickHouse (HTTP or native), create questions/queries with the UI or raw SQL.

All of them ultimately run SELECT queries; your job is to write efficient SQL (filter by sort key, use aggregations).

Pipelines: Airflow, dbt

Airflow: Use a ClickHouseHook or a task that runs clickhouse-client or sends HTTP requests to run ETL (e.g. “every hour, insert from S3 into ClickHouse”). dbt: Use the dbt-clickhouse adapter to define models (SQL) that build and transform tables in ClickHouse. Good for “raw table → cleaned table → aggregated table” workflows.

What’s next?

You now have a full path: install → create tables → query with SQL → aggregate and use windows → plug into dashboards and pipelines. Practice by building a small project: e.g. a table of events (time, name, value), insert some rows, then write queries for “totals per day,” “top 10 event types,” and a running sum. Use the official ClickHouse docs for reference and advanced features (replication, clusters, more table engines). Have fun with it.