clickhouse表結構匯出為

XSWClevo發表於2024-05-20

檢視程式碼 

#!/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

相關文章