clickhouse导出导入、备份等

clickhouse单表导出导入

导出
clickhouse-client --verbose --log-level trace --host 127.0.0.1 --port 9000 --user default --password '密码' --database your_db --query "SELECT * from your_table" --format CSV > your_table.CSV

导入
clickhouse-client --verbose --log-level trace --host 127.0.0.1 --port 9000 --user default --password '密码' --database your_db --query "INSERT INTO your_table FORMAT CSV" < your_table.CSV

clickhouse批量导出导入

环境信息

生产环境有A B 2台数据库服务器，安装了clickhouse数据，搭建了集群。 

A B都有本地表即 my_tbale_local 

A B都有视图view即 my_tbale，每个my_tbale都映射到2台的my_tbale_local表。 

测试环境有1台clickhouse服务器，要求把生产环境的数据全部导入到测试环境中。

 注：（其它情况的导出导入都大同小异，适当修改即可）

导出

导出my_tbale_local表的表结构 

导出view即my_tbale的表数据

导入

修改my_tbale_local表的表结构，去掉_local字符 执行表结构的sql 

导入view即my_tbale的表数据

导出脚本 

bash ck_export.sh

#!/bin/bash

# 数据库连接信息
HOST="127.0.0.1"
PORT="9000"
USER="default"
PASSWORD="密码"
DATABASE="your_db"
# 创建临时目录存放导出的文件
EXPORT_DIR="./ck_exports"
mkdir -p $EXPORT_DIR

# 获取所有表名
TABLES=$(clickhouse-client --host $HOST --port $PORT --user $USER --password "$PASSWORD" --database $DATABASE --session_timezone 'Asia/Shanghai' --query "SHOW TABLES" --format TSV)

echo "TABLES --> $TABLES"

# 导出表结构和数据
for TABLE in $TABLES
do
	if [[ $TABLE == *_local ]]; then
	    echo "字符串以 _local 结尾"
	    echo "导出表结构 $TABLE ..."
	    clickhouse-client --host $HOST --port $PORT --user $USER --password "$PASSWORD" --database $DATABASE --session_timezone 'Asia/Shanghai' --query "SHOW CREATE TABLE $TABLE" --format TSV > "$EXPORT_DIR/$TABLE.sql"
	    echo "表结构 $TABLE 导出完成。"
    else
    	echo "字符串不以 _local 结尾"
	    echo "导出表数据 $TABLE ..."
	    clickhouse-client --host $HOST --port $PORT --user $USER --password "$PASSWORD" --database $DATABASE --session_timezone 'Asia/Shanghai' --query "SELECT * FROM $TABLE" --format CSV > "$EXPORT_DIR/$TABLE.csv"
	    echo "表数据 $TABLE 导出完成。"
	fi
done

echo "所有操作完成。"

导入前修改

去掉sql文件中的_local
sed -i 's/_local//g' *.sql

去掉文件里面的\n
sed -i 's/\\n/ /g' *.sql

去掉文件里面的\
sed -i 's/\\//g' *.sql

其他修改
sed -i 's/your_db\.//g' *.sql

重命名sql文件，文件名去掉_local
for file in *_local.sql; do mv -- "$file" "${file/_local.sql/.sql}"; done

导入脚本

因为前面使用Asia/Shanghai导出的，那么这里设置为同样的时区导入，并设置使用client的时区

export TZ="Asia/Shanghai"

bash ck_import.sh

#!/bin/bash

# 数据库连接信息
HOST="127.0.0.1"
PORT="9000"
USER="default"
PASSWORD="密码"
DATABASE="your_db"
# 临时目录存放导出的文件
EXPORT_DIR="./ck_exports"

# 导入表结构和数据
for SQL_FILE in $EXPORT_DIR/*.sql
do
    TABLE=$(basename $SQL_FILE .sql)
    echo "from file name $TABLE"

    # 检查表是否存在
    TABLE_EXISTS=$(clickhouse-client --host $HOST --port $PORT --user $USER --password "$PASSWORD" --database $DATABASE --query "EXISTS TABLE $TABLE" --format TSV)

    if [ "$TABLE_EXISTS" == "1" ]; then
        echo "表 $TABLE 已存在，删除之..."
        clickhouse-client --host $HOST --port $PORT --user $USER --password "$PASSWORD" --database $DATABASE --query "DROP TABLE $TABLE"
        echo "表 $TABLE 删除完成。"
    else
    	echo "表 $TABLE 不存在"
    fi

    echo "导入表结构 $TABLE ..."
    clickhouse-client --host $HOST --port $PORT --user $USER --password "$PASSWORD" --database $DATABASE --query="$(cat $SQL_FILE)"
    echo "表结构 $TABLE 导入完成。"

    CSV_FILE="$EXPORT_DIR/$TABLE.csv"
    if [ -f "$CSV_FILE" ]; then
        echo "导入表数据 $TABLE ..."
        clickhouse-client --host $HOST --port $PORT --user $USER --password "$PASSWORD" --database $DATABASE --use_client_time_zone true --query "INSERT INTO $TABLE FORMAT CSV" < "$CSV_FILE"
        echo "表数据 $TABLE 导入完成。"
    else
        echo "警告: 找不到数据文件 $CSV_FILE，跳过数据导入。"
    fi
done

echo "所有操作完成。"

注意：

根据情况灵活调整！

时区要对应上！

关于clickhouse-client导入导出的时区问题

因为clickhouse-client导出的csv格式的数据，不包含时区信息，因此：

导出时候

如果不指定--session_timezone，那么clickhouse就使用config配置的（比如是UTC形式）。

再另一台机器导入的时候

如果当前客户端的TZ设置的是UTC，并且指定--use_client_time_zone 参数，那么clickhouse就按照UTC方式处理，那么没有问题。

如果不指定--use_client_time_zone，但此时config配置的是Asia/Shanghai，那么导入的数据就按照Asia/Shanghai处理了，那么数据整体就少了8小时。

总结

关键是导出时候使用的时区，和导入时候使用的时区，二者相同即可。因为clickhouse最终是忽略时区，按照Unix时间戳存储的。但是clickhouse存储前，需要先根据当前的时区转为UTC时区，再把UTC格式的时间对象转为Unix时间戳。

上面的脚本使用的是Asia/Shanghai时区导出和导入的，如果使用UTC时区或者其他时区导出和导入也是可以的。