物化视图

物化视图允许用户将计算成本从查询时间转移到插入时间，从而实现更快的 SELECT 查询。

与 Postgres 等事务型数据库不同，ClickHouse 物化视图只是一个在数据块插入到表中时运行查询的触发器。此查询的结果被插入到第二个“目标”表中。如果插入更多行，结果将再次发送到目标表，其中中间结果将被更新和合并。此合并结果相当于在所有原始数据上运行查询。

物化视图的主要动机是，插入到目标表中的结果表示对行进行聚合、过滤或转换的结果。这些结果通常将是原始数据的更小表示（在聚合的情况下为部分草图）。这与从目标表读取结果的最终查询很简单相结合，确保查询时间比在原始数据上执行相同计算更快，从而将计算（以及查询延迟）从查询时间转移到插入时间。

ClickHouse 中的物化视图会在数据流入它们所基于的表时实时更新，更像是不断更新的索引。这与其他数据库形成对比，在其他数据库中，物化视图通常是必须刷新查询的静态快照（类似于 ClickHouse 的可刷新物化视图）。

示例

假设我们想获取帖子每天的赞成票和反对票数量。

由于 ClickHouse 中有 toStartOfDay 函数，因此这在 ClickHouse 中是一个相当简单的查询

SELECT toStartOfDay(CreationDate) AS day,
       countIf(VoteTypeId = 2) AS UpVotes,
       countIf(VoteTypeId = 3) AS DownVotes
FROM votes
GROUP BY day
ORDER BY day ASC
LIMIT 10

┌─────────────────day─┬─UpVotes─┬─DownVotes─┐
│ 2008-07-31 00:00:00 │     6 │         0 │
│ 2008-08-01 00:00:00 │     182 │       50 │
│ 2008-08-02 00:00:00 │     436 │       107 │
│ 2008-08-03 00:00:00 │     564 │       100 │
│ 2008-08-04 00:00:00 │ 1306 │      259 │
│ 2008-08-05 00:00:00 │ 1368 │      269 │
│ 2008-08-06 00:00:00 │ 1701 │      211 │
│ 2008-08-07 00:00:00 │ 1544 │      211 │
│ 2008-08-08 00:00:00 │ 1241 │      212 │
│ 2008-08-09 00:00:00 │     576 │       46 │
└─────────────────────┴─────────┴───────────┘

10 rows in set. Elapsed: 0.133 sec. Processed 238.98 million rows, 2.15 GB (1.79 billion rows/s., 16.14 GB/s.)
Peak memory usage: 363.22 MiB.

由于 ClickHouse，此查询已经很快，但我们还能做得更好吗？

如果我们想使用物化视图在插入时计算此值，我们需要一个表来接收结果。此表应该每天只保留一行。如果收到对现有日期的更新，其他列应该合并到现有日期的行中。为了使增量状态的这种合并发生，必须为其他列存储部分状态。

这需要 ClickHouse 中的一种特殊引擎类型：SummingMergeTree。这将用包含数值列的求和值的单行替换所有具有相同排序键的行。下表将合并任何具有相同日期的行，并将所有数值列求和

CREATE TABLE up_down_votes_per_day
(
  `Day` Date,
  `UpVotes` UInt32,
  `DownVotes` UInt32
)
ENGINE = SummingMergeTree
ORDER BY Day

为了演示我们的物化视图，假设我们的投票表为空，尚未收到任何数据。我们的物化视图在数据插入 votes 时对上述 SELECT 进行操作，并将结果发送到 up_down_votes_per_day

CREATE MATERIALIZED VIEW up_down_votes_per_day_mv TO up_down_votes_per_day AS
SELECT toStartOfDay(CreationDate)::Date AS Day,
       countIf(VoteTypeId = 2) AS UpVotes,
       countIf(VoteTypeId = 3) AS DownVotes
FROM votes
GROUP BY Day

这里的 TO 子句是关键，它表示结果将发送到哪里，例如 up_down_votes_per_day。

我们可以从我们之前的插入重新填充我们的投票表

