ClickHouse自适应物化视图使用指导

操作场景

ClickHouse在实际使用过程中经常使用物化视图，主要用于保存预先计算耗时较多的操作结果。在获取数据时，可以通过查询物化视图避免进行耗时的查询原始表操作，从而快速的得到结果。

当前物化视图在使用上还存在易用性的一些问题，比如：用户可以基于原始表数据，根据使用场景创建一个到多个物化视图。在使用过程中，需要人为识别查询语句使用那个物化视图合适，然后再将对应原始表的查询语句转成物化视图的查询语句。这样人为识别的过程，很容易出错，使用效率低。

自适应物化视图功能很好的解决了上述问题，该功能支持在查询原始表时，自适应转化为查询匹配的物化视图，极大提高ClickHouse的易用性和使用效率。

自适应物化视图匹配规则说明

查询原始表的SQL最终是否能自适应转化为查询物化视图的SQL，需要满足如下匹配规则：

• SQL查询的表需要存在关联的物化视图。
• 创建的物化视图引擎为AggregatingMergeTree时才支持该功能。
• SQL查询的SELECT字段中需要存在聚合函数，且该聚合函数在物化视图中也存在。
• SQL查询若存在GROUP BY子句，则要求该字段在物化视图的GROUP BY子句中也存在。
• 物化视图若存在WHERE子句，则要求SQL查询的WHERE子句与物化视图WHERE子句完全相同。PREWHERE和HAVING匹配规则和WHERE使用规则一致。
• SQL查询的字段在物化视图中需要存在。
• 如果有多组物化视图满足以上要求，则不对SQL查询进行自适应匹配，继续使用原SQL查询。

常见的自适应物化视图匹配失败问题案例可以参考自适应物化视图匹配失败常见案例。

使用自适应物化视图

以下操作示例说明：“local_table”为原始表，“view_table”为基于“local_table”创建的物化视图。实际使用过程中建表和查询语句请根据需要修改。

1. 参考从零开始使用ClickHouse章节，使用ClickHouse客户端连接default数据库。
2. 执行以下建表语句，创建原始表“local_table”。

   CREATE TABLE local_table
   (
   id String,
   city String,
   code String,
   value UInt32,
   create_time DateTime,
   age UInt32
   )
   ENGINE = MergeTree
   PARTITION BY toDate(create_time)
   ORDER BY (id, city, create_time);

3. 基于原始表“local_table”，创建物化视图“view_table”。

   CREATE MATERIALIZED VIEW view_table
   ENGINE = AggregatingMergeTree
   PARTITION BY toDate(create_time)
   ORDER BY (id, city, create_time)
   AS SELECT
   create_time,
   id,
   city,
   uniqState(code),
   sumState(value) AS value_new,
   minState(create_time) AS first_time,
   maxState(create_time) AS last_time
   FROM local_table
   WHERE create_time >= toDateTime('2021-01-01 00:00:00')
   GROUP BY id, city, create_time;

4. 插入数据到“local_table”表。

   INSERT INTO local_table values('1','zzz','code1',1,toDateTime('2021-01-02 00:00:00'), 10);
   INSERT INTO local_table values('2','kkk','code2',2,toDateTime('2020-01-01 00:00:00'), 20);
   INSERT INTO local_table values('3','ccc','code3',3,toDateTime('2022-01-01 00:00:00'), 30);

5. 开启自适应物化视图开关。

   set adaptive_materialized_view = 1;

   

   adaptive_materialized_view为1表示开启自适应物化视图，为0表示关闭自适应物化视图。默认为关。此开关为session级别，客户端每次连接都需要重新设置。

6. 查询原始表“local_table”数据。

   SELECT sum(value)
   FROM local_table
   WHERE create_time >= toDateTime('2021-01-01 00:00:00');
   ┌─sumMerge(value_new) ───┐
   │                   4        │
   └──────────────┘

7. 执行explain syntax命令，查看6中查询SQL的执行计划。从结果上看，实际查询的是物化视图“view_table ”。

   EXPLAIN SYNTAX
   SELECT sum(value)
   FROM local_table
   WHERE create_time >= toDateTime('2021-01-01 00:00:00');
   ┌─explain────────── ┐
   │ SELECT sumMerge(value_new)   │
   │ FROM default.view_table      │
   └───────────────┘

自适应物化视图匹配失败常见案例

• 在创建物化视图时聚合函数需要使用State后缀，否则不能自适应匹配到物化视图。例如：

  #基于原始表test_table创建了物化视图agg_view，count聚合函数没有携带State后缀。
  CREATE MATERIALIZED VIEW agg_view
  ENGINE = AggregatingMergeTree
  PARTITION BY toDate(create_time)
  ORDER BY (id)
  AS SELECT
  create_time,
  id,
  count(id)
  FROM test_table
  GROUP BY id,create_time;
  
  #如果需要自适应匹配功能生效，则创建物化视图的时候需要携带State后缀，参考如下。
  CREATE MATERIALIZED VIEW agg_view
  ENGINE = AggregatingMergeTree
  PARTITION BY toDate(create_time)
  ORDER BY (id)
  AS SELECT
  create_time,
  id,
  countState(id)
  FROM test_table
  GROUP BY id,create_time;

