深入理解ClickHouse-本地表和分布式表_clickhouse分布式表和本地表_DataFlow范式的博客

link之家
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
在集群的每个机器上面建立本地表

这里需要谨记，在进行下面的操作前（使用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）每个分区下一个字段存储为一个被压缩的小文件，以及对应的索引/顺序标识，空值会有特殊的文件记录
 
                    在集群的每个机器上面建立本地表这里需要谨记，在进行下面的操作前（使用ReplicatedMergeTree表引擎），必须保证集群配置中internal_replication=true且配置了zookeeper。1. 先任选一台机器建个表插入数据(方便测试)CREATE TABLE IF NOT EXISTS bank (\age UInt16, \job String, \marit...
				TL;DR
如果预估自己的业务数据量不大(日增不到百万行), 那么写分布式表和本地表都可以, 但要注意如果选择写本地表, 请保证每次写入数据都建立新的连接, 且每个连接写入的数据量基本相同
如果预估自己的业务数据量大(日增百万以上, 并发插入大于10), 那么请写本地表
建议每次插入50W行左右数据, 最多不可超过100W行. 总之CH不像MySQL要小事务. 比如1000W行数据, MySQL建议一次插入1W左右, 使用小事务, 执行1000次. CH建议20次,每次50W. 这是MergeTree引擎原
外部表:ck中只有元数据,数据在hdfs等外部存储介质中
本地表:ck的单机表,在某台机器上创建,就只能在这个机器上使用
副本表:数据在ck集群上,每个分片有多个副本
分布式表:相当于传统数据库的视图,对大批量的数据不推荐直接插分布式表
a.安装 两分片两副本的Clickhouse集群
b.样例数据
1,羲凡,29,1992-02-27,nj
5,张三,27,1994-02-27,sh
3,李四,29
				1.clickhouse的表分为两种:
分布式表
一个逻辑上的表, 可以理解为数据库中的视图, 一般查询都查询分布式表. 分布式表引擎会将我们的查询请求路由本地表进行查询, 然后进行汇总最终返回给用户。
本地表:
实际存储数据的表。
2.不写分布式表的原因
1.分布式表接收到数据后会将数据拆分成多个parts, 并转发数据到其它服务器, 会引起服务器间网络流量增加、服务器merge的工作量增加, 导致写入速度变慢, 并且增加了Too many parts的可能性。
2.数据的一致性问题, 先在分布式表所在的
				在前面的文章中我们详细介绍了 MergeTree 表引擎、MergeTree 家族其他表引擎、MergeTree 二级索引等内容，clickhouse数据库都是在单节点上运行的，作为OLAP处理的大数据利器，clickhouse 显然少了两个功能——数据高可用（HA）和横向扩展。HA的目的是为了如果有一个数据副本丢失或者损坏不至于完全丢失数据，至于横向扩展自然是为了提高数据存储能力了。
1. MergeTree副本表
ClickHouse MergeTree 副本表的数据一致性同步是通过Zookeeper实