generateRandom

Generates random data with a given schema. Allows populating test tables with that data.

Generates random data with a given schema. Allows populating test tables with that data. All data types that can be stored in a table are supported, including JSON, Dynamic, Variant, BFloat16, Time and Time64. The types AggregateFunction, Interval, Nothing and QBit are not supported.

Syntax

generateRandom(['name TypeName[, name TypeName]...', [, 'random_seed'[, 'max_string_length'[, 'max_array_length']]]][, SETTINGS setting = value[, ...]])

Arguments

Argument	Description
name	Name of corresponding column.
TypeName	Type of corresponding column.
random_seed	Specify random seed manually to produce stable results. If NULL — seed is randomly generated.
max_string_length	Maximum string length for all generated strings. Defaults to 10.
max_array_length	Maximum elements for all generated arrays or maps. Defaults to 10.

Settings

SETTINGS is the last argument of the call and controls how Nullable, Variant, Dynamic and JSON values are generated.

Setting	Type	Default	Description
null_ratio	Float	0.0625	Probability that a Nullable, Variant or Dynamic value is NULL, and the base probability that a JSON key is absent from a row - a minority of sparse keys are absent several times more often. Must be in [0, 1].
max_json_depth	UInt64	3	Maximum nesting depth of generated JSON objects: 1 means flat objects, objects inside arrays count as a level. Must be in [1, 32].
max_json_keys_per_object	UInt64	8	Maximum number of generated keys on one level of a JSON object; the root object gets at least half of it. 0 means that only typed paths are generated. At most 1000.

SELECT * FROM generateRandom('x JSON', 3, 4, 2, SETTINGS max_json_depth = 1, max_json_keys_per_object = 3) LIMIT 3 FORMAT JSONEachRow;

{"x":{"cursor":"","id":[],"parent":-3723905664592977020}}
{"x":{"cursor":"","id":[7037090064212902336]}}
{"x":{"id":[287913403506346525],"parent":-4417942528676528098}}

SETTINGS can also be the only argument, which keeps the structure of the insertion table:

INSERT INTO test_table SELECT * FROM generateRandom(SETTINGS null_ratio = 0.5) LIMIT 10;

Returned value

A table object with requested schema.

Usage Example

SELECT * FROM generateRandom('a Array(Int8), d Decimal32(4), c Tuple(DateTime64(3), UUID)', 1, 10, 2) LIMIT 3;

┌─a────────┬────────────d─┬─c──────────────────────────────────────────────────────────────────┐
│ [77]     │ -124167.6723 │ ('2061-04-17 21:59:44.573','3f72f405-ec3e-13c8-44ca-66ef335f7835') │
│ [32,110] │ -141397.7312 │ ('1979-02-09 03:43:48.526','982486d1-5a5d-a308-e525-7bd8b80ffa73') │
│ [68]     │  -67417.0770 │ ('2080-03-12 14:17:31.269','110425e5-413f-10a6-05ba-fa6b3e929f15') │
└──────────┴──────────────┴────────────────────────────────────────────────────────────────────┘

CREATE TABLE random (a Array(Int8), d Decimal32(4), c Tuple(DateTime64(3), UUID)) ENGINE=Memory;
INSERT INTO random SELECT * FROM generateRandom() LIMIT 2;
SELECT * FROM random;

┌─a────────────────────────────┬────────────d─┬─c──────────────────────────────────────────────────────────────────┐
│ []                           │   68091.8197 │ ('2037-10-02 12:44:23.368','039ecab7-81c2-45ee-208c-844e5c6c5652') │
│ [8,-83,0,-22,65,9,-30,28,64] │ -186233.4909 │ ('2062-01-11 00:06:04.124','69563ea1-5ad1-f870-16d8-67061da0df25') │
└──────────────────────────────┴──────────────┴────────────────────────────────────────────────────────────────────┘

In combination with generateRandomStructure:

SELECT * FROM generateRandom(generateRandomStructure(4, 101), 101) LIMIT 3;

┌──────────────────c1─┬──────────────────c2─┬─c3─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─c4──────────────────────────────────────┐
│ 1996-04-15 06:40:05 │ 33954608387.2844801 │ ['232.78.216.176','9.244.59.211','211.21.80.152','44.49.94.109','165.77.195.182','68.167.134.239','212.13.24.185','1.197.255.35','192.55.131.232'] │ 45d9:2b52:ab6:1c59:185b:515:c5b6:b781   │
│ 2063-01-13 01:22:27 │ 36155064970.9514454 │ ['176.140.188.101']                                                                                                                                │ c65a:2626:41df:8dee:ec99:f68d:c6dd:6b30 │
│ 2090-02-28 14:50:56 │  3864327452.3901373 │ ['155.114.30.32']                                                                                                                                  │ 57e9:5229:93ab:fbf3:aae7:e0e4:d1eb:86b  │
└─────────────────────┴─────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────────────────────────┘

With missing structure argument (in this case the structure is random):

SELECT * FROM generateRandom() LIMIT 3;

