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 :) 
  1. 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表分布式查询的核心流程

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值