clickhouse MATERIALIZED VIEW issues

Question

I created MATERIALIZED VIEW like this :

create target table:

CREATE TABLE user_deatils_daily (
  day date,
  hour UInt8 ,
  appid UInt32,
  isp String,
  city String,
  country String,
  session_count UInt64,
  avg_score AggregateFunction(avg, Float32),
  min_revenue  AggregateFunction(min, Float32),
  max_load_time AggregateFunction(max, Int32)
)
ENGINE = SummingMergeTree()
PARTITION BY toRelativeWeekNum(day) 
ORDER BY (day,hour)

create mv:

CREATE MATERIALIZED VIEW user_deatils_daily_mv
TO user_deatils_daily as 
select toDate(session_ts) as day, toHour(toDateTime(session_ts)) as hour,appid,isp,city,country,
count(session_uuid) as session_count,avgState() as avg_score,
minState(revenue) as min_revenue,
maxState(perf_page_load_time) as max_load_time 
from user_deatils where toDate(session_ts)>='2020-08-26' group by session_ts,appid,isp,city,country

the data in the target table starting to fill with data. after some times the target table is getting fill with new data and doesn't' save the old one. why is that?

Denny Crane Denny Crane · Accepted Answer · 2020-08-30T15:48:18

SummingMergeTree() PARTITION BY toRelativeWeekNum(day) ORDER BY (day,hour)

means calculate sums groupby toRelativeWeekNum(day), day,hour)

user_deatils_daily knows nothing about user_deatils_daily_mv. They are not related.

user_deatils_daily_mv just does inserts into user_deatils_daily

SummingMergeTree knows nothing about group by session_ts,appid,isp,city,country

I would expect to see ORDER BY (ts,appid,isp,city,country);

I would do:

CREATE TABLE user_details_daily 
( ts DateTime, 
  appid UInt32, 
  isp String, 
  city String, 
  country String, 
  session_count SimpleAggregateFunction(sum,UInt64), 
  avg_score AggregateFunction(avg, Float32), 
  min_revenue SimpleAggregateFunction(min, Float32), 
  max_load_time SimpleAggregateFunction(max, Int32) ) 
ENGINE = AggregatingMergeTree() 
PARTITION BY toStartOfWeek(ts) 
ORDER BY (ts,appid,isp,city,country);

CREATE MATERIALIZED VIEW user_deatils_daily_mv TO user_details_daily 
as select 
   toStartOfHour(toDateTime(session_ts)) ts,
   appid,
   isp,
   city,
   country,
   count(session_uuid) as session_count ,
   avgState() as avg_score,
   min(revenue) as min_revenue,
   max(perf_page_load_time) as max_load_time 
   from user_details 
where toDate(session_ts)>='2020-08-26' group by ts,appid,isp,city,country;

clickhouse MATERIALIZED VIEW issues

1 Answers