CREATE VIEW

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

普通视图

语法

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [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] [TO[db.]name] [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查询结果中不存在某些列名，即使该列不是可空的，ClickHouse 也会使用默认值。安全的做法是在使用物化视图时为每一列添加别名。

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

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

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

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

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

注意

鉴于POPULATE的工作方式类似于CREATE TABLE ... AS SELECT ...，它有一些限制

• 它不支持复制数据库
• 它不支持 ClickHouse 云

可以使用单独的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
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，则第一次刷新将根据计划进行。

在复制数据库中

如果可刷新的物化视图位于复制数据库中，则副本之间会相互协调，以便在每次计划时间只有一个副本执行刷新。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性。例如，如果表具有DEPENDS ON，则执行不带DEPENDS ON的MODIFY REFRESH将删除依赖项。

其他操作

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

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

要等待刷新完成，请使用SYSTEM WAIT VIEW。特别是，在创建视图后等待初始刷新很有用。

注意

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

窗口视图[实验性]

信息

这是一个实验性功能，在将来的版本中可能会以不兼容的方式更改。使用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 构建可观测性解决方案 - 第 2 部分 - 追踪