ClickHouse Cheat Sheet
ClickHouse columnar OLAP database covering table engines, MergeTree ordering keys, materialized views, and analytical SQL.
MergeTree Table Definition
The core table engine for analytical workloads, with ordering and partitioning.
CREATE TABLE events( event_date Date, event_time DateTime, user_id UInt64, event_type LowCardinality(String), properties String)ENGINE = MergeTreePARTITION BY toYYYYMM(event_date)ORDER BY (event_type, user_id, event_time)TTL event_date + INTERVAL 90 DAYSETTINGS index_granularity = 8192;
Analytical Query Patterns
Common ClickHouse SQL idioms for fast aggregation over huge tables.
-- Approximate distinct count (fast, low memory)SELECT uniqCombined(user_id) FROM events WHERE event_date >= today() - 7;-- Array-based funnel-style aggregationSELECT event_type, count() AS cntFROM eventsWHERE event_date >= today() - 1GROUP BY event_typeORDER BY cnt DESCLIMIT 10;-- Quantiles in one passSELECT quantiles(0.5, 0.95, 0.99)(response_ms) FROM requests;-- FINAL forces merge-time dedup for ReplacingMergeTree readsSELECT * FROM users FINAL WHERE id = 42;
Materialized View for Real-Time Rollups
Incrementally aggregate incoming rows into a summary table as they're inserted.
CREATE TABLE events_by_hour( hour DateTime, event_type LowCardinality(String), cnt AggregateFunction(count))ENGINE = AggregatingMergeTreeORDER BY (event_type, hour);CREATE MATERIALIZED VIEW events_by_hour_mv TO events_by_hour ASSELECT toStartOfHour(event_time) AS hour, event_type, countState() AS cntFROM eventsGROUP BY hour, event_type;-- Query the rollupSELECT hour, event_type, countMerge(cnt) FROM events_by_hour GROUP BY hour, event_type;
Table Engine Cheat Sheet
Which MergeTree variant to reach for.
- MergeTree- the base engine; use for general append-only analytical data
- ReplacingMergeTree- deduplicates rows with the same ORDER BY key on merge, needs `FINAL` or `OPTIMIZE` for guaranteed correctness
- SummingMergeTree- automatically sums numeric columns for rows sharing an ORDER BY key
- AggregatingMergeTree- stores partial aggregate states, paired with materialized views for rollups
- CollapsingMergeTree- uses a sign column to cancel out old versions of a row, for mutable-record patterns
- Distributed- a virtual table that fans queries out across shards in a cluster
Sharded + Replicated Cluster Tables
Combine ReplicatedMergeTree (per-shard replication via Keeper) with a Distributed table for cluster-wide reads/writes, deployed with ON CLUSTER DDL.
CREATE TABLE events_local ON CLUSTER main_cluster( event_date Date, event_time DateTime, user_id UInt64, event_type LowCardinality(String))ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events_local', '{replica}')PARTITION BY toYYYYMM(event_date)ORDER BY (event_type, user_id, event_time);CREATE TABLE events ON CLUSTER main_cluster AS events_localENGINE = Distributed(main_cluster, default, events_local, cityHash64(user_id));-- Writes to `events` are routed by the sharding key (cityHash64(user_id));-- reads fan out to all shards and merge results.
Kafka Engine Streaming Ingestion
Consume a Kafka topic directly into ClickHouse using the Kafka engine as a pass-through, materialized into a MergeTree target.
CREATE TABLE events_kafka( event_date Date, event_time DateTime, user_id UInt64, event_type String)ENGINE = KafkaSETTINGS kafka_broker_list = 'kafka:9092', kafka_topic_list = 'events', kafka_group_name = 'clickhouse-consumer', kafka_format = 'JSONEachRow', kafka_num_consumers = 3;CREATE TABLE events_store( event_date Date, event_time DateTime, user_id UInt64, event_type LowCardinality(String))ENGINE = MergeTreePARTITION BY toYYYYMM(event_date)ORDER BY (event_type, user_id, event_time);CREATE MATERIALIZED VIEW events_kafka_mv TO events_store ASSELECT event_date, event_time, user_id, toLowCardinality(event_type) AS event_typeFROM events_kafka;
Projections for Alternate Query Patterns
Store a secondary, differently-ordered copy of the data alongside the table so ClickHouse picks it automatically for queries that don't match the primary ORDER BY.
ALTER TABLE events ADD PROJECTION events_by_user( SELECT event_date, user_id, event_type, count() GROUP BY event_date, user_id, event_type);ALTER TABLE events MATERIALIZE PROJECTION events_by_user;-- A query filtering/grouping by user_id instead of event_type transparently-- uses the projection instead of scanning the base table's granules:SELECT user_id, count() FROM eventsWHERE event_date = today() GROUP BY user_id;
JOIN Algorithms & Performance Settings
ClickHouse picks a join strategy based on table sizes and settings -- know which one you're paying for.
- hash join (default)- builds an in-memory hash table from the right side; fast but memory-bound on large right tables
- join_algorithm='partial_merge'- spills to disk, trades speed for bounded memory on huge joins
- ASOF JOIN- matches each left row to the nearest right row by a non-equal (usually time) condition, ideal for point-in-time price/metric lookups
- Dictionary instead of JOIN- for small, slow-changing lookup tables, a Dictionary + dictGet() avoids a join entirely and is far faster
- max_bytes_in_join- caps memory for the hash table side, throwing an error instead of OOM-killing the server
- GLOBAL JOIN- broadcasts the right-side subquery result to all shards once, avoiding redundant per-shard execution in a Distributed query
External Dictionary for Fast Enrichment
Load a slowly-changing lookup table into memory and join it via dictGet() instead of a real JOIN.
CREATE DICTIONARY user_country_dict( user_id UInt64, country String)PRIMARY KEY user_idSOURCE(CLICKHOUSE(TABLE 'user_countries'))LAYOUT(HASHED())LIFETIME(MIN 300 MAX 600);SELECT event_type, dictGet('user_country_dict', 'country', user_id) AS country, count()FROM eventsGROUP BY event_type, country;
Design your ORDER BY key around your most common WHERE/GROUP BY filters, not around uniqueness — unlike a primary key in Postgres, ClickHouse's ORDER BY key is a sparse sort index, and getting it wrong means full-partition scans instead of skipped granules.
