ClickHouse集群部署(四)

EMQX 客服发表于:2022年03月07日 14:20:02


  1. metrika.xml
        <?xml version="1.0"?>
        <yandex>    
            <!--ck集群节点-->    
            <clickhouse_remote_servers>        
            <emqx_cluster_all>            
            <!--分片1-->            
                <shard>                
                <internal_replication>true</internal_replication>                
                    <replica>                    
                    <host>192.168.0.27</host>                    
                    <port>9000</port>                    
                    <user>default</user>                    
                    <password>supersecrect</password>                
                </replica>                
                <!--复制集1-->                
                <replica>                    
                    <host>192.168.0.74</host>                    
                    <port>9002</port>                    
                    <user>default</user>                    
                    <password>supersecrect</password>                
                </replica>            
            </shard>            
            <!--分片2-->            
            <shard>                
            <internal_replication>true</internal_replication>                
                <replica>                    
                    <host>192.168.0.221</host>                   
                    <port>9000</port>                    
                    <user>default</user>                    
                    <password>supersecrect</password>                
                </replica>                
                <!--复制集2-->                
                <replica>                    
                    <host>192.168.0.27</host>                    
                    <port>9002</port>                    
                    <user>default</user>                    
                    <password>supersecrect</password>                
                </replica>            
            </shard>            
            <!--分片3-->            
            <shard>                
            <internal_replication>true</internal_replication>                
            <replica>                    
                <host>192.168.0.74</host>                    
                <port>9000</port>                    
                <user>default</user>                    
                <password>supersecrect</password>                
            </replica>                
            <!--复制集3-->                
            <replica>                    
                <host>192.168.0.221</host>                    
                <port>9002</port>                    
                <user>default</user>                    
                <password>supersecrect</password>                
            </replica>            
            </shard>        
            </emqx_cluster_all>    
            </clickhouse_remote_servers>    
            <!--zookeeper相关配置-->    
            <zookeeper-servers>        
                <node index="1">            
                    <host>192.168.0.27</host>            
                    <port>2181</port>        
                </node>        
                <node index="2">            
                    <host>192.168.0.221</host>            
                    <port>2181</port>        
                </node>        
                <node index="3">            
                    <host>192.168.0.74</host>            
                    <port>2181</port>        
                </node>    
                </zookeeper-servers>    
                <macros>        
                    <layer>01</layer>        
                    <shard>01</shard>        
                    <!--分片号-->        
                    <replica>node6</replica>        
                    <!--当前节点IP-->    
                </macros>    
                <networks>        
                    <ip>::/0</ip>    
                </networks>    
                <!--压缩相关配置-->    
                <clickhouse_compression>        
                <case>            
                    <min_part_size>10000000000</min_part_size>            
                    <min_part_size_ratio>0.01</min_part_size_ratio>            
                    <method>lz4</method>            
                    <!--压缩算法lz4压缩比zstd快, 更占磁盘-->        
                </case>    
                </clickhouse_compression>
            </yandex>

    image.png

    image.png

    users.xml

    每个node中users.xml是相同配置,拷贝node1中的users.xml

    (8)修改属主

    每台clickhouse 机器都执行

    chown -R  clickhouse.clickhouse /data/clickhouse

    image.png

    (9)进程守护

    使用systemd管理

    先删除每台机器上默认安装的文件:/etc/system/system/clickhouse-server.service

    同时移除:systemctl disable clickhouse-server.service

    image.png

    以node1为例:

    vim /etc/systemd/system/clickhouse_node1.service
    # vim /etc/systemd/system/clickhouse_node1.service  
    
    [Unit]
    Description=ClickHouse Server (analytic DBMS for big data)
    Requires=network-online.target
    After=network-online.target 
    
    [Service]
    #Type=simple
    Type=forking
    User=clickhouse
    Group=clickhouseRestart=always
    RestartSec=30
    RuntimeDirectory=clickhouse-server
    #ExecStart=/usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid
    ExecStart=/usr/bin/clickhouse-server --daemon --config=/data/clickhouse/ch_9000/config.xml --pid-file=/data/clickhouse/node1/clickhouse-server.pid
    #PIDFile=/data/clickhouse/node1/clickhouse-server.pid
    LimitCORE=infinityLimitNOFILE=500000
    CapabilityBoundingSet=CAP_NET_ADMIN CAP_IPC_LOCK CAP_SYS_NICE 
    
    [Install]
    WantedBy=multi-user.target

    image.png

    (10)启动

    1. esc-clickhouse-001:

    systemctl start clickhouse_node1.service

    systemctl start clickhouse_node4.service

    2. esc-clickhouse-001:

    systemctl start clickhouse_node2.service

    systemctl start clickhouse_node5.service

    3. esc-clickhouse-001:

    systemctl start clickhouse_node3.service

    systemctl start clickhouse_node6.service

    (11)设置开机自启

    检查是否开机启动

    systemctl list-unit-files |   grep enable|grep click

    image.png

    删除不需要的开机启动

    systemctl disable clickhouse-server.service

    image.png

    设置开机启动

    systemctl enable clickhouse_node2.service

    image.png

四、验证

  1. 连接

    Clickhouse-client -u default –password supersecrect –port 9000 -h192.168.0.27

    image.png

  2. 查询节点信息

    select * from system.clusters;

    image.png

五、创建表

  1. 创建database

    create database testdb on cluster emqx_cluster_all;

    image.png

  2. 创建表(ReplicatedMargeTree)

    CREATE TABLE testdb.test_local on cluster emqx_cluster_all
    (    
        msgid String,    
        topic String,    
        clientid String,    
        payload Nullable(String),    
        eventDate DateTime
    ) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/testdb/test_local', '{replica}')
    PARTITION BY toYYYYMM(eventDate)
    ORDER BY (msgid, eventDate, clientid);

    image.png

  3. 创建分布式表

    CREATE TABLE testdb.test ON CLUSTER emqx_cluster_all AS testdb.test_local engine = Distributed(emqx_cluster_all, testdb, test_local, rand());

    image.png

  4. 查看表

    use testdb;
    show tables;

    image.png

  5. 查看zookeeper目录

    select * from system.zookeeper WHERE path='/';

    image.png

六、EMQ对接

  1. 创建资源

    image.png

  2. 创建规则引擎

    Sql: SELECT * FROM "test/#"

    image.png

    响应动作:

    SQL:insert into testdb.test(eventDate,msgid, clientid, topic, payload) values (now(),'${id}', '${clientid}', '${topic}', '${payload}')

    image.png

  3. 客户端发送数据

    采用emq dashboard 自带客户端测试发布消息

    image.png

  4. 数据验证

    (1)规则引擎统计

    image.png

    (2)Clickhouse集群验证

             集群查询:select * from testdb.test;

    image.png

    查看是否在多节点上存数据:

    image.png



    您需要登录后才可以回复