Skip to content

Instantly share code, notes, and snippets.

What's happening?

Denny Crane den-crane

What's happening?
View GitHub Profile
den-crane /
Created March 17, 2023 18:20
Average / Total sum over group
create table A (pk_col Int64, col1 Float64, col4 Float64, day Date) Engine=Memory
as select * from values( 
            (1, 3, 4, today()), (1, 1, 4, today()), (1, 2, 0, today()-1), 
            (2, 3, 4, today()), (2, 3, 0, today()-1) );

Window function

den-crane /
Last active February 28, 2023 20:00
Buffer -> Distributed -> Replicated

Buffer -> Replicated

create database test on cluster '{cluster}' Engine=Ordinary;

create table test.test on cluster '{cluster}' (A Int64) 
Engine = ReplicatedMergeTree('/clickhouse/{cluster}/tables/{database}/{table}', '{replica}') order by A;

create table test.test_b on cluster  '{cluster}' as test.test
Engine = Buffer(test, test, 1, 1, 2, 10, 100, 10000000, 100000000);
den-crane / primary_key_and_memory_allocation.sql
Created February 14, 2023 19:01
primary key is loaded on Clickhouse start
View primary_key_and_memory_allocation.sql
create table idx( A Int64, D Date) Engine=MergeTree order by A partition by D;
insert into idx select number, today() from numbers(1e8);
insert into idx select number, today()-1 from numbers(1e8);
select sum(primary_key_bytes_in_memory), sum(primary_key_bytes_in_memory_allocated)
from where table = 'idx' and active;
│ 195360 │ 264192 │
den-crane / AggregatingMergeTree_projection.sql
Last active February 10, 2023 04:54
AggregatingMergeTree + projection
View AggregatingMergeTree_projection.sql
create table test (
A Int64,
B String,
SomeID AggregateFunction(uniq, Int64),
projection p1 (select B, uniqMergeState(SomeID) group by B)
Engine=AggregatingMergeTree order by (A, B);
insert into test select number A, number%3 B, uniqState(toInt64(rand64())) from numbers(1e7) group by A,B;
den-crane / UInt32_VS_DateTime.sql
Last active February 9, 2023 15:57
UInt32 VS DateTime
View UInt32_VS_DateTime.sql
create table test_d( a Int64, i UInt32, d DateTime) Engine=MergeTree order by a;
insert into test_d
select number+rand()%50, toUInt32(toDateTime('2020-01-01 00:00:00')+number) i , toDateTime(i)
from numbers(1e8);
den-crane /
Last active January 31, 2023 22:29
create table test_bug(A Int64, D Date, S String) 
Engine = ReplicatedMergeTree('/clickhouse/tables/test_bug_orphans', '{replica}')
partition by D order by A;

insert into test_bug select number, today(), '' from numbers(1000);
insert into test_bug select number, today()-1, '' from numbers(1000);
insert into test_bug select number, today()-1, '' from numbers(1001);
insert into test_bug select number, today()-1, '' from numbers(1002);
select sleep(.5);
den-crane /
Created January 22, 2023 14:55
Using Materialized Views in ClickHouse
    `date` Date,
    `project` LowCardinality(String),
    `hits` UInt32
ENGINE = SummingMergeTree
ORDER BY (date, project, hits);
den-crane /
Last active February 9, 2023 08:33
Clickhouse GIN

Plain Table VS Table with Projection VS Table with GIN

try to insert 100 000 000 rows fails with OOM

set allow_experimental_inverted_index=1;

CREATE TABLE bench(c_int Int64, c_str varchar(255), c_float Float64) 
Engine=ReplacingMergeTree partition by c_int%10 order by c_int as
select toInt64(cityHash64(number)), 
den-crane /
Last active January 20, 2023 21:30
UniqueMergeTree vs ReplacingMergeTree



set optimize_on_insert=0;
CREATE TABLE test_unique_mergetree(n1 UInt32,n2 UInt32,s String) ENGINE= UniqueMergeTree ORDER BY n1;

INSERT INTO test_unique_mergetree SELECT number, 1, 'hello' FROM numbers(1e8);
0 rows in set. Elapsed: 65.947 sec. Processed 100.65 million rows, 805.21 MB (1.53 million rows/s., 12.21 MB/s.)
den-crane /
Last active January 10, 2023 15:11
How to populate some column in AggregatingMergeTree

How to populate some column in AggregatingMergeTree if you use AggregateFunctions for all measurements? You can use simple insert and omit columns which should not be populated. AggregateFunction States do everything else magically

CREATE TABLE values_minute
    id LowCardinality(String),
    ts DateTime,
    count_state AggregateFunction(count),
    max_state AggregateFunction(max, Float64),