ClickHouse普通物化视图设计

建议

• 在查询方式固定的场景，建议使用物化视图加速。
  物化视图创建参考如下：

  1. 明细表创建

     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);

  2. 聚合表创建

     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函数。其中，*表示定义时使用的聚合函数。

  3. 物化视图创建

     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);