Import data using clickhouse-client - ApsaraDB for ClickHouse

Use the clickhouse-client tool to import data into ApsaraDB for ClickHouse. This example imports the On Time dataset into the ontime_local_distributed distributed table in the clickhouse_demo database.

Prerequisites

You have completed the following steps in the Quick Start guide:
1. Create a cluster
2. Create an account
3. Configure an IP address whitelist
  
  Note
  Add the IP address of the server where clickhouse-client is installed to the whitelist of the ApsaraDB for ClickHouse cluster.
4. Connect to a cluster
5. Create a database
6. Create a table
Ensure that the clickhouse-client tool is installed and its version is compatible with your ApsaraDB for ClickHouse cluster. For download links, see clickhouse-client.

Procedure

Click On Time Data to download the On Time dataset.
Unzip the downloaded On Time dataset.
```
unzip ontime-data(1).zip
```

Connect to the ApsaraDB for ClickHouse cluster and import the data into ApsaraDB for ClickHouse.

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

./clickhouse-client --host=<host> --port=<port> --user=<user> --password=<password> --query="INSERT INTO <ClickHouse_table> FORMAT CSVWithNames" < ontime-data.csv

The following table describes the parameters in the command.

Parameter	Description
`host`	The public endpoint or VPC endpoint. You can find this information on the Cluster Information page. Use the VPC endpoint if your client server and ApsaraDB for ClickHouse cluster are in the same VPC. Otherwise, use the public endpoint.
`port`	The TCP port number. You can find the port number on the Cluster Information page.
`user`	The database account that you created in the ApsaraDB for ClickHouse console.
`password`	The password for the database account.
`ClickHouse_table`	The target table in ApsaraDB for ClickHouse.

Example:

./clickhouse-client --host=cc-bp16qwvp7hy8i****.public.clickhouse.ads.aliyuncs.com --port=3306 --user=test --password=123456Aa --query="INSERT INTO clickhouse_demo.ontime_local_distributed FORMAT CSVWithNames" < ontime-data.csv

Query the data to verify that the import was successful.

SELECT OriginCityName, count(*) AS flights
FROM ontime_local_distributed
GROUP BY OriginCityName
ORDER BY flights DESC
LIMIT 10;

Expected output:

   OriginCityName      │ flights  
 ──────────────────────│────────
   Chicago, IL         │  24114    
   Atlanta, GA         │  22001  
 Dallas/Fort Worth, TX │  17340   
   Los Angeles, CA     │  14494  
   Denver, CO          │  14170  
   New York, NY        │  14075   
   Washington, DC      │  11985  
   Houston, TX         │  11483    
   San Francisco, CA   │  11259  
   St. Louis, MO       │  10721