跳到主要内容
跳到主要内容

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 查询可以包含 DISTINCTGROUP BYORDER BYLIMIT。请注意,相应的转换是在每个插入数据块上独立执行的。例如,如果设置了 GROUP BY,则在插入期间聚合数据,但仅在单个插入数据包内聚合。数据不会进一步聚合。例外情况是使用独立执行数据聚合的 ENGINE,例如 SummingMergeTree

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

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

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

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

SQL 安全

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

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

SQL 安全选项视图物化视图
DEFINER alicealice 必须对视图的源表具有 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 ONMODIFY 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*)。

窗口视图用法

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

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