文檔編寫(xiě)目的：記錄備忘

1.環(huán)境搭建

4臺(tái)centOS 7的主機(jī)，分別安裝了四個(gè)ClickHouse數(shù)據(jù)庫(kù)，安裝參考：http://m.itdecent.cn/p/2ce45a9c30ce

2.新增配置文件metrika.xml

vim /etc/clickhouse-server/metrika.xml

<yandex>
    <clickhouse_remote_servers>
        <!--集群名稱(chēng) 自定義 -->
        <my_cluster>
            <!-- 分片1  -->
            <shard>
                <internal_replication>true</internal_replication>
                <replica>
                    <host>192.168.120.87</host>
                    <port>9000</port>
                    <user>sunny</user>
                    <password>sunny</password>
                </replica>
            </shard>

            <!-- 分片2  -->
            <shard>
                <internal_replication>true</internal_replication>
                <replica>
                    <host>192.168.120.103</host>
                    <port>9000</port>
                    <user>sunny</user>
                    <password>sunny</password>
                </replica>
            </shard>

            <!-- 分片3  -->
            <shard>
                <internal_replication>true</internal_replication>
                <replica>
                    <host>192.168.120.105</host>
                    <port>9000</port>
                    <user>sunny</user>
                    <password>sunny</password>
                </replica>
            </shard>

            <!--分片4-->
            <shard>
                <internal_replication>true</internal_replication>
                <replica>
                    <host>192.168.120.140</host>
                    <port>9000</port>
                    <user>sunny</user>
                    <password>sunny</password>
                </replica>
            </shard>


        </my_cluster>
    </clickhouse_remote_servers>
    <!-- 副本名稱(chēng) -->
    <macros>
        <shard>node01</shard>
        <replica>192.168.120.87</replica>
    </macros>

    <networks>
        <ip>::/0</ip>
    </networks>
    <!-- 可不配置 -->
    <zookeeper-servers>
        <node index="1">
            <host>192.168.120.87</host>
            <port>2181</port>
        </node>
        <node index="2">
            <host>192.168.120.103</host>
            <port>2181</port>
        </node>
        <node index="3">
            <host>192.168.120.105</host>
            <port>2181</port>
        </node>
    </zookeeper-servers>

    <!-- 數(shù)據(jù)壓縮算法  -->
    <clickhouse_compression>
        <case>
            <min_part_size>10000000000</min_part_size>
            <min_part_size_ratio>0.01</min_part_size_ratio>
            <method>lz4</method>
        </case>
    </clickhouse_compression>
</yandex>

通過(guò)scp將metrika.xml文件發(fā)送到其他三臺(tái)服務(wù)器上

scp /etc/clickhouse-server/metrika.xml root@192.168.120.103:/etc/clickhouse-server/metrika.xml

然后修改其他三臺(tái)服務(wù)器上的metrika.xml，僅修改圖中紅框代碼，互不重復(fù)，其他都不變：

image.png

然后編輯4個(gè)服務(wù)器上的config.xml,引入metrika.xml：

vim /etc/clickhouse-server/config.xml

加上下面這句:

<include_from>/etc/clickhouse-server/metrika.xml</include_from>

image.png

重啟服務(wù):

 service clickhouse-server restart

3.測(cè)試

此種集群只適合表引擎為MergeTree的表，不適合ReplicatedMergeTree，在每個(gè)服務(wù)器數(shù)據(jù)庫(kù)創(chuàng)建如下表:

-- 本地表
create table user_local
(
    id         Int32,
    user_code  String,
    user_name  String,
    createDate Date
) engine = MergeTree(createDate,(id,user_code),8192);

--分布式表
create table user_all as user_local ENGINE = Distributed(my_cluster, ClickHouseTest, user_local, rand());

插入數(shù)據(jù):

insert into user_all (id, user_code, user_name, createDate) values (1, 1001, '張三', '2020-01-01');
insert into user_all (id, user_code, user_name, createDate) values (2, 1002, '張三', '2020-01-02');
insert into user_all (id, user_code, user_name, createDate) values (3, 1003, '張三', '2020-01-03');
insert into user_all (id, user_code, user_name, createDate) values (4, 1004, '張三', '2020-01-04');
insert into user_all (id, user_code, user_name, createDate) values (5, 1005, '張三', '2020-01-05');
insert into user_all (id, user_code, user_name, createDate) values (6, 1006, '張三', '2020-01-06');
insert into user_all (id, user_code, user_name, createDate) values (7, 1007, '張三', '2020-01-07');
insert into user_all (id, user_code, user_name, createDate) values (8, 1008, '張三', '2020-01-08');
insert into user_all (id, user_code, user_name, createDate) values (9, 1009, '張三', '2020-01-09');
insert into user_all (id, user_code, user_name, createDate) values (10, 1010, '張三', '2020-01-10');
insert into user_all (id, user_code, user_name, createDate) values (11, 1011, '張三', '2020-01-11');
insert into user_all (id, user_code, user_name, createDate) values (12, 1012, '張三', '2020-01-12');
insert into user_all (id, user_code, user_name, createDate) values (13, 1013, '張三', '2020-01-13');
insert into user_all (id, user_code, user_name, createDate) values (14, 1014, '張三', '2020-01-14');
insert into user_all (id, user_code, user_name, createDate) values (15, 1015, '張三', '2020-01-15');

查詢(xún)某個(gè)服務(wù)器的user_local和user_all
查詢(xún)結(jié)果如下:
user_local

image.png

user_all

image.png

總結(jié)：user_local僅展示本服務(wù)器上的存在的數(shù)據(jù),user_all查詢(xún)集群中所有user_local表的表數(shù)據(jù)之和。此種方式為多分片單副本的集群方式,僅通過(guò)clickhouse的配置文件即可實(shí)現(xiàn)，不需要zookeeper，通過(guò)user_all表插入數(shù)據(jù)時(shí)會(huì)將插入的數(shù)據(jù)隨機(jī)的插入至集群中的user_local表中，查詢(xún)時(shí)將集群中所有user_local表中符合條件的數(shù)據(jù)查詢(xún)出來(lái)，再匯總在一起。此種集群方式無(wú)法實(shí)現(xiàn)高可用，若集群中有一個(gè)服務(wù)器掛掉，集群中的其他節(jié)點(diǎn)也無(wú)法在提供數(shù)據(jù)查詢(xún)和存儲(chǔ)。