clickhouse部署应用以及分布式表

it2026-02-19 7

部署

[root@slave1 ~]# yum install yum-utils [root@slave1 ~]# rpm --import https://repo.clickhouse.tech/CLICKHOUSE-KEY.GPG [root@slave1 ~]# yum-config-manager --add-repo https://repo.clickhouse.tech/rpm/stable/x86_64 Loaded plugins: fastestmirror adding repo from: https://repo.clickhouse.tech/rpm/stable/x86_64 [repo.clickhouse.tech_rpm_stable_x86_64] name=added from: https://repo.clickhouse.tech/rpm/stable/x86_64 baseurl=https://repo.clickhouse.tech/rpm/stable/x86_64 enabled=1 [root@slave1 ~]# yum install clickhouse-server clickhouse-client Loaded plugins: fastestmirror repo.clickhouse.tech_rpm_stable_x86_64 | 3.0 kB 00:00:00 repo.clickhouse.tech_rpm_stable_x86_64/primary_db | 267 kB 00:00:01 Loading mirror speeds from cached hostfile * base: mirrors.aliyun.com * epel: hk.mirrors.thegigabit.com * extras: mirrors.aliyun.com * updates: mirrors.aliyun.com Resolving Dependencies --> Running transaction check ---> Package clickhouse-client.noarch 0:20.9.3.45-2 will be installed --> Processing Dependency: clickhouse-common-static = 20.9.3.45-2 for package: clickhouse-client-20.9.3.45-2.noarch ---> Package clickhouse-server.noarch 0:20.9.3.45-2 will be installed --> Running transaction check ---> Package clickhouse-common-static.x86_64 0:20.9.3.45-2 will be installed --> Finished Dependency Resolution Dependencies Resolved =================================================================================================================================================================== Package Arch Version Repository Size =================================================================================================================================================================== Installing: clickhouse-client noarch 20.9.3.45-2 repo.clickhouse.tech_rpm_stable_x86_64 123 k clickhouse-server noarch 20.9.3.45-2 repo.clickhouse.tech_rpm_stable_x86_64 147 k Installing for dependencies: clickhouse-common-static x86_64 20.9.3.45-2 repo.clickhouse.tech_rpm_stable_x86_64 135 M Transaction Summary =================================================================================================================================================================== Install 2 Packages (+1 Dependent package) Total download size: 136 M Installed size: 527 M Is this ok [y/d/N]: y Downloading packages: (1/3): clickhouse-client-20.9.3.45-2.noarch.rpm | 123 kB 00:00:01 (2/3): clickhouse-server-20.9.3.45-2.noarch.rpm | 147 kB 00:00:01 (3/3): clickhouse-common-static-20.9.3.45-2.x86_64.rpm | 135 MB 00:02:28 ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 934 kB/s | 136 MB 00:02:28 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : clickhouse-common-static-20.9.3.45-2.x86_64 1/3 Installing : clickhouse-client-20.9.3.45-2.noarch 2/3 Installing : clickhouse-server-20.9.3.45-2.noarch 3/3 Created symlink from /etc/systemd/system/multi-user.target.wants/clickhouse-server.service to /etc/systemd/system/clickhouse-server.service. Path to data directory in /etc/clickhouse-server/config.xml: /var/lib/clickhouse/ Verifying : clickhouse-client-20.9.3.45-2.noarch 1/3 Verifying : clickhouse-common-static-20.9.3.45-2.x86_64 2/3 Verifying : clickhouse-server-20.9.3.45-2.noarch 3/3 Installed: clickhouse-client.noarch 0:20.9.3.45-2 clickhouse-server.noarch 0:20.9.3.45-2 Dependency Installed: clickhouse-common-static.x86_64 0:20.9.3.45-2 Complete!

修改 ulimit 配置

[root@master apache-zookeeper-3.5.8]# cat /etc/security/limits.d/clickhouse.conf clickhouse soft nofile 1073741824 clickhouse hard nofile 1073741824

config文件配置

