ClickHouse Cheat Sheet

Connection & Basics


         clickhouse-client

Connect to ClickHouse server


         clickhouse-client --host=host --port=9000

Connect to remote server


         clickhouse-client --query "SELECT 1"

Execute query from command line


         SHOW DATABASES;

List all databases


         USE database_name;

Switch to database


         SHOW TABLES;

List tables in current database


         DESCRIBE TABLE table_name;

Show table structure


         SELECT version();

Show ClickHouse version

Table Engines


         ENGINE = MergeTree() ORDER BY (date, id)

Most common engine, sorted data


         ENGINE = ReplacingMergeTree() ORDER BY id

Deduplicate rows by ORDER BY key


         ENGINE = SummingMergeTree() ORDER BY (date, id)

Auto-sum numeric columns on merge


         ENGINE = AggregatingMergeTree()

Store pre-aggregated data


         ENGINE = CollapsingMergeTree(sign)

Track changes with sign column (+1/-1)


         ENGINE = Distributed(cluster, db, table)

Distributed table across cluster


         ENGINE = Memory

In-memory table (not persistent)


         ENGINE = Null

Discard all written data


         ENGINE = File(CSV)

Read/write CSV files


         ENGINE = URL('http://...', CSV)

Query data from HTTP endpoint

Data Types


         UInt8, UInt16, UInt32, UInt64

Unsigned integers (0 to max)


         Int8, Int16, Int32, Int64

Signed integers


         Float32, Float64

Floating-point numbers


         Decimal(P, S)

Fixed-point decimal (precision, scale)


         String

Variable-length string


         FixedString(N)

Fixed-length string (N bytes)


         Date

Date (year-month-day)


         DateTime

Date and time (Unix timestamp)


         DateTime64(3)

DateTime with millisecond precision


         Array(T)

Array of type T


         Tuple(T1, T2, ...)

Fixed set of typed values


         Nullable(T)

Type T that can be NULL


         LowCardinality(String)

Optimize for repeated values


         Enum8('val1' = 1, 'val2' = 2)

Enumeration (8-bit storage)

Creating Tables


         CREATE TABLE events (date Date, user_id UInt32) ENGINE = MergeTree() ORDER BY date;

Create table with MergeTree engine


         PARTITION BY toYYYYMM(date)

Partition by month


         PRIMARY KEY (user_id, date)

Define primary key (subset of ORDER BY)


         SAMPLE BY intHash32(user_id)

Enable sampling for queries


         SETTINGS index_granularity = 8192

Set index granularity


         TTL date + INTERVAL 30 DAY

Auto-delete old data


         DEFAULT today()

Default column value


         MATERIALIZED now()

Computed on insert

Inserting Data


         INSERT INTO table VALUES (1, 'text');

Insert single row


         INSERT INTO table SELECT * FROM other_table;

Insert from SELECT query


         INSERT INTO table FORMAT CSV

Insert CSV data (pipe in data)


         INSERT INTO table FORMAT JSONEachRow

Insert newline-delimited JSON


         cat data.csv | clickhouse-client --query="INSERT INTO table FORMAT CSV"

Load CSV file via CLI

Query Optimization


         PREWHERE condition

Filter before reading all columns (faster)


         SELECT * FROM table SAMPLE 0.1

Query 10% sample of data


         FINAL

Force final merge (slow, avoid if possible)


         LIMIT 10 BY user_id

Limit per group (top N per group)


         WITH TOTALS

Add totals row to GROUP BY


         EXPLAIN SELECT ...

Show query execution plan


         SETTINGS max_threads = 8

Control query parallelism

Aggregate Functions


         count(), sum(), avg(), min(), max()

Standard aggregation functions


         uniq(column)

Approximate unique count (HyperLogLog)


         uniqExact(column)

Exact unique count (slower)


         topK(10)(column)

Top 10 most frequent values


         quantile(0.95)(column)

95th percentile


         groupArray(column)

Collect values into array


         groupUniqArray(column)

Collect unique values into array


         any(column)

Get any value from group


         argMax(value, time)

Value at maximum time


         -State, -Merge

Combinator suffixes for AggregatingMergeTree

Date/Time Functions


         now()

Current date and time


         today()

Current date


         toDate('2024-01-01')

Parse date from string


         toDateTime('2024-01-01 12:00:00')

Parse datetime from string


         toStartOfMonth(date)

First day of month


         toStartOfWeek(date)

First day of week


         toYYYYMM(date)

Format as YYYYMM integer


         dateDiff('day', start, end)

Difference between dates


         addDays(date, 7)

Add days to date


         formatDateTime(dt, '%Y-%m-%d')

Format datetime as string

Array Functions


         arrayJoin(array_column)

Expand array into rows


         has(array, value)

Check if array contains value


         length(array)

Array length


         arrayMap(x -> x * 2, array)

Transform array elements


         arrayFilter(x -> x > 0, array)

Filter array elements


         arrayReduce('sum', array)

Reduce array with aggregate function

Materialized Views


         CREATE MATERIALIZED VIEW mv_name ENGINE = SummingMergeTree() AS SELECT ...

Create materialized view


         TO existing_table AS SELECT ...

Populate existing table


         POPULATE

Fill with existing data on creation


         DROP VIEW mv_name

Remove materialized view

System Tables


         SELECT * FROM system.tables

Query metadata about tables


         SELECT * FROM system.parts

Table parts and their sizes


         SELECT * FROM system.processes

Currently running queries


         SELECT * FROM system.query_log

Query history and performance


         SELECT * FROM system.mutations

Track ALTER/DELETE mutations


         SELECT * FROM system.metrics

Current server metrics

Table Maintenance


         OPTIMIZE TABLE table_name;

Force merge of table parts


         OPTIMIZE TABLE table_name FINAL;

Merge into single part


         ALTER TABLE table_name DELETE WHERE condition;

Delete rows (asynchronous mutation)


         ALTER TABLE table_name DROP PARTITION '202401';

Drop entire partition


         TRUNCATE TABLE table_name;

Delete all data