📚 Tom's Notes

Search

SearchSearch

Tech - DB - ClickHouse

Jan 22, 20259 min read

https://clickhouse.com/

  • OLAP, column-oriented DBMS

  • Started as a clickstream data warehouse at Yandex

  • ”Real-time data warehouse”

  • Parallel processing on multiple cores

  • Distributed processing on multiple servers

  • SQL syntax

  • At least 100x faster than row-oriented DBMS when used in OLAP scenarios

Pros

  • Simple installation
  • Strongly-typed schema
  • Hot / cold storage with automatic migration
  • Designed for scalability
  • Very high ingestion rate
  • Simple queries as fast as Timescale
  • Complex queries are faster than Timescale
  • Lower HW requirements than Druid
  • Engine configurable for each table

Cons

  • Query language is SQL-like, not true SQL
  • Engine configurable for each table (difficult tuning ?? )
  • JOINS not as straightforward - use dictionaries ???
  • Backup / restore has limitations
  • No strong atomicity guarantees

https://fr.slideshare.net/Altinity/migration-to-clickhouse-practical-guide-by-alexander-zaitsev

  • Comprehensive list of caveats

Setup

https://clickhouse.com/docs/en/getting-started

Local instance

https://clickhouse.com/docs/en/install#quick-install

  1. Install: 
    curl https://clickhouse.com/ | sh
  2. Start server: 
    ./clickhouse server
  3. Start client: 
    ./clickhouse

Courses

https://learn.clickhouse.com/user_catalog_class/show/1309771?title=March-27---28-2024

  • CH fundamentals, 6 hours

https://learn.clickhouse.com/class_catalog/category/116050

  • CH in depth, 12 hours

Fundamentals

Table engines

MergeTree (MergeTree family of engines)

  • most univeral and functional engine for high-load tasks
CREATE TABLE my_table
( ... )
ENGINE = MergeTree

Sort order and primary keys

  • sort order defines the primary keys
CREATE TABLE my_table
(
   column1 ...
   column2 ...
   column3 ... 
)
PRIMARY KEY (column1, column2)

same as

ORDER BY column1, column2
  • primary key is per-row, some rows might not have that key

Parts

Each insert creates a part

  • A part is “just a folder” and inside that folder there are immutable files
  • A part typically contains multiple rows

Best to load 1000’s or 10,000s of rows with one insert

Over time, parts can be merged in the background. Up to a configurable size limit.

Pro tip: async insert

If you insert one row at a time, enable configuration for async_insert

Primary index

The primary index is sparse and stored in primary.idx. Built by taking first row every 8192 rows (this is a granule)

  • Granule is the smallest data set that is read when searching rows
  • Size of granule determines the sparseness of the index

Granules

When searching with multiple primary keys ⇒ Stripes of granules Each stripe of granules is processed by an individual thread on a CPU core.

Primary keys ⇒ granules

Database

”It’s just a folder”

Predefined DBs:

  • system database is read-only, contains info about what’s happening with the DBMS

  • default database is where we create tables if we don’t specify a database

  • INFORMATION_SCHEMA

Partitions

  • Unlike other DBs, partitions are mostly for data management, not for performance

  • DON’T partition by high cardinality data

  • A good partition would be, for example, by month

    • Easy to delete data from a few months ago
    • If you have A LOT of data, might need to partition by day

Views and materialized views

By default, in CH views are not materialized.

By default, materialized views are based on INSERT trigger

  • when a row is inserted in the origin table, the materialized view query re-runs
  • the query runs only on the new rows
  • CAVEAT: deletes or updates don’t trigger a materialized view

In general, if you go back and change data, materialized view are not the best way for now but they are being improved

  • EXPERIMENTAL: Refreshable materialized views (at regular intervals)

Best approach to create a materialized view:

  1. Define destination table
  2. Define MV using the TO clause to the destination table
  3. Populate the destination table with historic data

Projections

It’s like a materialized view for a part or partition

You don’t define a separate materialized table, a table is built for you behind the scenes

CH chooses at query time which projection to use, if any

ALTER TABLE uk_price_paid
   ADD PROJECTION town_sort_projection (
      SELECT
         town, price, date, street, locality
      ORDER BY town
   )

Applies only to new parts (due to inserts or merge of other parts)

Cons:

  • New feature, less reliable than materialized views

Pros:

  • Great for re-sorted data

Deduplication / updating and deleting data

Mutations

Reminder: parts are immutable files Updates and deletes will result in creation of new parts This is called a mutation

See active mutations with:

SELECT * FROM system.mutations

CAVEAT: you cannot update a primary key column

Lightweight deletes

Available only in Cloud version, a row is marked as deleted

