0
282
0
0
首页/专栏/ 技术分享/ 查看内容

一文教会你使用强大的ClickHouse物化视图

 admin   发表于  2024-6-21 20:57
专栏 技术分享

本文字数：11558；估计阅读时间：29 分钟

审校：庄晓东（魏庄）


介绍

在现实世界中，数据不仅需要存储，还需要处理。处理通常在应用程序端完成。但是，有些关键的处理点可以转移到ClickHouse，以提高数据的性能和可管理性。ClickHouse中最强大的工具之一就是物化视图。在这篇文章中，我们将探秘物化视图以及它们如何完成加速查询以及数据转换、过滤和路由等任务。

如果您想了解更多关于物化视图的信息，我们后续会提供一个免费的培训课程。


什么是物化视图?

物化视图是一种特殊的触发器，当数据被插入时，它将数据上执行 SELECT 查询的结果存储为到一个目标表中：

在许多情场景下，这都非常有用，让我们看看最受欢迎的一个场景 - 使某些查询更快。


快速示例

以Wikistat的10亿行数据集为例：

CREATE TABLE wikistat(    `time` DateTime CODEC(Delta(4), ZSTD(1)),    `project` LowCardinality(String),    `subproject` LowCardinality(String),    `path` String,    `hits` UInt64)ENGINE = MergeTreeORDER BY (path, time);
Ok.
INSERT INTO wikistat SELECT *FROM s3('https://ClickHouse-public-datasets.s3.amazonaws.com/wikistat/partitioned/wikistat*.native.zst') LIMIT 1e9
假设我们经常查询某个日期最受欢迎的项目：
SELECT    project,    sum(hits) AS hFROM wikistatWHERE date(time) = '2015-05-01'GROUP BY projectORDER BY h DESCLIMIT 10
这个查询在测试实例上需要15秒来完成：
┌─project─┬────────h─┐│ en      │ 34521803 ││ es      │  4491590 ││ de      │  4490097 ││ fr      │  3390573 ││ it      │  2015989 ││ ja      │  1379148 ││ pt      │  1259443 ││ tr      │  1254182 ││ zh      │   988780 ││ pl      │   985607 │└─────────┴──────────┘
10 rows in set. Elapsed: 14.869 sec. Processed 972.80 million rows, 10.53 GB (65.43 million rows/s., 708.05 MB/s.)
如果我们有大量这样的查询，并且我们需要ClickHouse提供毫秒级性能，我们可以为这个查询创建一个物化视图：
CREATE TABLE wikistat_top_projects(    `date` Date,    `project` LowCardinality(String),    `hits` UInt32)ENGINE = SummingMergeTreeORDER BY (date, project);
Ok.
CREATE MATERIALIZED VIEW wikistat_top_projects_mv TO wikistat_top_projects ASSELECT    date(time) AS date,    project,    sum(hits) AS hitsFROM wikistatGROUP BY    date,    project;
在这两个查询中：

  • wikistat_top_projects 是我们要用来保存物化视图的表的名称，

  • wikistat_top_projects_mv 是物化视图本身（触发器）的名称，

  • 我们使用了SummingMergeTree表引擎，因为我们希望为每个date/project汇总hits值，

  • AS 后面的内容是构建物化视图的查询。

我们可以创建任意数量的物化视图，但每一个新的物化视图都是额外的存储负担，因此保持总数合理，即每个表下的物化视图数目控制在10个以内。

现在，我们使用与 wikistat 表相同的查询来填充物化视图的目标表：

INSERT INTO wikistat_top_projects SELECT    date(time) AS date,    project,    sum(hits) AS hitsFROM wikistatGROUP BY    date,    project


查询物化视图表

由于 wikistat_top_projects 是一个表，我们可以利用ClickHouse的SQL功能进行查询：

