Clickhouse接口表引擎(Merge、Dictionary、Distributed)

接口表引擎不储存数据，通过封装其它表实现特定的功能，提供一种统一的数据访问接口

1. Merge表引擎

应用场景：当针对相同的数据做了分表操作后，如果想通过一个表查询所有的分表数据，Merge表就可以实现

Merge表引擎的不足

• 不支持数据写入
• 各个分表位于同一个数据库
• 各个分表表结构也要相同，但表引擎和partition分区(MergeTree)可以不同

1. 城市为Beijing的表创建和数据插入

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

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

Query id: d39e7578-7e4f-4c7e-8093-a431bd20cedd

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

4 rows in set. Elapsed: 0.283 sec. 

clickhouse1 :) 
clickhouse1 :) create table table_beijing_all on cluster sharding_ha as table_beijing_local
:-] engine = Distributed(sharding_ha, default, table_beijing_local, id);

CREATE TABLE table_beijing_all ON CLUSTER sharding_ha AS table_beijing_local
ENGINE = Distributed(sharding_ha, default, table_beijing_local, id)

Query id: 20002e5f-b1f9-48c2-8838-1ca35ad49eef

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

4 rows in set. Elapsed: 0.228 sec. 

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

INSERT INTO table_beijing_all (id, name, city) VALUES

Query id: 35ed3ebd-8511-4804-a055-160d17e193a6

Ok.

3 rows in set. Elapsed: 0.038 sec. 

clickhouse1 :) 

1. 城市为Shanghai的表创建和数据插入

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

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

Query id: d499df55-5b3a-4235-8ac5-de2dfd1ae477

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

4 rows in set. Elapsed: 0.198 sec. 

clickhouse1 :) 
clickhouse1 :) create table table_shanghai_all on cluster sharding_ha as table_shanghai_local
:-] engine = Distributed(sharding_ha, default, table_shanghai_local, id);

CREATE TABLE table_shanghai_all ON CLUSTER sharding_ha AS table_shanghai_local
ENGINE = Distributed(sharding_ha, default, table_shanghai_local, id)

Query id: b65cd9dc-5df2-436f-99d5-da53d99ff139

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

4 rows in set. Elapsed: 0.137 sec. 

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

INSERT INTO table_shanghai_all (id, name, city) VALUES

Query id: b2fd686c-417d-4539-a58b-6ad10a72cee6

Ok.

3 rows in set. Elapsed: 0.016 sec. 

clickhouse1 :) 

3. Merge表的创建和数据查询

clickhouse1 :) 
clickhouse1 :) create table table_city_all as table_beijing_all
:-] engine = Merge(default, 'table_beijing_all|table_shanghai_all');

CREATE TABLE table_city_all AS table_beijing_all
ENGINE = Merge(default, 'table_beijing_all|table_shanghai_all')

Query id: b2ed6b54-ba59-4494-8492-c7c804386910

Ok.

0 rows in set. Elapsed: 0.012 sec. 

clickhouse1 :) 
clickhouse1 :) select _table, * from table_city_all where _table = 'table_shanghai_all';

SELECT
    _table,
    *
FROM table_city_all
WHERE _table = 'table_shanghai_all'

Query id: 83ab6773-d047-497f-82bb-f28a9b622666

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

3 rows in set. Elapsed: 0.051 sec. 

clickhouse1 :)

• Merge表引擎的table_name支持正则表达式
• select查询不符合where条件的分表，不会收到查询请求

2. Dictionary表引擎

todo

3. Distributed表引擎

Distributed表引擎的详细使用说明可以参考我的下面两篇文章： Clickhouse Distributed分布式表引擎的基本介绍和使用说明 Clickhouse Distributed表分布式查询的核心流程