<remote_servers incl="clickhouse_remote_servers" > <perftest_2shards_1replicas>  <shard> <internal_replication>true</internal_replication> <replica> <host>master</host> <port>9000</port> </replica> </shard>  <shard> <internal_replication>true</internal_replication> <replica> <host>slave1</host> <port>9000</port> </replica> <replica> <host>slave2</host> <port>9000</port> </replica> </shard> </perftest_2shards_1replicas> </remote_servers> <zookeeper incl="zookeeper-servers" optional="true" /> <zookeeper-servers> <node index="1"> <host>master</host> <port>2181</port> </node> <node index="2"> <host>slave1</host> <port>2181</port> </node> <node index="3"> <host>slave2</host> <port>2181</port> </node> </zookeeper-servers>

查询集群情况

两个分片，其中一个分片只有一个副本，另外一个分片有两个副本

master :) select * from clusters; SELECT * FROM clusters ┌─cluster────────────────────┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name─┬─host_address────┬─port─┬─is_local─┬─user────┬─default_database─┬─errors_count─┬─estimated_recovery_time─┐ │ perftest_2shards_1replicas │ 1 │ 1 │ 1 │ master │ 192.168.153.136 │ 9000 │ 1 │ default │ │ 0 │ 0 │ │ perftest_2shards_1replicas │ 2 │ 1 │ 1 │ salve1 │ │ 9000 │ 0 │ default │ │ 0 │ 0 │ │ perftest_2shards_1replicas │ 2 │ 1 │ 2 │ salve2 │ │ 9000 │ 0 │ default │ │ 0 │ 0 │ └────────────────────────────┴───────────┴──────────────┴─────────────┴───────────┴─────────────────┴──────┴──────────┴─────────┴──────────────────┴──────────────┴─────────────────────────┘ 3 rows in set. Elapsed: 45.672 sec.

测试分布式表