SELECT    project,    sum(hits) hitsFROM wikistat_top_projectsWHERE date = '2015-05-01'GROUP BY projectORDER BY hits DESCLIMIT 10
┌─project─┬─────hits─┐│ en      │ 34521803 ││ es      │  4491590 ││ de      │  4490097 ││ fr      │  3390573 ││ it      │  2015989 ││ ja      │  1379148 ││ pt      │  1259443 ││ tr      │  1254182 ││ zh      │   988780 ││ pl      │   985607 │└─────────┴──────────┘
10 rows in set. Elapsed: 0.003 sec. Processed 8.19 thousand rows, 101.81 KB (2.83 million rows/s., 35.20 MB/s.)
请注意，这只花费了ClickHouse 3ms来产生相同的结果，而原始查询则花费了15秒。另请注意，由于SummingMergeTree引擎是异步的（这节省了资源并减少了对查询处理的影响），所以某些值可能尚未被计算，我们仍然需要在此使用 GROUP BY 。


管理物化视图

我们可以使用 SHOW TABLES 查询列出物化视图：

SHOW TABLES LIKE 'wikistat_top_projects_mv'
┌─name─────────────────────┐│ wikistat_top_projects_mv │└──────────────────────────┘
我们可以使用 DROP TABLE 删除物化视图，但这只会删除触发器本身：
DROP TABLE wikistat_top_projects_mv
如果不再需要目标表，请记得也将其删除：
DROP TABLE wikistat_top_projects


获取物化视图在磁盘上的大小

所有关于物化视图表的元数据都存储在system数据库中，与其他表一样。例如，为了获取其在磁盘上的大小，我们可以执行以下操作：

SELECT    rows,    formatReadableSize(total_bytes) AS total_bytes_on_diskFROM system.tablesWHERE table = 'wikistat_top_projects'
┌──rows─┬─total_bytes_on_disk─┐│ 15336 │ 37.42 KiB           │└───────┴─────────────────────┘


更新物化视图中的数据

物化视图的最强大的特点是当向源表插入数据时，目标表中的数据会使用 SELECT 语句自动更新：

因此，我们不需要额外地刷新物化视图中的数据 - ClickHouse会自动完成一切操作。假设我们向 wikistat 表插入新数据：

INSERT INTO wikistatVALUES(now(), 'test', '', '', 10),      (now(), 'test', '', '', 10),      (now(), 'test', '', '', 20),      (now(), 'test', '', '', 30);
现在，让我们查询物化视图的目标表，以验证 hits 列是否已正确汇总。我们使用FINAL修饰符以确保SummingMergeTree引擎返回汇总的hits，而不是单个、未合并的行：
SELECT hitsFROM wikistat_top_projectsFINALWHERE (project = 'test') AND (date = date(now()))
┌─hits─┐│   70 │└──────┘
1 row in set. Elapsed: 0.005 sec. Processed 7.15 thousand rows, 89.37 KB (1.37 million rows/s., 17.13 MB/s.)
在生产环境中，避免在大表上使用 FINAL ，并始终优先使用 sum(hits) 。还请检查optimize_on_insert参数设置，该选项控制如何合并插入的数据。


使用物化视图加速聚合

如前一节所示，物化视图是一种提高查询性能的方法。对于分析查询，常见的聚合操作不仅仅是前面示例中展示的 sum() 。SummingMergeTree非常适用于计算汇总数据，但还有更高级的聚合可以使用AggregatingMergeTree引擎进行计算。

假设我们经常执行以下类型的查询：

SELECT    toDate(time) AS date,    min(hits) AS min_hits_per_hour,    max(hits) AS max_hits_per_hour,    avg(hits) AS avg_hits_per_hourFROM wikistatWHERE project = 'en'GROUP BY date
这为我们提供了给定项目的每日点击量的月最小值、最大值和平均值：
┌───────date─┬─min_hits_per_hour─┬─max_hits_per_hour─┬──avg_hits_per_hour─┐│ 2015-05-01 │                 1 │             36802 │  4.586310181621408 ││ 2015-05-02 │                 1 │             23331 │  4.241388590780171 ││ 2015-05-03 │                 1 │             24678 │  4.317835245126423 │...└────────────┴───────────────────┴───────────────────┴────────────────────┘
38 rows in set. Elapsed: 8.970 sec. Processed 994.11 million rows

注意，我们的原始数据已经按小时进行了汇总。