INSERT INTO votes SELECT toUInt32(Id) AS Id, toInt32(PostId) AS PostId, VoteTypeId, CreationDate, UserId, BountyAmount
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/votes.parquet')

0 rows in set. Elapsed: 111.964 sec. Processed 477.97 million rows, 3.89 GB (4.27 million rows/s., 34.71 MB/s.)
Peak memory usage: 283.49 MiB.

完成后，我们可以确认我们的 up_down_votes_per_day 的大小 - 我们应该每天有一行

SELECT count()
FROM up_down_votes_per_day
FINAL

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

我们实际上将行数从 2.38 亿（在 votes 中）减少到 5000，方法是存储我们查询的结果。然而，这里关键的是，如果新投票被插入到 votes 表中，新值将被发送到它们各自日期的 up_down_votes_per_day，在那里它们将被自动异步地合并到后台 - 每天只保留一行。因此，up_down_votes_per_day 将始终既小又最新。

由于行的合并是异步的，当用户查询时，每天可能有多个投票。为了确保在查询时合并任何未决行，我们有两个选择

在表名上使用 FINAL 修饰符。我们在上面的计数查询中是这样做的。
按最终表中使用的排序键（即 CreationDate）聚合，并将指标求和。通常，这更有效且更灵活（该表可用于其他目的），但对于某些查询，前者可能更简单。我们在下面展示了这两种方法

SELECT
    Day,
    UpVotes,
    DownVotes
FROM up_down_votes_per_day
FINAL
ORDER BY Day ASC
LIMIT 10

10 rows in set. Elapsed: 0.004 sec. Processed 8.97 thousand rows, 89.68 KB (2.09 million rows/s., 20.89 MB/s.)
Peak memory usage: 289.75 KiB.

SELECT Day, sum(UpVotes) AS UpVotes, sum(DownVotes) AS DownVotes
FROM up_down_votes_per_day
GROUP BY Day
ORDER BY Day ASC
LIMIT 10
┌────────Day─┬─UpVotes─┬─DownVotes─┐
│ 2008-07-31 │      6 │         0 │
│ 2008-08-01 │  182 │       50 │
│ 2008-08-02 │  436 │       107 │
│ 2008-08-03 │  564 │       100 │
│ 2008-08-04 │  1306 │      259 │
│ 2008-08-05 │  1368 │      269 │
│ 2008-08-06 │  1701 │      211 │
│ 2008-08-07 │  1544 │      211 │
│ 2008-08-08 │  1241 │      212 │
│ 2008-08-09 │  576 │       46 │
└────────────┴─────────┴───────────┘

10 rows in set. Elapsed: 0.010 sec. Processed 8.97 thousand rows, 89.68 KB (907.32 thousand rows/s., 9.07 MB/s.)
Peak memory usage: 567.61 KiB.

这将我们的查询速度从 0.133 秒提高到 0.004 秒 - 提高了 25 倍以上！

更复杂的示例

上面的示例使用物化视图来计算和维护每天两个总和。总和代表最简单的聚合形式来维护部分状态 - 我们可以在它们到达时将新值添加到现有值中。但是，ClickHouse 物化视图可用于任何聚合类型。

假设我们希望计算每天的帖子的一些统计信息：Score 的 99.9 百分位数和 CommentCount 的平均值。计算此值的查询可能如下所示

SELECT
    toStartOfDay(CreationDate) AS Day,
    quantile(0.999)(Score) AS Score_99th,
    avg(CommentCount) AS AvgCommentCount
FROM posts
GROUP BY Day
ORDER BY Day DESC
LIMIT 10

    ┌─────────────────Day─┬────────Score_99th─┬────AvgCommentCount─┐
 1. │ 2024-03-31 00:00:00 │  5.23700000000008 │ 1.3429811866859624 │
 2. │ 2024-03-30 00:00:00 │                 5 │ 1.3097158891616976 │
 3. │ 2024-03-29 00:00:00 │  5.78899999999976 │ 1.2827635327635327 │
 4. │ 2024-03-28 00:00:00 │                 7 │  1.277746158224246 │
 5. │ 2024-03-27 00:00:00 │ 5.738999999999578 │ 1.2113264918282023 │
 6. │ 2024-03-26 00:00:00 │                 6 │ 1.3097536945812809 │
 7. │ 2024-03-25 00:00:00 │                 6 │ 1.2836721018539201 │
 8. │ 2024-03-24 00:00:00 │ 5.278999999999996 │ 1.2931667891256429 │
 9. │ 2024-03-23 00:00:00 │ 6.253000000000156 │  1.334061135371179 │
