ClickHouse 视图(View)

ClickHouse支持创建普通视图(normal view)、物化视图(materialized view)、实时视图(live view)和窗口视图(window view)，其中实时视图和窗口视图目前还是试验功能，不能保证稳定性，所以请不要在生产环境中使用它们。

1. Normal View

普通视图和其他数据库中的视图一样，不存储任何数据，只是一个子查询语句。当从视图读取数据时，实际是查询创建视图的子查询语句（创建视图的查询被用作from子句中的子查询）。创建语句如下：

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] AS SELECT ...
1

下面的两个操作效果是相同的：

CREATE VIEW view AS SELECT ...;
SELECT a, b, c FROM view;
1
2

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

2. Materialized View

物化视图和普通视图最大的区别是物化视图实际存储了一份数据。用户查询的时候和表没有区别，更像是一张时刻在预计算的表。在创建物化视图的时候也需要定义存储引擎。

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] [TO[db.]name] 
[ENGINE = engine] [POPULATE] AS SELECT ...
1
2

1. 创建物化视图有两种方式：直接创建，即创建物化视图时不带TO [db].[table]，且必须指定ENGINE用于存储数据的表引擎，和建表类似，这种方法ClickHouse会创建一个隐藏的目标表来保存视图数据，可以通过SHOW TABLES查看；间接创建，即使用TO [db].[table]创建物化视图，[db].[table]必须是一张已经存在的表，用来保存视图数据，此时创建物化视图相当于在表上面附加了一个物化视图。需要注意，间接创建不能使用POPULATE关键字。
2. 物化视图存储由CREATE语句中的SELECT查询转换的数据，SELECT语句可以包含DISTINCT、 GROUP BY、ORDER BY、LIMIT，如果是聚合操作，建议使用SummingMergeTree等引擎表。
3. POPULATE关键字定义了物化视图的初始更新策略，如果指定了POPULATE，则在创建视图时将SELECT表的存量数据插入视图，就像执行 CREATE TABLE … AS SELECT … 。否则，视图只更新创建视图以后插入到表中的数据。需要注意的是如果定义了POPULATE关键字，在创建视图期间插入表中的数据不会被插入到表中，所以不建议在有数据更新的情况下使用POPULATE。如果一定要同步历史数据，则可以选择没有业务数据更新的窗口期执行。
4. 物化视图只同步插入的数据，对源表数据的任何更改（如更新、删除、删除分区等）都不会更改物化视图数据。
5. 物化视图使用ALTER语句有一些局限，如果创建视图时使用的是TO [db.]name方式，则可以使用DETACH 语句卸载视图，然后执行ALTER语句，再通过ATTACH 语句加载前面卸载的视图。
6. 删除视图可使用 DROP VIEW，虽然 DROP TABLE 也可以删除视图，但是不建议使用。

CREATE MATERIALIZED VIEW order_mv1
ENGINE=SummingMergeTree
PARTITION BY toYYYYMMDD(order_date) ORDER BY (id,order_date)
AS SELECT
id,
order_date,
sum(pay_number) as number,
sum(pay_amount) as amount
FROM order_detail
WHERE order_date > '2021-08-14'
GROUP BY id,order_date;
1
2
3
4
5
6
7
8
9
10
11

物化视图与表一样，也可以指定表引擎、分区键、主键以及设置参数。物化视图的本质是一个流式数据的使用场景，是累计计算的技术，所以要用历史数据做去重这样的分析，在物化视图里面是不太好用的。而且如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源，比如数据带宽占满、存储一下子增加了很多。

3. Live View

实时视图目前还是实验功能，会在未来的版本中正式发布（在旧版本中也无法试用）。所以如果现在想使用实时视图功能，需要设置allow_experimental_live_view = 1 启用live视图和WATCH查询。

CREATE LIVE VIEW [IF NOT EXISTS] [db.]table_name 
[WITH [TIMEOUT [value_in_sec] [AND]] [REFRESH [value_in_sec]]] AS SELECT ...
1
2

实时视图是一种特殊的视图，类似于ZooKeeper中的注册监听和Redis中的发布订阅，能够将一条SQL查询结果作为监控目标，当 Live view 变化时可以及时感知到。

实时视图存储CREATE语句中SELECT查询的结果，并在查询结果更改时进行更新。Live View将部分查询结果缓存在内存中，然后将部分结果与新数据合并以产生最终结果。查询结果以及与新数据相结合所需的部分结果存储在内存中，为重复查询提供了更高的性能。当使用WATCH查询的实时视图结果发生变化时，实时视图可以提供推送通知。

CREATE TABLE mt (x Int8) Engine = MergeTree ORDER BY x;
CREATE LIVE VIEW lv AS SELECT sum(x) FROM mt;
1
2

通过WATCH查看Live view的实时变化：

WATCH lv;
1

在新的会话中插入数据，并观察WATCH窗口的输出：

