Skip to content

Instantly share code, notes, and snippets.

@inkrement

inkrement/clickhousedump

Created August 19, 2017 14:26
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save inkrement/ea78bc8dce366866103df83ea8d36247 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save inkrement/ea78bc8dce366866103df83ea8d36247 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. inkrement created this gist Aug 19, 2017.
    27 changes: 27 additions & 0 deletions clickhousedump
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,27 @@
    #!/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")