dump all clickhouse databases and tables

clickhousedump

#!/bin/bash

PASSWORD="--password=password"
DBLIST="db1 db2 db3"

OUTDIR=.

while read -r db ; do
  while read -r table ; do

  if [[ "$table" == ".inner."* ]]; then
     echo "skip materialized view $table ($db)"
     continue;
  fi

  echo "export table $table from database $db"

    # dump schema
    clickhouse-client ${PASSWORD} -q "SHOW CREATE TABLE ${db}.${table}" > "${OUTDIR}/${db}_${table}_schema.sql"

    # dump 
    clickhouse-client ${PASSWORD} -q "SELECT * FROM ${db}.${table} FORMAT TabSeparated" | gzip > "${OUTDIR}/${db}_${table}_data.tsv.gz"

  done < <(clickhouse-client ${PASSWORD} -q "SHOW TABLES FROM $db") 
done < <(echo ${DBLIST})

Recommended to use TabSeparatedRaw format for schema migration:
clickhouse-client ${PASSWORD} -q "SHOW CREATE TABLE ${db}.${table}" --format=TabSeparatedRaw > "${OUTDIR}/${db}_${table}_schema.sql"

TabSeparatedRaw

Differs from TabSeparated format in that the rows are written without escaping.

I dont know why, but TabSeparated broke my migration, when TabSeparatedRaw didn't.