INSERT INTO mt VALUES (1);
INSERT INTO mt VALUES (2);
INSERT INTO mt VALUES (3);
1
2
3

┌─sum(x)─┬─_version─┐
│      1 │        1 │
└────────┴──────────┘
┌─sum(x)─┬─_version─┐
│      3 │        2 │
└────────┴──────────┘
┌─sum(x)─┬─_version─┐
│      6 │        3 │
└────────┴──────────┘
1
2
3
4
5
6
7
8
9

在WATCH语句中添加EVENTS关键词将只显示version（只获取更新通知）。

WATCH lv EVENTS;
1

1. 对于Live view同样也可以像查询普通表一样直接通过SELECT语句查询。
2. Live View的SELECT语句的最内层表变动会触发Live View。实时视图的工作原理与分布式表中的查询类似。但是不是将来自不同服务器的部分结果组合起来，而是将来自当前数据的部分结果与来自新数据的部分结果组合起来。当一个实时视图查询包含一个子查询时，缓存的部分结果只存储给最内层的子查询。
3. 表函数（Table function，如numbers）不支持作为最内层表。
4. 由Live View的工作机制可知，Live View不适用于需要完整数据集来计算最终结果或必须保留聚合状态的聚合的查询。
5. Live View不能用于在不同节点上执行插入操作的复制表或分布式表。
6. 只能跟踪一个表，不能被多个表触发。
7. 可以使用ALTER LIVE VIEW [db.]table_name REFRESH语句强制Live View刷新。

此外，还可以为Live View设置TTL，类似于Flink中的State维护：

CREATE LIVE VIEW [db.]table_name WITH TIMEOUT [value_in_sec] AS SELECT ...
1

CREATE TABLE mt (x Int8) Engine = MergeTree ORDER BY x;
CREATE LIVE VIEW lv WITH TIMEOUT 15 AS SELECT sum(x) FROM mt;
1
2

WITH TIMEOUT 15表示该LIVE VIEW在WATCH查询结束后经过15s后自动删除。如果没有指定超时时间值，则默认为temporary_live_view_timeout。

当使用with REFRESH子句创建Live View时，它将在自上次刷新或触发以来的指定秒数后自动刷新。

CREATE LIVE VIEW [db.]table_name WITH REFRESH [value_in_sec] AS SELECT ...;
CREATE LIVE VIEW [db.]table_name WITH TIMEOUT [value_in_sec] AND REFRESH [value_in_sec] AS SELECT ...;
1
2

如果没有指定刷新间隔时间，则默认使用periodic_live_view_refresh。

CREATE LIVE VIEW lv WITH REFRESH 5 AS SELECT now();
WATCH lv

┌───────────────now()─┬─_version─┐
│ 2021-02-21 08:47:05 │        1 │
└─────────────────────┴──────────┘
┌───────────────now()─┬─_version─┐
│ 2021-02-21 08:47:10 │        2 │
└─────────────────────┴──────────┘
┌───────────────now()─┬─_version─┐
│ 2021-02-21 08:47:15 │        3 │
└─────────────────────┴──────────┘
1
2
3
4
5
6
7
8
9
10
11
12

Live View的使用场景主要包括：为查询结果变化提供推送通知，以避免表轮询；缓存最频繁查询的结果以提供高效的即时查询；
定期刷新查看系统表中的指标。

4. Window View

Window View（窗口视图）同样是一种实验功能，所以在使用前也需要 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
1
2
3

Window View可以按时间窗口聚合数据，类似Flink中的Window，并在窗口结束时输出结果。它将部分聚合结果（预聚合）存储在一个内部(或指定的)表中，以减少延迟，并可以将处理结果推送到指定的表或使用WATCH语句查询推送通知。

根据 CREATE 语句可以发现，Window View的创建语句和物化视图的创建语句是很像的，也有直接和间接两种创建方式，直接创建也需要定义视图引擎存储数据，这是因为Window View需要一个内部存储引擎来存储中间数据。但是如果使用INNER ENGINE子句也可以不定义存储引擎，此时Window View将使用AggregatingMergeTree作为默认的内部表引擎。对于创建好的Window View，也可以通过ALTER TABLE … MODIFY QUERY语句修改CREATE WINDOW VIEW语句中的SELECT语句，但是修改前后的数据结构必须相同。

4.1 时间窗口函数

为了获取窗口时间边界，Window View需要与时间窗口函数（tumble、hop等）一起使用。在继续介绍之前，我们先来了解tumble和hop函数。

如上图所示，tumble表示滚动窗口，即窗口大小固定，每个窗口之间没有交集，或者说窗口滑动步长等于窗口大小。hop表示滑动窗口，当滑动步长小于窗口大小时，相邻的窗口之间就存在交集。当然也可以认为tumble是hop的一种特殊情况。
tumble和hop分别定义如下：

