clickHouse物化视图的原理及应用

1、物化视图设计约束

由于TTL规则不会从原始表中同步到物化视图表，因此源表中带有TTL规则时，物化视 图表同样需要配置TTL规则，并且建议与源表保持一致。

2、应用场景

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

3、普通物化视图与 projection 对比

物化视图类 型	原表数据与物化视 图一致性	灵活性	物化视图开发及维护复 杂度
普通物化视 图	● 数据从原表同 步到物化视图 需要时间窗	● 灵活性较高，有新 的业务可开发新的 物化视图； ● 可开发复杂逻辑SQL 语句的物化视图。	● 复杂度较高，需要 开发很多物化视 图，每个物化视图 都需要单独去管理 和维护
projection	● 数据实时同 步，数据写入 即可查询到物 化视图最新数 据	● 创建表时指定的物 化视图语法，新的 SQL业务需要修改表 结构	● 不需要开发很多物 化视图，任意查询 SQL会自动重写命中 物化视图

4、物化视图创建样例

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('2024-02-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子句返回列名 称与聚合表中列的名称保持一致。

● 物化视图创建时不会进行语法校验，只有发生实际数据插入与查询时才会出错。

● 物化视图上线前，需做好充分验证。

5、物化视图规则

物化视图（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方式创建物化视图期间，如果有数据插入，则可能丢失。

● 推荐的历史数据同步方式为通过select历史数据并insert进物化视图本地表中。