Import data using clickhouse-client

This example imports a CSV file into the test_tbl_distributed table in the default database of Alibaba Cloud ClickHouse. Replace parameters with your actual values:

• Destination database: default

• Destination table: test_tbl_distributed

• Source data file: testData.csv

Step 1: Prepare the data

In the clickhouse-client installation directory, create testData.csv with the following content.

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 integrates with Data Management Service (DMS). Connect to a ClickHouse cluster using DMS.

   Other clients: Connect to a database.

2. Create a table based on your cluster edition.

   Important

   Column order and data types must match the source file.

   Enterprise Edition requires only a local table. Community-Compatible Edition may also need a distributed table. CREATE TABLE syntax reference.

   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

   Select an engine based on your cluster's replica type.

   Important

   When you create a table in a dual-replica cluster, you must use a Replicated engine from the MergeTree engine family. If you create a table with a non-Replicated engine in a dual-replica cluster, data cannot be replicated between replicas, which may 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.

      Skip this step if you only need a local table.

      Recommended for multi-node clusters.

      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.

      Skip this step if you only need a local table.

      Recommended for multi-node clusters.

      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

Run the following command from the clickhouse-client installation directory.

To speed up imports, split the source file and run multiple clients in parallel.

For multi-node clusters, import into the distributed table.

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.

   Connect to a ClickHouse cluster using DMS.

2. Run a query statement.

   Important

   On multi-node Community-Compatible Edition clusters, query the distributed table to view all data. A local table returns data from one node only.

   SELECT * FROM test_tbl_local; 

   Expected output:

   +--------------+---------------------+---------------+----------------+----------------------+
   | 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   |
   +--------------+---------------------+---------------+----------------+----------------------+