Engines to handle deduplication

  • ReplacingMergeTree
  • CollapsingMergeTree

Best practices

  • Only SELECT the columns you need

  • Joins work but need to understand how they work

  • Choose primary keys that are used most often in queries

    • Lower cardinality key first
    • Add columns to primary key in order to skip granules

  • Don’t use Nullable on every single column Nullables are represented with an additional bool column

  • For low cardinality data (up to 10000 unique values), use LowCardinality(type) Creates a dictionary behind the scenes, don’t need to know the values in advance

  • Use dictionaries with JOINs

  • Avoid floats when possible, use Decimal

  • Understand how projections work

  • Use data types for UUID and IP addresses, not String

  • Prefer LowCardinality data types to Enums

  • Flatten your data at insertion time

  • Use materialized views

  • Don’t overuse regular views

  • View query details with EXPLAIN

Anti-patterns - things to avoid

https://clickhouse.com/blog/common-getting-started-issues-with-clickhouse

  1. Too many parts a. bad choice of partition key b. Many small inserts c. Excessive materialized views
  2. Going horizontal too early
  3. Mutation pain a. Deduplication
  4. Unnecessary use of complex types
  5. Deduplication at insert time a. replicated_deduplication_window
  6. Poor primary key selection
  7. Overuse of data skipping indices
  8. LIMIT doesn’t always short circuit + point lookups
  9. IP Filtering in Cloud
  10. Readonly tables
  11. Memory Limit Exceeded for Query a. Aggregations b. JOINs c. Rogue queries
  12. Issues relating to Materialized Views LOTS OF CAVEATS, RE-READ
  13. Experimental features in production

Other issues

  1. Not understanding the limitations of materialized views

How to

Inexpensive aggregates that are recomputed frequently

ENGINE = AggregatingMergeTree

CREATE TABLE uk_aggregated_prices (
   district String,
   avg_price AggregateFunction(avg, UInt32),
   max_price SimpleAggregateFunction(max, UInt32),
   quant90 AggregateFunction(quantiles(0.90), UInt32)
)
ENGINE = AggregatingMergeTree
PRIMARY KEY district;

See here for difference between Aggregate and SimpleAggregate

Custom codecs

https://clickhouse.com/docs/en/sql-reference/statements/create/table#specialized-codecs

  • DeltaDelta
  • Gorilla
  • T64 …

Hot and cold storage, TTL

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#table_engine-mergetree-multiple-volumes

Load large datasets from S3

https://altinity.com/blog/tips-for-high-performance-clickhouse-clusters-with-s3-object-storage

Pro tip: S3Queue table engine

https://clickhouse.com/docs/en/engines/table-engines/integrations/s3queue

  • Watches a S3 bucket and ingest every time a file is added

Pro tip: ingest as JSON to detect schema

https://clickhouse.com/blog/getting-data-into-clickhouse-part-2-json

  • for data with slowly changing schema (example: logs from apps), ingest as JSON
  • JSONEachRow

BEWARE: CH is working on new way to store JSON

Pro tip: For joins, smaller table on the right-hand side

Reason: in table joins, right table is turned into a map

Primary keys with multiple columns

primary keys by ascending cardinality

https://altinity.com/blog/2017/11/27/clickhouse-primary-keys

PRIMARY KEY and ORDER BY can be different

  • Set the keys on columns that are smaller
  • Order by larger data
PRIMARY KEY(user_id, timestamp)
ORDER BY(timestamp, user_id, message)

Updating and deleting rows

Mutations : heavyweight

Reminder: parts are immutable files

ALTER TABLE random UPDATE y = 'hello' WHERE x > 10

will result in creation of new parts This is called mutation

See active mutations with:

SELECT * FROM system.mutations

CAVEAT: you cannot update a primary key column

Lightweight deletes

CAVEAT: only available in Cloud version, not Open Source one

DELETE FROM my_table WHERE y != 'hello'

Each deleted row is marked using a special hidden column

FINAL to combine multiple parts and don’t show deleted rows

SELECT * FROM duplicate_demo FINAL;

https://clickhouse.com/docs/en/operations/settings/settings#final or configure it for all queries:

SET final = 1;

CAVEAT: Avoid OPTIMIZE TABLE ... FINAL https://clickhouse.com/docs/en/optimize/avoidoptimizefinal

Deleting records

https://stackoverflow.com/questions/52355143/is-it-possible-to-delete-old-records-from-clickhouse-table

Experience reports / use cases

Small inexpensive setup for log analysis

https://www.hybridlogic.co.uk/2023/02/clog/

Time series

https://clickhouse.com/docs/en/faq/use-cases/time-series

https://www.percona.com/sites/default/files/ple19-slides/day1-pm/clickhouse-for-timeseries.pdf

