Clickhouse其它类型表引擎(Live View、Null、URL)

1. Live View表引擎

Live View不是表引擎，是一种视图，通过监听一条SQL查询语句的结果，当开启监听时，会得到这条SQL查询的结果，之后每一次这条SQL查询语句的结果有变化时，Live View都会收到结果

1. 查询是否开启Live View，如果未开启，我们则开启

clickhouse1 :) 
clickhouse1 :) select name, value, type from system.settings where name = 'allow_experimental_live_view';

SELECT
    name,
    value,
    type
FROM system.settings
WHERE name = 'allow_experimental_live_view'

Query id: 1eda4d1d-884c-4bc7-a4d7-015c008f3f5b

┌─name─────────────────────────┬─value─┬─type─┐
│ allow_experimental_live_view │ 0     │ Bool │
└──────────────────────────────┴───────┴──────┘

1 rows in set. Elapsed: 0.015 sec. 

clickhouse1 :) 
clickhouse1 :) set allow_experimental_live_view = 1;

SET allow_experimental_live_view = 1

Query id: e2d6685e-35ef-4f4b-8326-009bb80fb316

Ok.

0 rows in set. Elapsed: 0.002 sec. 

clickhouse1 :)

这种开启方式只针对此次连接有效

2. 创建一张表origin_table_all，并插入数据，作为Live View的监听目标

clickhouse1 :)
clickhouse1 :) create table origin_table_local on cluster sharding_cluster(
:-] id UInt32,
:-] name String,
:-] city String
:-] ) engine = MergeTree()
:-] order by id
:-] ;

CREATE TABLE origin_table_local ON CLUSTER sharding_cluster
(
    `id` UInt32,
    `name` String,
    `city` String
)
ENGINE = MergeTree
ORDER BY id

Query id: 4fd0dac5-cdc8-4589-bbc7-9442bffbe4d4

┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │      0 │       │                   3 │                0 │
│ clickhouse3 │ 9000 │      0 │       │                   2 │                0 │
│ clickhouse1 │ 9000 │      0 │       │                   1 │                0 │
│ clickhouse4 │ 9000 │      0 │       │                   0 │                0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.157 sec. 

clickhouse1 :) 
clickhouse1 :) create table origin_table_all on cluster sharding_cluster as origin_table_local
:-] engine = Distributed(sharding_cluster, default, origin_table_local, id);

CREATE TABLE origin_table_all ON CLUSTER sharding_cluster AS origin_table_local
ENGINE = Distributed(sharding_cluster, default, origin_table_local, id)

Query id: 42b56a44-9c8a-44e2-a164-95e1297da55b

┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │      0 │       │                   3 │                0 │
│ clickhouse3 │ 9000 │      0 │       │                   2 │                0 │
│ clickhouse1 │ 9000 │      0 │       │                   1 │                0 │
│ clickhouse4 │ 9000 │      0 │       │                   0 │                0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.127 sec. 

clickhouse1 :) 
clickhouse1 :) insert into origin_table_all(id, name, city) values(1, 'name1', 'Beijing');

INSERT INTO origin_table_all (id, name, city) VALUES

Query id: e862bf51-c9ab-4a36-a22f-7b1907930f9a

Ok.

1 rows in set. Elapsed: 0.004 sec. 

clickhouse1 :) 

3. 创建Live View，并开启监听

clickhouse1 :) 
clickhouse1 :) create live view live_view_origin as select count(*) from origin_table_all;

CREATE LIVE VIEW live_view_origin AS
SELECT count(*)
FROM origin_table_all

Query id: 8491c2f3-e802-44bc-9295-7355b5e1e66d

Ok.

0 rows in set. Elapsed: 0.007 sec. 

clickhouse1 :) 
clickhouse1 :) watch live_view_origin;

WATCH live_view_origin

Query id: 1d163536-b6ce-4e4f-8cb2-ae3da0dff4b5

┌─count()─┬─_version─┐
│       1 │        1 │
└─────────┴──────────┘
→ Progress: 1.00 rows, 16.00 B (7.89 rows/s., 126.25 B/s.)

Live View不支持on cluster cluster name方式创建

4. 开启另外一个client, 并向origin_table_all表插入一条数据

clickhouse1 :) 
clickhouse1 :) insert into origin_table_all(id, name, city) values(2, 'name2', 'Shanghai');

INSERT INTO origin_table_all (id, name, city) VALUES

Query id: 41bc8daf-3b59-493f-a2d2-fd59ce560300

Ok.

1 rows in set. Elapsed: 0.018 sec. 

clickhouse1 :)

查看Live View, 可以看到自动监听到了变化的数据

clickhouse1 :)
clickhouse1 :) watch live_view_origin;

WATCH live_view_origin

Query id: 1d163536-b6ce-4e4f-8cb2-ae3da0dff4b5

┌─count()─┬─_version─┐
│       1 │        1 │
└─────────┴──────────┘
┌─count()─┬─_version─┐
│       2 │        2 │
└─────────┴──────────┘
↓ Progress: 2.00 rows, 32.00 B (0.01 rows/s., 0.17 B/s.)

5. 再向origin_table_all表插入一条数据

clickhouse1 :) 
clickhouse1 :) insert into origin_table_all(id, name, city) values(3, 'name3', 'Guangzhou');

INSERT INTO origin_table_all (id, name, city) VALUES

Query id: 33db4fc0-aac2-4f1c-b20d-53b4303667b8

Ok.

1 rows in set. Elapsed: 0.008 sec. 

clickhouse1 :) 

再次查看Live View, 可以看到自动监听到了变化的数据

clickhouse1 :) watch live_view_origin;

WATCH live_view_origin

Query id: 1d163536-b6ce-4e4f-8cb2-ae3da0dff4b5

┌─count()─┬─_version─┐
│       1 │        1 │
└─────────┴──────────┘
┌─count()─┬─_version─┐
│       2 │        2 │
└─────────┴──────────┘
┌─count()─┬─_version─┐
│       3 │        3 │
└─────────┴──────────┘
← Progress: 3.00 rows, 48.00 B (0.01 rows/s., 0.10 B/s.) 

2. Null表引擎

将数据insert到Null表引擎，系统不会报错，但是会忽略插入的数据，select的时候也查询不到数据

如果物化视图源表为Null表引擎，物化视图能同步到数据

1. 创建Null表和物化视图

clickhouse1 :) 
clickhouse1 :) create table null_table_local on cluster sharding_cluster(
:-] id UInt32,
:-] name String,
:-] city String
:-] ) engine = Null;

CREATE TABLE null_table_local ON CLUSTER sharding_cluster
(
    `id` UInt32,
    `name` String,
    `city` String
)
ENGINE = Null

Query id: f943303d-3142-4e89-8a50-61154728dd93

┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │      0 │       │                   3 │                0 │
│ clickhouse3 │ 9000 │      0 │       │                   2 │                0 │
│ clickhouse1 │ 9000 │      0 │       │                   1 │                0 │
│ clickhouse4 │ 9000 │      0 │       │                   0 │                0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.146 sec. 

clickhouse1 :) 
clickhouse1 :) create table null_table_all on cluster sharding_cluster as null_table_local
:-] engine = Distributed(sharding_cluster, default, null_table_local, id);

CREATE TABLE null_table_all ON CLUSTER sharding_cluster AS null_table_local
ENGINE = Distributed(sharding_cluster, default, null_table_local, id)

Query id: c62fdfde-1ba4-4122-bd36-46114731d5e6

┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse4 │ 9000 │      0 │       │                   3 │                3 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │      0 │       │                   2 │                0 │
│ clickhouse3 │ 9000 │      0 │       │                   1 │                0 │
│ clickhouse1 │ 9000 │      0 │       │                   0 │                0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.094 sec. 

clickhouse1 :) 
clickhouse1 :) create table data_table_local on cluster sharding_ha(
:-] id UInt32,
:-] name String,
:-] city String
:-] ) engine = ReplicatedMergeTree('/clickhouse/tables/data_table/{shard}', '{replica}')
:-] order by id;

CREATE TABLE data_table_local ON CLUSTER sharding_ha
(
    `id` UInt32,
    `name` String,
    `city` String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/data_table/{shard}', '{replica}')
ORDER BY id

Query id: 56d6fa19-6407-4738-b30f-936e157e479f

┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse3 │ 9000 │      0 │       │                   3 │                2 │
│ clickhouse1 │ 9000 │      0 │       │                   2 │                2 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │      0 │       │                   1 │                0 │
│ clickhouse4 │ 9000 │      0 │       │                   0 │                0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.171 sec. 

clickhouse1 :) 
clickhouse1 :) create materialized view view_table_all 
:-] engine = Distributed(sharding_ha, default, data_table_local, id)
:-] as select * from null_table_all;

CREATE MATERIALIZED VIEW view_table_all
ENGINE = Distributed(sharding_ha, default, data_table_local, id) AS
SELECT *
FROM null_table_all

Query id: 7612a63a-f0b8-4e16-877a-af7c79293c14

Ok.

0 rows in set. Elapsed: 0.006 sec. 

clickhouse1 :) 

2. 向null_table_all表插入数据

clickhouse1 :) insert into null_table_all(id, name, city) values(1, 'name1', 'Beijing');
clickhouse1 :) insert into null_table_all(id, name, city) values(2, 'name2', 'Shanghai');
clickhouse1 :) insert into null_table_all(id, name, city) values(3, 'name3', 'Guangzhou');

3. 查询null_table_all和view_table_all表数据

clickhouse1 :) 
clickhouse1 :) select * from null_table_all;

SELECT *
FROM null_table_all

Query id: 86652fdd-3307-4283-b2c1-290a6c4dc905

Ok.

0 rows in set. Elapsed: 0.014 sec. 

clickhouse1 :) 
clickhouse1 :) select * from view_table_all;

SELECT *
FROM view_table_all

Query id: 36f47e8d-5b53-4e79-b4f3-b2878fc43290

┌─id─┬─name──┬─city─────┐
│  2 │ name2 │ Shanghai │
└────┴───────┴──────────┘
┌─id─┬─name──┬─city────┐
│  1 │ name1 │ Beijing │
└────┴───────┴─────────┘
┌─id─┬─name──┬─city──────┐
│  3 │ name3 │ Guangzhou │
└────┴───────┴───────────┘

3 rows in set. Elapsed: 0.024 sec. 

clickhouse1 :) 

3. URL表引擎

todo