den-crane

DROP TABLE IF EXISTS requests;

CREATE TABLE requests (
  request_date Date, 
  request_time DateTime, 
  response_time Int, 
  request_uri String) 
ENGINE  = MergeTree(request_date, (request_time, request_uri), 8192);

den-crane

CREATE TABLE states_raw(
    d date,
    uid UInt64,
    first_name String,
    last_name String,
    modification_timestamp_mcs DateTime64(3) default now64(3)
) ENGINE = Null;

CREATE TABLE final_states_by_month(
    d date,

den-crane

CREATE TABLE wide_rows2(`A` Int64, `D` Date, `S` String)
ENGINE = MergeTree PARTITION BY toYYYYMM(D) ORDER BY A
settings merge_with_ttl_timeout=300, materialize_ttl_recalculate_only = 1;

insert into wide_rows2 select number, toDate('2020-01-01')+ number%20, arrayMap(i-> cityHash64((number*i)%999), range(2000)) from numbers(1e5);
insert into wide_rows2 select number, toDate('2020-01-01')+ number%20, arrayMap(i-> cityHash64((number*i)%899), range(2000)) from numbers(1e5);
insert into wide_rows2 select number, toDate('2020-01-01')+ number%20, arrayMap(i-> cityHash64((number*i)%799), range(2000)) from numbers(1e5);
insert into wide_rows2 select number, toDate('2020-01-01')+ number%20, arrayMap(i-> cityHash64((number*i)%699), range(2000)) from numbers(1e5);
set materialize_ttl_after_modify=0;

den-crane

-----------------------    SimpleAggregateFunction + groupArrayArray  ----------------------------

drop table if exists states_raw;
drop table if exists final_states_by_month;
drop table if exists final_states_by_month_mv;

CREATE TABLE states_raw(d date, uid UInt64, s String) ENGINE = Null;
CREATE TABLE final_states_by_month(d date, uid UInt64, a_state SimpleAggregateFunction(groupArrayArray, Array(String))) 
ENGINE = AggregatingMergeTree PARTITION BY toYYYYMM(d) ORDER BY (uid);

den-crane

CH 1-node

SELECT
    count(),
    min(paramkey),
    max(paramkey),
    min(ind),
    max(ind)
FROM data_table
┌───count()─┬────────min(paramkey)─┬───────max(paramkey)─┬─min(ind)─┬──max(ind)─┐

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

-- in different versions of CH it works differently, and does not work at all with circle replication 


drop table t1 on cluster segmented;
drop table t2 on cluster segmented;
create table t1 on cluster segmented (A Int64) Engine=MergeTree order by tuple();
create table t2 on cluster segmented (A Int64) Engine=MergeTree order by tuple();
create table t1d on cluster segmented as t1 Engine=Distributed(segmented, currentDatabase(), t1, A);
create table t2d on cluster segmented as t2 Engine=Distributed(segmented, currentDatabase(), t2, A);

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