den-crane

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

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

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 system.parts where table = 'idx' and active;
┌─sum(primary_key_bytes_in_memory)─┬─sum(primary_key_bytes_in_memory_allocated)─┐
│                           195360 │                                     264192 │
└──────────────────────────────────┴────────────────────────────────────────────┘

den-crane

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

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);

SELECT
    database,
    table,
    column,

den-crane

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

(
    `date` Date,
    `project` LowCardinality(String),
    `hits` UInt32
)
ENGINE = SummingMergeTree
ORDER BY (date, project, hits);

den-crane

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

Test1

UniqueMergeTree

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

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),