ClickHouse集群测试

作者: Huang Jinqiang 分类: Database 发布时间: 2018-06-30 16:20

本测试采用ReplicatedMergeTree + Distributed引擎作为集群结构的引擎
需要注意的是ReplicatedMergeTree('zk路径,如果要相互复制,这里必须一样', '副本名称, 必须不一样', 剩下的就是分区和主键,以及索引粒度)

[root@node100 ~]# clickhouse-client -m --password UHXQQmhb
ClickHouse client version 1.1.54385.
Connecting to localhost:9000.
Connected to ClickHouse server version 1.1.54385.
localhost 🙂 create database ontime; #所有节点执行
localhost 🙂 use ontime; #所有节点执行
localhost 🙂 CREATE TABLE ontime (FlightDate Date,Year UInt16) ENGINE = ReplicatedMergeTree('/clickhouse/tables/ontime/shard1','1',FlightDate,(Year, FlightDate),8192); #分片1的第1个副本执行
localhost 🙂 CREATE TABLE ontime (FlightDate Date,Year UInt16) ENGINE = ReplicatedMergeTree('/clickhouse/tables/ontime/shard1','2',FlightDate,(Year, FlightDate),8192); #分片1的第2个副本执行
localhost 🙂 CREATE TABLE ontime (FlightDate Date,Year UInt16) ENGINE = ReplicatedMergeTree('/clickhouse/tables/ontime/shard2','1',FlightDate,(Year, FlightDate),8192); #分片2的第1个副本执行
localhost 🙂 CREATE TABLE ontime (FlightDate Date,Year UInt16) ENGINE = ReplicatedMergeTree('/clickhouse/tables/ontime/shard2','2',FlightDate,(Year, FlightDate),8192); #分片2的第2个副本执行
localhost 🙂 CREATE TABLE ontime_all (FlightDate Date,Year UInt16) ENGINE= Distributed(test_ck_cluster, ontime, ontime, rand()); #所有节点

在100向本地表写入数据
172.16.120.100 🙂 insert into ontime (FlightDate,Year)values('2001-10-12',2001);

可以发现100和101两个节点的本地表均可查询到数据
172.16.120.100 🙂 select * from ontime;
SELECT *
FROM ontime
┌─FlightDate─┬─Year─┐
│ 2001-10-12 │ 2001 │
└────────────┴──────┘
172.16.120.101 🙂 select * from ontime;
SELECT *
FROM ontime
┌─FlightDate─┬─Year─┐
│ 2001-10-12 │ 2001 │
└────────────┴──────┘

在100和102两个节点分别查询分布式表也可以查询到数据
172.16.120.100 🙂 select * from ontime_all;
SELECT *
FROM ontime_all
┌─FlightDate─┬─Year─┐
│ 2001-10-12 │ 2001 │
└────────────┴──────┘
1 rows in set. Elapsed: 0.003 sec.
172.16.120.102 🙂 select * from ontime_all;
SELECT *
FROM ontime_all
┌─FlightDate─┬─Year─┐
│ 2001-10-12 │ 2001 │
└────────────┴──────┘
1 rows in set. Elapsed: 0.004 sec.

同样在102节点的本地表写入数据,102和103节点本地表可查询到数据, 101和102两个节点的分布式表同样可以查询到数据
172.16.120.102 🙂 insert into ontime (FlightDate,Year)values('1987-07-05',1987);
INSERT INTO ontime (FlightDate, Year) VALUES
Ok.
1 rows in set. Elapsed: 0.054 sec.
172.16.120.102 🙂 select * from ontime;
SELECT *
FROM ontime
┌─FlightDate─┬─Year─┐
│ 1987-07-05 │ 1987 │
└────────────┴──────┘
1 rows in set. Elapsed: 0.030 sec.
172.16.120.103 🙂 select * from ontime;
SELECT *
FROM ontime
┌─FlightDate─┬─Year─┐
│ 1987-07-05 │ 1987 │
└────────────┴──────┘
1 rows in set. Elapsed: 0.002 sec.

172.16.120.100 🙂 select * from ontime_all;
SELECT *
FROM ontime_all
┌─FlightDate─┬─Year─┐
│ 2001-10-12 │ 2001 │
└────────────┴──────┘
┌─FlightDate─┬─Year─┐
│ 1987-07-05 │ 1987 │
└────────────┴──────┘
2 rows in set. Elapsed: 0.004 sec.

172.16.120.102 🙂 select * from ontime_all;
SELECT *
FROM ontime_all
┌─FlightDate─┬─Year─┐
│ 1987-07-05 │ 1987 │
└────────────┴──────┘
┌─FlightDate─┬─Year─┐
│ 2001-10-12 │ 2001 │
└────────────┴──────┘
2 rows in set. Elapsed: 0.005 sec.

删除数据
localhost 🙂 select * from ontime_all;
SELECT *
FROM ontime_all
┌─FlightDate─┬─Year─┐
│ 2001-10-18 │ 2001 │
└────────────┴──────┘
┌─FlightDate─┬─Year─┐
│ 2001-10-12 │ 2001 │
│ 2001-10-13 │ 2001 │
│ 2001-10-14 │ 2001 │
│ 2001-10-15 │ 2001 │
│ 2001-10-16 │ 2001 │
└────────────┴──────┘
┌─FlightDate─┬─Year─┐
│ 2001-10-17 │ 2001 │
└────────────┴──────┘
┌─FlightDate─┬─Year─┐
│ 2001-10-19 │ 2001 │
└────────────┴──────┘
┌─FlightDate─┬─Year─┐
│ 1987-07-05 │ 1987 │
└────────────┴──────┘
9 rows in set. Elapsed: 0.008 sec.

localhost 🙂 ALTER table ontime DROP PARTITION '198707';
ALTER TABLE ontime
DROP PARTITION '198707'
Ok.
0 rows in set. Elapsed: 0.133 sec.

localhost 🙂 select * from ontime_all;
SELECT *
FROM ontime_all
┌─FlightDate─┬─Year─┐
│ 2001-10-18 │ 2001 │
└────────────┴──────┘
┌─FlightDate─┬─Year─┐
│ 2001-10-12 │ 2001 │
│ 2001-10-13 │ 2001 │
│ 2001-10-14 │ 2001 │
│ 2001-10-15 │ 2001 │
│ 2001-10-16 │ 2001 │
└────────────┴──────┘
┌─FlightDate─┬─Year─┐
│ 2001-10-19 │ 2001 │
└────────────┴──────┘
┌─FlightDate─┬─Year─┐
│ 2001-10-17 │ 2001 │
└────────────┴──────┘
8 rows in set. Elapsed: 0.005 sec.

经过测试
所有副本节点均可本地表和分布式表均可读写数据

2条评论
  • leven

    2018年8月1日 16:49

    可以将配置给出来吗?

    1. Huang Jinqiang

      2018年8月4日 10:16

      参考集群安装那篇文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注