A Practitioner’s Guide to ClickHouse

Yan Zeng, version 1.0, last updated on 2/25/2023

Downloadable at https://leanpub.com/sqf6-clickhouse-guide

Contents
Goals ............................................................................................................................................................. 2
References .................................................................................................................................................... 2
Row-Based DBMS vs. Column-Based DBMS ................................................................................................. 2
Sample Equity Tick Data................................................................................................................................ 3
Basic Statistics of One Day’s Tick Data .......................................................................................................... 3
Key Concepts in ClickHouse: Partition, Primary Key, Order BY, Skip Index .................................................. 4
Illustration of Data Storage in ClickHouse .................................................................................................... 5
Partition ........................................................................................................................................................ 5
Basics......................................................................................................................................................... 5
Directory creation, merging, and deletion................................................................................................ 5
Partition improves query performance .................................................................................................... 7
Key Takeaways for Partition ..................................................................................................................... 7
Primary Key, Order By ................................................................................................................................... 8
Sparse index to locate granules ................................................................................................................ 8
Build and use the primary index ............................................................................................................... 9
Use mark files.......................................................................................................................................... 11
Generic exclusion search algorithm ........................................................................................................ 11
Data skipping index: a secondary index to group and skip granules ...................................................... 12
Test, Test, Test! ........................................................................................................................................... 12
Additional Resources .................................................................................................................................. 13

1
Goals
• Provide a self-contained introduction to the inner working of ClickHouse
➢ How are data stored?
➢ How are data queried?
• Design a suitable table schema for equity tick data
• Intended audience: engineers implementing ClickHouse; quants using ClickHouse

References
• 朱凯：《ClickHouse 原理解析与应用实践》，机械工业出版社，2020.
• Vijay Anand: Up and Running with ClickHouse, BPB Publications, India, 2020.
• ClickHouse Official Documentation: https://clickhouse.com/docs/en/intro

Row-Based DBMS vs. Column-Based DBMS

• Row-based DBMS

• Column-based DBMS

2
Sample Equity Tick Data
• Data Source: https://firstratedata.com/tick-data
• Sample data: trades of AAPL and MSFT on 2020-01-02

• Table schema for trade data:

• Table creation for trade data

Basic Statistics of One Day’s Tick Data

• Based on Bloomberg BPIPE equity tick data (trades & quotes combined) on 2022-05-27
➢ ~19K distinct tickers
➢ ~136 million distinct time stamps
➢ ~595 million rows
➢ ~7.4 GB of compressed data and ~55.5 GB uncompressed data
➢ These numbers allow back-of-envelop estimation of query efficiency (see later)

3
 • Equity tick data is huge, so that storage and queries need to be extremely efficient

Key Concepts in ClickHouse: Partition, Primary Key, Order BY, Skip Index
Conceptually,

• Partition: directory for physical storage of data

• Order By: sort rows by lexicographic order of sort keys
• Primary Key: for indexing data location
• Data Skipping Index: additional data indexing

Physically,

• Data are divided by “partitions” (directories)

• Within each partition, column data are stored separately in [Column].bin
• Rows are in lexicographic ascending order by the primary key columns (and the additional sort
key columns)
• Rows from different columns are matched via [Column].mrk

4
Illustration of Data Storage in ClickHouse
Using web browsing data as an illustration: UserID, URL, EventTime

• PRIMARY KEY (UserID, URL) ORDER BY (UserID, URL, EventTime)

• Web browsing data are sorted first by UserID, then by URL, and lastly be EventTime

Partition
Basics
• Data are written to disk simultaneously so that table insertion is fast
• As a result, multiple directories for the same partition are created, and then merged (10-15 min.
after insertion); old directories will then be deleted (~8 min. after merging)
• MinBlockNum, MaxBlockNum: global counter across partitions, increase by 1 if a new
partition directory is generated
• Level: the number of merging for a particular partition; local counter of “age”
• Example: directory “201905_1_1_0” is the first directory created for the partition “201905”

Directory creation, merging, and deletion

In the example below, month is used as the partition key for table “partition_v5”, e. g. 201905,
201906, etc.

5
6
Partition improves query performance
Back to Bloomberg equity tick data. Assume we have 3 years of daily data and we use date as the
partition key

• This will lead to about 250 * 3 = 750 partitions

