LOAD 'chdb_hook'; /****************************************************************************/ -- Maps. No Postgres type maps to Map, so pg_chdb sees one only when a -- structure option names it. A composite array reads a Map but writes none, -- so the round trip below runs through a text array instead. CREATE TYPE kv AS (k text, v bigint); CREATE TYPE ikv AS (k bigint, v text); CREATE TYPE akv AS (k text, v bigint[]); -- Copy the corpus to /tmp, where the server definitely has read access. \! cp -f test/corpus/maps.jsonl /tmp/chdb-maps.jsonl \set maps_json file:///tmp/chdb-maps.jsonl -- A Map arrives as an array of its key/value pairs. CREATE TABLE maps (id int, m kv[]); COPY maps FROM :'maps_json' (format 'JSONEachRow', structure 'id Int32, m Map(String, Int64)'); SELECT id, array_dims(m), m FROM maps ORDER BY id; id | array_dims | m ----+------------+-------------------------------------------------------------------------------- 1 | [1:2] | {"(a,1)","(b,2)"} 2 | | {} 3 | [1:3] | {"(\"c,d\",9223372036854775807)","(\"e\"\"f\",-9223372036854775808)","(😀,0)"} (3 rows) -- Array(Tuple(K, V)) must decode to the same thing, as must a LowCardinality -- key. Both queries return no rows. CREATE TABLE tuples (LIKE maps); COPY tuples FROM :'maps_json' (format 'JSONEachRow', structure 'id Int32, t Array(Tuple(String, Int64))'); SELECT * FROM maps EXCEPT ALL SELECT * FROM tuples; id | m ----+--- (0 rows) TRUNCATE tuples; COPY tuples FROM :'maps_json' (format 'JSONEachRow', structure 'id Int32, m Map(LowCardinality(String), Int64)'); SELECT * FROM maps EXCEPT ALL SELECT * FROM tuples; id | m ----+--- (0 rows) /****************************************************************************/ -- A Nullable value crosses as a NULL field. ClickHouse rejects a Nullable key -- and a Nullable Map, so a missing pair and a NULL map cannot be expressed. CREATE TABLE null_maps (id int, n kv[]); COPY null_maps FROM :'maps_json' (format 'JSONEachRow', structure 'id Int32, n Map(String, Nullable(Int64))'); SELECT id, n FROM null_maps ORDER BY id; id | n ----+------------------ 1 | {"(a,1)","(b,)"} 2 | {} 3 | {"(\"\",)"} (3 rows) /****************************************************************************/ -- Keys need not be strings. CREATE TABLE int_keys (id int, i ikv[]); COPY int_keys FROM :'maps_json' (format 'JSONEachRow', structure 'id Int32, ik Map(Int64, String)'); SELECT id, i FROM int_keys ORDER BY id; id | i ----+-------------------------------------- 1 | {"(7,seven)","(-8,\"minus eight\")"} 2 | {} 3 | {"(0,\"\")"} (3 rows) -- An array value nests inside the pair. CREATE TABLE array_values (id int, a akv[]); COPY array_values FROM :'maps_json' (format 'JSONEachRow', structure 'id Int32, av Map(String, Array(Int64))'); SELECT id, a FROM array_values ORDER BY id; id | a ----+---------------------------- 1 | {"(x,\"{1,2}\")","(y,{})"} 2 | {} 3 | {"(\"g\\\\h\",{3})"} (3 rows) -- Array(Map) spans two Postgres dimensions, so its maps must be of equal -- length for the array to be rectangular. CREATE TABLE map_arrays (id int, am kv[]); COPY map_arrays FROM :'maps_json' (format 'JSONEachRow', structure 'id Int32, am Array(Map(String, Int64))'); SELECT id, array_dims(am), am FROM map_arrays ORDER BY id; id | array_dims | am ----+------------+--------------------------- 1 | [1:2][1:1] | {{"(a,1)"},{"(b,2)"}} 2 | | {} 3 | [1:1][1:2] | {{"(\"c,d\",3)","(e,4)"}} (3 rows) /****************************************************************************/ -- A text array takes the pairs as items rather than records, so the pair fills -- one more dimension, and it writes back into a Map. CREATE TABLE text_maps (id int, m text[]); COPY text_maps FROM :'maps_json' (format 'JSONEachRow', structure 'id Int32, m Map(String, Int64)'); SELECT id, array_dims(m), m FROM text_maps ORDER BY id; id | array_dims | m ----+------------+-------------------------------------------------------------------- 1 | [1:2][1:2] | {{a,1},{b,2}} 2 | | {} 3 | [1:3][1:2] | {{"c,d",9223372036854775807},{"e\"f",-9223372036854775808},{😀,0}} (3 rows) \set maps_out file:///tmp/chdb-maps.tmp COPY text_maps TO :'maps_out' (format 'JSONEachRow', structure 'id Int32, m Map(String, Int64)'); CREATE TABLE text_maps2 (LIKE text_maps); COPY text_maps2 FROM :'maps_out' (format 'JSONEachRow', structure 'id Int32, m Map(String, Int64)'); -- Round trip changes no row, so the difference is empty SELECT * FROM text_maps EXCEPT ALL SELECT * FROM text_maps2; id | m ----+--- (0 rows) -- A Tuple fills one array with its fields, each item parsed as its own field. CREATE TABLE tuple_text (id int, tt text[]); COPY tuple_text FROM :'maps_json' (format 'JSONEachRow', structure 'id Int32, tt Tuple(String, Int64)'); SELECT id, array_dims(tt), tt FROM tuple_text ORDER BY id; id | array_dims | tt ----+------------+--------- 1 | [1:2] | {x,5} 2 | [1:2] | {"",0} 3 | [1:2] | {😀,-1} (3 rows) COPY tuple_text TO :'maps_out' (format 'JSONEachRow', structure 'id Int32, tt Tuple(String, Int64)'); CREATE TABLE tuple_text2 (LIKE tuple_text); COPY tuple_text2 FROM :'maps_out' (format 'JSONEachRow', structure 'id Int32, tt Tuple(String, Int64)'); -- Round trip changes no row, so the difference is empty SELECT * FROM tuple_text EXCEPT ALL SELECT * FROM tuple_text2; id | tt ----+---- (0 rows) -- A Tuple takes exactly its fields, and cannot be NULL. COPY tuple_text2 TO :'maps_out' (format 'JSONEachRow', structure 'id Int32, tt Tuple(String, Int64, Int64)'); ERROR: chdb: Tuple requires 3 values, got 2 INSERT INTO tuple_text2 VALUES (4, NULL); COPY tuple_text2 TO :'maps_out' (format 'JSONEachRow', structure 'id Int32, tt Tuple(String, Int64)'); ERROR: chdb: cannot encode record into ClickHouse Tuple(String, Int64) (column "tt") /****************************************************************************/ -- The target composite must match the pair. CREATE TYPE wkv AS (k text, v bigint, w int); CREATE TABLE bad_width (id int, m wkv[]); COPY bad_width FROM :'maps_json' (format 'JSONEachRow', structure 'id Int32, m Map(String, Int64)'); ERROR: could not map tuple to returned type DETAIL: Number of returned columns (2) does not match expected column count (3). CREATE TYPE tkv AS (k text, v text); CREATE TABLE bad_value (id int, m tkv[]); COPY bad_value FROM :'maps_json' (format 'JSONEachRow', structure 'id Int32, m Map(String, Int64)'); ERROR: could not map tuple to returned type DETAIL: Returned type bigint does not match expected type text in column "v" (position 2). /****************************************************************************/ -- Reuse inferred source types, including quoted column names \! cp -f test/corpus/schema.tsv /tmp/chdb-schema.tsv \set schema_tsv file:///tmp/chdb-schema.tsv CREATE TABLE inferred_schema () WITH ( copy_from = :'schema_tsv', format = 'TSVWithNamesAndTypes', structure = 'auto' ); NOTICE: chdb: column "point "value"" of type "Tuple(Int32, String)" converted to text[] NOTICE: chdb: column "labels\name" of type "Map(String, Int64)" converted to text[][] SELECT * FROM inferred_schema ORDER BY id; id | point "value" | labels\name ----+---------------+------------- 1 | {10,x} | {{a,7}} 2 | {20,y} | {} (2 rows) /****************************************************************************/ -- Infer Nested and SimpleAggregateFunction from source type names \! cp -f test/corpus/nested.tsv /tmp/chdb-nested.tsv \set nested_tsv file:///tmp/chdb-nested.tsv CREATE TABLE nested () WITH ( copy_from = :'nested_tsv', format = 'TSVWithNamesAndTypes' ); NOTICE: chdb: column "items" of type "Nested(a Int32, b Nullable(String))" converted to text[][] SELECT attname, format_type(atttypid, atttypmod) AS type, attndims, attnotnull FROM pg_attribute WHERE attrelid = 'nested'::regclass AND attnum > 0 ORDER BY attnum; attname | type | attndims | attnotnull ---------+---------+----------+------------ id | integer | 0 | t items | text[] | 2 | t total | bigint | 0 | t (3 rows) SELECT id, array_dims(items) AS dims, items, total FROM nested ORDER BY id; id | dims | items | total ----+------------+-----------------+---------------------- 1 | [1:2][1:2] | {{10,x},{20,y}} | 7 2 | | {} | 0 3 | [1:1][1:2] | {{30,NULL}} | -9223372036854775808 (3 rows) -- Let chDB infer Nested from source metadata to preserve rows -- Explicit Nested in structure splits rows into one array per field CREATE TYPE ab AS (a int, b text); CREATE TABLE nested_rec (id int, items ab[], total bigint); COPY nested_rec FROM :'nested_tsv' (format 'TSVWithNamesAndTypes', structure 'auto'); SELECT id, items, total FROM nested_rec ORDER BY id; id | items | total ----+---------------------+---------------------- 1 | {"(10,x)","(20,y)"} | 7 2 | {} | 0 3 | {"(30,)"} | -9223372036854775808 (3 rows) CREATE TABLE nested_create (id int, items ab[], total bigint) WITH ( copy_from = :'nested_tsv', format = 'TSVWithNamesAndTypes', structure = 'auto' ); SELECT id, items, total FROM nested_create ORDER BY id; id | items | total ----+---------------------+---------------------- 1 | {"(10,x)","(20,y)"} | 7 2 | {} | 0 3 | {"(30,)"} | -9223372036854775808 (3 rows) SELECT id, item.a, item.b, pg_typeof(item.a), pg_typeof(item.b) FROM nested_create, unnest(items) AS item ORDER BY id, item.a; id | a | b | pg_typeof | pg_typeof ----+----+---+-----------+----------- 1 | 10 | x | integer | text 1 | 20 | y | integer | text 3 | 30 | | integer | text (3 rows) \set ECHO errors