1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

    <?xml version="1.0" encoding="UTF-8"?>
    <yandex>
        <clickhouse_remote_servers>
            <default_cluster>
                <shard>
                    <internal_replication>false</internal_replication>
                    <replica>
                        <host>10.0.3.27</host>
                        <port>9000</port>
                    </replica>
                    <replica>
                        <host>10.0.3.41</host>
                        <port>9000</port>
                    </replica>
                </shard>
                <shard>
                    <internal_replication>true</internal_replication>
                    <replica>
                        <host>10.0.3.46</host>
                        <port>9000</port>
                    </replica>
                    <replica>
                        <host>10.0.3.26</host>
                        <port>9000</port>
                    </replica>
                </shard>
            </default_cluster>
        </clickhouse_remote_servers>
        <zookeeper-servers>
            <node>
                <host>10.0.3.12</host>
                <port>2181</port>
            </node>
            <node>
                <host>10.0.3.3</host>
                <port>2181</port>
            </node>
            <node>
                <host>10.0.3.23</host>
                <port>2181</port>
            </node>
        </zookeeper-servers>
    </yandex>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

    -- 查看复制表数量
    select database,count(*) cnt
            ,sum(case when engine ='ReplicatedMergeTree' then 1 else 0 end) cnt_rmt
    from clusterAllReplicas('default_cluster', 'system.tables')
    group by database order by count(*) desc

    -- 查看复制表明细
    select name ,engine, hostname(), metadata_modification_time, total_rows, total_bytes
    from clusterAllReplicas('default_cluster', 'system.tables')
    where database = 'db' and engine = 'ReplicatedMergeTree';

1
2
3

    -- 复制表结构，可以快速建立 相同表结构，但是不同引擎和存储方案 的表。 
    -- 例如大量数据的导入场景，直接通过JDBC写入分布式分区复制表 对CK的压力（ZK同步的压力） 会比 JDBC先写入分布式分区表 再ck内部insert转移到分布式分区复制表 的方案小，且稳定
    CREATE TABLE [IF NOT EXISTS] [db.]table_name CLONE AS [db2.]name2 [ENGINE = engine]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

        Properties properties = new Properties();
        properties.setProperty("socket_keepalive", "true"); //socket_timeout时间由系统设置
        properties.setProperty("auto_discovery", "true"); // 节点自动发现
        properties.setProperty("load_balancing_policy", "roundRobin"); // 负载均衡
        properties.setProperty("health_check_interval", "1000"); // 健康检查间隔(以毫秒为单位)
        properties.setProperty("health_check_query", "select 1"); // 健康检查语句
        properties.setProperty("node_check_interval", "1000"); // 节点检查间隔(以毫秒为单位)
        properties.setProperty("failover", "2"); // 发生故障转移最大次数
        properties.setProperty("retry", "2"); // 故障重试最大次数

        // 客户端负载均衡的方式
        String url = "jdbc:clickhouse://h1:p1,h2:p2,h3:p3,h4:p4/default?socket_timeout=6000000";
        ClickHouseDataSource dataSource = new ClickHouseDataSource(url, properties)