• Partitioning indexing (minmax.idx) is triggered when the partition column “timestamp” is
used in the “WHERE” condition, allowing ClickHouse to skip many irrelevant partitions.
➢ SELECT * FROM equity_tickdata LIMIT 10 ⇒ full table scan, 750 partitions
will be scanned
➢ SELECT * FROM equity_tickdata WHERE timestamp >=
toDateTime64(‘2020-01-02 00:00:00.000000’,6) AND timestamp
<= toDateTime64(‘2020-01-02 23:59:59.000000’,6) LIMIT 10 ⇒
scan data for 01/02/2020, 1 partition will be scanned
• Number of partitions affects efficiency, up to 10x (Source: Altinity): month vs. date as partition
key

Key Takeaways for Partition

• A partition is a directory for physical storage of data
• Partition allows fast table insertion: multiple directories are created for the same partition, and
then merged and deleted
• Partition allows fast data query: when the column(s) for partitioning appears in WHERE statement,
partition indexing is triggered and only the relevant partitions are scanned for query result
• Number of partitions should not be too big: building and reading partition index files take time
and memory

7
Primary Key, Order By
Recall that web browsing data are sorted first by UserID, then by URL, and lastly by EventTime:
PRIMARY KEY (UserID, URL) ORDER BY (UserID, URL, EventTime)

• Ordered data storage allows for efficient search algorithm, e. g. binary search algorithm
• Web browsing data are sorted first by UserID, then by URL, and lastly be EventTime

Sparse index to locate granules

• Primary key columns are used to build a sparse index, which, when combined with column level
offset files (“mark”), can quickly locate matching data
➢ First element of the primary key columns is used for binary search algorithm
➢ Other elements of the primary key columns are used for generic exclusion search
algorithm (more on this later)
• Data are logically grouped into “granules”
➢ typically, 8192 rows, set by index_granularity
➢ for Bloomberg equity tick data on 5/27/2022, 1 granule = 55.5 GB / 595 mil. * 8192 =
0.76 MB, 1 ticker = 55.5 GB / 19K = 3 MB = 4 granules
• After being located by the sparse index, relevant granules are loaded into memory for parallel
data processing

8
Build and use the primary index
• The primary index has one entry per granule. The orange marked columns values are the
minimum values of each primary key column in each granule; they will be the entries in the
table’s primary index. The primary index file is completely loaded into the main memory (~6MB
for equity_tickdata table on 5/27, ~120MB if partitioning by month)
• The primary index is used for selecting granules: SELECT * FROM equity_tickdata
WHERE id = ‘AAPL’ AND timestamp >= toDateTime64(‘2022-05-27
00:00:00.000000,6)’ AND timestamp <= ‘2022-05-27
23:59:59.000000,6)’
➢ “id” is used for binary search algorithm
➢ “timestamp” is used for generic exclusion search algorithm to locate the relevant
granules

9
10
Use mark files
Primary index file locates the logical location of relevant granules, mark files locate the physical location
of the granules

• Locating via mark files happens to each column in parallel (hence the speed)
• Why not store that information directly in primary index? The primary index file needs to fit into
the main memory

Generic exclusion search algorithm

• The generic exclusion search algorithm is most effective when the predecessor key column has
low(er) cardinality
• On 5/27/2022, equity_tickdata has ~19K distinct IDs and ~136 mil. distinct timestamps,
#id ≪ #timestamp
• Details of this algorithm can be found at https://clickhouse.com/docs/en/guides/improving-query-
performance/sparse-primary-indexes/sparse-primary-indexes-multiple/#generic-exclusion-search-
algorithm

11
Data skipping index: a secondary index to group and skip granules
A secondary data skipping index on the URL column of the web browsing data with compound primary
key (UserID, URL)

• A secondary data skipping index on URL helps with excluding granules only if the #UserID ≪
#URL
• Data skipping index should only be used after investigating other alternatives (projections,
materialized views, etc.)
• Data skipping index behavior is not obvious from thought experiments alone

Test, Test, Test!

• Design of table schemas needs to be carefully considered for each business application.
• Use ClickHouse command-line client to have detailed performance information for each design.

12
Additional Resources
• ClickHouse Academy - Free self-paced ClickHouse Training (requires login to track
progress): https://clickhouse.com/learn/
• Monthly ClickHouse release webinars: https://clickhouse.com/company/news-events
• Monthly newsletter: https://clickhouse.com/company/news-events
• YouTube channel - recent recordings from Monthly releases & meetups:
https://www.youtube.com/c/ClickHouseDB
• Blogs - many recent articles of technical nature: https://clickhouse.com/blog
• ClickHouse Roadmap 2023: https://github.com/ClickHouse/ClickHouse/issues/44767

13