CREATE VIEW

创建新的视图。视图可以是 普通视图、物化视图、可刷新物化视图 和 窗口视图（可刷新物化视图和窗口视图是实验性功能）。

普通视图

语法

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [(alias1 [, alias2 ...])] [ON CLUSTER cluster_name]
[DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER | NONE }]
AS SELECT ...
[COMMENT 'comment']

普通视图不存储任何数据。它们仅在每次访问时从另一个表执行读取操作。换句话说，普通视图只不过是一个已保存的查询。从视图读取时，此保存的查询用作 FROM 子句中的子查询。

例如，假设您创建了一个视图

CREATE VIEW view AS SELECT ...

并编写了一个查询

SELECT a, b, c FROM view

此查询完全等效于使用子查询

SELECT a, b, c FROM (SELECT ...)

参数化视图

参数化视图类似于普通视图，但可以使用未立即解析的参数创建。这些视图可以与表函数一起使用，表函数将视图的名称指定为函数名称，并将参数值指定为其参数。

CREATE VIEW view AS SELECT * FROM TABLE WHERE Column1={column1:datatype1} and Column2={column2:datatype2} ...

以上操作为表创建了一个视图，该视图可以通过替换参数用作表函数，如下所示。

SELECT * FROM view(column1=value1, column2=value2 ...)

物化视图

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster_name] [TO[db.]name [(columns)]] [ENGINE = engine] [POPULATE]
[DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER | NONE }]
AS SELECT ...
[COMMENT 'comment']

提示

这是一个关于使用 物化视图 的逐步指南。

物化视图存储由相应的 SELECT 查询转换的数据。

创建不带 TO [db].[table] 的物化视图时，您必须指定 ENGINE – 用于存储数据的表引擎。

创建带 TO [db].[table] 的物化视图时，您也不能使用 POPULATE。

物化视图的实现方式如下：当将数据插入到 SELECT 中指定的表时，部分插入的数据将通过此 SELECT 查询进行转换，结果将插入到视图中。

注意

ClickHouse 中的物化视图在插入到目标表期间使用列名而不是列顺序。如果某些列名在 SELECT 查询结果中不存在，即使该列不是 Nullable，ClickHouse 也会使用默认值。一种安全的做法是在使用物化视图时为每一列添加别名。

ClickHouse 中的物化视图更像是插入触发器。如果视图查询中存在一些聚合，则仅将其应用于新插入的数据批次。对源表现有数据的任何更改（如更新、删除、删除分区等）都不会更改物化视图。

在发生错误的情况下，ClickHouse 中的物化视图不具有确定性行为。这意味着已写入的块将保留在目标表中，但错误之后的所有块将不会。

默认情况下，如果推送到其中一个视图失败，则 INSERT 查询也会失败，并且某些块可能不会写入目标表。可以使用 materialized_views_ignore_errors 设置（您应该为 INSERT 查询设置它）更改此行为，如果您将 materialized_views_ignore_errors=true 设置为 true，则在推送到视图时发生的任何错误都将被忽略，并且所有块都将写入目标表。

另请注意，对于 system.*_log 表，materialized_views_ignore_errors 默认设置为 true。

如果您指定 POPULATE，则现有表数据将在创建视图时插入到视图中，就像执行 CREATE TABLE ... AS SELECT ... 一样。否则，查询仅包含在创建视图后插入表中的数据。我们不建议使用 POPULATE，因为在视图创建期间插入表中的数据将不会插入到视图中。

注意

鉴于 POPULATE 的工作方式类似于 CREATE TABLE ... AS SELECT ...，它具有局限性

• Replicated 数据库不支持它
• ClickHouse Cloud 中不支持它

相反，可以使用单独的 INSERT ... SELECT。

SELECT 查询可以包含 DISTINCT、GROUP BY、ORDER BY、LIMIT。请注意，相应的转换是在每个插入数据块上独立执行的。例如，如果设置了 GROUP BY，则在插入期间聚合数据，但仅在单个插入数据包内聚合。数据不会进一步聚合。例外情况是使用独立执行数据聚合的 ENGINE，例如 SummingMergeTree。

对物化视图执行 ALTER 查询具有局限性，例如，您无法更新 SELECT 查询，因此这可能很不方便。如果物化视图使用 TO [db.]name 构造，您可以 DETACH 视图，为目标表运行 ALTER，然后 ATTACH 先前分离的 (DETACH) 视图。

请注意，物化视图受 optimize_on_insert 设置的影响。数据在插入视图之前合并。

