ClickHouse Batch Data Import

Scenario

If a large number of data files need to be imported, you can use the multi-thread import tool to import ClickHouse data files in batches.

Prerequisites

• The ClickHouse client has been installed in a directory, for example, /opt/client.
• For a cluster in security mode, a user with ClickHouse permissions has been created, for example, clickhouseuser. For details, see ClickHouse User and Permission Management.
• The data file to be imported has been uploaded to a client node directory, for example, /opt/data. For details about all data types supported by ClickHouse, visit https://clickhouse.com/docs/en/interfaces/formats.

Procedure

1. Log in to the node where the client is installed as the client installation user.
2. Go to the directory where the multi-thread write tool clickhouse_insert_tool is deployed.

   cd /opt/client/ClickHouse/clickhouse_insert_tool

3. Use the text editor to open clickhouse_insert_tool.sh and enter required information based on the comments.

   Parameter	Description	Example
   datapath	Directory containing the data to be imported	/opt/data
   balancer_ip_list	IP addresses of Balancer instances of the ClickHouse service. The IP addresses must be enclosed in parentheses. A single IP address must be enclosed in double quotation marks, and IP addresses must be separated by spaces.	("192.168.1.1" "192.168.1.2")
   balancer_tcp_port	TCP port for the Balancer instance of the ClickHouse service	21428
   local_table_name	Names of the local library and table to be imported	testdb1.testtb1
   thread_num	Number of concurrent threads for importing data	10
   data_format	Format of the data to be imported	CSV
   is_security_cluster	Whether the security mode is used true indicates that the security mode. false indicates the normal mode.	true

4. Save the modified clickhouse_insert_tool.sh file and run the following commands:

   cd /opt/client

   source bigdata_env

   In security mode (Kerberos authentication is enabled), run the kinit command. In normal mode (Kerberos authentication is disabled), you do not need to run the following command:

   kinit clickhouseuser

5. Run the script to import data.

   ./ClickHouse/clickhouse_insert_tool/clickhouse_insert_tool.sh

6. Log in to the ClickHouse client node and connect the server. For details, see Using ClickHouse from Scratch.
7. Run the following command to query the distributed table corresponding to the local table where data is inserted and check the result:

   select count(1) from testdb1.testtb1_all;