ClickHouse Cheatsheet: MergeTree, Materialized Views and OLAP Reference

# Docker — single-node dev instance
docker run -d --name ch \
    -p 8123:8123 -p 9000:9000 \
    -e CLICKHOUSE_USER=dev -e CLICKHOUSE_PASSWORD=dev \
    clickhouse/clickhouse-server:24

# Native client (binary protocol on 9000)
docker exec -it ch clickhouse-client --user dev --password dev

# HTTP — handy from curl / scripts (port 8123)
curl 'http://dev:dev@localhost:8123/?query=SELECT+version()'

# Local-only — in-process tool, no server needed
clickhouse-local -q "SELECT count() FROM file('events.parquet')"

# Python — official driver
pip install "clickhouse-connect>=0.7"

python - <<'PY'
import clickhouse_connect
client = clickhouse_connect.get_client(host='localhost', username='dev', password='dev')
print(client.query('SELECT version()').result_rows[0][0])
PY

-- Canonical MergeTree table for an events stream.
-- Rules of thumb: ORDER BY puts low-cardinality cols first;
-- PARTITION BY is coarse (months/days), never user-level.
CREATE TABLE events
(
    ts        DateTime CODEC(DoubleDelta, LZ4),
    user_id   UInt64,
    event     LowCardinality(String),           -- dict-encoded → small + fast
    country   LowCardinality(FixedString(2)),
    revenue   Decimal(18, 4),
    props     Map(LowCardinality(String), String),

    -- Skip indexes: planner can prune granules cheaply
    INDEX idx_event event TYPE set(0)        GRANULARITY 4,
    INDEX idx_user  user_id TYPE minmax      GRANULARITY 4
)
ENGINE = MergeTree
ORDER BY (event, toStartOfHour(ts), user_id)    -- primary key prefix
PARTITION BY toYYYYMM(ts)                       -- one part dir per month
TTL ts + INTERVAL 90 DAY                        -- drop old parts automatically
SETTINGS index_granularity = 8192;

-- Bulk insert (always batch — VALUES one-by-one is a smell)
INSERT INTO events FROM INFILE 'events.parquet' FORMAT Parquet;

-- Force a merge after a backfill (rarely needed on hot tables)
OPTIMIZE TABLE events PARTITION '202605' FINAL;

-- The -State / -Merge / -MergeState combinator family.
-- Pattern: pre-aggregate now (cheap on write), finalize on read (cheap on query).

-- A bare aggregate column — the "finalized" result
SELECT uniq(user_id) FROM events;            -- → 1234567

-- -State: same function, but returns the internal aggregator state
SELECT uniqState(user_id) FROM events;       -- → AggregateFunction(uniq, UInt64) blob

-- Store states in a column (often via an AggregatingMergeTree MV)
CREATE TABLE u_daily
(
    day  Date,
    uniq AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree ORDER BY day;

INSERT INTO u_daily
SELECT toDate(ts) AS day, uniqState(user_id) FROM events GROUP BY day;

-- -Merge: combine multiple states back into a finalized value
SELECT day, uniqMerge(uniq) AS unique_users
FROM u_daily WHERE day >= today() - 30
GROUP BY day ORDER BY day;

-- -MergeState: combine into ANOTHER state (handy when downsampling MVs)
SELECT toStartOfWeek(day) AS w, uniqMergeState(uniq) AS uniq
FROM u_daily GROUP BY w;

-- Incremental MV: hourly rollup of an events stream.
-- The MV is a trigger on INSERT — it runs on every new block, writes
-- partial aggregates into the target table, and queries combine them.

CREATE TABLE events_hourly
(
    hour        DateTime,
    event       LowCardinality(String),
    visits      AggregateFunction(uniq, UInt64),  -- HLL state
    revenue_sum SimpleAggregateFunction(sum, Decimal(18, 4))
)
ENGINE = AggregatingMergeTree
ORDER BY (event, hour)
PARTITION BY toYYYYMM(hour);

CREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly AS
SELECT toStartOfHour(ts)     AS hour,
       event,
       uniqState(user_id)    AS visits,           -- -State suffix
       sum(revenue)          AS revenue_sum
FROM events
GROUP BY hour, event;

-- Read with -Merge to combine partial states
SELECT event,
       hour,
       uniqMerge(visits)     AS unique_users,
       sum(revenue_sum)      AS revenue
FROM events_hourly
WHERE hour >= now() - INTERVAL 24 HOUR
GROUP BY event, hour
ORDER BY hour DESC, revenue DESC;

import clickhouse_connect
import pandas as pd

client = clickhouse_connect.get_client(
    host='clickhouse.internal',
    port=8443, secure=True,
    username='reader', password='****',
    database='analytics',
)

# Server-side parameters — bound, not string-formatted
df: pd.DataFrame = client.query_df(
    """
    SELECT event,
           uniqMerge(visits) AS uniq,
           sum(revenue_sum)  AS revenue
    FROM events_hourly
    WHERE hour BETWEEN {start:DateTime} AND {end:DateTime}
    GROUP BY event
    ORDER BY revenue DESC
    """,
    parameters={'start': '2026-05-01 00:00:00', 'end': '2026-05-19 00:00:00'},
)

# Bulk insert — pass a DataFrame; column names map to the table
client.insert_df('events_raw', df)

# Streaming a huge result without materializing in RAM
with client.query_rows_stream('SELECT * FROM events WHERE date = today()') as stream:
    for batch in stream:                  # batches are list[tuple]
        process(batch)

# Native HTTP for ad-hoc — useful in notebooks
client.command('OPTIMIZE TABLE events_hourly FINAL')

-- End-to-end: raw event stream → hourly rollup MV → dashboard query.

-- 1. Raw event table (the firehose).
CREATE TABLE events
(
    ts       DateTime CODEC(DoubleDelta, LZ4),
    user_id  UInt64,
    event    LowCardinality(String),
    revenue  Decimal(18, 4)
)
ENGINE = MergeTree
ORDER BY (event, toStartOfHour(ts), user_id)
PARTITION BY toYYYYMM(ts)
TTL ts + INTERVAL 180 DAY;

-- 2. Hourly rollup target — keeps HLL state + running sums.
CREATE TABLE events_hourly
(
    hour        DateTime,
    event       LowCardinality(String),
    visits      AggregateFunction(uniq, UInt64),
    revenue_sum SimpleAggregateFunction(sum, Decimal(18, 4))
)
ENGINE = AggregatingMergeTree
ORDER BY (event, hour) PARTITION BY toYYYYMM(hour);

-- 3. MV that wires (1) → (2) incrementally on every INSERT.
CREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly AS
SELECT toStartOfHour(ts) AS hour, event,
       uniqState(user_id) AS visits, sum(revenue) AS revenue_sum
FROM events GROUP BY hour, event;

-- 4. Dashboard query — finalized via -Merge.
SELECT hour, event,
       uniqMerge(visits) AS unique_users,
       sum(revenue_sum)  AS revenue
FROM events_hourly
WHERE hour >= now() - INTERVAL 7 DAY
GROUP BY hour, event ORDER BY hour DESC, revenue DESC;