视图看起来与普通表相同。例如，它们列在 SHOW TABLES 查询的结果中。

要删除视图，请使用 DROP VIEW。虽然 DROP TABLE 也适用于 VIEW。

SQL 安全

DEFINER 和 SQL SECURITY 允许您指定在执行视图底层查询时要使用的 ClickHouse 用户。SQL SECURITY 具有三个合法值：DEFINER、INVOKER 或 NONE。您可以在 DEFINER 子句中指定任何现有用户或 CURRENT_USER。

下表将解释哪些用户需要哪些权限才能从视图中选择。请注意，无论 SQL 安全选项如何，在每种情况下，仍然需要具有 GRANT SELECT ON <view> 才能从中读取。

SQL 安全选项	视图	物化视图
DEFINER alice	alice 必须对视图的源表具有 SELECT 授权。	alice 必须对视图的源表具有 SELECT 授权，并对视图的目标表具有 INSERT 授权。
INVOKER	用户必须对视图的源表具有 SELECT 授权。	物化视图不能指定 SQL SECURITY INVOKER。
NONE	-	-

注意

SQL SECURITY NONE 是一个已弃用的选项。任何有权创建具有 SQL SECURITY NONE 的视图的用户都将能够执行任何任意查询。因此，需要具有 GRANT ALLOW SQL SECURITY NONE TO <user> 才能创建具有此选项的视图。

如果未指定 DEFINER/SQL SECURITY，则使用默认值

• SQL SECURITY：普通视图为 INVOKER，物化视图为 DEFINER（可通过设置配置）
• DEFINER：CURRENT_USER（可通过设置配置）

如果附加视图时未指定 DEFINER/SQL SECURITY，则物化视图的默认值为 SQL SECURITY NONE，普通视图的默认值为 SQL SECURITY INVOKER。

要更改现有视图的 SQL 安全，请使用

ALTER TABLE MODIFY SQL SECURITY { DEFINER | INVOKER | NONE } [DEFINER = { user | CURRENT_USER }]

示例

CREATE VIEW test_view
DEFINER = alice SQL SECURITY DEFINER
AS SELECT ...

CREATE VIEW test_view
SQL SECURITY INVOKER
AS SELECT ...

实时视图

已弃用功能

此功能已弃用，将来将被移除。

为了您的方便，旧文档位于 此处

可刷新物化视图

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
REFRESH EVERY|AFTER interval [OFFSET interval]
[RANDOMIZE FOR interval]
[DEPENDS ON [db.]name [, [db.]name [, ...]]]
[SETTINGS name = value [, name = value [, ...]]]
[APPEND]
[TO[db.]name] [(columns)] [ENGINE = engine] 
[EMPTY]
AS SELECT ...
[COMMENT 'comment']

其中 interval 是简单间隔的序列

number SECOND|MINUTE|HOUR|DAY|WEEK|MONTH|YEAR

定期运行相应的查询并将其结果存储在表中。

• 如果查询声明 APPEND，则每次刷新都会将行插入表中，而不会删除现有行。插入不是原子的，就像常规的 INSERT SELECT 一样。
• 否则，每次刷新都会原子性地替换表之前的全部内容。

与常规不可刷新的物化视图的区别

• 没有插入触发器。即，当新数据插入到 SELECT 中指定的表时，它不会自动推送到可刷新物化视图。定期刷新会运行整个查询。
• 对 SELECT 查询没有限制。表函数（例如 url()）、视图、UNION、JOIN 都是允许的。

注意

查询的 REFRESH ... SETTINGS 部分中的设置是刷新设置（例如 refresh_retries），与常规设置（例如 max_threads）不同。常规设置可以使用查询末尾的 SETTINGS 指定。

刷新计划

刷新计划示例

REFRESH EVERY 1 DAY -- every day, at midnight (UTC)
REFRESH EVERY 1 MONTH -- on 1st day of every month, at midnight
REFRESH EVERY 1 MONTH OFFSET 5 DAY 2 HOUR -- on 6th day of every month, at 2:00 am
REFRESH EVERY 2 WEEK OFFSET 5 DAY 15 HOUR 10 MINUTE -- every other Saturday, at 3:10 pm
REFRESH EVERY 30 MINUTE -- at 00:00, 00:30, 01:00, 01:30, etc
REFRESH AFTER 30 MINUTE -- 30 minutes after the previous refresh completes, no alignment with time of day
-- REFRESH AFTER 1 HOUR OFFSET 1 MINUTE -- syntax error, OFFSET is not allowed with AFTER
REFRESH EVERY 1 WEEK 2 DAYS -- every 9 days, not on any particular day of the week or month;
                            -- specifically, when day number (since 1969-12-29) is divisible by 9
REFRESH EVERY 5 MONTHS -- every 5 months, different months each year (as 12 is not divisible by 5);
                       -- specifically, when month number (since 1970-01) is divisible by 5

RANDOMIZE FOR 随机调整每次刷新的时间，例如

REFRESH EVERY 1 DAY OFFSET 2 HOUR RANDOMIZE FOR 1 HOUR -- every day at random time between 01:30 and 02:30

对于给定的视图，一次最多只能运行一次刷新。例如，如果 REFRESH EVERY 1 MINUTE 的视图需要 2 分钟才能刷新，则它只会每 2 分钟刷新一次。如果它随后变得更快并开始在 10 秒内刷新，它将恢复为每分钟刷新一次。（特别是，它不会每 10 秒刷新一次以赶上错过刷新的积压 - 没有这样的积压。）

此外，除非在 CREATE 查询中指定了 EMPTY，否则在创建物化视图后立即启动刷新。如果指定了 EMPTY，则第一次刷新将按照计划进行。

在 Replicated DB 中

如果可刷新物化视图位于 Replicated 数据库 中，则副本彼此协调，以便每次计划时间只有一个副本执行刷新。ReplicatedMergeTree 表引擎是必需的，以便所有副本都能看到刷新产生的数据。

在 APPEND 模式下，可以使用 SETTINGS all_replicas = 1 禁用协调。这使得副本独立于彼此进行刷新。在这种情况下，不需要 ReplicatedMergeTree。

在非 APPEND 模式下，仅支持协调刷新。对于非协调刷新，请使用 Atomic 数据库和 CREATE ... ON CLUSTER 查询在所有副本上创建可刷新物化视图。

协调通过 Keeper 完成。znode 路径由 default_replica_path 服务器设置确定。

依赖关系

DEPENDS ON 同步不同表的刷新。例如，假设有两个可刷新物化视图的链

CREATE MATERIALIZED VIEW source REFRESH EVERY 1 DAY AS SELECT * FROM url(...)
CREATE MATERIALIZED VIEW destination REFRESH EVERY 1 DAY AS SELECT ... FROM source

如果没有 DEPENDS ON，两个视图都将在午夜开始刷新，并且 destination 通常会在 source 中看到昨天的数据。如果我们添加依赖项

CREATE MATERIALIZED VIEW destination REFRESH EVERY 1 DAY DEPENDS ON source AS SELECT ... FROM source

那么 destination 的刷新将仅在 source 的当日刷新完成后才开始，因此 destination 将基于最新数据。

或者，可以使用以下方法实现相同的结果

CREATE MATERIALIZED VIEW destination REFRESH AFTER 1 HOUR DEPENDS ON source AS SELECT ... FROM source

其中 1 HOUR 可以是小于 source 刷新周期的任何持续时间。依赖表刷新的频率不会高于其任何依赖项。这是一种设置可刷新视图链的有效方法，而无需多次指定实际刷新周期。

更多示例

• REFRESH EVERY 1 DAY OFFSET 10 MINUTE (destination) 依赖于 REFRESH EVERY 1 DAY (source)
  如果 source 刷新时间超过 10 分钟，destination 将等待它。
• REFRESH EVERY 1 DAY OFFSET 1 HOUR 依赖于 REFRESH EVERY 1 DAY OFFSET 23 HOUR
  与上面类似，即使相应的刷新发生在不同的日历日。destination 在 X+1 天的刷新将等待 source 在 X 天的刷新（如果它需要超过 2 小时）。
• REFRESH EVERY 2 HOUR 依赖于 REFRESH EVERY 1 HOUR
  每隔一小时，2 小时的刷新会在 1 小时的刷新之后发生，例如，午夜刷新之后，然后在凌晨 2 点刷新之后等。
• REFRESH EVERY 1 MINUTE 依赖于 REFRESH EVERY 2 HOUR
REFRESH AFTER 1 MINUTE 依赖于 REFRESH EVERY 2 HOUR
REFRESH AFTER 1 MINUTE 依赖于 REFRESH AFTER 2 HOUR
destination 在每次 source 刷新后刷新一次，即每 2 小时刷新一次。1 MINUTE 实际上被忽略了。
• REFRESH AFTER 1 HOUR 依赖于 REFRESH AFTER 1 HOUR
  目前不建议这样做。

注意

DEPENDS ON 仅在可刷新物化视图之间有效。在 DEPENDS ON 列表中列出常规表将阻止视图永远刷新（可以使用 ALTER 删除依赖项，请参见下文）。

