Backing Up and Restoring ClickHouse Data Using a Data File

Updated on 2024-10-08 GMT+08:00

View PDF

Scenario

This section describes how to back up data by exporting ClickHouse data to a CSV file and restore data using the CSV file.

Prerequisites

You have installed the ClickHouse client.
You have created a user with related permissions on ClickHouse tables on Manager.
You have prepared a server for backup.

Backing Up Data

Log in to the node where the client is installed as the client installation user.
Run the following command to go to the client installation directory:

cd /opt/client
Run the following command to configure environment variables:

source bigdata_env
If Kerberos authentication is enabled for the current cluster, run the following command to authenticate the current user. The current user must have the permission to create ClickHouse tables. If Kerberos authentication is disabled for the current cluster, skip this step.
1. Run the following command if it is an MRS 3.1.0 cluster:
  export CLICKHOUSE_SECURITY_ENABLED=true
2. kinit Component service user
  Example: kinit clickhouseuser
Run the ClickHouse client command to export the ClickHouse table data to be backed up to a specified directory.

clickhouse client --host Host name/Instance IP address --secure --port 9440 --query="Table query statement" > Path of the exported CSV file

The following shows an example of backing up data in the test table to the default_test.csv file on the ClickHouse instance 10.244.225.167.

clickhouse client --host 10.244.225.167 --secure --port 9440 --query="select * from default.test FORMAT CSV" > /opt/clickhouse/default_test.csv
Upload the exported CSV file to the backup server.

Restoring Data

Upload the backup data file on the backup server to the directory where the ClickHouse client is located.

For example, upload the default_test.csv backup file to the /opt/clickhouse directory.
Log in to the node where the client is installed as the client installation user.
Run the following command to go to the client installation directory:

cd /opt/client
Run the following command to configure environment variables:

source bigdata_env
If Kerberos authentication is enabled for the current cluster, run the following command to authenticate the current user. The current user must have the permission to create ClickHouse tables. If Kerberos authentication is disabled for the current cluster, skip this step.
1. Run the following command if it is an MRS 3.1.0 cluster:
  export CLICKHOUSE_SECURITY_ENABLED=true
2. kinit Component service user
  Example: kinit clickhouseuser
Run the ClickHouse client command to log in to the ClickHouse cluster.

clickhouse client --host Host name/Instance IP address --secure --port 9440
Create a table with the format corresponding to the CSV file.

CREATE TABLE [IF NOT EXISTS] [database_name.]table_name [ON CLUSTER Cluster name]

(

name1 [type1] [DEFAULT|materialized|ALIAS expr1],

name2 [type2] [DEFAULT|materialized|ALIAS expr2],

...

) ENGINE = engine
Import the content in the backup file to the table created in 7 to restore data.

clickhouse client --host Host name/Instance IP address --secure --port 9440 --query="insert into Table name FORMAT CSV" < CSV file path

The following shows an example of restoring data from the default_test.csv backup file to the test_cpy table on the ClickHouse instance 10.244.225.167.

clickhouse client --host 10.244.225.167 --secure --port 9440 --query="insert into default.test_cpy FORMAT CSV" < /opt/clickhouse/default_test.csv

Parent topic: ClickHouse O&M Management

Previous topic: Expanding the Data Disk Capacity of a ClickHouse Node

Next topic: Migrating Data Between ClickHouseServer Nodes in a Cluster

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot