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

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

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

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

1CREATE TABLE IF NOT EXISTS bank (\ 2age UInt16, \ 3job String, \ 4marital String, \ 5education String, \ 6default String, \ 7housing String, \ 8loan String, \ 9contact String, \ 10month String, \ 11day_of_week String, \ 12duration UInt32, \ 13campaign UInt32, \ 14pdays UInt64, \ 15previous UInt8, \ 16poutcome String, \ 17empvar_rate Float64, \ 18cons_price_idx Float64, \ 19cons_conf_idx Float64, \ 20euribor3m Float64, \ 21nr_employed Float64 \ 22) ENGINE = MergeTree() \ 23PARTITION BY month \ 24ORDER BY (education, age) \ 25SETTINGS index_granularity = 8192; 26 27

导入数据:

1# 插入文件数据 2cat /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 3 4 5

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

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

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

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

1CREATE TABLE IF NOT EXISTS bank_replica (\ 2age UInt16,\ 3job String,\ 4marital String,\ 5education String,\ 6default String,\ 7housing String,\ 8loan String,\ 9contact String,\ 10month String,\ 11day_of_week String,\ 12duration UInt32,\ 13campaign UInt32,\ 14pdays UInt64,\ 15previous UInt8,\ 16poutcome String,\ 17empvar_rate Float64,\ 18cons_price_idx Float64,\ 19cons_conf_idx Float64,\ 20euribor3m Float64,\ 21nr_employed Float64\ 22) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/bank_replica', '{replica}')\ 23PARTITION BY month\ 24ORDER BY (education, age) \ 25SETTINGS index_granularity = 8192; 26 27

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

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

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

1-- Replicated Table 2CREATE TABLE IF NOT EXISTS bank_replica ON CLUSTER mcd_prod_cluster_1st (\ 3age UInt16,\ 4job String,\ 5marital String,\ 6education String,\ 7default String,\ 8housing String,\ 9loan String,\ 10contact String,\ 11month String,\ 12day_of_week String,\ 13duration UInt32,\ 14campaign UInt32,\ 15pdays UInt64,\ 16previous UInt8,\ 17poutcome String,\ 18empvar_rate Float64,\ 19cons_price_idx Float64,\ 20cons_conf_idx Float64,\ 21euribor3m Float64,\ 22nr_employed Float64\ 23) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/bank_replica', '{replica}')\ 24PARTITION BY month\ 25ORDER BY (education, age) \ 26SETTINGS index_granularity = 8192; 27 28

删除表:

1DROP TABLE bank_replica ON CLUSTER mcd_prod_cluster_1st 2 3

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

1INSERT INTO bank_replica SELECT * FROM bank 2 3

查询结果:

1ckprd1 :) select count(1) from bank_replica; 2 3SELECT count(1) 4FROM bank_replica 5 6┌─count(1)─┐ 7275958└──────────┘ 9 10ckprd2 :) select count(1) from bank_replica; 11 12SELECT count(1) 13FROM bank_replica 14 15┌─count(1)─┐ 162759517└──────────┘ 18 19ckprd3 :) select count(1) from bank_replica; 20 21SELECT count(1) 22FROM bank_replica 23 24┌─count(1)─┐ 25026└──────────┘ 27 28ckprd4 :) select count(1) from bank_replica; 29 30SELECT count(1) 31FROM bank_replica 32 33┌─count(1)─┐ 34035└──────────┘ 36 37

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

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

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

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

1CREATE TABLE bank_dist ON CLUSTER mcd_prod_cluster_1st \ 2AS bank_replica \ 3ENGINE = Distributed(mcd_prod_cluster_1st, default, bank_replica, rand()); 4 5

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

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

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

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

可以往各服务器上的任何一个分布式表里面写入数据(插入数据之前,可以先把bank_replica表数据删除):

1INSERT INTO bank_dist SELECT * FROM bank; 2 3

查询结果:

1ckprd1 :) select count(1) from bank_replica; 2 3SELECT count(1) 4FROM bank_replica 5 6┌─count(1)─┐ 7138328└──────────┘ 9 10 11ckprd2 :) select count(1) from bank_replica; 12 13SELECT count(1) 14FROM bank_replica 15 16┌─count(1)─┐ 171383218└──────────┘ 19 20 21 22ckprd3 :) select count(1) from bank_replica; 23 24SELECT count(1) 25FROM bank_replica 26 27┌─count(1)─┐ 281376329└──────────┘ 30 31 32 33ckprd4 :) select count(1) from bank_replica; 34 35SELECT count(1) 36FROM bank_replica 37 38┌─count(1)─┐ 391376340└──────────┘ 41 42 43

4. ClickHouse数据存储

1)一个分区一个文件夹

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

查看分区信息:

1ckprd1 :) SELECT partition, name, active FROM system.parts WHERE table = 'bank_replica'; 2 3SELECT 4 partition, 5 name, 6 active 7FROM system.parts 8WHERE table = 'bank_replica' 9 10┌─partition─┬─name─────────────────────────────────┬─active─┐ 11│ dec │ 0dcda14aa879a9e9de8ce0a075dac042_3_3_0 │ 112│ mar │ 534f9f773916a62e1ce21b79e23ba5e7_3_3_0 │ 113│ oct │ 6d3d797dfda12e0b8c837064b52bacc8_3_3_0 │ 114│ jun │ 9a34545ffc3f5f4b8bee104063c6dd61_3_3_0 │ 115│ nov │ 9bbaa4d2c2df481f7661e7257563da2d_3_3_0 │ 116│ sep │ a6ad89f019506d5c2359e353d73e033d_3_3_0 │ 117│ apr │ b3478f1e0b48f24b48cfc42239749609_3_3_0 │ 118│ jul │ e0f64a6601e692e6c7761024aa627976_3_3_0 │ 119│ aug │ f3fd28549b760f6d22e03bab5d963e1b_3_3_0 │ 120│ may │ f6139eb0d4e1e322ceebd4cc93d30326_3_3_0 │ 121└───────────┴──────────────────────────────────────┴───────┘ 22 2310 rows in set. Elapsed: 0.002 sec. 24 25 26

2)每个分区下一个字段存储为一个被压缩的小文件,以及对应的索引/顺序标识,空值会有特殊的文件记录
在这里插入图片描述

代码交流 2021