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")

Have you ever tried this command?
I got an error seems like that process substitution can not use varible.

dump_ck.sh: line 26: syntax error near unexpected token `<'
dump_ck.sh: line 26: `  done < <(clickhouse-client -q "SHOW TABLES FROM $db")'

Yes, I did so. I also tried the basic logic and it still seems to work for me (just tested it with gnu bash 5.0.17(1)-release). Have you used bash or another shell?

You are right!
I tried follow command with bash 5.x on this site ,and it just worked fine!

echo "test with bash:$BASH_VERSION"
while read p;do
    while read q;do
        echo "Root path:$p contains $q"
    done < <(ls /$p | head -3)
done < <(ls / | head -3)

I just use the for loop for quickly finish the job,and added set +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 with sh or bash also worked.

Apologize for my bad english :-)

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