深入理解ClickHouse-本地表和分布式表

在集群的每个机器上面建立本地表

这里需要谨记，在进行下面的操作前（使用ReplicatedMergeTree表引擎），必须保证集群配置中internal_replication=true且配置了zookeeper。

1. 先任选一台机器建个表插入数据(方便测试)

CREATE TABLE IF NOT EXISTS bank (\
age UInt16, \
job String, \
marital String, \
education String, \
default String, \
housing String, \
loan String, \
contact String, \
month String, \
day_of_week String, \
duration UInt32, \
campaign UInt32, \
pdays UInt64, \
previous UInt8, \
poutcome String, \
empvar_rate Float64, \
cons_price_idx Float64, \
cons_conf_idx Float64, \
euribor3m Float64, \
nr_employed Float64 \
) ENGINE = MergeTree() \ 
PARTITION BY month \
ORDER BY (education, age) \ 
SETTINGS index_granularity = 8192;

导入数据：

# 插入文件数据
cat /root/clickhouse-packages/data/bank_data.csv | clickhouse-client --host=ckprd1 --port=9000 --database=default --query="INSERT INTO bank FORMAT CSVWithNames" --input_format_allow_errors_num=100000

这里有几个需要注意的点：

• FORMAT CSVWithNames是带字段名的，如果单纯是FORMAT CSV是会将首行当做数据行而不是字段名处理会出错的；关于FORMAT后面会再单独谈
• input_format_allow_errors_num这个参数是读取csv格式时候容错的数量

2. 在集群的每个机器上面建立本地表

1）方式一：在每个集群上分别运行下面的代码

CREATE TABLE IF NOT EXISTS bank_replica (\
age UInt16,\
job String,\
marital String,\
education String,\
default String,\
housing String,\
loan String,\
contact String,\
month String,\
day_of_week String,\
duration UInt32,\
campaign UInt32,\
pdays UInt64,\
previous UInt8,\
poutcome String,\
empvar_rate Float64,\
cons_price_idx Float64,\
cons_conf_idx Float64,\
euribor3m Float64,\
nr_employed Float64\
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/bank_replica', '{replica}')\
PARTITION BY month\
ORDER BY (education, age) \
SETTINGS index_granularity = 8192;

ReplicatedMergeTree用的就是macros里面配置的参数了：

• /clickhouse/tables/是一般性前缀，建议别动保持原样
• {layer}-{shard}是分片识别符，同一个分片内的所有机器应该保持相同。我这里使用的是集群名+分片名的配置，我也建议大家效法我的做法，还是比较靠谱的。
• /clickhouse/tables/{layer}-{shard}/bank_replica + {replica}作为整体可以理解为表在zookeeper中的定位和识别符，因此每个表必须不同；bank_replica这个地方建议使用表名，{replica}参数建议在macros里面配置成机器的hostname，因为每台机器的hostname都是不一样的，因此就能确保每个表的识别符都是唯一的了（当然那种3台机器相互备份的集群可能在这里就不合适了，但是毕竟3台机器相互备份是个野路子，不建议大家采用的）

2）方式二：一次性在集群每个机器上建立本地表

-- Replicated Table
CREATE TABLE IF NOT EXISTS bank_replica ON CLUSTER mcd_prod_cluster_1st (\
age UInt16,\
job String,\
marital String,\
education String,\
default String,\
housing String,\
loan String,\
contact String,\
month String,\
day_of_week String,\
duration UInt32,\
campaign UInt32,\
pdays UInt64,\
previous UInt8,\
poutcome String,\
empvar_rate Float64,\
cons_price_idx Float64,\
cons_conf_idx Float64,\
euribor3m Float64,\
nr_employed Float64\
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/bank_replica', '{replica}')\
PARTITION BY month\
ORDER BY (education, age) \
SETTINGS index_granularity = 8192;

删除表：

DROP TABLE bank_replica ON CLUSTER mcd_prod_cluster_1st

这里为了验证ReplicatedMergeTree本身是可以同步和复制数据的，可以往一个分片里面某个表里面写入数据，看下其它备份是否也会有同样的数据：

INSERT INTO bank_replica SELECT * FROM bank

查询结果：

ckprd1 :) select count(1) from bank_replica;         

SELECT count(1)
FROM bank_replica 

┌─count(1)─┐
│    27595 │
└──────────┘

ckprd2 :) select count(1) from bank_replica;         

SELECT count(1)
FROM bank_replica 

┌─count(1)─┐
│    27595 │
└──────────┘

ckprd3 :) select count(1) from bank_replica;   

SELECT count(1)
FROM bank_replica 

