How to Export and Import Data with ClickHouse

ClickHouse is a fast and powerful columnar database management system, perfect for handling large-scale data analytics. This guide will walk you through the process of efficiently exporting data from one ClickHouse database and importing it into another using a simple yet effective Go application.

Prerequisites for ClickHouse Data Export and Import

• Go version 1.22.2+
• https://clickhouse.com/docs/en/install

Installation Guide for ClickHouse Import-Export App

• Clone the repository:

git clone https://github.com/kankou-aliaksei/clickhouse-import-export.git
cd clickhouse-import-export

• Ensure Go is installed:
  Follow the instructions here to install Go if it is not already installed.
• Ensure ClickHouse is installed:
  Follow the instructions here to install ClickHouse if it is not already installed.

Usage Instructions for ClickHouse Data Export and Import

How to Export Data from ClickHouse

To export data from a ClickHouse database, use the export_data.go script.

1. Build and run the export script:

go run export_data.go \
    -host=mydb1 \
    -port=9000 \
    -user=admin \
    -password=your_password \
    -dbname=my_db \
    -chunkSize=1000000 \
    -clickhouseClientPath=../clickhouse_bin/clickhouse

How to Import Data into ClickHouse

To import data into a ClickHouse database, use the import_data.go script.

1. Build and run the import script:

go run import_data.go \
    -host=mydb2 \
    -port=9000 \
    -user=admin \
    -password=your_password \
    -dbname=my_db \
    -clickhouseClientPath=../clickhouse_bin/clickhouse

Configuration Options for ClickHouse Data Export-Import

Configuration for both scripts is done through command-line flags:

• -host: ClickHouse host
• -port: ClickHouse port
• -user: ClickHouse user
• -password: ClickHouse password
• -dbname: ClickHouse database name
• -readTimeout: Read timeout in seconds (default: 30)
• -writeTimeout: Write timeout in seconds (default: 30)
• -chunkSize: Number of rows to fetch per batch (only for export, default: 10000)
• -clickhouseClientPath: Path to the ClickHouse client executable (default: “clickhouse”)

Code Explanation for ClickHouse Import-Export App

Understanding export_data.go for ClickHouse

This script exports the schema and data from a ClickHouse database.

1. Load configuration from command-line flags.
2. Create and test the database connection.
3. Prepare directories for schema and data dumps.
4. Fetch all tables and process each one:
   • Dump the schema of each table.
   • Dump the data of each table in batches using clickhouse client.

Understanding import_data.go for ClickHouse

This script imports the schema and data into a ClickHouse database.

1. Load configuration from command-line flags.
2. Create and test the initial database connection.
3. Ensure the database exists.
4. Reconnect to the database with the specified database name.
5. Import schema and data:
   • Import schema and views from the specified directory.
   • Import data for tables from the specified directory using clickhouse client.

Example Commands for ClickHouse Data Export-Import

Example to Export Data from ClickHouse

go run export_data.go \
    -host=mydb1 \
    -port=9000 \
    -user=admin \
    -password=your_password \
    -dbname=my_db \
    -chunkSize=1000000 \
    -clickhouseClientPath=../clickhouse_bin/clickhouse

Example to Import Data into ClickHouse

go run import_data.go \
    -host=mydb2 \
    -port=9000 \
    -user=admin \
    -password=your_password \
    -dbname=my_db \
    -clickhouseClientPath=../clickhouse_bin/clickhouse

Important Notes for ClickHouse Data Transfer

• Ensure the ClickHouse client executable path is correctly specified.
• For large datasets, adjust the -chunkSize flag to optimize performance.