• 如果查询原始表语句的WHERE子句和物化视图的WHERE子句完全相同，注意，不仅仅是指逻辑上相同，字段字符、顺序都要相同才能自适应匹配到物化视图。

  否则，如原始表语句的WHERE子句为：where a=b，物化视图WHERE子句为：where b=a，该场景下自适应匹配不上物化视图。

  但是如果查询原始表时不携带数据库名称，则可以自适应匹配到物化视图。例如：

  #物化视图view_test基于db_test.table_test创建，where子句中携带db_test数据库名
  CREATE MATERIALIZED VIEW db_test.view_test ENGINE = AggregatingMergeTree ORDER BY phone AS
  SELECT
  name,
  phone,
  uniqExactState(class) as uniq_class,
  sumState(CRC32(phone))
  FROM db_test.table_test
  WHERE (class, name) GLOBAL IN
  (
  SELECT class, name FROM db_test.table_test
  WHERE
  name = 'zzzz'
  AND class = 'calss one'
  )
  GROUP BY
  name, phone;
  #查询原始表数据的时候，WHERE子句中不携带数据库名db_test则可以自适应匹配到物化视图。
  USE db_test;
  EXPLAIN SYNTAX
  SELECT
  name,
  phone,
  uniqExact(class) as uniq_class,
  sum(CRC32(phone))
  FROM table_test
  WHERE (class, name) GLOBAL IN
  (
  SELECT class, name FROM table_test
  WHERE
  name = 'zzzz'
  AND class = 'calss one'
  )
  GROUP BY
  name, phone;

• GROUP BY子句中如果有函数，则要求GROUP BY子句函数中的列字段和原始表查询中列字段名称一致才能自适应匹配成功。例如：

  #基于test_table创建物化视图agg_view
  CREATE MATERIALIZED VIEW agg_view
  ENGINE = AggregatingMergeTree
  PARTITION BY toDate(create_time)
  ORDER BY (id, city, create_time)
  AS SELECT
  create_time,
  id,
  city,
  value as value1,
  uniqState(code),
  sumState(value) AS value_new,
  minState(create_time) AS first_time,
  maxState(create_time) AS last_time
  FROM test_table
  GROUP BY id, city, create_time, value1 % 2, value1;
  #以下语句能匹配成功
  SELECT uniq(code) FROM test_table GROUP BY id, city, value1 % 2; 
  #以下语句不能匹配成功，value在物化视图的GROUP BY条件中不存在
  SELECT uniq(code) FROM test_table GROUP BY id, city, value % 2; 

• 建立的物化视图，from子句不能是一个SELECT查询语句，否则无法自适应匹配成功。如下示例from对应的是一个SELECT查询语句，该场景下则无法自适应匹配成功。

  CREATE MATERIALIZED VIEW agg_view
  ENGINE = AggregatingMergeTree
  PARTITION BY toDate(create_time)
  ORDER BY (id)
  AS SELECT
  create_time,
  id,
  countState(id)
  FROM
  (SELECT id, create_time FROM test_table)
  GROUP BY id,create_time;

• 在查询原始表数据或者建立物化视图时，聚合函数不能跟聚合函数组合，聚合函数不能跟普通函数组合。如下示例不能自适应匹配成功。

  #情况1：查询原始表数据的时候，存在聚合函数组合
  #创建物化视图
  CREATE MATERIALIZED VIEW agg_view
  ENGINE = AggregatingMergeTree
  PARTITION BY toDate(create_time)
  ORDER BY (id)
  AS SELECT
  create_time,
  id,
  countState(id)
  FROM test_table
  GROUP BY id,create_time;
  #查询原始表数据时带有聚合函数组合查询，则不能自适应匹配成功
  SELECT count(id) + count(id) FROM test_table;
  #情况2：创建物化视图时候，包含聚合函数组合，则不能自适应匹配成功
  #创建物化视图因为包含了countState(id) + countState(id)函数组合，则后续原始表查询不能自适应匹配成功
  CREATE MATERIALIZED VIEW agg_view
  ENGINE = AggregatingMergeTree
  PARTITION BY toDate(create_time)
  ORDER BY (id)
  AS SELECT
   create_time,
   id,
  (countState(id) + countState(id)) AS new_count
  FROM test_table
  GROUP BY id,create_time;
  #如下查询原始表时，实际不能自适应匹配成功
  SELECT new_count FROM test_table;

  如果聚合函数的参数是字段运算组合，是可以自适应匹配成功，如下：

  CREATE MATERIALIZED VIEW agg_view
  ENGINE = AggregatingMergeTree
  PARTITION BY toDate(create_time)
  ORDER BY (id)
  AS SELECT
  create_time,
  id,
  countState(id + id）
  FROM test_table
  GROUP BY id,create_time;
  #查询时可以匹配成功
  SELECT count(id + id) FROM test_table;