← All Cheat Codes Library / Cheat Codes / ClickHouse

🍒 ClickHouse Cheat Codes

ClickHouse Cheat Codes

ClickHouse Cheat Codes for MergeTree engines, columnar OLAP queries, arrayJoin, and materializations. 20 essential cheat codes ready to copy and execute.

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

# 1 SELECT DQL

Query columnar data at high execution speed.

Syntax:

SELECT col1, col2 FROM tbl;

Working Example:

SELECT user_id, event_type FROM user_events;

# 2 WHERE DQL

Filter rows matching partition pruning.

Syntax:

SELECT * FROM tbl WHERE col = val;

Working Example:

SELECT * FROM logs WHERE status = 500;

# 3 GROUP BY Aggregations

Aggregate billions of rows in memory.

Syntax:

SELECT col, COUNT(*) FROM tbl GROUP BY col;

Working Example:

SELECT domainWithoutWWW(url), COUNT(*) FROM web_clicks GROUP BY 1;

# 4 ENGINE = MergeTree() Table Engine

Default MergeTree table engine DDL declaration.

Syntax:

CREATE TABLE tbl (...) ENGINE = MergeTree() ORDER BY (col);

Working Example:

CREATE TABLE logs (dt Date, user_id UInt64) ENGINE = MergeTree() ORDER BY (dt, user_id);

# 5 PARTITION BY Partitioning

Partition MergeTree table parts by month or date.

Syntax:

CREATE TABLE tbl (...) ENGINE = MergeTree() PARTITION BY toYYYYMM(dt) ORDER BY (col);

Working Example:

CREATE TABLE sales (dt Date, amt Float64) ENGINE = MergeTree() PARTITION BY toYYYYMM(dt) ORDER BY dt;

# 6 ReplacingMergeTree Table Engine

Background deduplicating table engine.

Syntax:

CREATE TABLE tbl (...) ENGINE = ReplacingMergeTree(ver) ORDER BY (id);

Working Example:

CREATE TABLE profiles (id UInt64, email String, updated DateTime) ENGINE = ReplacingMergeTree(updated) ORDER BY id;

# 7 SummingMergeTree Table Engine

Automatically sum numeric columns on background merge.

Syntax:

CREATE TABLE tbl (...) ENGINE = SummingMergeTree() ORDER BY (id);

Working Example:

CREATE TABLE daily_totals (date Date, category String, amount Float64) ENGINE = SummingMergeTree() ORDER BY (date, category);

# 8 CREATE MATERIALIZED VIEW Views

Continuous streaming aggregation materialized view.

Syntax:

CREATE MATERIALIZED VIEW mv ENGINE = SummingMergeTree() ORDER BY (col) AS SELECT ...;

Working Example:

CREATE MATERIALIZED VIEW mv_sales ENGINE = SummingMergeTree() ORDER BY (date) AS SELECT dt AS date, SUM(amt) FROM sales GROUP BY date;

# 9 arrayJoin() Array Functions

Unpack array elements into individual output rows.

Syntax:

SELECT col, arrayJoin(arr_col) FROM tbl;

Working Example:

SELECT user_id, arrayJoin(tags) FROM user_profiles;

# 10 quantileExact() Aggregations

Compute exact 95th/99th percentile query latency.

Syntax:

SELECT quantileExact(0.95)(latency) FROM tbl;

Working Example:

SELECT service, quantileExact(0.95)(duration_ms) FROM logs GROUP BY service;

# 11 domainWithoutWWW() String Functions

Parse clean domain from URL string.

Syntax:

SELECT domainWithoutWWW(url_col) FROM tbl;

Working Example:

SELECT domainWithoutWWW(referrer) FROM web_clicks;

# 12 argMax() Aggregations

Fetch column value matching maximum timestamp.

Syntax:

SELECT argMax(val, timestamp) FROM tbl GROUP BY grp;

Working Example:

SELECT user_id, argMax(status, updated_at) FROM status_logs GROUP BY user_id;

# 13 system.parts System Catalog

Inspect physical MergeTree part file sizes.

Syntax:

SELECT table, sum(bytes), sum(rows) FROM system.parts GROUP BY table;

Working Example:

SELECT table, formatReadableSize(sum(bytes)) FROM system.parts WHERE active = 1 GROUP BY table;

# 14 OPTIMIZE TABLE FINAL Maintenance

Force immediate background partition merge.

Syntax:

OPTIMIZE TABLE tbl FINAL;

Working Example:

OPTIMIZE TABLE user_profiles FINAL;

# 15 FORMAT JSON Output Format

Output query results as formatted JSON text.

Syntax:

SELECT * FROM tbl FORMAT JSON;

Working Example:

SELECT * FROM user_events LIMIT 5 FORMAT JSON;

# 16 EXPLAIN Performance

View ClickHouse query execution plan.

Syntax:

EXPLAIN SELECT * FROM tbl WHERE col = val;

Working Example:

EXPLAIN SELECT COUNT(*) FROM user_events WHERE event_date = '2026-01-01';

# 17 toYYYYMM() Date Functions

Convert date to numeric YYYYMM integer for partitioning.

Syntax:

SELECT toYYYYMM(date_col);

Working Example:

SELECT toYYYYMM(today());

# 18 dictGetString() Dictionary

Perform high-speed external dictionary lookup.

Syntax:

SELECT dictGetString('dict_name', 'attr', key_col);

Working Example:

SELECT user_id, dictGetString('users_dict', 'email', user_id) FROM clicks;

# 19 INSERT INTO SELECT DML

Bulk insert aggregated data into target MergeTree table.

Syntax:

INSERT INTO target_tbl SELECT * FROM src_tbl;

Working Example:

INSERT INTO archive_events SELECT * FROM user_events WHERE event_date < '2025-01-01';

# 20 ALTER TABLE DELETE DML

Asynchronously delete rows matching condition.

Syntax:

ALTER TABLE tbl DELETE WHERE condition;

Working Example:

ALTER TABLE user_events DELETE WHERE user_id = 999;