Skip to content

Instantly share code, notes, and snippets.

@javisantana
Created January 17, 2020 14:03
Show Gist options
  • Save javisantana/f334cc1dc6a8f2933f53cacc55e65855 to your computer and use it in GitHub Desktop.
Save javisantana/f334cc1dc6a8f2933f53cacc55e65855 to your computer and use it in GitHub Desktop.
SummingMergetree multiple keys in nested maps
MacBook-Pro-de-javi.local :) create table multiple_keys (tmp Int32, testMap Nested (a Int32, bKey Int32, value Int32)) Engine=SummingMergeTree() order by (tmp);
CREATE TABLE multiple_keys
(
`tmp` Int32,
`testMap` Nested(
a Int32,
bKey Int32,
value Int32)
)
ENGINE = SummingMergeTree()
ORDER BY tmp
Ok.
0 rows in set. Elapsed: 0.005 sec.
MacBook-Pro-de-javi.local :) insert into multiple_keys values (1, [1,2,3],[1,1,2], [1,1,1]);
INSERT INTO multiple_keys VALUES
INSERT INTO multiple_keys VALUES
Ok.
1 rows in set. Elapsed: 0.008 sec.
MacBook-Pro-de-javi.local :) select * from multiple_keys;
SELECT *
FROM multiple_keys
┌─tmp─┬─testMap.a─┬─testMap.bKey─┬─testMap.value─┐
│ 1 │ [1,2,3] │ [1,1,2] │ [1,1,1] │
└─────┴───────────┴──────────────┴───────────────┘
1 rows in set. Elapsed: 0.006 sec.
MacBook-Pro-de-javi.local :) optimize table multiple_keys final;
OPTIMIZE TABLE multiple_keys FINAL
Ok.
0 rows in set. Elapsed: 0.007 sec.
MacBook-Pro-de-javi.local :) select * from multiple_keys;
SELECT *
FROM multiple_keys
┌─tmp─┬─testMap.a─┬─testMap.bKey─┬─testMap.value─┐
│ 1 │ [1,2,3] │ [1,1,2] │ [1,1,1] │
└─────┴───────────┴──────────────┴───────────────┘
1 rows in set. Elapsed: 0.005 sec.
MacBook-Pro-de-javi.local :) insert into multiple_keys values (1, [1,2,3],[1,1,2], [1,1,1]);
INSERT INTO multiple_keys VALUES
INSERT INTO multiple_keys VALUES
Ok.
1 rows in set. Elapsed: 0.008 sec.
MacBook-Pro-de-javi.local :) select * from multiple_keys;
SELECT *
FROM multiple_keys
┌─tmp─┬─testMap.a─┬─testMap.bKey─┬─testMap.value─┐
│ 1 │ [1,2,3] │ [1,1,2] │ [1,1,1] │
└─────┴───────────┴──────────────┴───────────────┘
┌─tmp─┬─testMap.a─┬─testMap.bKey─┬─testMap.value─┐
│ 1 │ [1,2,3] │ [1,1,2] │ [1,1,1] │
└─────┴───────────┴──────────────┴───────────────┘
2 rows in set. Elapsed: 0.006 sec.
MacBook-Pro-de-javi.local :) optimize table multiple_keys final;
OPTIMIZE TABLE multiple_keys FINAL
Ok.
0 rows in set. Elapsed: 0.009 sec.
MacBook-Pro-de-javi.local :) select * from multiple_keys;
SELECT *
FROM multiple_keys
┌─tmp─┬─testMap.a─┬─testMap.bKey─┬─testMap.value─┐
│ 1 │ [1,2,3] │ [1,1,2] │ [2,2,2] │
└─────┴───────────┴──────────────┴───────────────┘
1 rows in set. Elapsed: 0.006 sec.
MacBook-Pro-de-javi.local :) insert into multiple_keys values (1, [1,1,3],[1,1,2], [1,1,1]);
INSERT INTO multiple_keys VALUES
INSERT INTO multiple_keys VALUES
Ok.
1 rows in set. Elapsed: 0.008 sec.
MacBook-Pro-de-javi.local :) optimize table multiple_keys final;
OPTIMIZE TABLE multiple_keys FINAL
Ok.
0 rows in set. Elapsed: 0.008 sec.
MacBook-Pro-de-javi.local :) select * from multiple_keys;
SELECT *
FROM multiple_keys
┌─tmp─┬─testMap.a─┬─testMap.bKey─┬─testMap.value─┐
│ 1 │ [1,2,3] │ [1,1,2] │ [4,2,3] │
└─────┴───────────┴──────────────┴───────────────┘
1 rows in set. Elapsed: 0.005 sec.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment