Skip to content

Instantly share code, notes, and snippets.

@knoguchi
Forked from inkrement/clickhousedump
Last active March 18, 2022 22:02
Show Gist options
  • Save knoguchi/b9e24e6970fd869790f952273c313a81 to your computer and use it in GitHub Desktop.
Save knoguchi/b9e24e6970fd869790f952273c313a81 to your computer and use it in GitHub Desktop.
dump all clickhouse databases and tables
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment