Updated on 2024-11-29 GMT+08:00

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;