GenerateRandom

The GenerateRandom table engine produces random data for given table schema.

Usage examples:

• Use in test to populate reproducible large table.
• Generate random input for fuzzing tests.

Usage in ClickHouse Server

ENGINE = GenerateRandom([random_seed [,max_string_length [,max_array_length]]])
[SETTINGS null_ratio = ..., max_json_depth = ..., max_json_keys_per_object = ...]

The max_array_length and max_string_length parameters specify maximum length of all array or map columns and strings correspondingly in generated data.

The SETTINGS clause controls how Nullable, Variant, Dynamic and JSON values are generated: null_ratio (default 0.0625) is the probability that a 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, max_json_depth (default 3) bounds the nesting depth of generated JSON objects, and max_json_keys_per_object (default 8) bounds the number of generated keys on one level of an object. The generateRandom table function accepts the same settings.

Generate table engine supports only SELECT queries.

It supports all DataTypes that can be stored in a table, except AggregateFunction, Interval, Nothing and QBit.

Example

1. Set up the generate_engine_table table:

CREATE TABLE generate_engine_table (name String, value UInt32) ENGINE = GenerateRandom(1, 5, 3)

2. Query the data:

SELECT * FROM generate_engine_table LIMIT 3

┌─name─┬──────value─┐
│ c4xJ │ 1412771199 │
│ r    │ 1791099446 │
│ 7#$  │  124312908 │
└──────┴────────────┘

Details of Implementation

• Not supported:
  • ALTER
  • SELECT ... SAMPLE
  • INSERT
  • Indices
  • Replication

Syntax

ENGINE = GenerateRandom([random_seed[, max_string_length[, max_array_length]]]) [SETTINGS null_ratio = ..., max_json_depth = ..., max_json_keys_per_object = ...]

Related

• FuzzJSON
• FuzzQuery