大数据ClickHouse进阶（二十一）：ClickHouse的Projection投影

Lansonli • 2023-01-02 • 云技术社区 • 291 阅读

​ClickHouse的Projection投影

在使用ClickHouse MergeTree引擎时，如果某张MergeTree表建表排序规则如下：

order by A,B,C

那么通常过滤查询Where A很快，但是Where C会慢一些。

此外，我们在使用物化视图时，经常基于一张底表构建许多物化视图，以帮助更进一步提升查询性能、降低数据分析开销，例如：

#创建普通MergeTree 表作为底表
create table personinfo(id UInt32,name String,age UInt32,score UInt32) engine = MergeTree() order by id;

#基于以上底表创建统计平均年龄物化视图表
create materialized view  t_view1 engine = Log as select sum(age)/count(name) as avg_age from personinfo;

#基于以上底表创建统计每人总分数物化视图表
create materialized view  t_view2 engine = Log as select name,sum(score) as total_score from personinfo group by name;

#向表personinfo中插入以下数据
insert into personinfo values (1,'zs',18,100),(2,'ls',19,200),(3,'ww',20,300),(4,'zs',18,400),(5,'ls',19,500),(6,'ww','20',600);

#查询t_view1与t_view2两张物化视图结果
node1 :) select * from t_view1;
┌─avg_age─┐
│      19 │
└─────────┘

node1 :) select * from t_view2;
┌─name─┬─total_score─┐
│ ls   │         700 │
│ ww   │         900 │
│ zs   │         500 │
└──────┴─────────────┘

以上构建的物化视图实际上在ClickHouse中相当于是独立的表，也会单独存储在数据目录“/var/lib/ClickHouse/data/${DB}”中：

既然物化视图也是独立的表，那么就存在与原表数据一致性问题，如果物化视图很多，维护起来也是问题。

目前使用ClickHouse我们遇到以上不完美的地方，总结下来就是：

  • MergeTree只支持一种排序规则
  • 物化视图不够智能

一、Projection投影介绍

ClickHouse Projection功能的出现完美解决了以上两点不完美。Projection(投影)指一组列的组合，可以按照与原表不同的排序存储，并且支持聚合函数查询，可以将Projection看成一种更加智能的物化视图，与物化视图一样本质也是用空间换时间，其具备以下特点：

  • part-level存储：

相比普通物化视图是一张独立的表，Projection 物化的数据就保存在原表的分区目录中，支持明细数据的普通Projection和预聚合Projection。

  • 无感使用，自动命中：

可以对一张 MergeTree 创建多个 Projection ，当执行 Select 语句的时候，能根据查询范围，自动匹配最优的 Projection 提供查询加速。如果没有命中 Projection,就直接查询底表。

  • 数据同源、同生共死：

因为物化的数据保存在原表的分区，所以数据的更新、合并都是同源的，也就不会出现不一致的情况了。

二、创建Projection投影测试

下面我们通过案例来测试Projection的使用性能，示例如下：

#向MySQL 库ck_db中导入 song表,数据量为17万左右，在ClickHouse库mysql_ck_db中会有对应的物化引擎表，这时在ClickHouse默认default库中执行如下语句，将song表数据导入到default.song_info表中
node1 :) create table song_info engine=MergeTree() order by source_id partition by status as select * from mysql_ck_db.song;

#没有Projection的时候，查询非主键name列
node1 :) select name from song_info where name = '独家女孩';
1 rows in set. Elapsed: 0.017 sec. Processed 177.31 thousand rows, 4.16 MB (10.68 million rows/s., 250.61 MB/s.)

针对name列创建一个Projection，为特定的where字段加速，按照查询的需求生成有别于主键的另一种排序规则：

ALTER TABLE song_info ADD PROJECTION p1
( 
    SELECT 
      name,album
    ORDER BY name
)

注意：只有在创建Projection之后，再被写入的数据才会自动物化，对于历史数据需要执行如下命令手动触发物化：

ALTER TABLE song_info MATERIALIZE PROJECTION p1;

我们可以执行如下命令查询物化是否完成：

SELECT
    table,
    mutation_id,
    command,
    is_done
FROM system.mutations AS m
where table = 'song_info';

以上完成后，我们可以进入到song_info数据目录：“/var/lib/ClickHouse/data/default/song_info/”，发现生成新的数据目录：

​编辑且每个分区对应的新目录中多了一个p1.proj子目录，进入到此子目录，我们发现与MergeTree表存储格式一样，如下：

有了p1 projection后我们执行同样查询，首先需要设置参数开启projection功能：

SET allow_experimental_projection_optimization = 1;

开启之后，重新执行原有的SQL，我们发现由原来扫码4.16M数据变成了扫描407Kb数据：

node1 :) select name from song_info where name = '独家女孩';
1 rows in set. Elapsed: 0.011 sec. Processed 17.08 thousand rows, 407.83 KB (1.60 million rows/s., 38.23 MB/s.)

三、explain验证是否使用Projection优化

我们可以执行如下命令来查看执行的SQL是否使用了Projection功能：

node1 :)explain select name from song_info where name = '独家女孩';

如上，如果看到“MergeTree(with 0 projection p1)”代表使用了Projection优化。

四、Projection支持预聚合

projection同样支持预聚合，在没有优化的情况下，以下查询会全表扫描：

node1 :) select source,count(*) from song_info group by source;
7 rows in set. Elapsed: 0.011 sec. Processed 177.31 thousand rows, 886.58 KB (16.12 million rows/s., 80.60 MB/s.)

创建另外一个Projection：

ALTER TABLE song_info ADD PROJECTION agg_p2
   ( 
     SELECT
         source, 
         count(*)
       GROUP BY source
   )

由于历史数据存在，需要手动触发下物化：

ALTER TABLE song_info MATERIALIZE PROJECTION agg_p2;

执行完成之后，再次执行相同查询：

node1 :) select source,count(*) from song_info group by source;
7 rows in set. Elapsed: 0.009 sec.

可以通过以上看出速度快了很多。

五、​​​​​​​删除Projection投影

我们可以执行如下命令删除Projection：

node1 :) ALTER TABLE song_info DROP PROJECTION p1;
node1 :) ALTER TABLE song_info DROP PROJECTION agg_p2;

六、​​​​​​​​​​​​​​Projection投影总结

在使用Projection时，查询使用Projection的匹配规则如下：

  • 设置SET allow_experimental_projection_optimization = 1
  • 返回的数据行小于基表总数
  • 查询覆盖的分区part超过一半
  • Where必须是Projection定义中Group By的子集
  • Group By必须是Projection定义中Group By的子集
  • Select 必须是Projection定义中Select的子集
  • 匹配多个Projection的时候，自动选取读取part最少的

利用Projection，我们只需要面对一张表查询就行，即拥有了原来物化视图的性能，又免去了维护成本与数据一致性的问题，相信未来可以使用Projection替换物化视图。

本站文章资源均来源自网络，除非特别声明，否则均不代表站方观点，并仅供查阅，不作为任何参考依据！
如有侵权请及时跟我们联系，本站将及时删除！
如遇版权问题，请查看 本站版权声明
THE END
分享
二维码
海报
大数据ClickHouse进阶（二十一）：ClickHouse的Projection投影
在使用ClickHouse MergeTree引擎时，如果某张MergeTree表建表排序规则如下：
<<上一篇
下一篇>>