den-crane

https://github.com/ClickHouse/ClickHouse/issues/47092#issuecomment-1485052499

23.3.8.21
<default>
   <access_management>1</access_management>
</default>

create user foo;
grant all on *.* to foo;

den-crane

create table test(tenant_id UInt32,  ts1 DateTime, timestamp DateTime)
engine=MergeTree
partition by toDate(ts1)
PRIMARY KEY (tenant_id, toStartOfHour(timestamp))
ORDER BY (tenant_id, toStartOfHour(timestamp),  timestamp)
as select 1, now() - number/10 x, x from numbers(1e8);

insert into test select number%1000, now() - number/10 x, 0 from numbers(1e8);

den-crane

Test data

clickhouse-client -q "select number A, now() B, 'x' C from numbers(1e6) format Parquet" > test.parquet

Maven

    <dependencies>

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