dump_clickhouse_metadata_as_schema.sh

#!/usr/bin/env bash

for db_sql_full_filename in /var/lib/clickhouse/metadata/*.sql
do
  dbfilename="${db_sql_full_filename##*/}";
  dbname="${dbfilename%.sql}"

  printf "\n/*************\n ** $dbname \n *************/\n"
  perl -0777 -npe 's/^ATTACH/CREATE/; s/$/;\n/' $db_sql_full_filename;
  printf "USE $dbname;\n\n"
  for table_sql_full_filename in /var/lib/clickhouse/metadata/$dbname/*.sql
  do
      perl -0777 -npe 's/^ATTACH/CREATE/; s/$/;\n/' $table_sql_full_filename;
  done;
done;

dump_clickhouse_schema.sh

clickhouse-client --query="SELECT concat('CREATE DATABASE \\\`', name, '\\\` ENGINE = ', engine, ';') FROM system.databases WHERE name <> 'system' UNION ALL WITH substring(name, 1, 7) = '.inner.' AS mv_inner_table, if(mv_inner_table, substring(name, 8), name) AS namex, argMin(create_table_query, mv_inner_table) AS create_table_queryx, argMin(engine, mv_inner_table) AS enginex SELECT concat(create_table_queryx, ';') FROM system.tables WHERE name <> 'system' GROUP BY  database,  namex HAVING create_table_queryx != '' ORDER BY   enginex = 'MaterializedView' ASC,  database ASC,   namex ASC" --format=TSVRaw | while read -r line; do echo "$line" | clickhouse-format ; printf ";\n\n" ; done | perl -e '$/=undef;$_=<>;s/(\s+;)+/;/g;print $_'