Skip to content

Instantly share code, notes, and snippets.

View den-crane's full-sized avatar
🙀
What's happening?

Denny Crane den-crane

🙀
What's happening?
View GitHub Profile
@den-crane
den-crane / gist:f2062240195c9a59f11137603db19120
Last active May 29, 2019 20:06
Clickhouse ReplacingMergeTree new syntax (single partition)
View gist:f2062240195c9a59f11137603db19120
drop table replacing_test;
create table replacing_test(userid Int64, propery0 String, property1 Int8, deleted UInt8 default 0)
ENGINE = ReplacingMergeTree ORDER BY (userid);
insert into replacing_test select number, concat('propery',toString(number)), number+4,0 from system.numbers limit 500000000;
insert into replacing_test select number, concat('propery',toString(number)), number+4,0 from system.numbers limit 5000;
OPTIMIZE table replacing_test PARTITION tuple() final;
@den-crane
den-crane / gist:6eff375752a236a456e1b3dc2ca7db62
Last active September 30, 2023 08:43
Clickhouse example AggregatingMergeTree, (max, min, avg ) State / Merge
View gist:6eff375752a236a456e1b3dc2ca7db62
DROP TABLE IF EXISTS requests;
CREATE TABLE requests (
request_date Date,
request_time DateTime,
response_time Int,
request_uri String)
ENGINE = MergeTree(request_date, (request_time, request_uri), 8192);
View CH dictionary source included
# cat /etc/metrika.xml
<yandex>
<dictmysql1>
<port>3306</port>
<connect_timeout>3600</connect_timeout>
<rw_timeout>3600</rw_timeout>
<user>userx</user>
<password>passwordx</password>
<host>hostx</host>
<priority>1</priority>
View Clickhouse PK (sortkey)
create table test(a Int64, b Int64, c Int64, d String)
engine=MergeTree partition by tuple() order by (a,b,c);
insert into test select 1, 0, number, toString(number) from numbers(1000000);
insert into test select 2, 2, number, toString(number) from numbers(100);
insert into test select 3, 3, number, toString(number) from numbers(1000000);
select count() from test where a=2 and c=1;
1 rows in set. Elapsed: 0.002 sec.
View Clickhouse groupArray arrayJoin arraySlice
drop table tg
create table tg (a Int64, b Int64, c Int64) Engine = TinyLog;
insert into tg values (1,1433419200,15),(1,1433332800,23),(1,1433246400,41),(1,1433160000,55),
(1,1432900800,24),(2,1433419200,52),(2,1433332800,23),(2,1433246400,39),
(2,1433160000,22),(3,1433419200,11),(3,1433246400,58)
select a,tupleElement(x,1) b, tupleElement(x,2) c from (
select a,arrayJoin(arraySlice(groupArray(tuple(b,c)),1,3)) x from tg group by a)
@den-crane
den-crane / uniqState_uniqMerge
Last active December 29, 2023 02:02
CH AggregatingMergeTree uniqState uniqMerge
View uniqState_uniqMerge
drop table z;
drop table mvz;
create table z(d Date, u String) Engine=MergeTree partition by tuple() order by tuple();
CREATE MATERIALIZED VIEW mvz ENGINE = AggregatingMergeTree(d, (d), 8192) as select d, uniqState(u) as us from z group by d
insert into z select today()-number%571, concat('usr',toString(rand()%664579)) from numbers(100000000);
optimize table mvz final;
optimize table z final;
select (uniqMerge(us)) as unique from mvz group by d order by d;
571 rows in set. Elapsed: 0.300 sec.
@den-crane
den-crane / gist:f7382cd4f1f859ff6ac46afe7dc9925a
Created October 15, 2018 15:06
Populate AggregatingMergeTree through null table
View gist:f7382cd4f1f859ff6ac46afe7dc9925a
create table z(a date, b Int64) Engine=MergeTree Partition by toYYYYMM(a) order by a;
insert into z select today(), number from numbers(1000000000);
insert into z select yesterday(), number from numbers(1000);
create table mv_z_store(a date, max_b AggregateFunction(MAX,Int64)) ENGINE = AggregatingMergeTree Partition by toYYYYMM(a) order by a;
create table temp(a date, b Int64) Engine=Null;
create MATERIALIZED VIEW mv_z to mv_z_store AS SELECT a, maxState(b) AS max_b FROM temp GROUP BY a;
insert into temp select * from z;
drop table mv_z;
drop table temp;
@den-crane
den-crane / CH-event-sourcing-using-AggregatingMergeTree
Last active December 29, 2023 02:02
event sourcing using AggregatingMergeTree
View CH-event-sourcing-using-AggregatingMergeTree
DROP TABLE IF EXISTS states_raw;
DROP TABLE IF EXISTS final_states_by_day;
DROP TABLE IF EXISTS final_states_by_day_mv;
CREATE TABLE states_raw
(
process String,
state String,
stateint Int64,
statevalue Float64,
@den-crane
den-crane / Clickhouse-fast-not-exists
Last active November 3, 2023 10:53
Clickhouse fast not exists
View Clickhouse-fast-not-exists
ClickHouse server version 18.14.12 revision 54409.
create table data(K Int64, V String) engine=MergeTree order by K;
insert into data select number, toString(number) from numbers(100,100000000);
optimize table data final;
create table buffer(K Int64, V String) engine=Memory;
insert into buffer select number, toString(number) from numbers(0,1000);
@den-crane
den-crane / CH-argMin-vs-Limit
Last active September 8, 2020 07:28
CH argMin vs Limit
View CH-argMin-vs-Limit
create table test (p Int64, t Int64, x String, y Int64) engine=MergeTree order by (p,t)
insert into test select intDiv(number,10000), rand() r, toString(r), number from numbers(10000000);
select count(), min(x) from (
select * from test order by p, t limit 1 by p
)
┌─count()─┬─min(x)─┐
│ 1000 │ 100420 │
└─────────┴────────┘