tumble(time_attr, interval [, timezone]) 
-- time_attr：DateTime类型的时间数据，窗口起点；interval：Interval类型的窗口大小
hop(time_attr, hop_interval, window_interval [, timezone])
-- time_attr：DateTime类型的时间数据，窗口起点；hop_interval：Interval类型的滑动间隔，需要大于0，滑动步长；window_interval：Interval类型的窗口大小，需要大于0。
1
2
3
4

4.2 处理时间和事件时间

了解Flink的同学一定对这两个概念非常熟悉，包括下面要介绍的WATERMARK都和Flink Window中的概念完全一样。ClickHouse Window View支持处理时间（processing time）和事件时间（event time）处理。

• 处理时间：处理一条数据的实际本地时间。
• 事件时间：一条数据的实际发生时间。

例如：某用户在2022-08-08 10:12:26在某电商平台下了一笔订单，然后经过web传输、kafka等渠道，在2022-08-08 10:12:29插入到ClickHouse表中开始处理，对于Window View来说2022-08-08 10:12:26就是事件时间，2022-08-08 10:12:29就是处理时间。处理时间是最直接的时间概念，也是Window View默认使用的时间。可以通过将时间窗口函数的time_attr设置为表列或使用now()函数来定义处理时间属性。下面的查询创建一个基于处理时间、窗口大小为5s、计算窗口count的滚动窗口Window View：

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
1
2

tumble的time_attr参数可以是表字段，也可以是now()。

4.3 WATERMARK

基于处理时间的计算时比较简单的，因为处理时间一定是有序的，也不存在延迟的概念，但是基于事件时间的处理就比较麻烦了，例如上文的电商订单，实际处理时间比事件时间晚了2s，而且有可能事件时间为2022-08-08 10:12:24的订单数据比2022-08-08 10:12:26的订单数据更早到ClickHouse表中，换句话说就是数据可能是无序的。应该怎么解决呢？Window View通过WATERMARK（水印）支持事件时间的无序和延时问题的处理。Window View提供了三种水印策略：

• STRICTLY_ASCENDING：事件时间即是时间戳水印，例如窗口大小是5min的滑动窗口收到一条事件时间10:00:00的数据（截止到当前最大的时间），则WATERMARK小于10:00:00的窗口全部关闭，假如后面又来了一条09:59:55的数据，则09:55:00~10:00:00的窗口不会统计到该条数据。
• ASCENDING：发出到目前为止观察到的最大时间戳减1的WATERMARK。刚好包含10:00:00数据的窗口不会漏掉10:00:00的数据。
• BOUNDED：支持设置延时策略WATERMARK=INTERVAL，09:55:00~10:00:00的窗口会在大于等于10:00:00 + INTERVAL的事件时间数据过来以后才会被关闭。

创建三种策略的WATERMARK Window View：

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);
-- 时间窗口为5s，允许延迟3s（窗口在指定结束时间+3s的数据到达后才会关闭并输出结果）
CREATE WINDOW VIEW wv WATERMARK=INTERVAL '3' SECOND AS 
SELECT count(number) FROM date GROUP BY tumble(timestamp, INTERVAL '5' SECOND);
1
2
3
4
5
6
7

对于超过WATERMARK的数据ClickHouse也提供了ALLOWED_LATENESS语句兼容，即在窗口关闭后也可以接收延迟的数据，但是会输出多条结果（参考Flink中的延时数据侧输出流）。例如：

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

对于09:59:55-10:00:00的窗口会在10:00:00的数据到来后输出统计结果，但是窗口并没有关闭，直到10:00:03的数据到来后窗口才会关闭。如果在10:00:03的数据之前还有09:59:55-10:00:00的数据到来，同样也会被统计输出，但是并不是直到10:00:03的数据来临窗口关闭才会被输出，而是每来一条数据都会输出，所以一个窗口可能会输出多次（更新之前的计算结果），在使用的时候需要注意去重或者upsert操作。

4.4 监控Window view

Window view也支持WATCH查询来监控变化，或使用to语句将结果输出到表中：

WATCH [db.]window_view
[EVENTS]
[LIMIT n]
[FORMAT format]
1
2
3
4

WATCH查询的行为类似于LIVE VIEW，可以指定LIMIT来设置在终止查询之前接收的更新数，使用EVENTS关键词可只获得最新的WATERMARK，而不是完整的查询结果。假设我们需要在一个名为data的日志表中滚动统计每10秒点击日志的次数，原始日志表结构为：

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

创建WINDOW VIEW：

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;
1
2

使用WATCH查询：

WATCH wv
1

或者在创建WINDOW VIEW的时候保存到目标表中。

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;
1
2

一般WINDOW VIEW可用于按时间聚合并计算业务指标，并将结果输出到目标表，前后端可直接读取目标表显示或者计算。或者做一些基于时间窗口的预处理，如机器学习模型的实时特征计算等。

参考资料

[1] https://altinity.com/blog/battle-of-the-views-clickhouse-window-view-vs-live-view