博客 / 工程

在 ClickHouse 中使用物化视图

Denys Golotiuk

2023 年 1 月 19 日 - 17 分钟阅读

materialized views blog.png

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

如果您想了解更多关于物化视图的信息，我们在此提供免费的按需培训课程 here。

物化视图是一个特殊的触发器，它将 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

此查询在 ClickHouse Cloud 开发服务上需要 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，因为我们希望为每个日期/项目对汇总点击量值，
在 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 只用了 3 毫秒，而原始查询则用了 15 秒。另请注意，我们仍然需要在此处使用 GROUP BY，因为 SummingMergeTree 引擎是异步的（这节省了资源并减少了对查询处理的影响），并且某些值可能未计算。

可以使用 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

与其他任何表一样，物化视图表的所有元数据都可以在系统数据库中找到。例如，要获取其在磁盘上的大小，我们可以执行以下操作


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 修饰符以确保 summing 引擎返回汇总的点击量，而不是单独的、未合并的行


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

请注意，我们的原始数据已按小时聚合。

让我们使用物化视图存储这些聚合结果，以便更快地检索。聚合结果使用状态组合器定义。状态组合器要求 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 结合使用。

为了优化存储空间，我们还可以显式声明列类型，以确保 schema 是最优的。假设我们只想为 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
10 亿行	约 2700 万行

此处的重要提示是，仅当结果行数减少至少 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.)

要实现验证过滤，我们需要 2 个表 - 一个包含所有数据的表和一个仅包含干净数据的表。物化视图的目标表将充当最终的干净数据表，而源表将是临时的。我们可以根据 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 │
└────────────┴──────┴───────────────────────────────┴──────┘

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

暂停写入源表。
创建物化视图。
用源表中的数据填充目标表。
重新开始写入源表。

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


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`

由于物化视图使用 SQL 查询的结果，因此我们可以使用 JOIN 以及任何其他 SQL 功能。但应谨慎使用 JOIN。

假设我们有一个包含 wikistat 数据集的页面标题的表


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

此表具有与路径关联的页面标题


SELECT *
FROM wikistat_titles

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

我们现在可以创建一个物化视图，将 wikistat_titles 表中的 title 连接到 path 值


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.

这是因为物化视图仅在其源表接收到插入时才会触发。它只是源表上的一个触发器，对连接表一无所知。请注意，这不仅适用于连接查询，而且在使用物化视图的 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 与物化视图一起使用。当不需要聚合或过滤时，请考虑将物化列作为快速替代方案。

分享这篇文章