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

ClickHouseInc 2023-09-14

本文字数：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 = MergeTree
ORDER 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 h
FROM wikistat
WHERE date(time) = '2015-05-01'
GROUP BY project
ORDER BY h DESC
LIMIT 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 = SummingMergeTree
ORDER BY (date, project);


Ok.


CREATE MATERIALIZED VIEW wikistat_top_projects_mv TO wikistat_top_projects AS
SELECT
    date(time) AS date,
    project,
    sum(hits) AS hits
FROM wikistat
GROUP 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 hits
FROM wikistat
GROUP BY
    date,
    project

查询物化视图表

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

SELECT
    project,
    sum(hits) hits
FROM wikistat_top_projects
WHERE date = '2015-05-01'
GROUP BY project
ORDER BY hits DESC
LIMIT 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_disk
FROM system.tables
WHERE table = 'wikistat_top_projects'


┌──rows─┬─total_bytes_on_disk─┐
│ 15336 │ 37.42 KiB           │
└───────┴─────────────────────┘

更新物化视图中的数据

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

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

INSERT INTO wikistat
VALUES(now(), 'test', '', '', 10),
      (now(), 'test', '', '', 10),
      (now(), 'test', '', '', 20),
      (now(), 'test', '', '', 30);

现在，让我们查询物化视图的目标表，以验证 hits 列是否已正确汇总。我们使用FINAL修饰符以确保SummingMergeTree引擎返回汇总的hits，而不是单个、未合并的行：

