Created
August 19, 2017 14:26
-
-
Save inkrement/ea78bc8dce366866103df83ea8d36247 to your computer and use it in GitHub Desktop.
dump all clickhouse databases and tables
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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:
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
You are right!
I tried follow command with bash 5.x on this site ,and it just worked fine!
I just use the
for loop
for quickly finish the job,and addedset +o posix
at the first line also worked fine. (After read your answer then pay attention on the version of bash,so i got this QA )Tested which bash is
GNU bash, version 4.2.46(2)-release (x86_64-redhat-linux-gnu)
.Conclusion: use -ge 5.x bash or
set +o posix
withsh
orbash
also worked.Apologize for my bad english :-)