Clickhouse Live View

概述：
Live view 是一种特殊的视图，不属于表引擎，作用类似于事件监听器，能够将一条SQL查询结果作为监控目标，当目标
增加 Live view 可以及时作出响应。

在数据分析领域，分析师经常使用移动平均值（MA）。移动平均线有助于平滑数据序列并确定长期趋势。新的Live View功能
开始为ClickHouse带来实时计算功能（Real-time）。

Live View表的适用之一是对实时指标进行实时计算。事件数据流的示例包括来自IoT传感器数据， 证券交易所的价格波动
或来自生产服务器的某些监控指标。ClickHouse 可以以良好的压缩率存储所有这些数据以及提供出色的分析查询性能


版本演进：
19.14 版本提供 Live view 视图
20.1.2.4 版本提供实时移动平均功能

相关参数：
Clickhouse> select name ,value,changed,min,max,readonly,type from system.settings where name like '%live_view%';

SELECT 
    name, 
    value, 
    changed, 
    min, 
    max, 
    readonly, 
    type
FROM system.settings
WHERE name LIKE '%live_view%'

┌─name───────────────────────────────────────┬─value─┬─changed─┬─min──┬─max──┬─readonly─┬─type───────────┐
│ allow_experimental_live_view               │ 0     │       0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ SettingBool    │
│ live_view_heartbeat_interval               │ 15    │       0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ SettingSeconds │
│ max_live_view_insert_blocks_before_refresh │ 64    │       0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ SettingUInt64  │
│ temporary_live_view_timeout                │ 5     │       0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ SettingSeconds │
└────────────────────────────────────────────┴───────┴─────────┴──────┴──────┴──────────┴────────────────┘

4 rows in set. Elapsed: 0.018 sec. 
Live view 目前还是实验功能（experimental feature）需要开启参数allow_experimental_live_view ：
手动设置：
Clickhouse> set allow_experimental_live_view=1;

SET allow_experimental_live_view = 1

Ok.

0 rows in set. Elapsed: 0.001 sec. 

Clickhouse> select name ,value,changed,min,max,readonly,type from system.settings where name like '%live_view%';

 

┌─name───────────────────────────────────────┬─value─┬─changed─┬─min──┬─max──┬─readonly─┬─type───────────┐
│ allow_experimental_live_view               │ 1     │       1 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ SettingBool    │
 .........
└────────────────────────────────────────────┴───────┴─────────┴──────┴──────┴──────────┴────────────────┘

4 rows in set. Elapsed: 0.006 sec. 

简单示例：
Clickhouse> create table t_lv(id UInt64)engine=Log;

CREATE TABLE t_lv
(
    `id` UInt64
)
ENGINE = Log

Ok.

0 rows in set. Elapsed: 0.011 sec. 

Clickhouse> create live view lv_count as select count(1) from t_lv;

CREATE LIVE VIEW lv_count AS
SELECT count(1)
FROM t_lv

Ok.

0 rows in set. Elapsed: 0.015 sec. 

Clickhouse> watch lv_count;

WATCH lv_count

┌─count(1)─┬─_version─┐
│        0 │        1 │
└──────────┴──────────┘
live view 进入监听模式。在另外的可以客户端执行插入数据的操作:
Clickhouse> insert into t_lv select rand() from numbers(10);

┌─count(1)─┬─_version─┐
│       10 │        2 │
└──────────┴──────────┘


移动平均计算示例：
Clickhouse> create table t_events(id UInt64,create_time datetime default now())ENGINE = Memory;


Clickhouse> select sum(id)/10 from(select * from t_events order by create_time desc limit 10);

插入测试数据：

Clickhouse> INSERT INTO t_events VALUES (1,now()-9), (2,now()-8), (3,now()-7), (4,now()-6), (5,now()-5), (6,now()-4), (7,now()-3), (8,now()-2), (9,now()-1), (10,now());


计算平均值：
Clickhouse> select sum(id)/10 from(select * from t_events order by create_time desc limit 10);

SELECT sum(id) / 10
FROM 
(
    SELECT *
    FROM t_events
    ORDER BY create_time DESC
    LIMIT 10
)

┌─divide(sum(id), 10)─┐
│                 5.5 │
└─────────────────────┘

1 rows in set. Elapsed: 0.013 sec.

Clickhouse> CREATE LIVE VIEW latest_10 as select sum(id)/10 from(select * from t_events order by create_time desc limit 10);


Clickhouse> select * from latest_10;

SELECT *
FROM latest_10

┌─divide(sum(id), 10)─┐
│                 5.5 │
└─────────────────────┘

1 rows in set. Elapsed: 0.007 sec. 

--- 插入新的记录查看最近10个数据的平均值：
Clickhouse> select * from t_events;

SELECT *
FROM t_events

┌─id─┬─────────create_time─┐
│  1 │ 2020-06-15 15:17:21 │
│  2 │ 2020-06-15 15:17:22 │
│  3 │ 2020-06-15 15:17:23 │
│  4 │ 2020-06-15 15:17:24 │
│  5 │ 2020-06-15 15:17:25 │
│  6 │ 2020-06-15 15:17:26 │
│  7 │ 2020-06-15 15:17:27 │
│  8 │ 2020-06-15 15:17:28 │
│  9 │ 2020-06-15 15:17:29 │
│ 10 │ 2020-06-15 15:17:30 │
└────┴─────────────────────┘
┌─id─┬─────────create_time─┐
│ 11 │ 2020-06-15 15:21:17 │
└────┴─────────────────────┘

11 rows in set. Elapsed: 0.004 sec. 

Clickhouse> select * from latest_10;

SELECT *
FROM latest_10

┌─divide(sum(id), 10)─┐
│                 6.5 │
└─────────────────────┘

1 rows in set. Elapsed: 0.008 sec. 


sum([2, 3, 4, 5, 6, 7, 8, 9, 10, 1])/10 = 5.5
sum([2, 3, 4, 5, 6, 7, 8, 9, 10, 11])/10 = 6.5

参考：

https://www.altinity.com/blog/2020/3/9/real-time-moving-average-with-clickhouse-live-views

https://www.altinity.com/blog/2019/11/13/making-data-come-to-life-with-clickhouse-live-view-tables

https://www.altinity.com/blog/2019/12/05/taking-a-closer-look-at-clickhouse-live-view-tables

https://www.altinity.com/blog/2019/11/14/using-live-view-tables-with-a-real-dataset