clickhouse数据迁移（clickhouse-copier）

首先谈一谈clickhouse-copier工具的优点

1、支持并发同步，可以运行多个clickhouse-copier实例

2、使用zookeeper同步写入状态，支持增量同步

3、可以在配置文件内重新定义写入的表引擎

4、表名与数据库名不需要相同

5、可指定source与sink的shard,replica

缺点

配置文件较为繁琐，每个不同实例clickhouse-copier任务的配置文件都需要上传至zookeeper相应实例节点上慢。相比于直接迁移文件，clickhouse-copier相当于多次执行insert into操作，因此迁移速度较慢。

注意点

1、source表需要定义了partition,不然任务同步报错。

2、zookeeper内存储的是已处理过的partition信息，再次运行clickhouse-copier,同步的仅是未处理过的partition数据。对于已经同步后的partition，即使partition内有新增数据，也不会同步到目标集群上。

3、clickhouse-copier无法同步普通视图，但可以同步物化视图,若同步物化视图，需要在source的表名前加上.inner.,不然会提示找不到table。

4、使用Clickhouse-copier需要借助zookeeper，为减少网络流量，建议clickhouse-copier在源数据所在的服务器上运行。

clickhouse-copier --daemon --config zookeeper.xml --task-path /task/path --base-dir /path/to/dir

常用参数

daemon — 后台运行copier工具，进程将在后台启动。 

config — zookeeper.xml的存放路径，用来连接zookeeper集群。

task-path — zookeeper上的存储节点路径，例如：/clickhouse/copier_task/task1;该路径中的内容用来存储任务，以及多个copier进程间的协调信息，建议不同的数据任务，路径不要重名，例如：/clickhouse/copier_task/task2,task3,task4,或者每天同步做一次数据copy的话，也可以以当天日期命名，task-2021-01-27，但同一任务的不同copier进程要保持一致的配置路径。

task-file — 指向配置了任务的配置文件，例如：copy-job.xml,该文件内容会上传到zookeeper的/clickhouse/copier_task/task1/description节点。

task-upload-force — 若设置为true,那么将根据task-file文件的内容，强制刷新覆盖上个参数提到的zookeeper的description节点。

base-dir — 会存储一些日志以及相关的辅助型文件，copier工具进程启动后，会在$base-dir创建copier_YYYYMMHHSS_<PID>格式的子目录（日志文件会在该子目录下，以及辅助型分布式表的相关信息在data目录下），若没有传该参数，则在copier运行的当前目录创建。

clickhouse-copier同步流程

1、创建zookeeper.xml

<clickhouse>
    <logger>
        <level>trace</level>
        <size>100M</size>
        <count>3</count>
    </logger>

    <zookeeper>
        <node index="1">
            <host>127.0.0.1</host>
            <port>2181</port>
        </node>
    </zookeeper>
</clickhouse>

2、创建task.xml配置

<clickhouse>
    <!-- 配置源端集群 -->
    <remote_servers>
        <source_cluster>
            <shard>
                <internal_replication>false</internal_replication>
                    <replica>
                        <host>127.0.0.1</host>
                        <port>9000</port>
                    </replica>
            </shard>
            ...
        </source_cluster>
        
        <!-- 配置目标端集群 -->
        <destination_cluster>
        ...
        </destination_cluster>
    </remote_servers>

    <!-- How many simultaneously active workers are possible. If you run more workers superfluous workers will sleep. 最大工作进程数-->
    <max_workers>2</max_workers>

    <!-- 用于从源集群表中获取(提取)数据的设置 -->
    <settings_pull>
        <readonly>1</readonly>
    </settings_pull>

    <!-- 用于向目标集群表插入(推送)数据的设置 -->
    <settings_push>
        <readonly>0</readonly>
    </settings_push>

    <!-- Common setting for fetch (pull) and insert (push) operations. Also, copier process context uses it.
         They are overlaid by <settings_pull/> and <settings_push/> respectively. -->
    <settings>
        <connect_timeout>3</connect_timeout>
        <!-- Sync insert is set forcibly, leave it here just in case. -->
        <insert_distributed_sync>1</insert_distributed_sync>
    </settings>

    <!-- Copying tasks description.
         You could specify several table task in the same task description (in the same ZooKeeper node), they will be performed
         sequentially.
    -->
    <tables>
        <!-- A table task, copies one table. -->
        <table_hits>
            <!-- Source cluster name (from <remote_servers/> section) and tables in it that should be copied -->
            <cluster_pull>source_cluster</cluster_pull>
            <database_pull>test</database_pull>
            <table_pull>hits</table_pull>   <!-- 多分片多副本模式下这里采用本地表 -->

            <!-- Destination cluster name and tables in which the data should be inserted -->
            <cluster_push>destination_cluster</cluster_push>
            <database_push>test</database_push>
            <table_push>hits2</table_push>  <!-- 多分片多副本模式下这里采用本地表 -->

            <!-- Engine of destination tables.
                 If destination tables have not be created, workers create them using columns definition from source tables and engine
                 definition from here.

                 NOTE: If the first worker starts insert data and detects that destination partition is not empty then the partition will
                 be dropped and refilled, take it into account if you already have some data in destination tables. You could directly
                 specify partitions that should be copied in <enabled_partitions/>, they should be in quoted format like partition column of
                 system.parts table.
            -->
            <engine>
            ENGINE=ReplicatedMergeTree('/clickhouse/tables/{cluster}/{shard}/hits2', '{replica}')
            PARTITION BY toMonday(date)
            ORDER BY (CounterID, EventDate)
            </engine>

            <!-- Sharding key used to insert data to destination cluster -->
            <sharding_key>jumpConsistentHash(intHash64(UserID), 2)</sharding_key>

            <!-- Optional expression that filter data while pull them from source servers -->
            <where_condition>CounterID != 0</where_condition>

            <!-- This section specifies partitions that should be copied, other partition will be ignored.
                 Partition names should have the same format as
                 partition column of system.parts table (i.e. a quoted text).
                 Since partition key of source and destination cluster could be different,
                 these partition names specify destination partitions.

                 NOTE: In spite of this section is optional (if it is not specified, all partitions will be copied),
                 it is strictly recommended to specify them explicitly.
                 If you already have some ready partitions on destination cluster they
                 will be removed at the start of the copying since they will be interpeted
                 as unfinished data from the previous copying!!!
            -->
            <enabled_partitions>
                <partition>'2018-02-26'</partition>
                <partition>'2018-03-05'</partition>
                ...
            </enabled_partitions>
        </table_hits>

        <!-- Next table to copy. It is not copied until previous table is copying. -->
        <table_visits>
        ...
        </table_visits>
        ...
    </tables>
</clickhouse>

注意：源端目标端表在多分片多副本模式下为本地表，clickhouse-copier会从源端每个节点本地表拉取数据，然后均衡发送到目标端本地表，此工具会自动完成负载均衡，这也是优点之一。

3、将task.xml写入zk，在对应zk集群节点上执行

./zkCli.sh -server localhost:2181 create /clickhouse/copier_task
./zkCli.sh -server localhost:2181 create /clickhouse/copier_task/task
# 创建任务信息
./zkCli.sh -server localhost:2181 create /clickhouse/copier_task/task/description "`cat task.xml`"
# 查看任务信息
./zkCli.sh -server localhost:2181 get /clickhouse/copier_task/task/description
# 更新任务信息
./zkCli.sh -server localhost:12181 set /clickhouse/copier_task/task/description "`cat task.xml`"

4、启动任务

clickhouse-copier --config zookeeper.xml --task-path /clickhouse/copier_task/task --base-dir ./logs &