SELECT hits
FROM wikistat_top_projects
FINAL
WHERE (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_hour
FROM wikistat
WHERE 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 = AggregatingMergeTree
ORDER BY (project, date);


Ok.


CREATE MATERIALIZED VIEW wikistat_daily_summary_mv
TO wikistat_daily_summary AS
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_hour
FROM wikistat
GROUP 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_hour
FROM wikistat
GROUP 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_hour
FROM wikistat_daily_summary
WHERE 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 TO
wikistat_monthly AS
SELECT
    toDate(toStartOfMonth(time)) AS month,
    path,
    sum(hits) AS hits
FROM wikistat
GROUP BY
    path,
    month

原始表（按小时存储的数据）占用的磁盘空间是聚合的物化视图的3倍：

wikistat（原始表）	wikistat_daily（物化视图）
1.78GiB	565.68 MiB
1b rows	~ 27m rows

这里要注意的一个点是，只有当结果行的数量至少减少10倍时，压缩才有意义。在其他情况下，ClickHouse的强大压缩和编码算法将展现出与没有任何聚合情况下相匹配的存储效率。

现在我们有了月度聚合，我们可以为原始表添加一个TTL表达式，这样数据在1周后就会被删除：

ALTER TABLE wikistat MODIFY TTL time + INTERVAL 1 WEEK

验证和过滤数据

使用物化视图的另一个流行的示例是在插入后立即处理数据。数据验证就是一个很好的例子。

假设我们想要滤掉所有包含不需要的符号的path，再保存到结果表中。我们的表中有大约1%这样的值：

SELECT count(*)
FROM wikistat
WHERE NOT match(path, '[a-z0-9\\-]')
LIMIT 5


┌──count()─┐
│ 12168918 │
└──────────┘


1 row in set. Elapsed: 46.324 sec. Processed 994.11 million rows, 28.01 GB (21.46 million rows/s., 604.62 MB/s.)

为了实现验证过滤，我们需要两个表 - 一个带有所有数据的表和一个只带有干净数据的表。物化视图的目标表将扮演一个只带有干净数据的最终表的角色，源表将是暂时的。我们可以根据TTL从源表中删除数据，就像我们在上一节中所做的那样，或者将此表的引擎更改为Null，该引擎不存储任何数据（数据只会存储在物化视图中）：

CREATE TABLE wikistat_src
(
    `time` DateTime,
    `project` LowCardinality(String),
    `subproject` LowCardinality(String),
    `path` String,
    `hits` UInt64
)
ENGINE = Null

现在，让我们使用数据验证查询创建一个物化视图：

CREATE TABLE wikistat_clean AS wikistat;


Ok.


CREATE MATERIALIZED VIEW wikistat_clean_mv TO wikistat_clean
AS SELECT *
FROM wikistat_src
WHERE match(path, '[a-z0-9\\-]')

当我们插入数据时， wikistat_src 将保持为空：

INSERT INTO wikistat_src SELECT * FROM s3('https://ClickHouse-public-datasets.s3.amazonaws.com/wikistat/partitioned/wikistat*.native.zst') LIMIT 1000

让我们确保原表是空的：

SELECT count(*)
FROM wikistat_src


┌─count()─┐
│       0 │
└─────────┘

但是，我们的 wikistat_clean 物化表现在只有有效的行：

SELECT count(*)
FROM wikistat_clean


┌─count()─┐
│      58 │
└─────────┘

其他942行（1000 - 58）在插入时被我们的验证语句排除了。

数据路由到表格

物化视图可以用于的另一个示例是基于某些条件将数据路由到不同的表：

例如，我们可能希望将无效数据路由到另一个表，而不是删除它。在这种情况下，我们创建另一个物化视图，但使用不同的查询：

CREATE TABLE wikistat_invalid AS wikistat;


Ok.


CREATE MATERIALIZED VIEW wikistat_invalid_mv TO wikistat_invalid
AS SELECT *
FROM wikistat_src
WHERE NOT match(path, '[a-z0-9\\-]')

当我们有单个物化视图用于同一源表时，它们将按字母顺序进行处理。请记住，不要为源表创建超过几十个物化视图，因为插入性能可能会下降。

如果我们再次插入相同的数据，我们会在 wikistat_invalid 物化视图中找到942个无效的行：

SELECT count(*)
FROM wikistat_invalid


┌─count()─┐
│     942 │
└─────────┘

数据转换

由于物化视图基于查询的结果，所以我们可以在SQL中使用所有ClickHouse函数的功能来转换源值，以丰富和提升数据的清晰度。作为一个快速的例子，让我们将project、subproject和path列合并到一个单一的page列，并将时间分割为date和hour列：

CREATE TABLE wikistat_human
(
    `date` Date,
    `hour` UInt8,
    `page` String
)
ENGINE = MergeTree
ORDER BY (page, date);


Ok.


CREATE MATERIALIZED VIEW wikistat_human_mv TO wikistat_human
AS SELECT
    date(time) AS date,
    toHour(time) AS hour,
    concat(project, if(subproject != '', '/', ''), subproject, '/', path) AS page,
    hits
FROM wikistat

现在， wikistat_human 将填充转换后的数据：

┌───────date─┬─hour─┬─page──────────────────────────┬─hits─┐
│ 2015-11-08 │    8 │ en/m/Angel_Muñoz_(politician) │    1 │
│ 2015-11-09 │    3 │ en/m/Angel_Muñoz_(politician) │    1 │
└────────────┴──────┴───────────────────────────────┴──────┘

在生产环境中创建物化视图

当源数据到达时，新数据会自动添加到物化视图的目标表中。但是，为了在生产环境中用现有数据填充物化视图，我们必须遵循以下简单步骤：

1. 暂停向源表写入。

2. 创建物化视图。

3. 使用源表中的数据填充目标表。

4. 重新开始向源表写入。

或者，在创建物化视图时，我们可以使用未来的某个时间点：

CREATE MATERIALIZED VIEW mv TO target_table
AS SELECT …
FROM soruce_table WHERE date > `$todays_date`

其中 $todays_date 应替换为绝对日期。因此，我们的物化视图将从明天开始触发，所以我们必须等到明天并用以下查询填充历史数据：

INSERT INTO target_table
SELECT ...
FROM soruce_table WHERE date <= `$todays_date`

物化视图和JOIN操作

由于物化视图是基于SQL查询的结果工作的，我们可以使用JOIN操作以及任何其他SQL功能。但是应该小心使用JOIN操作。

假设我们有一个带有页面标题的表：

CREATE TABLE wikistat_titles
(
    `path` String,
    `title` String
)
ENGINE = MergeTree
ORDER BY path

这个表中的title与path关联：

SELECT *
FROM wikistat_titles


┌─path─────────┬─title────────────────┐
│ Ana_Sayfa    │ Ana Sayfa - artist   │
│ Bruce_Jenner │ William Bruce Jenner │
└──────────────┴──────────────────────┘

现在我们可以创建一个物化视图，从 wikistat_titles 表中通过joinpath值连接title：

CREATE TABLE wikistat_with_titles
(
    `time` DateTime,
    `path` String,
    `title` String,
    `hits` UInt64
)
ENGINE = MergeTree
ORDER BY (path, time);


Ok.


CREATE MATERIALIZED VIEW wikistat_with_titles_mv TO wikistat_with_titles
AS SELECT time, path, title, hits
FROM wikistat AS w
INNER JOIN wikistat_titles AS wt ON w.path = wt.path

注意，我们使用了 INNER JOIN ，所以在填充后，我们只会得到在 wikistat_titles 表中有对应值的记录：

SELECT * FROM wikistat_with_titles LIMIT 5


┌────────────────time─┬─path──────┬─title──────────────┬─hits─┐
│ 2015-05-01 01:00:00 │ Ana_Sayfa │ Ana Sayfa - artist │    5 │
│ 2015-05-01 01:00:00 │ Ana_Sayfa │ Ana Sayfa - artist │    7 │
│ 2015-05-01 01:00:00 │ Ana_Sayfa │ Ana Sayfa - artist │    1 │
│ 2015-05-01 01:00:00 │ Ana_Sayfa │ Ana Sayfa - artist │    3 │
│ 2015-05-01 01:00:00 │ Ana_Sayfa │ Ana Sayfa - artist │  653 │
└─────────────────────┴───────────┴────────────────────┴──────┘

我们在 wikistat 表中插入一个新记录，看看我们的新物化视图是如何工作的：

INSERT INTO wikistat VALUES(now(), 'en', '', 'Ana_Sayfa', 123);


1 row in set. Elapsed: 1.538 sec.

注意这里的插入时间 - 1.538秒。我们可以在 wikistat_with_titles 中看到我们的新行：

SELECT *
FROM wikistat_with_titles
ORDER BY time DESC
LIMIT 3


┌────────────────time─┬─path─────────┬─title────────────────┬─hits─┐
│ 2023-01-03 08:43:14 │ Ana_Sayfa    │ Ana Sayfa - artist   │  123 │
│ 2015-06-30 23:00:00 │ Bruce_Jenner │ William Bruce Jenner │  115 │
│ 2015-06-30 23:00:00 │ Bruce_Jenner │ William Bruce Jenner │   55 │
└─────────────────────┴──────────────┴──────────────────────┴──────┘

但是，如果我们向 wikistat_titles 表添加数据会发生什么呢？：

INSERT INTO wikistat_titles
VALUES('Academy_Awards', 'Oscar academy awards');

尽管我们在 wikistat 表中有相应的值，但物化视图中不会出现任何内容：

SELECT *
FROM wikistat_with_titles
WHERE path = 'Academy_Awards'


0 rows in set. Elapsed: 0.003 sec.

这是因为物化视图只在其源表接收插入时触发。它只是源表上的一个触发器，对连接表一无所知。注意，这不仅仅适用于join查询，并且在物化视图的SELECT语句中引入任何外部表时都很相关，例如使用 IN SELECT 。

在我们的情况下， wikistat 是物化视图的源表，而 wikistat_titles 是我们要连接的表：

这就是为什么在我们的物化视图中没有任何东西出现的原因 - 没有插入到 wikistat 表中。但让我们向它插入一些内容：

INSERT INTO wikistat VALUES(now(), 'en', '', 'Academy_Awards', 456);

我们可以在物化视图中看到新记录：

SELECT *
FROM wikistat_with_titles
WHERE path = 'Academy_Awards'


┌────────────────time─┬─path───────────┬─title────────────────┬─hits─┐
│ 2023-01-03 08:56:50 │ Academy_Awards │ Oscar academy awards │  456 │
└─────────────────────┴────────────────┴──────────────────────┴──────┘

要小心，因为JOIN操作可能会在连接大表时显著降低插入性能，如上所示。考虑使用字典作为更有效的替代方法。

总结

在这篇博客文章中，我们探讨了物化视图在ClickHouse中如何成为一个强大的工具，用于提高查询性能和扩展数据管理能力。你甚至可以使用物化视图与JOIN操作。当不需要聚合或过滤时，考虑物化列作为一个快速的替代方法。

clickhouse 物化视图聚合数据数据库视图 date

文章转载自ClickHouseInc，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

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

评论