den-crane

# создаем таблицу заранее

CREATE TABLE kostya_test.view_result_table
  date Date,
  customer_id UInt32,
  val AggregateFunction (sum, UInt32)) 
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/tables/kostya_test.view_table', 
'{replica}', date, (date, prom_customer_id, val), 8192);

den-crane

Right:
select arrayFilter((v, k) -> k = 'a', values, keys) from
(select ['a','a','b','a'] keys, [1,2,3,4] values)

Wrong:
select arrayMap(i -> values[i], arrayFilter( i -> keys[i] = 'a', arrayEnumerate(keys))) from
(select ['a','a','b','a'] keys, [1,2,3,4] values)

den-crane

create table z(d Date, z String, u String) Engine=MergeTree partition by tuple() order by tuple();
CREATE MATERIALIZED VIEW mvz ENGINE = AggregatingMergeTree order by (z,d) settings index_granularity = 8  
as select d, z,uniqState(u) as us from z group by z,d;

insert into z select today()-1, toString(rand()%1000),concat('usr',toString(number)) from numbers(100000000);
insert into z select today()-2, toString(rand()%1000),concat('usr',toString(number)) from numbers(100000000);
insert into z select today()-3, toString(rand()%1000),concat('usr',toString(number)) from numbers(100000000);

select  uniq(u) as unique from z prewhere z='555' group by d order by d;
┌─unique─┐

den-crane

create table rank (id UInt64, rank_column LowCardinality(String), c0 AggregateFunction(sum, Int32))
engine = AggregatingMergeTree order by id;

create table nop (id UInt64, rank_column LowCardinality(String), c Int32) Engine= Null;

create materialized view nop_mv to rank as select id, rank_column, sumState(c) as c0 from nop group by id, rank_column

insert into nop select number id, toString(id % 150000), toInt32(rand() % 4234234) from numbers(100000000)

optimize table rank final;

den-crane

create table rank (id UInt64, rank_column LowCardinality(String), c0 AggregateFunction(sum, Int32))
engine = AggregatingMergeTree order by id;

create table nop (id UInt64, rank_column LowCardinality(String), c Int32) Engine= Null;

create materialized view nop_mv to rank as select id, rank_column, sumState(c) as c0 from nop group by id, rank_column

insert into nop select number id, toString(id % 150000), toInt32(rand() % 4234234) from numbers(100000000)

optimize table rank final;

den-crane

create table flows(date DateTime, src_id UInt64, dst_id UInt64, bytes UInt64)
Engine = MergeTree() order by date;

create table item_dict_t(id UInt64, attr String) Engine=MergeTree order by id;
insert into item_dict_t values(1, '1'),(2,'2');

CREATE DICTIONARY item_dict ( id UInt64, attr String ) 
PRIMARY KEY id SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000 
TABLE item_dict_t DB 'dw' USER 'default')) 
LIFETIME(MIN 0 MAX 0) LAYOUT(HASHED());

den-crane

There are two options - implicit storages in MV (MV without TO) and explicit (table created in advance) (MV with TO).

And I have not been using MV without TO for a long time. Because it’s much more obvious and convenient to stupidly drop MV and create a new one.


# alter MV with implicit storage .inner (without TO) 
1. stop ingestion 
2. detach table MVX
3. alter table `.inner.MVX` add column ..... 
4. attach MATERIALIZED VIEW MVX as select new_SELECT_.....

den-crane

create table source (A Int64, amount Float64) Engine=MergeTree order by A;

create materialized view target Engine=SummingMergeTree order by A
as select A, sum(amount) s from source group by A

insert into source values(1,1);

alter table source add column B Int64 after A;

detach table target;

den-crane

clickhouse-client -q "drop table json"
clickhouse-client -q "select  arrayStringConcat(arrayMap(x->toString (cityHash64(x)) , range(1000)),',\"''') from numbers(100000);" >test.json
clickhouse-client -q "create table json(j String) Engine=Memory"
time clickhouse-client --format_csv_allow_single_quotes=0 --format_csv_allow_double_quotes=0 --format_csv_delimiter=$'\x0B' -q 'insert into json format CSV' <test.json

19.13.6.51
real	0m2.069s user	0m0.520s sys	0m0.746s
real	0m2.043s user	0m0.511s sys	0m0.722s

19.17.6.36

den-crane

create table source (A Int64, B String) Engine=MergeTree order by A;
insert into source values(1,'1');

--
-- stop ingestion
--

alter table source freeze;

create materialized view newMV Engine=SummingMergeTree order by A