4 ms·

Here's a workable example that I came up with: CREATE TABLE mt2 ( whatever Date DEFAULT '2000-01-01', key String, value String, first Aggre

Here's a workable example that I came up with: CREATE TABLE mt2 ( whatever Date DEFAULT '2000-01-01', key String, value String, first AggregateFunction(min, DateTime), last AggregateFunction(max, DateTime), total AggregateFunction(count, UInt64) ) ENGINE = AggregatingMergeTree(whatever, (key, value), 8192) insert into mt2 (key, value, first,last,total) select 'www.google.com', '1.2.3.4', minState(toDateTime(1498241729)),maxState(toDateTime(1498241729)), countState(cast(1 as UInt64)); insert into mt2 (key, value, first,last,total) select 'www.google.com', '1.2.3.5', minState(toDateTime(1498242729)),maxState(toDateTime(1498242729)), countState(cast(1 as UInt64)); insert into mt2 (key, value, first,last,total) select 'www.google.com', '1.2.3.6', minState(toDateTime(1498242729)),maxState(toDateTime(1498242829)), countState(cast(1 as UInt64)); select key, value, minMerge(first), maxMerge(last), countMerge(total) from mt2 group by key, value; optimize table mt; -- compact the table and merge multiple rows with the same (key, value) You have to add the date column, but you can ignore it. The last query can be a view or even a materialized view.

IanCal 9y ago
Thanks! I was also playing yesterday with CollapsingMergeTree. CREATE TABLE cmt ( whatever Date DEFAULT '2000-01-01', key String, value String sign Int8 ) ENGINE = CollapsingMergeTree(whatever, (key, value), 8192, sign) Now you can 'delete' a row by sending the same row again but with a sign of -1: insert into cmt (key, value, sign) values ('k1', 'v1', 1) insert into cmt (key, value, sign) values ('k1', 'v1', -1) insert into cmt (key, value, sign) values ('k1', 'v1 update', 1) insert into cmt (key, value, sign) values ('k2', 'just delete this one', 1) insert into cmt (key, value, sign) values ('k2', 'just delete this one', -1) You have to either add FINAL onto the query or optimise the table as far as I can tell for this to work, or hope it's done it in the background. I thought you had to use the sign in the query otherwise it wouldn't work, but creating this example this morning works fine. If you're not getting the response you expect after optimising, try adding the sign column to the query. :) select key, value, sign from cmt SELECT key, value, sign FROM cmt ┌─key─┬─value─────┬─sign─┐ │ k1 │ v1 update │ 1 │ └─────┴───────────┴──────┘ 1 rows in set. Elapsed: 0.002 sec. :) [disclaimer - I know very little about how to make high performance things in clickhouse, tbh the Log format has been easily fast enough for my data so far]

IanCal 9y ago

Thanks! I was also playing yesterday with CollapsingMergeTree. CREATE TABLE cmt ( whatever Date DEFAULT '2000-01-01', key String, value String sign Int8 ) ENGINE = CollapsingMergeTree(whatever, (key, value), 8192, sign) Now you can 'delete' a row by sending the same row again but with a sign of -1: insert into cmt (key, value, sign) values ('k1', 'v1', 1) insert into cmt (key, value, sign) values ('k1', 'v1', -1) insert into cmt (key, value, sign) values ('k1', 'v1 update', 1) insert into cmt (key, value, sign) values ('k2', 'just delete this one', 1) insert into cmt (key, value, sign) values ('k2', 'just delete this one', -1) You have to either add FINAL onto the query or optimise the table as far as I can tell for this to work, or hope it's done it in the background. I thought you had to use the sign in the query otherwise it wouldn't work, but creating this example this morning works fine. If you're not getting the response you expect after optimising, try adding the sign column to the query. :) select key, value, sign from cmt SELECT key, value, sign FROM cmt ┌─key─┬─value─────┬─sign─┐ │ k1 │ v1 update │ 1 │ └─────┴───────────┴──────┘ 1 rows in set. Elapsed: 0.002 sec. :) [disclaimer - I know very little about how to make high performance things in clickhouse, tbh the Log format has been easily fast enough for my data so far]