ClickHouse普通物化视图设计

建议

在查询方式固定的场景，建议使用物化视图加速。

物化视图创建参考如下：

明细表创建

CREATE TABLE counter ON CLUSTER default_cluster
(
when DateTime DEFAULT now(),
device UInt32,
value Float32
) ENGINE=MergeTree
PARTITION BY toYYYYMM(when)
ORDER BY (device, when);

聚合表创建

CREATE TABLE counter_daily_agg ON CLUSTER default_cluster
(
day DateTime,
device UInt32,
count UInt64,
max_value_state AggregateFunction(max, Float32),
min_value_state AggregateFunction(min, Float32),
avg_value_state AggregateFunction(avg, Float32)
)
ENGINE = SummingMergeTree()
PARTITION BY tuple()
ORDER BY (device, day);

AggregateFunction类型的字段使用二进制存储，在写入数据时，需要调用*State函数；而在查询数据时，则需要调用相应的*Merge函数。其中，*表示定义时使用的聚合函数。

物化视图创建

CREATE MATERIALIZED VIEW counter_daily_mv ON CLUSTER default_cluster
TO counter_daily_agg
AS
SELECT
toStartOfDay(when) as day,
device,
count(*) as count,
maxState(value) AS max_value_state,
minState(value) AS min_value_state,
avgState(value) AS avg_value_state
FROM counter
WHERE when >= toDate('2019-01-01 00:00:00')
GROUP BY device, day
ORDER BY device, day;

创建物化视图counter_daily_mv，数据存储到表counter_daily_agg中，数据源来自counter。

聚合表在明细表名后加上_{type}_agg后缀；物化视图添加 _{type}_mv后缀。
物化视图、聚合表保持与明细表同样的分区类型及ttl时间。
物化视图中的group by字段名称与明细表对应字段名称一致；select子句返回列名称与聚合表中列的名称保持一致。
物化视图创建时不会进行语法校验，只有发生实际数据插入与查询时才会出错。
物化视图上线前，需做好充分验证。

规则

物化视图（Materialized View）显式指定聚合表。
在创建物化视图时，使用TO关键字为物化视图指定数据存储表。

如果不显示指定聚合表，则会创建隐式表.inner.mv1，与物化视图绑定。

用于数据预聚合的物化视图，聚合表使用聚合引擎。
如果不用聚合引擎，则每次数据插入，会对明细表的全量数据重新计算，而不是只处理增量数据。

聚合表中，聚合指标定义成聚合类型（AggregateFunction）。

物化视图的指标列与聚合表中对应字段名称一致，命名规范如下：

{aggrateFunction}_{columnName}_state

聚合表创建样例：

CREATE TABLE counter_daily_agg ON CLUSTER default_cluster
(
day DateTime,
device UInt32,
count UInt64,
max_value_state AggregateFunction(max, Float32),
min_value_state AggregateFunction(min, Float32),
avg_value_state AggregateFunction(avg, Float32)
)
ENGINE = SummingMergeTree()
PARTITION BY tuple()
ORDER BY (device, day);

在创建物化视图时，如果用到了多表联查，只有左表发生数据插入时才会触发物化视图数据修改。
禁止在创建物化视图时使用POPULATE关键字。
使用POPULATE方式创建物化视图期间，如果有数据插入，则可能丢失。

推荐的历史数据同步方式：

-- create MV с where date >= in_the_future
CREATE MATERIALIZED VIEW mv1 ON CLUSTER default_cluster
TO dest
AS
SELECT  a,  d,  count()  AS cnt
FROM source
WHERE d >= '2020-11-01'
GROUP BY a, d;
-- arrives 2020-11-01
INSERT INTO dest -- insert all for before in_the_future
SELECT  a,  d,  count()  AS cnt
FROM source
WHERE d < '2020-11-01' -- piece by piece by 1 month (or .. day)
GROUP BY a, d;