ApsaraDB for ClickHouse:Import data using clickhouse-client

This topic provides an example of how to import data from a CSV file into the distributed table in an ApsaraDB for ClickHouse cluster. In practice, modify the parameters to suit your business requirements. This example uses the following environment:

• 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 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.

   You can use DMS to connect to a ClickHouse cluster.

   If you are using other clients, see Connect to a database.

2. Create a table based on your cluster's edition.

   Important

   Data import can fail if the column order and data types in your destination table do not exactly match the data in your source file. Always verify that the table schema corresponds to the file's structure before importing.

   Enterprise Edition clusters only require creating a local table, while Community Compatible Edition clusters may need to create a distributed table based on your environment and requirements. The following are example statements. For more information about the table creation 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;

   When creating a table in an Enterprise Edition cluster, you may get the error ON CLUSTER is not allowed for Replicated database. This is a known issue in older minor versions. To fix this, upgrade the minor engine version of your cluster.

   Community-Compatible Edition

   The engines for single-replica and two-replica clusters differ. Select an engine based on your replica type.

   Important

   When you create tables in a Double-replica Edition cluster, make sure that the tables use Replicated table engines from the MergeTree family. If the tables use non-Replicated table engines, the data on the tables is not replicated across replicas. This can lead to 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 need to import data only to a local table, you can skip this step.

      If you have a multi-node cluster, we recommend that you create a distributed table.

      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 need to import data only to a local table, you can skip this step.

      If you have a multi-node cluster, we recommend that you create a distributed table.

      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 the data

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

To accelerate the data import, you can split the source file into multiple parts and run multiple client processes to import the parts in parallel.

If you have a multi-node cluster, we recommend that you import the data into a 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 result

1. Connect to the cluster.

2. Run a query statement.

   Important

   On a multi-node Community-Compatible Edition cluster, always query the distributed table. Querying a local table directly returns results from only a single node, which means your data will be incomplete.

   SELECT * FROM test_tbl_local;

   The following result is returned.

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