den-crane

create table A (A Int64, B Int64, S String) Engine=MergeTree order by A 
as select number,number, toString(arrayMap(i->cityHash64(i*number), range(100))) from numbers(1e7);


select * from A a join A as b on a.A = b.A format Null;


SET join_algorithm = 'hash';
Peak memory usage (for query): 32.62 GiB.
0 rows in set. Elapsed: 20.700 sec. Processed 20.00 million rows, 41.31 GB (966.18 thousand rows/s., 2.00 GB/s.)

den-crane

Denny Crane [not a Yandex bot], [Mar 10, 2020 at 6:34:43 PM (2020-03-10, 6:34:50 PM)]:
create table XXN (A Int64, B Nullable(Int64)) Engine=MergeTree order by tuple()
insert into XXN select 45545645, null from numbers(100000000);


select column, formatReadableSize(sum(column_bytes_on_disk)) bytes_on_disk, formatReadableSize(sum(column_data_uncompressed_bytes)) uncompressed
from system.parts_columns 
where active = 1 and table like '%XXN%' 
group by database,table, column

den-crane

2024.03.08 03:54:38.022312 [ 141144 ] {} <Trace> SystemLog (system.query_log): Flushing system log, 2 entries to flush up to offset 86304192
2024.03.08 03:54:38.024139 [ 140747 ] {} <Trace> BaseDaemon: Received signal 11
2024.03.08 03:54:38.024287 [ 930727 ] {} <Fatal> BaseDaemon: ########## Short fault info ############
2024.03.08 03:54:38.024679 [ 930727 ] {} <Fatal> BaseDaemon: (version 24.1.3.31 (official build), build id: E65ACEFD4C4A4F209A1529998C6032754B52A0FC, git hash: 135b08cbd28a5832e9e70c3b7d09dd4134845ed3) (from thread 141144) Received signal 11
2024.03.08 03:54:38.024693 [ 930727 ] {} <Fatal> BaseDaemon: Signal description: Segmentation fault
2024.03.08 03:54:38.024700 [ 930727 ] {} <Fatal> BaseDaemon: Address: 0x70. Access: read. Address not mapped to object.
2024.03.08 03:54:38.024705 [ 930727 ] {} <Fatal> BaseDaemon: Stack trace: 0x0000000007234f2e 0x0000000010d3bdd3 0x00000000114c0215 0x00000000114c39f2 0x000000001170b275 0x000000000c931314 0x000000000c8ee83e 0x00007f5233abdfd4 0x00007f5233b

den-crane

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

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

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

drop database uniq_test;
create database uniq_test;
USE uniq_test;

create table events(ts DateTime, uid UInt64, site_id UInt32, browser LowCardinality(String))
Engine=MergeTree partition by toYYYYMM(ts) order by (site_id, toStartOfHour(ts));

CREATE MATERIALIZED VIEW uniq_aggr
ENGINE = AggregatingMergeTree
partition by toYYYYMM(day)  order by (site_id, browser, day)

den-crane

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

CREATE TABLE t
(
    key_zstd1    Int64 CODEC(ZSTD(1)),
    key_lz4      Int64 CODEC(LZ4),
    key_t64_zstd Int64 CODEC(T64,ZSTD(1)),
    key_int8     Int8 CODEC(ZSTD(1)),
    r Int32
)
ENGINE = MergeTree
ORDER BY (key_zstd1, r);

den-crane

create table x (a Int64, b Int64, c Int64, 
 u AggregateFunction(uniqHLL12, String), 
 t AggregateFunction(uniqTheta, String)) 
Engine=MergeTree order by (a,b,c);

insert into x select number%11111 a, number%44 b, number%3 c, uniqHLL12State(number::String), uniqThetaState(number::String)
from numbers(1e7) group by a,b,c;

optimize table x final;