8.4.13. Manual import and export of ClickHouse tables

Using manual import and export, you can create backups of your tables and transfer them between instances. All commands are executed on your device (not on the hosting). They require Linux or macOS; on Windows devices, you can use WSL. In all commands, use your own credentials (host, port, user, password, and database and table names).

1. In the instance settings, enable the ClickHouse TCP port.
2. In the security settings, allow access for your IP address.
3. Install the clickhouse-client console utility on your device by following the instructions for your operating system.

Connect to instance:

clickhouse-client --host example.clickhouse.tools --port 59123 --user user1 --password='pass'

Once the connection is established, you can run SQL queries to ClickHouse.

View list of databases:

SHOW DATABASES;

Select database:

USE db1;

View list of tables in selected database:

SHOW TABLES;

Disconnect:

exit

Table structures and their data are exported separately: the structure is exported in SQL format, and the data is exported in Native format. The Native format is the most optimal — it exports and imports faster and takes up less space.

Export table structure:

clickhouse-client --host example.clickhouse.tools --port 59123 --user user1 --password='pass' --database db1 --query "SHOW CREATE TABLE db1.table1 FORMAT TabSeparatedRaw;" > table1.sql

Export structure of all tables with the DB name removed (useful for later import into a DB with a different name):

clickhouse-client --host example.clickhouse.tools --port 59123 --user user1 --password='pass' --query "SELECT replaceOne(create_table_query, 'CREATE TABLE db1.', 'CREATE TABLE ') FROM system.tables WHERE database = 'db1' AND is_temporary = 0 FORMAT TabSeparatedRaw;" > tables.sql

Export table data:

clickhouse-client --host example.clickhouse.tools --port 59123 --user user1 --password='pass' --query "SELECT * FROM db1.table1 FORMAT Native;" > table1.native

You cannot export data from all tables at once; each table must be exported separately.

Import structure:

clickhouse-client --host example.clickhouse.tools --port 59123 --user user1 --password='pass' --multiquery < table1.sql

Import data:

clickhouse-client --host example.clickhouse.tools --port 59123 --user user1 --password='pass' --query="INSERT INTO db1.table1 FORMAT Native" < table1.native

Check result (counts and displays the number of records in the table):

clickhouse-client --host example.clickhouse.tools --port 59123 --user user1 --password='pass' --query="SELECT count() FROM db1.table1"