Native random sampling in ClickHouse

Summary

ClickHouse native random sampling enables aggregate queries to execute against a representative fraction of data when querying full datasets is too slow. To maintain sampling accuracy, tables require a sample key derived from high-cardinality columns using hash functions like sipHash64 to ensure an even distribution. The sample key must be included in the primary key ORDER BY expression, preferably at the front to let ClickHouse quickly skip index granules. Queries can sample data deterministically by specifying a fractional ratio or a minimum row threshold, scaling aggregate counts using the virtual column _sample_factor. In benchmark tests on a 30-million-row dataset, sampling 10% of records reduced query latency by approximately 80% while retaining approximate metrics suitable for exploratory analysis.

Takeaways

1. 1

   Sample keys must be high-cardinality expressions with uniform distributions, such as sipHash64 on detailed identifiers; low-cardinality fields like county skew bucket distributions.

2. 2

   The SAMPLE BY expression must be part of the table primary key ORDER BY clause, and placing it first allows ClickHouse to skip unneeded index granules.

3. 3

   The SAMPLE clause supports both fractional sampling and minimum row count thresholds, producing deterministic query results that scale counts with the _sample_factor virtual column.