我们使用物化视图存储这些聚合结果以便更快地检索。使用状态组合器(state combinators)定义聚合结果。状态组合器要求ClickHouse保存内部聚合状态，而不是最终的聚合结果。这允许使用聚合操作，而无需保存带有原始值的所有记录。这种方法很简单 - 我们在创建物化视图时使用*State()函数，然后在查询时使用其对应的*Merge()函数获取正确的聚合结果：

在我们的示例中，我们将使用 min 、 max 和 avg 状态。在新物化视图的目标表中，我们将使用 AggregateFunction 类型存储聚合状态而不是值：

CREATE TABLE wikistat_daily_summary(    `project` String,    `date` Date,    `min_hits_per_hour` AggregateFunction(min, UInt64),    `max_hits_per_hour` AggregateFunction(max, UInt64),    `avg_hits_per_hour` AggregateFunction(avg, UInt64))ENGINE = AggregatingMergeTreeORDER BY (project, date);
Ok.
CREATE MATERIALIZED VIEW wikistat_daily_summary_mvTO wikistat_daily_summary ASSELECT    project,    toDate(time) AS date,    minState(hits) AS min_hits_per_hour,    maxState(hits) AS max_hits_per_hour,    avgState(hits) AS avg_hits_per_hourFROM wikistatGROUP BY project, date
现在，让我们为它填充数据：
INSERT INTO wikistat_daily_summary SELECT    project,    toDate(time) AS date,    minState(hits) AS min_hits_per_hour,    maxState(hits) AS max_hits_per_hour,    avgState(hits) AS avg_hits_per_hourFROM wikistatGROUP BY project, date
0 rows in set. Elapsed: 33.685 sec. Processed 994.11 million rows
在查询时，我们使用相应的 Merge 组合器来检索值：
SELECT    date,    minMerge(min_hits_per_hour) min_hits_per_hour,    maxMerge(max_hits_per_hour) max_hits_per_hour,    avgMerge(avg_hits_per_hour) avg_hits_per_hourFROM wikistat_daily_summaryWHERE project = 'en'GROUP BY date
请注意，我们得到的结果完全相同，但速度快了数千倍：
┌───────date─┬─min_hits_per_hour─┬─max_hits_per_hour─┬──avg_hits_per_hour─┐│ 2015-05-01 │                 1 │             36802 │  4.586310181621408 ││ 2015-05-02 │                 1 │             23331 │  4.241388590780171 ││ 2015-05-03 │                 1 │             24678 │  4.317835245126423 │...└────────────┴───────────────────┴───────────────────┴────────────────────┘
32 rows in set. Elapsed: 0.005 sec. Processed 9.54 thousand rows, 1.14 MB (1.76 million rows/s., 209.01 MB/s.)
任何聚合函数都可以作为一个聚合物化视图的一部分与State/Merge组合器一起使用。


压缩数据来优化存储

在某些情况下，我们只需要存储聚合数据，但数据的写入是基于事件的方式进行的。如果我们仍然需要原始数据的最近几天的数据，并且可以保存聚合的历史数据，我们可以结合物化视图和源表的TTL来实现。

为了优化存储空间，我们还可以明确声明列类型，以确保表结构是最优的。假设我们想要仅存储来自 wikistat 表的每个path的月度聚合数据：

CREATE MATERIALIZED VIEW wikistat_monthly_mv TOwikistat_monthly ASSELECT    toDate(toStartOfMonth(time)) AS month,    path,    sum(hits) AS hitsFROM wikistatGROUP BY    
              
              

                  





路过




雷人




握手




鲜花




鸡蛋







              

                            

                          

收藏
邀请


            

上一篇：一文教你使用ClickHouse的字典(dictionary)下一篇：在 ClickHouse 中处理更新和删除

          
        
      
      
版权声明：本文为 clickhouse 社区用户原创文章，遵循 CC BY-NC-SA 4.0 版权协议，转载请附上原文出处链接和本声明。

      

        

          
评论

          

            

              

              
              

              

                

                    

                 




                    
                    
                    
                  

                    

                      
                    

                    

                      
                    

                  

                                    

                                

              

            

          

          

            

              

                
    
                                

              

            

          

        

      

    
    

     相关阅读
          相关分类
        版本发布
        客户案例
        技术分享
        meetup活动回顾
        视频分享