Analytics at scale

https://clickhouse.com/blog/denic-improves-query-times-by-10x-with-clickhouse

  • Moving on from RDBMS
  • Clustered arch
  • Data privacy requirements
  • Query optimization
  • Clickhouse support

Customers’ use cases

Log ingestion

  • Disney+: 2 billion rows / s

Cloudflare

  • …
  • 8M / s

Didi

  • Logs
  • Moved from Elasticsearch to ClickHouse

DeepL

  • Metrics, logging

With Pandas for data science

https://altinity.com/blog/2019/2/25/clickhouse-and-python-jupyter-notebooks

Performance

Vs Redshift

https://altinity.com/blog/2017/6/20/clickhouse-vs-redshift

https://news.ycombinator.com/item?id=19019943

Vs Timescale

https://www.pradeepchhetri.xyz/clickhousevstimescaledb/#conclusion

Vs BigQuery

  • with set up scripts for Postgres, Clickhouse, BigQuery

https://tech.marksblogg.com/faster-geospatial-enrichment.html

Vs Druid and Pinot

https://link.medium.com/yAP7PKsFHsb

Vs other columnar store

https://www.percona.com/blog/2017/03/17/column-store-database-benchmarks-mariadb-columnstore-vs-clickhouse-vs-apache-spark/

https://pixeljets.com/blog/clickhouse-as-a-replacement-for-elk-big-query-and-timescaledb/

Ecosystem

Cloud platform

  • ClickHouse Cloud
  • ClickPipes
    • To import and process

Integrations

https://clickhouse.com/docs/en/integrations

Schema migration

https://github.com/golang-migrate/migrate

https://kb.altinity.com/altinity-kb-setup-and-maintenance/schema-migration-tools/

BI and data visualization

Grafana

https://clickhouse.com/blog/visualizing-data-with-grafana

Superset

https://clickhouse.com/blog/visualizing-data-with-superset

Metabase

https://clickhouse.com/blog/visualizing-data-with-metabase

Courses

ClickHouse class

This course you’re taking the next two days is here, which is where all the course content and assets will be: slides, labs, and eventually the chat/Q&A logs recording once the course is complete. Each day’s recording is generally uploaded here in about 1 day: https://learn.clickhouse.com/visitor_catalog_class/show/1328622

SQL

https://clickhouse.com/docs/en/sql-reference/ansi

Data types from ANSI to ClickHouse:

SELECT * FROM system.data_type_families;

Functions

https://clickhouse.com/docs/en/sql-reference/functions

SELECT * FROM system.functions;
ORDER BY name

Specific to ClickHouse:

Regular

lower(town)

position(...)
multiFuzzyMatchAny(...)
multiSearchAllPositionsCaseInsensitive(...)

Aggregate

count
min/max
sum
avg
median
quantile/quantiles/quantilesExact
any
uniq
uniqExact/uniqTheta/uniqHLL12/uniqCombined

varPop/stddevPop/covarPop
simpleLinearRegression
stochasticLinearRegression/stochasticLogisticRegression
corr
topK/topKWeight
studentTTest/welchTTest/meanZTest/mannWhitneyUTest

Table

Functions for creating tables

SELECT ...
FROM url(...)

Nice to know

If variant of aggregate functions

https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators

All aggregate functions have an If variant:

SELECT topKIf(10)(street, street != '') FROM uk_price_paid;

vs

SELECT topK(10)(street)

More readable numbers

  • formatReadableQuantity() as in
select formatReadableQuantity(count()) from table;

bar() to visualize values as bars

Change output format

SELECT ...
FORMAT Vertical

other common formats: CSV CVSNames JSONEachRow Parquet

argMin/argMax

To return the value for another column associated with a min or max:

SELECT town,max(price),argMax(street,price) FROM uk_price_paid;

Split into words

SELECT arrayJoin(splitByChar(' ', street)) FROM uk_price_paid;

Views

CREATE VIEW name AS ...

CREATE MATERIALIZED VIEW {name}
ENGINE = MergeTree
ORDER BY {columns}
POPULATE AS
   {query}

Open questions

  • Configuration change requires restart ?
  • What is the difference between the primary key defined in as an argument of the storage engine, ie MergeTree(EventDate, (CounterID, EventDate), 8192) and the Order By clause?

Performance optimizations

https://www.highlight.io/blog/lw5-clickhouse-performance-optimization

  • Reducing Merges - Batch Inserts
  • Keeping Data in Wide Parts
  • Optimizing Order By Granularity
  • Checking Merge Levels
  • Avoiding Use of Projections
  • TTL Optimization / Clearing Old Parts

Graph View

Backlinks