┌─count(1)─┐
│        0 │
└──────────┘

ckprd4 :) select count(1) from bank_replica;   

SELECT count(1)
FROM bank_replica 

┌─count(1)─┐
│        0 │
└──────────┘

因为执行插入操作在ckprd1节点，而ckprd2和ckprd1属于同一个分片，所以数据相同。而ckprd3和ckprd4属于另一个分片，没有数据。

下面我会介绍使用分布式表的方式在两个分片上面分布数据。

3. 在集群的每个机器上面建立分布式表

同样的，和建立本地表一样，分布式表也需要在每个机器上建立，同样的也可以一个一个机器去分别建立，也可以一次性在所有节点创建表。 这里直接放出一次性建立所有的代码：

CREATE TABLE bank_dist ON CLUSTER mcd_prod_cluster_1st \
AS bank_replica \
ENGINE = Distributed(mcd_prod_cluster_1st, default, bank_replica, rand());

Distributed表引擎后面依次是集群名、库名、表名、数据分配方式。

关于分布式表我划重点如下：

• 分布式表本身并不存储数据，只是提供了一个可以分布式访问数据的框架，查询分布式表的时候clickhouse会自动去查询对应的每个本地表中的数据
• 注意AS bank_replica，它表明了分布式表所对应的本地表（本地表是存储数据的）
• 可以配置Distributed表引擎中的最后一个参数来设置数据条目的分配方式
• 可以直接往分布式表中写数据，clickhouse会自动按照上一点所说的方式来分配数据和自平衡
• 也可以自己写算法，然后往本地表中写数据（当然这个就比较高级了）

另外，这里在每个机器上建立分布式表和上面的在每个机器上建立本地表的目的完全不同，这里即使只在一个机器上建分布式表也是可以的。只是说，在每个机器上建分布式表的话，那么可以在每个服务器上都做分布式查询了。

可以往各服务器上的任何一个分布式表里面写入数据（插入数据之前，可以先把bank_replica表数据删除）：

INSERT INTO bank_dist SELECT * FROM bank;

查询结果：

ckprd1 :) select count(1) from bank_replica;

SELECT count(1)
FROM bank_replica 

┌─count(1)─┐
│    13832 │
└──────────┘


ckprd2 :) select count(1) from bank_replica;

SELECT count(1)
FROM bank_replica 

┌─count(1)─┐
│    13832 │
└──────────┘



ckprd3 :) select count(1) from bank_replica;

SELECT count(1)
FROM bank_replica 

┌─count(1)─┐
│    13763 │
└──────────┘



ckprd4 :) select count(1) from bank_replica;

SELECT count(1)
FROM bank_replica 

┌─count(1)─┐
│    13763 │
└──────────┘

4. ClickHouse数据存储

1）一个分区一个文件夹

default数据库下面的bank_replica表的数据结构如下:
[外链图片转存失败(img-kFDRvV9i-1568252836912)(evernotecid://4CF2DB7A-63C8-4462-AF1B-D4FBD7AC5C92/appyinxiangcom/215407/ENResource/p779)]

查看分区信息：

ckprd1 :) SELECT      partition,     name,      active FROM system.parts  WHERE table = 'bank_replica';

SELECT 
    partition, 
    name, 
    active
FROM system.parts 
WHERE table = 'bank_replica'

┌─partition─┬─name─────────────────────────────────┬─active─┐
│ dec     │ 0dcda14aa879a9e9de8ce0a075dac042_3_3_0 │    1 │
│ mar     │ 534f9f773916a62e1ce21b79e23ba5e7_3_3_0 │    1 │
│ oct     │ 6d3d797dfda12e0b8c837064b52bacc8_3_3_0 │    1 │
│ jun     │ 9a34545ffc3f5f4b8bee104063c6dd61_3_3_0 │    1 │
│ nov     │ 9bbaa4d2c2df481f7661e7257563da2d_3_3_0 │    1 │
│ sep     │ a6ad89f019506d5c2359e353d73e033d_3_3_0 │    1 │
│ apr     │ b3478f1e0b48f24b48cfc42239749609_3_3_0 │    1 │
│ jul     │ e0f64a6601e692e6c7761024aa627976_3_3_0 │    1 │
│ aug     │ f3fd28549b760f6d22e03bab5d963e1b_3_3_0 │    1 │
│ may     │ f6139eb0d4e1e322ceebd4cc93d30326_3_3_0 │    1 │
└───────────┴──────────────────────────────────────┴───────┘

10 rows in set. Elapsed: 0.002 sec. 

2）每个分区下一个字段存储为一个被压缩的小文件，以及对应的索引/顺序标识，空值会有特殊的文件记录