Clickhouse数据库八：搭建分片集群(高并发)

川长思鸟来 2022-11-12 10:53 226阅读 0赞

# Clickhouse数据库八：搭建分片集群(高并发) #

复本虽然能够提高数据的可用性，降低丢失风险，但是对数据的横向扩容没有解决。每台机子实际上必须容纳全量数据。

要解决数据`水平切分`的问题，需要引入`分片`的概念。

`通过分片把一份完整的数据进行切分，不同的分片分布到不同的节点上`。再通过`Distributed表引擎`把数据拼接起来一同使用。

Distributed表引擎本身不存储数据，有点类似于MyCat之于MySql，成为一种中间件，通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。

## 8.1 读写原理 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 1]

## 8.2 分片集群规划 ##

规划一个即分片, 有复本的集群.  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][20210323205402376.png]

**说明:**

1.  shard1 一共两个复本(hadoop102, hadoop103)
2.  shard2 只有一个复本(hadoop104)

## 8.3配置分片集群 ##

*  在hadoop102, 打开配置文件:

sudo vim /etc/clickhouse-server/config.d/metrika.xml

<?xml version="1.0"?>
    <yandex>
        <clickhouse_remote_servers>
        <gmall_cluster>  
        <shard>         
            <internal_replication>true</internal_replication>
            <replica>    
                <host>hadoop102</host>
                <port>9000</port>
            </replica>
            <replica>    
                <host>hadoop103</host>
                <port>9000</port>
            </replica>
        </shard>
    
        <shard>  
            <internal_replication>true</internal_replication>
            <replica>    
                <host>hadoop104</host>
                <port>9000</port>
            </replica>
        </shard>
         
        </gmall_cluster>
         
        </clickhouse_remote_servers>
         
        <zookeeper-servers>
            <node index="1">
                <host>hadoop102</host>
                <port>2181</port>
            </node>
    
            <node index="2">
                <host>hadoop103</host>
                <port>2181</port>
            </node>
            <node index="3">
                <host>hadoop104</host>
                <port>2181</port>
            </node>
        </zookeeper-servers>
         
    	
        <macros>
            <shard>01</shard>   
            <replica>hadoop102</replica>  
        </macros>
    </yandex>

分发metrika.xml到hadoop103,hadoop104

1.  不要忘记先在hadoop104安装clickhouse
2.  分发/etc/clickhouse-server/config.xml
3.  分发/etc/clickhouse-server/config.d/metrika.xml

hadoop103 的宏

<macros>
            <shard>01</shard>   
            <replica>hadoop103</replica>  
        </macros>
    </yandex>

hadoop104的宏

<macros>
            <shard>02</shard>   
            <replica>hadoop104</replica>  
        </macros>
    </yandex>

## 8.4 3个节点创建数据库 ##

分别在hadoop102,hadoop103,hadoop104上创建数据库gmall

create database gmall;

## 8.5 任意节点创建本地表 ##

选择`任意一节点`创建本地表, 会自动同步到其他节点  
本地表只负责存储自己的切片数据!

create table st_order_mt_gmall on cluster gmall_cluster (
        id UInt32,
        sku_id String,
        total_amount Decimal(16,2),
        create_time  Datetime
    ) engine =ReplicatedMergeTree('/clickhouse/tables/gmall/{shard}/st_order_mt_gmall','{replica}')
    partition by toYYYYMMDD(create_time)
    primary key (id)
    order by (id,sku_id);

## 8.6 创建分布式表st\_order\_mt\_gmall\_all ##

在hadoop102创建分布式表

create table st_order_mt_gmall_all on cluster gmall_cluster
    (
        id UInt32,
        sku_id String,
        total_amount Decimal(16,2),
        create_time  Datetime
    )engine = Distributed(gmall_cluster,gmall, st_order_mt_gmall,hiveHash(sku_id));

## 8.7 通过分布式表添加数据 ##

insert into  st_order_mt_gmall_all 
    values(201,'sku_001',1000.00,'2020-06-01 12:00:00') ,
    (202,'sku_002',2000.00,'2020-06-01 12:00:00'),
    (203,'sku_004',2500.00,'2020-06-01 12:00:00'),
    (204,'sku_002',2000.00,'2020-06-01 12:00:00')
    (205,'sku_003',600.00,'2020-06-02 12:00:00')

## 8.8 查询数据 ##

*  通过分布式表查询

select * from st_order_mt_gmall_all;

可以查询到所有数据!  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 3]

*  通过本地表查询

select * from st_order_mt_gmall;

只能查到当前节点的分片数据  
![在这里插入图片描述][20210323205739944.png]

![在这里插入图片描述][20210323205743714.png]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 4]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70]: /images/20221022/39a5994945104315b3157a9b4e66e3ef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 1]: /images/20221022/1f125067617f4da0b509363ac5d3b230.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 2]: /images/20221022/e96276fd8e684aa2b99fc3b71ccc0867.png
[20210323205402376.png]: /images/20221022/8903a484578f4604ab7145ccf76ed1c3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 3]: /images/20221022/a51271fcbf1b4e10b3c20f19d97e5723.png
[20210323205739944.png]: /images/20221022/afb5ed874d8a496a9c7787d55fdac9e8.png
[20210323205743714.png]: /images/20221022/b94958e183114336b3d606a3f8f9cb0d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc5NjQwMw_size_16_color_FFFFFF_t_70 4]: /images/20221022/aa6ddc9f0360434cafcc4cb897d9e29c.png