10. │ 2024-03-22 00:00:00 │ 9.310999999999694 │ 1.2388059701492538 │
    └─────────────────────┴───────────────────┴────────────────────┘

10 rows in set. Elapsed: 0.113 sec. Processed 59.82 million rows, 777.65 MB (528.48 million rows/s., 6.87 GB/s.)
Peak memory usage: 658.84 MiB.

与之前一样，我们可以创建一个物化视图，该视图在将新帖子插入到我们的 posts 表中时执行上述查询。

为了举例说明，并避免从 S3 加载帖子数据，我们将创建一个与 posts 相同模式的副本表 posts_null。但是，此表将不存储任何数据，而只是在插入行时由物化视图使用。为了防止存储数据，我们可以使用 Null 表引擎类型。

CREATE TABLE posts_null AS posts ENGINE = Null

Null 表引擎是一个强大的优化 - 可以将其视为 /dev/null。我们的物化视图将在我们的 posts_null 表在插入时接收行时计算并存储我们的摘要统计信息 - 它只是一个触发器。但是，原始数据不会被存储。虽然在我们的例子中，我们可能仍然希望存储原始帖子，但这种方法可以用于计算聚合，同时避免原始数据的存储开销。

因此，物化视图变为

CREATE MATERIALIZED VIEW post_stats_mv TO post_stats_per_day AS
       SELECT toStartOfDay(CreationDate) AS Day,
       quantileState(0.999)(Score) AS Score_quantiles,
       avgState(CommentCount) AS AvgCommentCount
FROM posts_null
GROUP BY Day

请注意，我们在聚合函数的末尾附加了后缀 State。这将确保返回函数的聚合状态，而不是最终结果。这将包含其他信息，以允许此部分状态与其他状态合并。例如，在平均值的情况下，这将包括列的计数和总和。

部分聚合状态对于计算正确的结果是必要的。例如，为了计算平均值，简单地平均子范围的平均值会产生不正确的结果。

现在我们创建了此视图的目标表 post_stats_per_day，它存储这些部分聚合状态

CREATE TABLE post_stats_per_day
(
  `Day` Date,
  `Score_quantiles` AggregateFunction(quantile(0.999), Int32),
  `AvgCommentCount` AggregateFunction(avg, UInt8)
)
ENGINE = AggregatingMergeTree
ORDER BY Day

虽然之前 SummingMergeTree 足以存储计数，但我们需要更高级的引擎类型来使用其他函数：AggregatingMergeTree。为了确保 ClickHouse 知道将存储聚合状态，我们将 Score_quantiles 和 AvgCommentCount 定义为类型 AggregateFunction，指定部分状态的函数源及其源列的类型。与 SummingMergeTree 一样，具有相同 ORDER BY 键值的行将被合并（在上面的示例中为 Day）。

为了通过我们的物化视图填充我们的 post_stats_per_day，我们可以简单地将 posts 中的所有行插入到 posts_null 中

INSERT INTO posts_null SELECT * FROM posts

0 rows in set. Elapsed: 13.329 sec. Processed 119.64 million rows, 76.99 GB (8.98 million rows/s., 5.78 GB/s.)

在生产环境中，您可能希望将物化视图附加到 posts 表中。我们在这里使用 posts_null 来演示空表。

我们的最终查询需要为我们的函数使用 Merge 后缀（因为列存储部分聚合状态）

SELECT
    Day,
    quantileMerge(0.999)(Score_quantiles),
    avgMerge(AvgCommentCount)
FROM post_stats_per_day
GROUP BY Day
ORDER BY Day DESC
LIMIT 10

请注意，我们在这里使用 GROUP BY 而不是使用 FINAL。