在分片2上建本地表节点1 slave1 :) CREATE TABLE image_label2 ( label_id UInt32, label_name String, insert_time Date) ENGINE = ReplicatedMergeTree('/clickhouse/tables/01-01/image_label2','cluster01-01-1',insert_time, (label_id, insert_time), 8192) CREATE TABLE image_label2 ( `label_id` UInt32, `label_name` String, `insert_time` Date ) ENGINE = ReplicatedMergeTree('/clickhouse/tables/01-01/image_label2', 'cluster01-01-1', insert_time, (label_id, insert_time), 8192) Ok. 0 rows in set. Elapsed: 0.049 sec. 节点2 slave2 :) CREATE TABLE image_label2 ( label_id UInt32, label_name String, insert_time Date) ENGINE = ReplicatedMergeTree('/clickhouse/tables/01-01/image_label2','cluster01-01-2',insert_time, (label_id, insert_time), 8192) CREATE TABLE image_label2 ( `label_id` UInt32, `label_name` String, `insert_time` Date ) ENGINE = ReplicatedMergeTree('/clickhouse/tables/01-01/image_label2', 'cluster01-01-2', insert_time, (label_id, insert_time), 8192) Ok. 0 rows in set. Elapsed: 0.070 sec. 在分片1上建本地表 master :) CREATE TABLE image_label2 ( label_id UInt32, label_name String, insert_time Date) ENGINE = ReplicatedMergeTree('/clickhouse/tables/02-01/image_label2','cluster02-01-1',insert_time, (label_id, insert_time), 8192) CREATE TABLE image_label2 ( `label_id` UInt32, `label_name` String, `insert_time` Date ) ENGINE = ReplicatedMergeTree('/clickhouse/tables/02-01/image_label2', 'cluster02-01-1', insert_time, (label_id, insert_time), 8192) Ok. 0 rows in set. Elapsed: 0.059 sec. 在3个节点建立分布式表 slave1 :) CREATE TABLE image_label_all2 AS image_label2 ENGINE = Distributed(perftest_2shards_1replicas, default, image_label2, rand()) CREATE TABLE image_label_all2 AS image_label2 ENGINE = Distributed(perftest_2shards_1replicas, default, image_label2, rand()) Ok. 0 rows in set. Elapsed: 0.003 sec. slave2 :) CREATE TABLE image_label_all2 AS image_label2 ENGINE = Distributed(perftest_2shards_1replicas, default, image_label2, rand()) CREATE TABLE image_label_all2 AS image_label2 ENGINE = Distributed(perftest_2shards_1replicas, default, image_label2, rand()) Ok. 0 rows in set. Elapsed: 0.002 sec. master :) CREATE TABLE image_label_all2 AS image_label2 ENGINE = Distributed(perftest_2shards_1replicas, default, image_label2, rand()) CREATE TABLE image_label_all2 AS image_label2 ENGINE = Distributed(perftest_2shards_1replicas, default, image_label2, rand()) Ok. 0 rows in set. Elapsed: 0.003 sec. 往分片2的本地节点1写入数据 slave1 :) insert into image_label2(label_id,label_name)values(100,'test100'); INSERT INTO image_label2 (label_id, label_name) VALUES Ok. 1 rows in set. Elapsed: 0.021 sec. slave1 :) select * from image_label2; SELECT * FROM image_label2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 100 │ test100 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ 由于分片间有数据复制作用，分片1的节点2的本地表也有数据了 slave2 :) select * from image_label2; SELECT * FROM image_label2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 100 │ test100 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ 分片1本地表就没有数据 master :) select * from image_label2; SELECT * FROM image_label2 Ok. 0 rows in set. Elapsed: 0.003 sec. 查看分布式表 slave1 :) select * from image_label_all2 SELECT * FROM image_label_all2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 100 │ test100 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ 1 rows in set. Elapsed: 0.007 sec. slave2 :) select * from image_label_all2 SELECT * FROM image_label_all2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 100 │ test100 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ 1 rows in set. Elapsed: 0.009 sec. master :) select * from image_label_all2 SELECT * FROM image_label_all2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 100 │ test100 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ 1 rows in set. Elapsed: 0.008 sec. 然后往分片1的本地表写数据 master :) insert into image_label2(label_id,label_name)values(1000,'master1000'); INSERT INTO image_label2 (label_id, label_name) VALUES Ok. 1 rows in set. Elapsed: 0.020 sec. master :) select * from image_label2 SELECT * FROM image_label2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 1000 │ master1000 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ 1 rows in set. Elapsed: 0.002 sec. 分片2的两个节点的本地表看不到数据的 slave2 :) select * from image_label2; SELECT * FROM image_label2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 100 │ test100 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ 1 rows in set. Elapsed: 0.002 sec. slave1 :) select * from image_label2; SELECT * FROM image_label2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 100 │ test100 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ 1 rows in set. Elapsed: 0.002 sec. 再看分布式表，大家都能看到 slave1 :) select * from image_label_all2 SELECT * FROM image_label_all2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 100 │ test100 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ ┌─label_id─┬─label_name─┬─insert_time─┐ │ 1000 │ master1000 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ 2 rows in set. Elapsed: 0.006 sec. slave2 :) select * from image_label_all2 SELECT * FROM image_label_all2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 100 │ test100 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ ┌─label_id─┬─label_name─┬─insert_time─┐ │ 1000 │ master1000 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ 2 rows in set. Elapsed: 0.007 sec. master :) select * from image_label_all2 SELECT * FROM image_label_all2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 1000 │ master1000 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ ┌─label_id─┬─label_name─┬─insert_time─┐ │ 100 │ test100 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ 2 rows in set. Elapsed: 0.005 sec. 测试写分布式表 master :) insert into image_label_all2(label_id,label_name)values(2000,'dist2000'); INSERT INTO image_label_all2 (label_id, label_name) VALUES Ok. 1 rows in set. Elapsed: 0.003 sec. master :) select * from image_label_all2 SELECT * FROM image_label_all2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 1000 │ master1000 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ ┌─label_id─┬─label_name─┬─insert_time─┐ │ 100 │ test100 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ ┌─label_id─┬─label_name─┬─insert_time─┐ │ 2000 │ dist2000 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ 3 rows in set. Elapsed: 0.008 sec. master :) select * from image_label2 SELECT * FROM image_label2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 1000 │ master1000 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ 1 rows in set. Elapsed: 0.001 sec. 新写入的分布式表的数据落在分片2上 slave1 :) select * from image_label2 SELECT * FROM image_label2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 100 │ test100 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ ┌─label_id─┬─label_name─┬─insert_time─┐ │ 2000 │ dist2000 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ slave2 :) select * from image_label2 SELECT * FROM image_label2 ┌─label_id─┬─label_name─┬─insert_time─┐ │ 100 │ test100 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ ┌─label_id─┬─label_name─┬─insert_time─┐ │ 2000 │ dist2000 │ 1970-01-01 │ └──────────┴────────────┴─────────────┘ 2 rows in set. Elapsed: 0.002 sec.

最新回复(0)