# 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);
     ```
     ![](https://support.huaweicloud.com/devg-rule-mrs/public_sys-resources/note_3.0-zh-cn.png)
     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;
     ```
     ![](https://support.huaweicloud.com/devg-rule-mrs/public_sys-resources/note_3.0-zh-cn.png)
     创建物化视图counter_daily_mv，数据存储到表counter_daily_agg中，数据源来自counter。
     
    
- 聚合表在明细表名后加上_{type}_agg后缀；物化视图添加 _{type}_mv后缀。
- 物化视图、聚合表保持与明细表同样的分区类型及TTL时间。
- 物化视图中的group by字段名称与明细表对应字段名称一致；select子句返回列名称与聚合表中列的名称保持一致。
- 物化视图创建时不会进行语法校验，只有发生实际数据插入与查询时才会出错。
- 物化视图上线前，需做好充分验证。
 
#### 规则
- 物化视图（Materialized View）显式指定聚合表。 在创建物化视图时，使用TO关键字为物化视图指定数据存储表。
  如果不显式指定聚合表，则会创建隐式表.inner.mv1，与物化视图绑定。
  

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

- 聚合表中，聚合指标定义成聚合类型（AggregateFunction）。 物化视图的指标列与聚合表中对应字段名称一致，命名规范如下：
  {aggregateFunction}_{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;
  ```
  

- 修改明细表、聚合表结构，严格按照以下步骤实施：
  1. 停止明细表数据插入；
  
  2. 修改聚合表结构设计；
  
  3. 删除物化视图表；
  
  4. 重新创建新转化关系的物化视图。