ApsaraDB for ClickHouse:Use clickhouse-client to import data

This example shows how to import a CSV file into a test_tbl_distributed distributed table in the default database of an ApsaraDB for ClickHouse cluster. You can modify the parameters based on your business requirements. Sample environment:

• Destination database: default

• Destination table: test_tbl_distributed

• Data source: the testData.csv file

Step 1: Prepare data

In the directory in which clickhouse-client is installed, create a CSV file named testData.csv and write the following data to the file:

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.

   ApsaraDB for ClickHouse is integrated with Data Management (DMS), which facilitates connection to a cluster. For more information about how to connect to a cluster by using DMS, see Use DMS to connect to an ApsaraDB for ClickHouse cluster.

   If you use another client, see Database connectivity.

2. Create a table based on the cluster edition.

   Important

   Pay attention to the mappings between table fields and the data types of the table fields, and text fields and the data types of the text fields. Make sure that the column order and data types in the destination table exactly match the column order and data types in the source text. Otherwise, errors may occur during data import.

   For an Enterprise Edition cluster, you need to only create local tables. For a Community-compatible cluster, you may need to create distributed tables based on your business requirements. The following sample statement provides an example on how to create a table. For more information, 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 the ON CLUSTER is not allowed for Replicated database error message appears when you execute the preceding statement, you can update the minor engine version of the cluster to resolve the issue. For more information, see Update the minor engine version.

   Community-compatible Edition

   The table engines that are used by Single-replica Edition and Dual-replica Edition are different. Select a table engine based on the replica type you use.

   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 Edition

   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 want to import data only to the local table, skip this step.

      If your cluster is 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());

   Dual-replica Edition

   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 want to import data only to the local table, skip this step.

      If your cluster is 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 sample data

Run the following command in the directory in which clickhouse-client is installed.

If you want to accelerate data import, you can split the original file into equal segments, start multiple client processes, and concurrently import the segments.

If your cluster is a multi-node cluster, we recommend that you import data to 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: View the import results

1. Connect to the cluster.

   Use DMS to connect to the cluster. For more information, see Use DMS to connect to an ApsaraDB for ClickHouse cluster.

2. Execute the following query statement.

   Important

   If the cluster runs Community-compatible Edition and contains multiple nodes, we recommend that you query the distributed table to retrieve complete data from all nodes. If you query a non-distributed table, you can retrieve data only from a single node in the cluster, which is less than the data you imported.

   SELECT * FROM test_tbl_local; 

   The following query results are 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   |
   +--------------+---------------------+---------------+----------------+----------------------+