其他应用

上面主要侧重于使用物化视图来增量更新数据的部分聚合，从而将计算从查询时间转移到插入时间。除了这种常见用例之外，物化视图还有许多其他应用。

过滤和转换

在某些情况下，我们可能希望仅在插入时插入部分行和列。在这种情况下，我们的posts_null表可以接收插入，并使用SELECT查询在插入到posts表之前筛选行。例如，假设我们希望转换posts表中的Tags列。它包含一个用管道分隔的标签名称列表。通过将它们转换为数组，我们可以更容易地按单个标签值进行聚合。

我们可以在运行INSERT INTO SELECT时执行此转换。物化视图允许我们在 ClickHouse DDL 中封装此逻辑，并使我们的INSERT保持简单，并将转换应用于任何新行。

此转换的物化视图如下所示

CREATE MATERIALIZED VIEW posts_mv TO posts AS
    SELECT * EXCEPT Tags, arrayFilter(t -> (t != ''), splitByChar('|', Tags)) as Tags FROM posts_null

查找表

用户在选择 ClickHouse 排序键时应考虑其访问模式，其中经常用于筛选和聚合子句的列。这对于用户具有更多样化的访问模式且无法封装在一个单一列集中的场景来说可能具有限制性。例如，考虑以下comments表

CREATE TABLE comments
(
    `Id` UInt32,
    `PostId` UInt32,
    `Score` UInt16,
    `Text` String,
    `CreationDate` DateTime64(3, 'UTC'),
    `UserId` Int32,
    `UserDisplayName` LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY PostId

0 rows in set. Elapsed: 46.357 sec. Processed 90.38 million rows, 11.14 GB (1.95 million rows/s., 240.22 MB/s.)

此处的排序键针对按PostId筛选的查询优化了表。

假设用户希望按特定UserId进行筛选并计算其平均Score

SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

   ┌──────────avg(Score)─┐
1. │ 0.18181818181818182 │
   └─────────────────────┘

1 row in set. Elapsed: 0.778 sec. Processed 90.38 million rows, 361.59 MB (116.16 million rows/s., 464.74 MB/s.)
Peak memory usage: 217.08 MiB.

虽然速度很快（ClickHouse 的数据量很小），但我们可以从处理的行数（9038 万行）看出，这需要进行全表扫描。对于更大的数据集，我们可以使用物化视图来查找用于筛选列UserId的排序键值PostId。然后，可以使用这些值执行有效的查找。

在本例中，我们的物化视图可以非常简单，仅在插入时从comments中选择PostId和UserId。然后，将这些结果发送到按UserId排序的表comments_posts_users。我们在下面创建了Comments表的空版本，并使用它来填充我们的视图和comments_posts_users表

CREATE TABLE comments_posts_users (
  PostId UInt32,
  UserId Int32
) ENGINE = MergeTree ORDER BY UserId 


CREATE TABLE comments_null AS comments
ENGINE = Null

CREATE MATERIALIZED VIEW comments_posts_users_mv TO comments_posts_users AS
SELECT PostId, UserId FROM comments_null

INSERT INTO comments_null SELECT * FROM comments

0 rows in set. Elapsed: 5.163 sec. Processed 90.38 million rows, 17.25 GB (17.51 million rows/s., 3.34 GB/s.)

我们现在可以在子查询中使用此视图来加速我们之前的查询

SELECT avg(Score)
FROM comments
WHERE PostId IN (
    SELECT PostId
    FROM comments_posts_users
    WHERE UserId = 8592047
) AND UserId = 8592047


   ┌──────────avg(Score)─┐
1. │ 0.18181818181818182 │
   └─────────────────────┘

1 row in set. Elapsed: 0.012 sec. Processed 88.61 thousand rows, 771.37 KB (7.09 million rows/s., 61.73 MB/s.)

链接

物化视图可以链接，从而建立复杂的流程。有关实际示例，我们推荐这篇文章博客文章.

物化视图

示例​

更复杂的示例​

其他应用​

过滤和转换​

查找表​

链接​

示例

更复杂的示例

其他应用

过滤和转换

查找表

链接