clickhouse表结构导出为

 

查看代码

#!/bin/bash

OUTDIR=/opt/backup/

# 获取所有数据库并保存到文件中
clickhouse-client --user default --password 123456 -q "SHOW DATABASES" > /opt/backup/db.txt

# 读取数据库文件
while read -r db; do
    # 排除 system 和 INFORMATION_SCHEMA 数据库 default
    if [ "$db" == "system" ] || [ "$db" == "INFORMATION_SCHEMA" ] || [ "$db" == "information_schema" ] || [ "$db" == "default" ]; then
        echo "skip system db"
        continue
    fi

    # 获取指定数据库中的所有表并保存到文件中
    clickhouse-client -q "SHOW TABLES FROM $db" > /opt/backup/table.txt
    echo "--------------------------------${db}"

    # 读取表文件
    while read -r table; do
        # 排除内部表
        if [[ "$table" == ".inner."* ]]; then
            echo "skip materialized view $table ($db)"
            continue
        fi

        echo "export table $table from database $db"

        # 导出表的模式, 制表符转义格式
        clickhouse-client -q "SHOW CREATE TABLE ${db}.${table} FORMAT TabSeparatedRaw" >> "${OUTDIR}/schema.sql"

    done < /opt/backup/table.txt
done < /opt/backup/db.txt