> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Generates random data with a given schema. Allows populating test tables with that data. # generateRandom 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

```sql theme={null} 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`. | ```sql theme={null} SELECT * FROM generateRandom('x JSON', 3, 4, 2, SETTINGS max_json_depth = 1, max_json_keys_per_object = 3) LIMIT 3 FORMAT JSONEachRow; ``` ```text theme={null} {"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: ```sql theme={null} INSERT INTO test_table SELECT * FROM generateRandom(SETTINGS null_ratio = 0.5) LIMIT 10; ```

Returned value

A table object with requested schema.

Usage Example

```sql theme={null} SELECT * FROM generateRandom('a Array(Int8), d Decimal32(4), c Tuple(DateTime64(3), UUID)', 1, 10, 2) LIMIT 3; ``` ```text theme={null} ┌─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') │ └──────────┴──────────────┴────────────────────────────────────────────────────────────────────┘ ``` ```sql theme={null} 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; ``` ```text theme={null} ┌─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](/reference/functions/regular-functions/other-functions#generateRandomStructure): ```sql theme={null} SELECT * FROM generateRandom(generateRandomStructure(4, 101), 101) LIMIT 3; ``` ```text theme={null} ┌──────────────────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): ```sql theme={null} SELECT * FROM generateRandom() LIMIT 3; ``` ```text theme={null} ┌───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: ```sql theme={null} SELECT * FROM generateRandom(11) LIMIT 3; ``` ```text theme={null} ┌───────────────────────────────────────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. ```sql theme={null} SELECT * FROM generateRandom('x JSON', 1) LIMIT 3 FORMAT JSONEachRow; ``` ```text theme={null} {"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: ```sql theme={null} 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](https://clickhouse.com/blog/generating-random-test-distribution-data-for-clickhouse)