Generating test data in ClickHouse

You rarely need a dump of production to test a schema, a query or a new ClickHouse feature. The server can generate millions of plausible rows by itself, in seconds. Three tools do almost everything: numbers() as a row source, the rand* functions for values, and generateRandom() for “random data of this schema”.

The row source: numbers

numbers(N) is a table function that yields N rows with one column, number, counting from 0. Every generated table starts there. numbers_mt(N) is the multi-threaded variant for very large N.

SELECT number FROM numbers(3);   -- 0, 1, 2

Random values

Function	Gives
rand() / rand32()	uniform UInt32
rand64()	uniform UInt64
randNormal(mean, sd)	normally distributed Float64
randExponential(rate)	exponential, for inter-arrival times
randomPrintableASCII(n)	random printable string of length n
randomString(n) / randomFixedString(n)	random bytes
fuzzBits(s, p)	flips each bit with probability p; handy for corrupt-input tests
sipHash64(x), cityHash64(x)	deterministic pseudo-random from a value

Use rand() % k and arrayElement to pick from a list. For repeatable data, derive values from number with a hash instead of calling rand.

The gotcha: identical rand() calls are one call

ClickHouse treats rand() called twice with the same arguments as a common sub-expression and computes it once. Two columns defined as rand() are therefore identical:

SELECT rand() AS a, rand() AS b, rand(1) AS c, rand(2) AS d FROM numbers(2);

4098586221  4098586221  1497128603  583753963
70203861    70203861    2757369292  3670883049

Columns a and b match; c and d differ. The extra argument to rand is ignored as a value, it only makes the calls distinct. Always give every independent random column its own argument, or event_type and country will move in lockstep.

A realistic events table

CREATE TABLE events (event_time DateTime, user_id UInt64, event_type LowCardinality(String),
                     url String, duration_ms UInt32, country LowCardinality(String))
ENGINE = MergeTree ORDER BY (event_type, event_time);

INSERT INTO events
SELECT
    toDateTime('2026-01-01 00:00:00') + intDiv(number, 10)     AS event_time,
    rand(1) % 10000                                            AS user_id,
    arrayElement(['view', 'click', 'buy'], 1 + rand(2) % 3)    AS event_type,
    concat('/product/', toString(rand(3) % 500))               AS url,
    toUInt32(abs(randNormal(3000, 800)))                       AS duration_ms,
    arrayElement(['US', 'DE', 'TR', 'JP'], 1 + rand(4) % 4)    AS country
FROM numbers(1000000);

Ten events per second, ten thousand users, a bell-shaped duration. Check it:

SELECT count() AS rows, uniq(user_id) AS users, min(event_time), max(event_time) FROM events;

rows 1000000   users 10000   min 2026-01-01 00:00:00   max 2026-01-02 03:46:39

The counts per event type and country come out near uniform; they differ by chance on every run, so do not hard-code expected values in tests.

A time series with structure is as easy: a daily sine wave plus noise.

SELECT toDateTime('2026-01-01') + number * 3600 AS ts,
       round(20 + 5 * sin(number / 24 * 2 * pi()) + randNormal(0, 1), 2) AS temperature
FROM numbers(5);

Deterministic data

If a test needs the same data on every run, derive everything from number:

SELECT sipHash64(number) % 5 AS bucket, count() FROM numbers(1000) GROUP BY bucket ORDER BY bucket;

0 221, 1 200, 2 190, 3 196, 4 193

A hash is evenly distributed but identical each time. This also gives you a stable “random” sample of users: cityHash64(user_id) % 100 < 10 always selects the same 10% of ids.

generateRandom: data for any schema

When you only care about the shape (types, sizes) and not the distribution:

SELECT *
FROM generateRandom(
    'user_id UInt64, plan Enum8(\'free\' = 1, \'pro\' = 2), score Float32, tags Array(String), ts DateTime',
    42,     -- random seed
    5,      -- max string length
    2       -- max array length
)
LIMIT 3;

Every call produces fresh rows with valid values for each type (full-range numbers, so the floats look wild, and the dates are anywhere in the type’s range). It is excellent for fuzzing: pipe it into an INSERT ... SELECT, a format round-trip or a compression-codec comparison. There is also a table engine, GenerateRandom, that makes the same generator a table you can SELECT from.

Always add a LIMIT. Without it the query never ends, because the source is infinite.

Dimension tables from lists

For small lookup data (names, products), define the lists inline and cross join them:

SELECT n1.name AS first, n2.name AS last
FROM (SELECT arrayJoin(['Ada', 'Bo', 'Cy']) AS name) AS n1
CROSS JOIN (SELECT arrayJoin(['Lee', 'Kim']) AS name) AS n2
ORDER BY rand()
LIMIT 4;

Three first names times two last names give six combinations; with twenty of each you get four hundred distinct people. Persist the result with INSERT INTO ... SELECT into a normal table.

Tips

• Generate in batches of a few hundred thousand to a few million rows per INSERT. One giant statement holds a lot of memory.
• To test merges, run several INSERT ... SELECTs in a row: each makes a part.
• Make the data’s cardinality match what you want to test. A column of 10 distinct values and a column of 10 million behave very differently in LowCardinality, GROUP BY and index tests.
• Use the same demo tables across articles and bug reports; a reproducible example beats a screenshot.