简介
在现实世界中，数据不仅需要存储，还需要处理。处理通常在应用程序端使用 ClickHouse 的一个可用库完成。但是，也有一些关键的处理点可以转移到 ClickHouse 中，以提高数据的性能和可管理性。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
此查询在 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 修饰符以确保汇总引擎返回汇总后的点击次数，而不是单个、未合并的行。
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 结合起来。
为了优化存储空间，我们还可以显式声明列类型以确保 架构是最优的。假设我们只想存储来自 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 倍。
这里需要注意的是，只有当最终行数减少至少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 │ └────────────┴──────┴───────────────────────────────┴──────┘
在生产环境中创建物化视图
当源数据到达时，新数据会自动添加到物化视图的目标表中。但是，为了在生产环境中使用现有数据填充物化视图，我们需要遵循一些简单的步骤。
- 暂停写入源表。
- 创建物化视图。
- 使用源表中的数据填充目标表。
- 重新开始写入源表。
或者，我们可以在创建物化视图时使用将来的某个时间点。
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。
假设我们有一个包含我们wikistat数据集的页面标题的表。
CREATE TABLE wikistat_titles ( `path` String, `title` String ) ENGINE = MergeTree ORDER BY path
此表具有与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与物化视图一起使用。如果不需要聚合或过滤，请考虑将物化列作为快速替代方案。
