dump all clickhouse databases and tables

clickhousedump

#!/bin/bash

OUTDIR=.
IGNORE_DBS="system information_schema INFORMATION_SCHEMA default tmp"
# supply options here as needed
CLIENT="clickhouse-client"

$CLIENT -q "SHOW DATABASES" | while read -r db ; do
    $CLIENT -q "SHOW TABLES FROM $db" | while read -r table ; do

        if [[ " ${IGNORE_DBS} " =~ " ${db} " ]]; then
            # ignore specified dbs
            continue 2;
        fi

        if [[ "${table}" =~ ^(\.inner\.|\.inner_id\.) ]]; then
            # ignore MV inner tables 
            continue;
        fi

        OUT="${OUTDIR}/${db}_${table}_schema.sql"
        # dump schema
        $CLIENT -q "SHOW CREATE TABLE ${db}.${table}" |
            # unescape \n and \' that are escaped by clickhouse
            awk '{gsub(/\\n/, "\n")} {gsub(/\\'\''/, "'\''")} 1' > "$OUT"

    done
done