Skip to content

Instantly share code, notes, and snippets.

@almoorthi
Forked from inkrement/clickhousedump
Created May 22, 2020 05:51
Show Gist options
  • Save almoorthi/7119b109de7a9e9344e1cd48bc806a35 to your computer and use it in GitHub Desktop.
Save almoorthi/7119b109de7a9e9344e1cd48bc806a35 to your computer and use it in GitHub Desktop.
dump all clickhouse databases and tables
#!/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")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment