Skip to content

Instantly share code, notes, and snippets.

🙀
What's happening?

Denis Zhuravlev den-crane

🙀
What's happening?
Block or report user

Report or block den-crane

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View flows.sql
create table flows(date DateTime, src_id UInt64, dst_id UInt64, bytes UInt64)
Engine = MergeTree() order by date;
create table item_dict_t(id UInt64, attr String) Engine=MergeTree order by id;
insert into item_dict_t values(1, '1'),(2,'2');
CREATE DICTIONARY item_dict ( id UInt64, attr String )
PRIMARY KEY id SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000
TABLE item_dict_t DB 'dw' USER 'default'))
LIFETIME(MIN 0 MAX 0) LAYOUT(HASHED());
View gist:d88f9ef887f879c7be71fb40ae943a94
create table rank (id UInt64, rank_column LowCardinality(String), c0 AggregateFunction(sum, Int32))
engine = AggregatingMergeTree order by id;
create table nop (id UInt64, rank_column LowCardinality(String), c Int32) Engine= Null;
create materialized view nop_mv to rank as select id, rank_column, sumState(c) as c0 from nop group by id, rank_column
insert into nop select number id, toString(id % 150000), toInt32(rand() % 4234234) from numbers(100000000)
optimize table rank final;
View gist:9b5f871e7949fec54e86837eb0949747
create table rank (id UInt64, rank_column LowCardinality(String), c0 AggregateFunction(sum, Int32))
engine = AggregatingMergeTree order by id;
create table nop (id UInt64, rank_column LowCardinality(String), c Int32) Engine= Null;
create materialized view nop_mv to rank as select id, rank_column, sumState(c) as c0 from nop group by id, rank_column
insert into nop select number id, toString(id % 150000), toInt32(rand() % 4234234) from numbers(100000000)
optimize table rank final;
View MV_vs_T
create table z(d Date, z String, u String) Engine=MergeTree partition by tuple() order by tuple();
CREATE MATERIALIZED VIEW mvz ENGINE = AggregatingMergeTree order by (z,d) settings index_granularity = 8
as select d, z,uniqState(u) as us from z group by z,d;
insert into z select today()-1, toString(rand()%1000),concat('usr',toString(number)) from numbers(100000000);
insert into z select today()-2, toString(rand()%1000),concat('usr',toString(number)) from numbers(100000000);
insert into z select today()-3, toString(rand()%1000),concat('usr',toString(number)) from numbers(100000000);
select uniq(u) as unique from z prewhere z='555' group by d order by d;
┌─unique─┐
@den-crane
den-crane / CH-arrays-iteration-using-arrayFilter
Created Apr 26, 2019
CH arrays (K/V) iteration by arrayFilter
View CH-arrays-iteration-using-arrayFilter
Right:
select arrayFilter((v, k) -> k = 'a', values, keys) from
(select ['a','a','b','a'] keys, [1,2,3,4] values)
Wrong:
select arrayMap(i -> values[i], arrayFilter( i -> keys[i] = 'a', arrayEnumerate(keys))) from
(select ['a','a','b','a'] keys, [1,2,3,4] values)
View gist:bc5256cb3f64ed3789819d40c11bda84
# создаем таблицу заранее
CREATE TABLE kostya_test.view_result_table
date Date,
customer_id UInt32,
val AggregateFunction (sum, UInt32))
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/tables/kostya_test.view_table',
'{replica}', date, (date, prom_customer_id, val), 8192);
@den-crane
den-crane / CH_percentage_from_total
Created Apr 10, 2019
CH percentage from total using groupArray
View CH_percentage_from_total
SELECT grp, ga.1 key, ga.2 value, vsum, value / vsum*100 percent
FROM (SELECT grp, groupArray(tuple (key,value)) ga, SUM(value) vsum
FROM (SELECT 1 grp,'k1' key,33 value UNION ALL SELECT 1, 'k2', 11 UNION ALL SELECT 2,'k1', 0 UNION ALL SELECT 2,'k2',1)
GROUP BY grp) Array JOIN ga
@den-crane
den-crane / sample_offset
Created Mar 14, 2019
sample 0.33 offset 0.33 example
View sample_offset
CREATE TABLE fff ( tx Int64) ENGINE = MergeTree() ORDER BY (intHash32(tx)) SAMPLE BY intHash32(tx)
insert into fff select number from numbers(10);
select groupArray(tx) from fff sample 0.33 offset 0
[9,6,3,1]
select groupArray(tx) from fff sample 0.33 offset 0.33
[8,2,5]
select groupArray(tx) from fff sample 0.33 offset 0.66
@den-crane
den-crane / CH-skip-index
Last active Mar 11, 2019
CH-skip-index
View CH-skip-index
create table BX(I Int64, S String) Engine=MergeTree order by I;
insert into BX select number, toString(rand()) from numbers(10000000);
ALTER TABLE BX ADD INDEX idx0 S TYPE minmax GRANULARITY 10;
optimize table BX final;
select count() from BX prewhere S = '666';
0 rows in set. Processed 10.00 million rows,
select count() from BX prewhere S = 'A';
0 rows in set. Elapsed: 0.001 sec.
View several_MV_one_internal_store
create table tableA (A String) Engine=MergeTree order by tuple();
create table tableB (B String) Engine=MergeTree order by tuple();
create table tableC (C String) Engine=Null;
create table storeABC(ABC String) Engine=MergeTree order by tuple();
create materialized view MVA to storeABC as select A ABC from tableA;
create materialized view MVB to storeABC as select B ABC from tableB;
create materialized view MVC to storeABC as select C ABC from tableC;
insert into tableA values('A');
insert into tableB values('B');
insert into tableC values('C');
You can’t perform that action at this time.