#表对表数据迁移脚本
#根据partition进行迁移,由old partition开始
#支持src_table与dest_table字段数目不一致的情况,以src_table字段为准,dest_table多余的字段为默认值
#可以指定迁移分区范围:
#不指定可以使用空字符串
#P_BEGIN 分区开始条件,不包含,如:20220529 ,P_END 分区结束条件,包含,如:20220530
#等同于条件:partition > P_BEGIN and partition <= P_END
# src_database src_table dest_database dest_table '' '' src_create dest_create
#脚本必须在src端运行
#src_table必须为本地表,因为要生成分区 , SRC_CREATE=y 可以设定是否需要对src_table创建分布式表,反之=''
#dest_table可为分布式表或本地表,DEST_CREATE=y可以指定是否对dest_table创建分布式表,反之=''
#使用方法:
#集群内表对表迁移:
#src_create=y dest_create=y 自动创建分布式表
#跨集群表对表迁移:
#1.先在原集群添加新集群 如;remote_1
#2.在src端创建分布式表映射dest端数据表
#导入数据:
#src_create=y dest_create=y 在每个shard(多个node)上并行迁移,数据一致
#src_create=y dest_create='' 在当前node执行,读取src分布式表写入dest_table,数据一致
#src_create='' dest_create='' 只迁移当前节点数据 数据一致
#库对库迁移
#src_table和dst_table字段传入空参数
#只迁移所有的MergeTree表和ReplicatedMergeTree表
#获取当前目录
cur=$(dirname $(readlink -f $0))
判断执行脚本输入的参数 没有达到10个参数就提示参数
if [ $# != 10 ];then
echo "Usage: $0 src_database src_table dest_database dest_table partition_begin partition_end src_create_distributed dest_create_distributed username password "
echo "eg. sh $0 src_database src_table dest_database dest_table 20220521 20220530 Y Y username password " ## y或任意字符表示开启
echo "eg. sh $0 db1 table1 db2 table2 '' '' '' '' username password " # '' 表示关闭
echo "eg. sh $0 src_database '' dest_database '' 20220521 20220539 Y Y username password"
exit 1
fi
SRC_DATABASE="$1"
SRC_TABLE="$2"
DEST_DATABASE="$3"
DEST_TABLE="$4"
P_BEGIN="$5"
P_END="$6"
SRC_CREATE="$7"
DEST_CREATE="$8"
PARALLEL_PATITION=6
USERNAME="$9"
PASSWORD="${10}"
#clickhouse-clinet
EXEC="$cur/clickhouse-client -u $USERNAME --password $PASSWORD --port 9010"
#不等于0 执行报错
function exit_code(){
if [[ $? -ne 0 ]]
then
echo "exec faild $1"
exit 2
fi
}
function copy_table(){
table=$1
#查看迁移表分区去重
partition=$($EXEC -q "select distinct partition as ptn from system.parts where database='$SRC_DATABASE' and active=1 and table='$table' $P_BEGIN $P_END order by ptn ")
echo "partition: $partition"
#表指定的分区表达式
partition_key=$($EXEC -q "select partition_key from system.tables where name='$table' and database='$SRC_DATABASE'")
src_table_migrate=""
dest_table_migrate=""
if [[ $SRC_CREATE != "" ]]
then
src_table_migrate="${table}_migrate"
#创建本地分布式映射表
$EXEC -q "create table if not exists ${SRC_DATABASE}.${src_table_migrate} on cluster xhraptor as ${SRC_DATABASE}.${table} engine=Distributed('xhraptor','${SRC_DATABASE}','${table}',rand())" > /dev/null
exit_code "create failed!"
else
src_table_migrate=$table
fi
dest_table=$DEST_TABLE
if [[ $DEST_TABLE == "" ]]
then
dest_table=$table
fi
if [[ $DEST_CREATE != "" ]]
then
dest_table_migrate="${dest_table}_migrate"
#远程分布式映射表
$EXEC -q "create table if not exists ${DEST_DATABASE}.${dest_table_migrate} on cluster xhraptor as ${DEST_DATABASE}.${dest_table} engine=Distributed('xhraptor','${DEST_DATABASE}','${dest_table}',rand())" > /dev/null
exit_code "create failed"
else
dest_table_migrate=${dest_table}
fi
#拼接 insert 语句
insert_select=$($EXEC -q "select concat('INSERT INTO ','$DEST_DATABASE','.','${dest_table_migrate}','(',arrayStringConcat(groupArray(concat('\`',name,'\`')),','),') select ',arrayStringConcat(groupArray(concat('\`',name,'\`')),',') , ' from ','$SRC_DATABASE.${src_table_migrate}') from system.columns where name not like '\_%' and database ='$SRC_DATABASE' and table='${src_table_migrate}' group by database,table")
#echo $insert_select
d=0
for i in ${partition[@]}
do
d=`expr $d + 1`
{
prewhere_partiton=""
## 是否存在分区
if [[ $i != "tuple()" ]];
then
#迁移表有分区就不等于tuple() ,每个分区赋值
prewhere_partiton="prewhere $partition_key='$i'"
fi
#显示迁移分区
echo -n "##MIGRATE ${SRC_DATABASE}.${table} -> ${DEST_DATABASE}.${dest_table} PARTITION: $i "
#echo "select * from $SRC_DATABASE.${SRC_TABLE}_migrate prewhere $partition_key='$i'"
#查看原表迁移条数
src_cnt=$($EXEC -q "select count() from ${SRC_DATABASE}.${src_table_migrate} $prewhere_partiton")
exit_code "count failed!"
#输出迁移条数
echo "COUNT: $src_cnt ,starting ....."
s_time=$(date '+%s')
#拼接迁移语句参数
$EXEC --max_insert_block_size=10000 --parallel_distributed_insert_select=2 --connect_timeout 30000 --receive_timeout 30000 --send_timeout 30000 -q "$insert_select $prewhere_partiton"
exit_code "insert select failed!"
#迁移分区时间
e_time=$(date '+%s')
sleep 3
#迁移映射表表分区条数
dest_cnt=$($EXEC -q "select count() from ${DEST_DATABASE}.${dest_table_migrate} $prewhere_partiton")
speed=$(echo "scale=2;$dest_cnt/($e_time-$s_time+1)" |bc)
exit_code "count failed!"
echo "##COMPLETE ${DEST_DATABASE}.${dest_table} MIGRATE PARTITION: $i COUNT: $dest_cnt SPEED: $speed "
if [[ $src_cnt -ne $dest_cnt ]]
then
echo "Migration data is inconsistent!"
# exit 3
fi
}&
[ `expr $d % $PARALLEL_PATITION` -eq 0 ] && wait
done
wait
echo "##TABLE ${DEST_DATABASE}.${dest_table} MIGRATE SUCCESS"
}
if [[ $P_BEGIN != "" ]];
then
P_BEGIN=" and partition > '$P_BEGIN' "
fi
if [[ $P_END != "" ]];
then
P_END=" and partition <= '$P_END' "
fi
tables=$SRC_TABLE
if [[ $SRC_TABLE == "" ]]
then
tables=$($EXEC -q "select name from (select name from system.tables where database='$SRC_DATABASE' and engine in ('ReplicatedMergeTree','MergeTree')) as table1 join (select name from system.tables where database='$DEST_DATABASE') as table2 on table1.name=table2.name;")
DEST_TABLE=""
fi
for table in ${tables[@]}
do
copy_table $table
done
全网最细~ Clickhouse ~ 数据迁移脚本(支持集群内迁移,支持集群外迁移)《大力仑出品必属精品》
于 2023-04-17 17:47:25 首次发布