Relational Data - Data Types - Dynamic
This type allows to store values of any type inside it without knowing all of them in advance.
To declare a column of Dynamic type, use the following syntax:
<column_name> Dynamic(max_types=N)Where N is an optional parameter between 0 and 254 indicating how many different data types can be stored as separate subcolumns inside a column with type Dynamic across single block of data that is stored separately (for example across single data part for MergeTree table). If this limit is exceeded, all values with new types will be stored together in a special shared data structure in binary form. Default value of max_types is 32.
The Dynamic data type is an experimental feature. To use it, set allow_experimental_dynamic_type = 1.
Creating Dynamic
Using Dynamic type in table column definition:
CREATE TABLE test (d Dynamic) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT d, dynamicType(d) FROM test;┌─d─────────────┬─dynamicType(d)─┐
│ ᴺᵁᴸᴸ │ None │
│ 42 │ Int64 │
│ Hello, World! │ String │
│ [1,2,3] │ Array(Int64) │
└───────────────┴────────────────┘Using CAST from ordinary column:
SELECT 'Hello, World!'::Dynamic as d, dynamicType(d);┌─d─────────────┬─dynamicType(d)─┐
│ Hello, World! │ String │
└───────────────┴────────────────┘Using CAST from Variant column:
SET allow_experimental_variant_type = 1, use_variant_as_common_type = 1;
SELECT multiIf((number % 3) = 0, number, (number % 3) = 1, range(number + 1), NULL)::Dynamic AS d, dynamicType(d) FROM numbers(3)┌─d─────┬─dynamicType(d)─┐
│ 0 │ UInt64 │
│ [0,1] │ Array(UInt64) │
│ ᴺᵁᴸᴸ │ None │
└───────┴────────────────┘
Reading Dynamic nested types as subcolumns
Dynamic type supports reading a single nested type from a Dynamic column using the type name as a subcolumn. So, if you have column d Dynamic you can read a subcolumn of any valid type T using syntax d.T, this subcolumn will have type Nullable(T) if T can be inside Nullable and T otherwise. This subcolumn will be the same size as original Dynamic column and will contain NULL values (or empty values if T cannot be inside Nullable) in all rows in which original Dynamic column doesn’t have type T.
Dynamic subcolumns can be also read using function dynamicElement(dynamic_column, type_name).
Examples:
CREATE TABLE test (d Dynamic) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT d, dynamicType(d), d.String, d.Int64, d.`Array(Int64)`, d.Date, d.`Array(String)` FROM test;┌─d─────────────┬─dynamicType(d)─┬─d.String──────┬─d.Int64─┬─d.Array(Int64)─┬─d.Date─┬─d.Array(String)─┐
│ ᴺᵁᴸᴸ │ None │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ [] │ ᴺᵁᴸᴸ │ [] │
│ 42 │ Int64 │ ᴺᵁᴸᴸ │ 42 │ [] │ ᴺᵁᴸᴸ │ [] │
│ Hello, World! │ String │ Hello, World! │ ᴺᵁᴸᴸ │ [] │ ᴺᵁᴸᴸ │ [] │
│ [1,2,3] │ Array(Int64) │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ [1,2,3] │ ᴺᵁᴸᴸ │ [] │
└───────────────┴────────────────┴───────────────┴─────────┴────────────────┴────────┴─────────────────┘SELECT toTypeName(d.String), toTypeName(d.Int64), toTypeName(d.`Array(Int64)`), toTypeName(d.Date), toTypeName(d.`Array(String)`) FROM test LIMIT 1;┌─toTypeName(d.String)─┬─toTypeName(d.Int64)─┬─toTypeName(d.Array(Int64))─┬─toTypeName(d.Date)─┬─toTypeName(d.Array(String))─┐
│ Nullable(String) │ Nullable(Int64) │ Array(Int64) │ Nullable(Date) │ Array(String) │
└──────────────────────┴─────────────────────┴────────────────────────────┴────────────────────┴─────────────────────────────┘SELECT d, dynamicType(d), dynamicElement(d, 'String'), dynamicElement(d, 'Int64'), dynamicElement(d, 'Array(Int64)'), dynamicElement(d, 'Date'), dynamicElement(d, 'Array(String)') FROM test;```┌─d─────────────┬─dynamicType(d)─┬─dynamicElement(d, 'String')─┬─dynamicElement(d, 'Int64')─┬─dynamicElement(d, 'Array(Int64)')─┬─dynamicElement(d, 'Date')─┬─dynamicElement(d, 'Array(String)')─┐
│ ᴺᵁᴸᴸ │ None │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ [] │ ᴺᵁᴸᴸ │ [] │
│ 42 │ Int64 │ ᴺᵁᴸᴸ │ 42 │ [] │ ᴺᵁᴸᴸ │ [] │
│ Hello, World! │ String │ Hello, World! │ ᴺᵁᴸᴸ │ [] │ ᴺᵁᴸᴸ │ [] │
│ [1,2,3] │ Array(Int64) │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ [1,2,3] │ ᴺᵁᴸᴸ │ [] │
└───────────────┴────────────────┴─────────────────────────────┴────────────────────────────┴───────────────────────────────────┴───────────────────────────┴────────────────────────────────────┘To know what variant is stored in each row function dynamicType(dynamic_column) can be used. It returns String with value type name for each row (or 'None' if row is NULL).
Example:
CREATE TABLE test (d Dynamic) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT dynamicType(d) from test;┌─dynamicType(d)─┐
│ None │
│ Int64 │
│ String │
│ Array(Int64) │
└────────────────┘
Conversion between Dynamic column and other columns
There are 4 possible conversions that can be performed with Dynamic column.
Converting an ordinary column to a Dynamic column
SELECT 'Hello, World!'::Dynamic as d, dynamicType(d);┌─d─────────────┬─dynamicType(d)─┐
│ Hello, World! │ String │
└───────────────┴────────────────┘
Converting a String column to a Dynamic column through parsing
To parse Dynamic type values from a String column you can enable setting cast_string_to_dynamic_use_inference:
SET cast_string_to_dynamic_use_inference = 1;
SELECT CAST(materialize(map('key1', '42', 'key2', 'true', 'key3', '2020-01-01')), 'Map(String, Dynamic)') as map_of_dynamic, mapApply((k, v) -> (k, dynamicType(v)), map_of_dynamic) as map_of_dynamic_types;┌─map_of_dynamic──────────────────────────────┬─map_of_dynamic_types─────────────────────────┐
│ {'key1':42,'key2':true,'key3':'2020-01-01'} │ {'key1':'Int64','key2':'Bool','key3':'Date'} │
└─────────────────────────────────────────────┴──────────────────────────────────────────────┘
Converting a Dynamic column to an ordinary column
It is possible to convert a Dynamic column to an ordinary column. In this case all nested types will be converted to a destination type:
CREATE TABLE test (d Dynamic) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('42.42'), (true), ('e10');
SELECT d::Nullable(Float64) FROM test;┌─CAST(d, 'Nullable(Float64)')─┐
│ ᴺᵁᴸᴸ │
│ 42 │
│ 42.42 │
│ 1 │
│ 0 │
└──────────────────────────────┘
Converting a Variant column to Dynamic column
CREATE TABLE test (v Variant(UInt64, String, Array(UInt64))) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('String'), ([1, 2, 3]);
SELECT v::Dynamic as d, dynamicType(d) from test;┌─d───────┬─dynamicType(d)─┐
│ ᴺᵁᴸᴸ │ None │
│ 42 │ UInt64 │
│ String │ String │
│ [1,2,3] │ Array(UInt64) │
└─────────┴────────────────┘
Converting a Dynamic(max_types=N) column to another Dynamic(max_types=K)
If K >= N than during conversion the data doesn’t change:
CREATE TABLE test (d Dynamic(max_types=3)) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), (43), ('42.42'), (true);
SELECT d::Dynamic(max_types=5) as d2, dynamicType(d2) FROM test;┌─d─────┬─dynamicType(d)─┐
│ ᴺᵁᴸᴸ │ None │
│ 42 │ Int64 │
│ 43 │ Int64 │
│ 42.42 │ String │
│ true │ Bool │
└───────┴────────────────┘If K < N, then the values with the rarest types will be inserted into a single special subcolumn, but still will be accessible:
CREATE TABLE test (d Dynamic(max_types=4)) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), (43), ('42.42'), (true), ([1, 2, 3]);
SELECT d, dynamicType(d), d::Dynamic(max_types=2) as d2, dynamicType(d2), isDynamicElementInSharedData(d2) FROM test;┌─d───────┬─dynamicType(d)─┬─d2──────┬─dynamicType(d2)─┬─isDynamicElementInSharedData(d2)─┐
│ ᴺᵁᴸᴸ │ None │ ᴺᵁᴸᴸ │ None │ false │
│ 42 │ Int64 │ 42 │ Int64 │ false │
│ 43 │ Int64 │ 43 │ Int64 │ false │
│ 42.42 │ String │ 42.42 │ String │ false │
│ true │ Bool │ true │ Bool │ true │
│ [1,2,3] │ Array(Int64) │ [1,2,3] │ Array(Int64) │ true │
└─────────┴────────────────┴─────────┴─────────────────┴──────────────────────────────────┘Functions isDynamicElementInSharedData returns true for rows that are stored in a special shared data structure inside Dynamic and as we can see, resulting column contains only 2 types that are not stored in shared data structure.
If K=0, all types will be inserted into single special subcolumn:
CREATE TABLE test (d Dynamic(max_types=4)) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), (43), ('42.42'), (true), ([1, 2, 3]);
SELECT d, dynamicType(d), d::Dynamic(max_types=0) as d2, dynamicType(d2), isDynamicElementInSharedData(d2) FROM test;┌─d───────┬─dynamicType(d)─┬─d2──────┬─dynamicType(d2)─┬─isDynamicElementInSharedData(d2)─┐
│ ᴺᵁᴸᴸ │ None │ ᴺᵁᴸᴸ │ None │ false │
│ 42 │ Int64 │ 42 │ Int64 │ true │
│ 43 │ Int64 │ 43 │ Int64 │ true │
│ 42.42 │ String │ 42.42 │ String │ true │
│ true │ Bool │ true │ Bool │ true │
│ [1,2,3] │ Array(Int64) │ [1,2,3] │ Array(Int64) │ true │
└─────────┴────────────────┴─────────┴─────────────────┴──────────────────────────────────┘
Reading Dynamic type from the data
All text formats (TSV, CSV, CustomSeparated, Values, JSONEachRow, etc) supports reading Dynamic type. During data parsing ClickHouse tries to infer the type of each value and use it during insertion to Dynamic column.
Example:
SELECT
d,
dynamicType(d),
dynamicElement(d, 'String') AS str,
dynamicElement(d, 'Int64') AS num,
dynamicElement(d, 'Float64') AS float,
dynamicElement(d, 'Date') AS date,
dynamicElement(d, 'Array(Int64)') AS arr
FROM format(JSONEachRow, 'd Dynamic', $$
{"d" : "Hello, World!"},
{"d" : 42},
{"d" : 42.42},
{"d" : "2020-01-01"},
{"d" : [1, 2, 3]}
$$)┌─d─────────────┬─dynamicType(d)─┬─str───────────┬──num─┬─float─┬───────date─┬─arr─────┐
│ Hello, World! │ String │ Hello, World! │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ [] │
│ 42 │ Int64 │ ᴺᵁᴸᴸ │ 42 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ [] │
│ 42.42 │ Float64 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 42.42 │ ᴺᵁᴸᴸ │ [] │
│ 2020-01-01 │ Date │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 2020-01-01 │ [] │
│ [1,2,3] │ Array(Int64) │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ [1,2,3] │
└───────────────┴────────────────┴───────────────┴──────┴───────┴────────────┴─────────┘
Comparing values of Dynamic type
Values of Dynamic types are compared similar to values of Variant type: The result of operator < for values d1 with underlying type T1 and d2 with underlying type T2 of a type Dynamic is defined as follows: - If T1 = T2 = T, the result will be d1.T < d2.T (underlying values will be compared). - If T1 != T2, the result will be T1 < T2 (type names will be compared).
Examples:
CREATE TABLE test (d1 Dynamic, d2 Dynamic) ENGINE=Memory;
INSERT INTO test VALUES (42, 42), (42, 43), (42, 'abc'), (42, [1, 2, 3]), (42, []), (42, NULL);SELECT d2, dynamicType(d2) as d2_type from test order by d2;┌─d2──────┬─d2_type──────┐
│ [] │ Array(Int64) │
│ [1,2,3] │ Array(Int64) │
│ 42 │ Int64 │
│ 43 │ Int64 │
│ abc │ String │
│ ᴺᵁᴸᴸ │ None │
└─────────┴──────────────┘SELECT d1, dynamicType(d1) as d1_type, d2, dynamicType(d2) as d2_type, d1 = d2, d1 < d2, d1 > d2 from test;┌─d1─┬─d1_type─┬─d2──────┬─d2_type──────┬─equals(d1, d2)─┬─less(d1, d2)─┬─greater(d1, d2)─┐
│ 42 │ Int64 │ 42 │ Int64 │ 1 │ 0 │ 0 │
│ 42 │ Int64 │ 43 │ Int64 │ 0 │ 1 │ 0 │
│ 42 │ Int64 │ abc │ String │ 0 │ 1 │ 0 │
│ 42 │ Int64 │ [1,2,3] │ Array(Int64) │ 0 │ 0 │ 1 │
│ 42 │ Int64 │ [] │ Array(Int64) │ 0 │ 0 │ 1 │
│ 42 │ Int64 │ ᴺᵁᴸᴸ │ None │ 0 │ 1 │ 0 │
└────┴─────────┴─────────┴──────────────┴────────────────┴──────────────┴─────────────────┘If you need to find the row with specific Dynamic value, you can do one of the following:
- Cast value to the
Dynamictype:
SELECT * FROM test WHERE d2 == [1,2,3]::Array(UInt32)::Dynamic;┌─d1─┬─d2──────┐
│ 42 │ [1,2,3] │
└────┴─────────┘- Compare
Dynamicsubcolumn with required type:
SELECT * FROM test WHERE d2.`Array(Int65)` == [1,2,3] -- or using variantElement(d2, 'Array(UInt32)')┌─d1─┬─d2──────┐
│ 42 │ [1,2,3] │
└────┴─────────┘Sometimes it can be useful to make additional check on dynamic type as subcolumns with complex types like Array/Map/Tuple cannot be inside Nullable and will have default values instead of NULL on rows with different types:
SELECT d2, d2.`Array(Int64)`, dynamicType(d2) FROM test WHERE d2.`Array(Int64)` == [];┌─d2───┬─d2.Array(UInt32)─┬─dynamicType(d2)─┐
│ 42 │ [] │ Int64 │
│ 43 │ [] │ Int64 │
│ abc │ [] │ String │
│ [] │ [] │ Array(Int32) │
│ ᴺᵁᴸᴸ │ [] │ None │
└──────┴──────────────────┴─────────────────┘SELECT d2, d2.`Array(Int64)`, dynamicType(d2) FROM test WHERE dynamicType(d2) == 'Array(Int64)' AND d2.`Array(Int64)` == [];┌─d2─┬─d2.Array(UInt32)─┬─dynamicType(d2)─┐
│ [] │ [] │ Array(Int64) │
└────┴──────────────────┴─────────────────┘Note: values of dynamic types with different numeric types are considered as different values and not compared between each other, their type names are compared instead.
Example:
CREATE TABLE test (d Dynamic) ENGINE=Memory;
INSERT INTO test VALUES (1::UInt32), (1::Int64), (100::UInt32), (100::Int64);
SELECT d, dynamicType(d) FROM test ORDER by d;┌─v───┬─dynamicType(v)─┐
│ 1 │ Int64 │
│ 100 │ Int64 │
│ 1 │ UInt32 │
│ 100 │ UInt32 │
└─────┴────────────────┘
Reaching the limit in number of different data types stored inside Dynamic
Dynamic data type can store only limited number of different data types as separate subcolumns. By default, this limit is 32, but you can change it in type declaration using syntax Dynamic(max_types=N) where N is between 0 and 254 (due to implementation details, it’s impossible to have more than 254 different data types that can be stored as separate subcolumns inside Dynamic). When the limit is reached, all new data types inserted to Dynamic column will be inserted into a single shared data structure that stores values with different data types in binary form.
Let’s see what happens when the limit is reached in different scenarios.
Reaching the limit during data parsing
During parsing of Dynamic values from the data, when the limit is reached for current block of data, all new values will be inserted into shared data structure:
SELECT d, dynamicType(d), isDynamicElementInSharedData(d) FROM format(JSONEachRow, 'd Dynamic(max_types=3)', '
{"d" : 42}
{"d" : [1, 2, 3]}
{"d" : "Hello, World!"}
{"d" : "2020-01-01"}
{"d" : ["str1", "str2", "str3"]}
{"d" : {"a" : 1, "b" : [1, 2, 3]}}
')┌─d──────────────────────┬─dynamicType(d)─────────────────┬─isDynamicElementInSharedData(d)─┐
│ 42 │ Int64 │ false │
│ [1,2,3] │ Array(Int64) │ false │
│ Hello, World! │ String │ false │
│ 2020-01-01 │ Date │ true │
│ ['str1','str2','str3'] │ Array(String) │ true │
│ (1,[1,2,3]) │ Tuple(a Int64, b Array(Int64)) │ true │
└────────────────────────┴────────────────────────────────┴─────────────────────────────────┘As we can see, after inserting 3 different data types Int64, Array(Int64) and String all new types were inserted into special shared data structure.
During merges of data parts in MergeTree table engines
During merge of several data parts in MergeTree table the Dynamic column in the resulting data part can reach the limit of different data types that can be stored in separate subcolumns inside and won’t be able to store all types as subcolumns from source parts. In this case ClickHouse chooses what types will remain as separate subcolumns after merge and what types will be inserted into shared data structure. In most cases ClickHouse tries to keep the most frequent types and store the rarest types in shared data structure, but it depends on the implementation.
Let’s see an example of such merge. First, let’s create a table with Dynamic column, set the limit of different data types to 3 and insert values with 5 different types:
CREATE TABLE test (id UInt64, d Dynamic(max_types=3)) engine=MergeTree ORDER BY id;
SYSTEM STOP MERGES test;
INSERT INTO test SELECT number, number FROM numbers(5);
INSERT INTO test SELECT number, range(number) FROM numbers(4);
INSERT INTO test SELECT number, toDate(number) FROM numbers(3);
INSERT INTO test SELECT number, map(number, number) FROM numbers(2);
INSERT INTO test SELECT number, 'str_' || toString(number) FROM numbers(1);Each insert will create a separate data pert with Dynamic column containing single type:
SELECT count(), dynamicType(d), isDynamicElementInSharedData(d), _part FROM test GROUP BY _part, dynamicType(d), isDynamicElementInSharedData(d) ORDER BY _part, count();┌─count()─┬─dynamicType(d)──────┬─isDynamicElementInSharedData(d)─┬─_part─────┐
│ 5 │ UInt64 │ false │ all_1_1_0 │
│ 4 │ Array(UInt64) │ false │ all_2_2_0 │
│ 3 │ Date │ false │ all_3_3_0 │
│ 2 │ Map(UInt64, UInt64) │ false │ all_4_4_0 │
│ 1 │ String │ false │ all_5_5_0 │
└─────────┴─────────────────────┴─────────────────────────────────┴───────────┘Now, let’s merge all parts into one and see what will happen:
SYSTEM START MERGES test;
OPTIMIZE TABLE test FINAL;
SELECT count(), dynamicType(d), isDynamicElementInSharedData(d), _part FROM test GROUP BY _part, dynamicType(d), isDynamicElementInSharedData(d) ORDER BY _part, count() desc;┌─count()─┬─dynamicType(d)──────┬─isDynamicElementInSharedData(d)─┬─_part─────┐
│ 5 │ UInt64 │ false │ all_1_5_2 │
│ 4 │ Array(UInt64) │ false │ all_1_5_2 │
│ 3 │ Date │ false │ all_1_5_2 │
│ 2 │ Map(UInt64, UInt64) │ true │ all_1_5_2 │
│ 1 │ String │ true │ all_1_5_2 │
└─────────┴─────────────────────┴─────────────────────────────────┴───────────┘As we can see, ClickHouse kept the most frequent types UInt64 and Array(UInt64) as subcolumns and inserted all other types into shared data.
JSONExtract functions with Dynamic
All JSONExtract* functions support Dynamic type:
SELECT JSONExtract('{"a" : [1, 2, 3]}', 'a', 'Dynamic') AS dynamic, dynamicType(dynamic) AS dynamic_type;┌─dynamic─┬─dynamic_type───────────┐
│ [1,2,3] │ Array(Nullable(Int64)) │
└─────────┴────────────────────────┘SELECT JSONExtract('{"obj" : {"a" : 42, "b" : "Hello", "c" : [1,2,3]}}', 'obj', 'Map(String, Dynamic)') AS map_of_dynamics, mapApply((k, v) -> (k, dynamicType(v)), map_of_dynamics) AS map_of_dynamic_types┌─map_of_dynamics──────────────────┬─map_of_dynamic_types────────────────────────────────────┐
│ {'a':42,'b':'Hello','c':[1,2,3]} │ {'a':'Int64','b':'String','c':'Array(Nullable(Int64))'} │
└──────────────────────────────────┴─────────────────────────────────────────────────────────┘SELECT JSONExtractKeysAndValues('{"a" : 42, "b" : "Hello", "c" : [1,2,3]}', 'Dynamic') AS dynamics, arrayMap(x -> (x.1, dynamicType(x.2)), dynamics) AS dynamic_types```┌─dynamics───────────────────────────────┬─dynamic_types─────────────────────────────────────────────────┐
│ [('a',42),('b','Hello'),('c',[1,2,3])] │ [('a','Int64'),('b','String'),('c','Array(Nullable(Int64))')] │
└────────────────────────────────────────┴───────────────────────────────────────────────────────────────┘
Binary output format
In RowBinary format values of Dynamic type are serialized in the following format:
<binary_encoded_data_type><value_in_binary_format_according_to_the_data_type>