设置

可用的刷新设置

• refresh_retries - 如果刷新查询因异常而失败，则重试多少次。如果所有重试都失败，则跳到下一个计划刷新时间。0 表示不重试，-1 表示无限次重试。默认值：0。
• refresh_retry_initial_backoff_ms - 如果 refresh_retries 不为零，则首次重试之前的延迟。每次后续重试都会使延迟加倍，直到达到 refresh_retry_max_backoff_ms。默认值：100 毫秒。
• refresh_retry_max_backoff_ms - 限制刷新尝试之间延迟的指数增长。默认值：60000 毫秒（1 分钟）。

更改刷新参数

要更改刷新参数

ALTER TABLE [db.]name MODIFY REFRESH EVERY|AFTER ... [RANDOMIZE FOR ...] [DEPENDS ON ...] [SETTINGS ...]

注意

这将一次替换所有刷新参数：计划、依赖项、设置和 APPEND-ness。例如，如果表具有 DEPENDS ON，则执行不带 DEPENDS ON 的 MODIFY REFRESH 将删除依赖项。

其他操作

所有可刷新物化视图的状态都可在表 system.view_refreshes 中找到。特别是，它包含刷新进度（如果正在运行）、上次和下次刷新时间、刷新失败时的异常消息。

要手动停止、启动、触发或取消刷新，请使用 SYSTEM STOP|START|REFRESH|WAIT|CANCEL VIEW。

要等待刷新完成，请使用 SYSTEM WAIT VIEW。特别适用于等待创建视图后的初始刷新。

注意

有趣的事实：刷新查询可以从正在刷新的视图中读取数据，看到数据的刷新前版本。这意味着您可以实现 Conway 的生命游戏：https://pastila.nl/?00021a4b/d6156ff819c83d490ad2dcec05676865#O0LGWTO7maUQIA4AcGUtlA==

窗口视图

实验性功能。 了解更多。

ClickHouse Cloud 中不支持

信息

这是一个实验性功能，在未来的版本中可能会以向后不兼容的方式进行更改。使用 allow_experimental_window_view 设置启用窗口视图和 WATCH 查询的使用。输入命令 set allow_experimental_window_view = 1。

CREATE WINDOW VIEW [IF NOT EXISTS] [db.]table_name [TO [db.]table_name] [INNER ENGINE engine] [ENGINE engine] [WATERMARK strategy] [ALLOWED_LATENESS interval_function] [POPULATE]
AS SELECT ...
GROUP BY time_window_function
[COMMENT 'comment']

窗口视图可以按时间窗口聚合数据，并在窗口准备好触发时输出结果。它将部分聚合结果存储在内部（或指定的）表中以减少延迟，并且可以使用 WATCH 查询将处理结果推送到指定的表或推送通知。

创建窗口视图类似于创建 MATERIALIZED VIEW。窗口视图需要内部存储引擎来存储中间数据。可以使用 INNER ENGINE 子句指定内部存储，窗口视图将使用 AggregatingMergeTree 作为默认内部引擎。

创建不带 TO [db].[table] 的窗口视图时，您必须指定 ENGINE – 用于存储数据的表引擎。

时间窗口函数

时间窗口函数用于获取记录的下限和上限窗口边界。窗口视图需要与时间窗口函数一起使用。

时间属性

窗口视图支持处理时间和事件时间处理。

处理时间允许窗口视图根据本地机器的时间生成结果，默认情况下使用处理时间。它是最直接的时间概念，但不提供确定性。可以通过将时间窗口函数的 time_attr 设置为表列或使用函数 now() 来定义处理时间属性。以下查询创建一个具有处理时间的窗口视图。

CREATE WINDOW VIEW wv AS SELECT count(number), tumbleStart(w_id) as w_start from date GROUP BY tumble(now(), INTERVAL '5' SECOND) as w_id

事件时间是每个单独事件在其生成设备上发生的时间。此时间通常在生成记录时嵌入在记录中。即使在事件乱序或延迟事件的情况下，事件时间处理也允许获得一致的结果。窗口视图通过使用 WATERMARK 语法支持事件时间处理。

窗口视图提供三种水印策略

• STRICTLY_ASCENDING：发出迄今为止观察到的最大时间戳的水印。时间戳小于最大时间戳的行不是延迟的。
• ASCENDING：发出迄今为止观察到的最大时间戳减 1 的水印。时间戳等于和小于最大时间戳的行不是延迟的。
• BOUNDED：WATERMARK=INTERVAL。发出水印，水印是观察到的最大时间戳减去指定的延迟。