┌───c1─┬─────────c2─┬─────────────────────c3─┬──────────────────────c4─┬─c5───────┐
│ -128 │  317300854 │ 2030-08-16 08:22:20.65 │ 1994-08-16 12:08:56.745 │ R0qgiC46 │
│   40 │ -744906827 │ 2059-04-16 06:31:36.98 │ 1975-07-16 16:28:43.893 │ PuH4M*MZ │
│  -55 │  698652232 │ 2052-08-04 20:13:39.68 │ 1998-09-20 03:48:29.279 │          │
└──────┴────────────┴────────────────────────┴─────────────────────────┴──────────┘

With random seed both for random structure and random data:

SELECT * FROM generateRandom(11) LIMIT 3;

┌───────────────────────────────────────c1─┬─────────────────────────────────────────────────────────────────────────────c2─┬─────────────────────────────────────────────────────────────────────────────c3─┬─────────c4─┬─────────────────────────────────────────────────────────────────────────────c5─┬──────────────────────c6─┬─c7──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─c8──────────────────────────────────────┬─────────c9─┐
│  -77422512305044606600216318673365695785 │   636812099959807642229.503817849012019401335326013846687285151335352272727523 │ -34944452809785978175157829109276115789694605299387223845886143311647505037529 │  544473976 │ 111220388331710079615337037674887514156741572807049614590010583571763691328563 │       22016.22623506465 │ {'2052-01-31 20:25:33':4306400876908509081044405485378623663,'1993-04-16 15:58:49':164367354809499452887861212674772770279,'2101-08-19 03:07:18':-60676948945963385477105077735447194811,'2039-12-22 22:31:39':-59227773536703059515222628111999932330} │ a7b2:8f58:4d07:6707:4189:80cf:92f5:902d │ 1950-07-14 │
│ -159940486888657488786004075627859832441 │  629206527868163085099.8195700356331771569105231840157308480121506729741348442 │ -53203761250367440823323469081755775164053964440214841464405368882783634063735 │ 2187136525 │  94881662451116595672491944222189810087991610568040618106057495823910493624275 │ 1.3095786748458954e-104 │ {}                                                                                                                                                                                                                                                      │ a051:e3da:2e0a:c69:7835:aed6:e8b:3817   │ 1943-03-25 │
│   -5239084224358020595591895205940528518 │ -529937657954363597180.1709207212648004850138812370209091520162977548101577846 │  47490343304582536176125359129223180987770215457970451211489086575421345731671 │ 1637451978 │ 101899445785010192893461828129714741298630410942962837910400961787305271699002 │  2.4344456058391296e223 │ {'2013-12-22 17:42:43':80271108282641375975566414544777036006,'2041-03-08 10:28:17':169706054082247533128707458270535852845,'1986-08-31 23:07:38':-54371542820364299444195390357730624136,'2094-04-23 21:26:50':7944954483303909347454597499139023465}  │ 1293:a726:e899:9bfc:8c6f:2aa1:22c9:b635 │ 1924-11-20 │
└──────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────────┴────────────┴────────────────────────────────────────────────────────────────────────────────┴─────────────────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────────────────────────┴────────────┘

generateRandom(generateRandomStructure(), [random seed], max_string_length, max_array_length) with a large enough max_array_length can generate a really huge output due to possible big nesting depth (up to 16) of complex types (Array, Tuple, Map, Nested).

Generating JSON

A generated JSON column reads like a stream of documents of one schema: the set of keys and the type of every key are derived from the random seed and stay the same for the whole column, while the values, the absent keys and the array lengths change from row to row. The depth of the objects is bounded by max_json_depth, the number of keys on one level by max_json_keys_per_object, and a key is absent from a row with probability null_ratio. The leaves take the types the JSON parser infers for real documents: Int64, UInt64, Float64, Bool, String, Date, DateTime, arrays of those, arrays of objects and mixed arrays; a few keys carry a different type in a small fraction of the rows, as they do in data collected from an application.

The type declaration shapes the generated schema: typed paths are always present with their declared type, paths excluded by SKIP and SKIP REGEXP are never generated, and both max_dynamic_paths and max_dynamic_types are respected, so the paths beyond max_dynamic_paths end up in the shared data of the column.

SELECT * FROM generateRandom('x JSON', 1) LIMIT 3 FORMAT JSONEachRow;

{"x":{"product_id":"2050-12-17 01:46:35","stage":"g|(&Ql","started":{"group":-80611897324989285}}}
{"x":{"product_id":"2013-10-17 22:35:26","stage":"^ipx|,=a5N","started":{"group":-1326235429680389454}}}
{"x":{"product_id":"1974-11-17 22:22:46","stage":"(U]p'l`","started":{"group":-344141642787805595}}}

The structure can also be left to the insertion table, which is convenient for filling a table with JSON, Dynamic or Variant columns:

CREATE TABLE t (x JSON) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO t SELECT * FROM generateRandom() LIMIT 10;

Related content

• Blog: Generating random data in ClickHouse