ClickHouse Query Cookbook

Column-oriented open-source relational database built for online analytical processing (OLAP). 10 production-tested query recipes ready to copy and execute.

🐘 PostgreSQL 🐬 MySQL 🪶 SQLite 🔴 Oracle Database 💻 Microsoft SQL Server 🍃 MongoDB 🔴 Redis ❄️ Snowflake 🍒 ClickHouse 🦆 DuckDB

1. Create MergeTree Table with Partitioning

Design high-performance analytical event table.

CREATE TABLE user_events (
  event_date Date,
  user_id UInt64,
  event_type String,
  duration UInt32
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, user_id, event_date);

#mergetree#ddl#olap

2. Deduplicate Records with ReplacingMergeTree

Keep latest version of records sharing primary key.

CREATE TABLE user_profiles (
  user_id UInt64,
  email String,
  updated_at DateTime
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

#replacingmergetree#dedup#engine

3. Continuous Aggregate Materialized View

Pre-aggregate incoming stream metrics automatically.

CREATE MATERIALIZED VIEW daily_sales_mv
ENGINE = SummingMergeTree()
ORDER BY (date, category)
AS SELECT event_date AS date, category, SUM(amount) AS total
FROM raw_orders GROUP BY date, category;

#materialized_view#realtime#aggregate

4. Unpack Arrays with arrayJoin()

Expand array elements into separate table rows.

SELECT user_id, arrayJoin(tags) AS tag
FROM user_tags;

#arrayjoin#array#transform

5. Compute Percentiles with quantileExact()

Calculate 95th and 99th percentile query latency metrics.

SELECT service_name,
       quantileExact(0.95)(duration_ms) AS p95,
       quantileExact(0.99)(duration_ms) AS p99
FROM app_logs GROUP BY service_name;

#quantile#percentile#analytics

6. Extract Domain from URL Strings

Parse web domain from raw request URL string.

SELECT domainWithoutWWW(url) AS domain, COUNT(*)
FROM web_clicks GROUP BY domain ORDER BY COUNT(*) DESC;

#url#string#helpers

7. Query System Tables for Part Sizes

Retrieve table storage metrics from system catalog.

SELECT table, formatReadableSize(sum(bytes)) AS size, sum(rows) AS total_rows
FROM system.parts WHERE active = 1 GROUP BY table ORDER BY sum(bytes) DESC;

#system#storage#dba

8. Fetch Top Record Attributes with argMax

Retrieve column value associated with maximum timestamp.

SELECT user_id, argMax(status, timestamp) AS latest_status
FROM user_status_logs GROUP BY user_id;

#argmax#aggregate#latest

9. External Dictionary Lookup

Join fast in-memory dictionary data without SQL JOINs.

SELECT user_id, dictGetString('country_dict', 'country_name', toUInt64(country_id)) AS country
FROM clicks;

#dictionary#lookup#speed

10. Force Background Partition Merge

Manually trigger partition merge and deduplication.

OPTIMIZE TABLE user_profiles FINAL;

#optimize#merge#maintenance

Test your ClickHouse architectural knowledge!

Practice key concepts, memory structures, and indexing questions with flashcards.

Study ClickHouse Flashcards →