以下查询是创建带有 WATERMARK 的窗口视图的示例

CREATE WINDOW VIEW wv WATERMARK=STRICTLY_ASCENDING AS SELECT count(number) FROM date GROUP BY tumble(timestamp, INTERVAL '5' SECOND);
CREATE WINDOW VIEW wv WATERMARK=ASCENDING AS SELECT count(number) FROM date GROUP BY tumble(timestamp, INTERVAL '5' SECOND);
CREATE WINDOW VIEW wv WATERMARK=INTERVAL '3' SECOND AS SELECT count(number) FROM date GROUP BY tumble(timestamp, INTERVAL '5' SECOND);

默认情况下，窗口将在水印到达时触发，并且在水印之后到达的元素将被丢弃。窗口视图通过设置 ALLOWED_LATENESS=INTERVAL 支持延迟事件处理。延迟处理的一个例子是

CREATE WINDOW VIEW test.wv TO test.dst WATERMARK=ASCENDING ALLOWED_LATENESS=INTERVAL '2' SECOND AS SELECT count(a) AS count, tumbleEnd(wid) AS w_end FROM test.mt GROUP BY tumble(timestamp, INTERVAL '5' SECOND) AS wid;

请注意，延迟触发的元素应被视为先前计算的更新结果。窗口视图不会在窗口结束时触发，而是在延迟事件到达时立即触发。因此，它将为同一窗口产生多个输出。用户需要考虑这些重复的结果或对其进行去重。

您可以使用 ALTER TABLE ... MODIFY QUERY 语句修改在窗口视图中指定的 SELECT 查询。当使用或不使用 TO [db.]name 子句时，新 SELECT 查询产生的数据结构应与原始 SELECT 查询相同。请注意，当前窗口中的数据将丢失，因为中间状态无法重用。

监控新窗口

窗口视图支持 WATCH 查询以监控更改，或使用 TO 语法将结果输出到表。

WATCH [db.]window_view
[EVENTS]
[LIMIT n]
[FORMAT format]

WATCH 查询的作用类似于 LIVE VIEW。可以指定 LIMIT 以设置在终止查询之前要接收的更新次数。EVENTS 子句可用于获取 WATCH 查询的简短形式，在该形式中，您将仅获得最新的查询水印，而不是查询结果。

设置

• window_view_clean_interval：窗口视图的清理间隔（以秒为单位），用于释放过时的数据。系统将保留尚未根据系统时间或 WATERMARK 配置完全触发的窗口，其他数据将被删除。
• window_view_heartbeat_interval：心跳间隔（以秒为单位），用于指示 watch 查询处于活动状态。
• wait_for_window_view_fire_signal_timeout：在事件时间处理中，等待窗口视图触发信号的超时时间。

示例

假设我们需要在名为 data 的日志表中每 10 秒钟计算一次点击日志的数量，其表结构为

CREATE TABLE data ( `id` UInt64, `timestamp` DateTime) ENGINE = Memory;

首先，我们创建一个间隔为 10 秒的滚动窗口的窗口视图

CREATE WINDOW VIEW wv as select count(id), tumbleStart(w_id) as window_start from data group by tumble(timestamp, INTERVAL '10' SECOND) as w_id

然后，我们使用 WATCH 查询来获取结果。

WATCH wv

当日志被插入到表 data 时，

INSERT INTO data VALUES(1,now())

WATCH 查询应该按如下方式打印结果

┌─count(id)─┬────────window_start─┐
│         1 │ 2020-01-14 16:56:40 │
└───────────┴─────────────────────┘

或者，我们可以使用 TO 语法将输出附加到另一个表。

CREATE WINDOW VIEW wv TO dst AS SELECT count(id), tumbleStart(w_id) as window_start FROM data GROUP BY tumble(timestamp, INTERVAL '10' SECOND) as w_id

更多示例可以在 ClickHouse 的有状态测试中找到（它们在那里被命名为 *window_view*）。

窗口视图用法

窗口视图在以下场景中非常有用

• 监控：按时间聚合和计算指标日志，并将结果输出到目标表。仪表板可以使用目标表作为源表。
• 分析：自动聚合和预处理时间窗口中的数据。这在分析大量日志时非常有用。预处理消除了多个查询中的重复计算，并减少了查询延迟。

相关内容

• 博客： 在 ClickHouse 中使用时间序列数据
• 博客： 使用 ClickHouse 构建可观测性解决方案 - 第二部分 - Traces