Import data using clickhouse-client

This example demonstrates how to import a CSV file into the test_tbl_distributed distributed table in the default database of Alibaba Cloud ClickHouse. Replace the example parameters with your actual values. The environment for this example is as follows:

• Destination database: default

• Destination table: test_tbl_distributed

• Source data file: testData.csv

Step 1: Prepare the data

In the installation directory of the clickhouse-client tool, create a file named testData.csv and add the following data to it.

1,yang,32,shanghai,http://example.com
2,wang,22,beijing,http://example.com
3,xiao,23,shenzhen,http://example.com
4,jess,45,hangzhou,http://example.com
5,jack,14,shanghai,http://example.com
6,tomy,25,hangzhou,http://example.com
7,lucy,45,shanghai,http://example.com
8,tengyin,26,shanghai,http://example.com
9,wangli,27,shenzhen,http://example.com
10,xiaohua,37,shanghai,http://example.com

Step 2: Create a table

1. Connect to the database.

   Alibaba Cloud ClickHouse is integrated with Data Management Service (DMS), a visual tool for connecting to your cluster. For more information, see Connect to a ClickHouse cluster using DMS.

   If you use a different client, see Connect to a database.

2. Create a table based on your cluster edition.

   Important

   The order and data type of the columns in the table must match those in the source file to prevent import failures.

   For an Enterprise Edition cluster, you only need to create a local table. For a Community-Compatible Edition cluster, you might also need to create a distributed table, depending on your setup. The following examples show the required statements. For more information about the CREATE TABLE syntax, see CREATE TABLE.

   Enterprise edition

   CREATE TABLE test_tbl_local ON cluster default 
   (
   id UInt8,
   user_name String,
   age UInt16,
   city String,
   access_url String
   )
   ENGINE = MergeTree()
   ORDER BY id;

   If you receive the error message ON CLUSTER is not allowed for Replicated database when you run this statement, you can upgrade the minor engine version to fix the error.

   Community-compatible edition

   The engines for single-replica and two-replica clusters are different. Select an engine based on the replica type of your cluster.

   Important

   Using a non-Replicated engine prevents replication between the replicas and can cause data inconsistency.

   Single-replica

   1. Create a local table.

      CREATE TABLE test_tbl_local ON cluster default
      (
      id UInt8,
      user_name String,
      age UInt16,
      city String,
      access_url String
      )
      ENGINE = MergeTree()
      ORDER BY id;

   2. (Optional) Create a distributed table.

      If you only need to import the file into a local table, you can skip this step.

      For multi-node clusters, creating a distributed table is recommended.

      CREATE TABLE test_tbl_distributed ON cluster default
      (
      id UInt8,
      user_name String,
      age UInt16,
      city String,
      access_url String
      )
      ENGINE = Distributed(default, default, test_tbl_local, rand());

   Two-replica

   1. Create a local table.

      CREATE TABLE test_tbl_local ON cluster default
      (
      id UInt8,
      user_name String,
      age UInt16,
      city String,
      access_url String
      )
      ENGINE = ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}')
      order by id;

   2. (Optional) Create a distributed table.

      If you only need to import the file into a local table, you can skip this step.

      For multi-node clusters, creating a distributed table is recommended.

      CREATE TABLE test_tbl_distributed ON cluster default
      (
      id UInt8,
      user_name String,
      age UInt16,
      city String,
      access_url String
      )
      ENGINE = Distributed(default, default, test_tbl_local, rand());

Step 3: Import data

In the installation directory of the clickhouse-client tool, run the following command.

To speed up the import, split the source file into parts and run multiple client processes to import them in parallel.

For multi-node clusters, importing data into a distributed table is recommended.

cat <file_name> | ./clickhouse-client --host=<host> --port=<port> --user=<user> --password=<password> --query="INSERT INTO <table_name> FORMAT <file_type>";

Step 4: Verify the import result

1. Connect to the cluster.

   To connect to the cluster with DMS, see Connect to a ClickHouse cluster using DMS.

2. Run a query statement.

   Important

   On a multi-node Community-Compatible Edition cluster, query the distributed table to view all imported data. If you query a local table, you will see data from only a single node, resulting in an incomplete dataset.

   SELECT * FROM test_tbl_local; 

   The query returns the following result:

   +--------------+---------------------+---------------+----------------+----------------------+
   | id           | user_name           | age           | city           | access_url           |
   +--------------+---------------------+---------------+----------------+----------------------+
   | 1            | yang                | 32            | shanghai       | http://example.com   |
   | 2            | wang                | 22            | beijing        | http://example.com   |
   | 3            | xiao                | 23            | shenzhen       | http://example.com   |
   | 4            | jess                | 45            | hangzhou       | http://example.com   |
   | 5            | jack                | 14            | shanghai       | http://example.com   |
   | 6            | tomy                | 25            | hangzhou       | http://example.com   |
   | 7            | lucy                | 45            | shanghai       | http://example.com   |
   | 8            | tengyin             | 26            | shanghai       | http://example.com   |
   | 9            | wangli              | 27            | shenzhen       | http://example.com   |
   | 10           | xiaohua             | 37            | shanghai       | http://example.com   |
   +--------------+---------------------+---------------+----------------+----------------------+