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
,则使用默认值
如果附加视图时未指定 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==
窗口视图
这是一个实验性功能,在未来的版本中可能会以向后不兼容的方式进行更改。使用 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*
)。
窗口视图用法
窗口视图在以下场景中非常有用
- 监控:按时间聚合和计算指标日志,并将结果输出到目标表。仪表板可以使用目标表作为源表。
- 分析:自动聚合和预处理时间窗口中的数据。这在分析大量日志时非常有用。预处理消除了多个查询中的重复计算,并减少了查询延迟。