dump all clickhouse databases and tables

clickhousedump

#!/bin/bash

OUTDIR=.

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

  if [ "$db" == "system" ]; then
     echo "skip system db"
     continue 2;
  fi

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

  echo "export table $table from database $db"

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

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

  done < <(clickhouse-client -q "SHOW TABLES FROM $db") 
done < <(clickhouse-client -q "SHOW DATABASES")

Does this work for Merge engine?

@bumarcell the script should be more or less independent from the engine. I used it with merge tree and log engines.

Edit: I see, you are referring to the merge engine (which is a special one) and not the the merge tree engine. TBO I haven't tried it with any special engines yet.

And clickhouse-client with host/port/user/password

alias clickhouse-client="./clickhouse client -h <host> --port 9000 -u <username> --password <password> "
alias clickhouse-client="clickhouse-client -h <host> --port <port> -u <username> --password <password> "

If y'all are fans of Go, I wrote a small utility to do this because I hate dealing with bashisms:

https://github.com/runreveal/chdump