Skip to content

Instantly share code, notes, and snippets.

@alexey-milovidov
Created June 17, 2016 21:15
Show Gist options
  • Save alexey-milovidov/d6ffc9e0bc0bc72dd7bca90e76e3b83b to your computer and use it in GitHub Desktop.
Save alexey-milovidov/d6ffc9e0bc0bc72dd7bca90e76e3b83b to your computer and use it in GitHub Desktop.
Example of Nested data type in ClickHouse.
:) CREATE TABLE test.nested (EventDate Date, UserID UInt64, Attrs Nested(Key String, Value String)) ENGINE = MergeTree(EventDate, UserID, 8192)
CREATE TABLE test.nested
(
EventDate Date,
UserID UInt64,
Attrs Nested(
Key String,
Value String)
) ENGINE = MergeTree(EventDate, UserID, 8192)
Ok.
0 rows in set. Elapsed: 0.003 sec.
:) INSERT INTO test.nested VALUES ('2016-01-01', 123, ['price', 'color'], ['high', 'red'])
INSERT INTO test.nested VALUES
Ok.
1 rows in set. Elapsed: 0.003 sec.
:) SELECT * FROM test.nested
SELECT *
FROM test.nested
┌──EventDate─┬─UserID─┬─Attrs.Key─────────┬─Attrs.Value────┐
│ 2016-01-01 │ 123 │ ['price','color'] │ ['high','red'] │
└────────────┴────────┴───────────────────┴────────────────┘
1 rows in set. Elapsed: 0.004 sec.
:) SELECT * FROM test.nested ARRAY JOIN Attrs
SELECT *
FROM test.nested
ARRAY JOIN Attrs
┌──EventDate─┬─UserID─┬─Attrs.Key─┬─Attrs.Value─┐
│ 2016-01-01 │ 123 │ price │ high │
│ 2016-01-01 │ 123 │ color │ red │
└────────────┴────────┴───────────┴─────────────┘
2 rows in set. Elapsed: 0.002 sec.
:) SELECT * FROM test.nested WHERE Attrs.Value[indexOf(Attrs.Key, 'color')] = 'red'
SELECT *
FROM test.nested
WHERE Attrs.Value[indexOf(Attrs.Key, 'color')] = 'red'
┌──EventDate─┬─UserID─┬─Attrs.Key─────────┬─Attrs.Value────┐
│ 2016-01-01 │ 123 │ ['price','color'] │ ['high','red'] │
└────────────┴────────┴───────────────────┴────────────────┘
1 rows in set. Elapsed: 0.008 sec.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment