1. Live View表引擎
Live View不是表引擎,是一种视图,通过监听一条SQL查询语句的结果,当开启监听时,会得到这条SQL查询的结果,之后每一次这条SQL查询语句的结果有变化时,Live View都会收到结果
- 查询是否开启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 :)
这种开启方式只针对此次连接有效
- 创建一张表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 :)
- 创建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
方式创建
- 开启另外一个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.)
- 再向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表引擎,物化视图能同步到数据
- 创建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 :)
- 向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');
- 查询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