文檔編寫目的：記錄備忘

1.環(huán)境搭建

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

2.新增配置文件metrika.xml

vim /etc/clickhouse-server/metrika.xml

<yandex>
    <clickhouse_remote_servers>
        <!--集群名稱 自定義 -->
        <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>
    <!-- 副本名稱 -->
    <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>

通過scp將metrika.xml文件發(fā)送到其他三臺服務器上

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

然后修改其他三臺服務器上的metrika.xml，僅修改圖中紅框代碼，互不重復，其他都不變：

image.png

然后編輯4個服務器上的config.xml,引入metrika.xml：

vim /etc/clickhouse-server/config.xml

加上下面這句:

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

image.png

重啟服務:

 service clickhouse-server restart

3.測試

此種集群只適合表引擎為MergeTree的表，不適合ReplicatedMergeTree，在每個服務器數(shù)據(jù)庫創(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');

查詢某個服務器的user_local和user_all
查詢結(jié)果如下:
user_local

image.png

user_all

image.png

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