Importing DWS Table Data to ClickHouse

Updated on 2024-10-08 GMT+08:00

View PDF

ClickHouse supports the import and export of files in CSV or JSON format. This section describes how to export table data from the GaussDB(DWS) service to a CSV file and then import the CSV file to a ClickHouse table.

Prerequisites

The ClickHouse cluster and instances are normal.
You have created a GaussDB(DWS) cluster and obtained the username and password of the database where the related table is located.
The MRS client has been installed in a directory, for example, /opt/client. The client directory in the following operations is only an example. Change it to the actual installation directory. Before using the client, download and update the client configuration file, and ensure that the active management node of Manager is available.

Importing GaussDB(DWS) Service Data to ClickHouse

Download Data Studio. For details, see "Data Studio GUI Client" in Downloading the Related Tools.
Connect to a GaussDB(DWS) database using the username and password of the database in the GaussDB(DWS) cluster. For details, see Adding a Connection.

Export table data from the GaussDB(DWS) database to a CSV file.

(Optional) If a table and data already exist in the GaussDB(DWS) database, skip this step. The following demonstrates how to create a test table in GaussDB(DWS) and insert test data.

Use Data Studio to create a test table warehouse_t1 and insert test data.

CREATE TABLE warehouse_t1 
 (  
   W_WAREHOUSE_SK INTEGER NOT NULL, 
   W_WAREHOUSE_ID CHAR ( 16 ) NOT NULL, 
   W_WAREHOUSE_NAME VARCHAR ( 20 ), 
   W_WAREHOUSE_SQ_FT INTEGER, 
   W_STREET_NUMBER CHAR ( 10 ), 
   W_STREET_NAME VARCHAR ( 60 ), 
   W_STREET_TYPE CHAR ( 15 ), 
   W_SUITE_NUMBER CHAR ( 10 ), 
   W_CITY VARCHAR ( 60 ), 
   W_COUNTY VARCHAR ( 30 ), 
   W_STATE CHAR ( 2 ), 
   W_ZIP CHAR ( 10 ), 
   W_COUNTRY VARCHAR ( 20 ), 
   W_GMT_OFFSET DECIMAL ( 5,2 ),
   W_DATE DATE  
 ); 

INSERT INTO warehouse_t1 VALUES(1314, 123, 'name1', 2324, 123, 'STREET_NAME1', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:07');
INSERT INTO warehouse_t1 VALUES(1314, 123, 'name2', 2324, 123, 'STREET_NAME2', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:08');
INSERT INTO warehouse_t1 VALUES(1314, 123, 'name3', 2324, 123, 'STREET_NAME3', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:09');
INSERT INTO warehouse_t1 VALUES(1314, 123, 'name4', 2324, 123, 'STREET_NAME4', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:00');
INSERT INTO warehouse_t1 VALUES(1314, 123, 'name5', 2324, 123, 'STREET_NAME5', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:01');
INSERT INTO warehouse_t1 VALUES(1314, 123, 'name6', 2324, 123, 'STREET_NAME6', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:02');
INSERT INTO warehouse_t1 VALUES(1314, 123, 'name7', 2324, 123, 'STREET_NAME7', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:03');
INSERT INTO warehouse_t1 VALUES(1314, 123, 'name8', 2324, 123, 'STREET_NAME8', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:04');
INSERT INTO warehouse_t1 VALUES(1314, 123, 'name9', 2324, 123, 'STREET_NAME9', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:05');
INSERT INTO warehouse_t1 VALUES(1314, 123, 'name0', 2324, 123, 'STREET_NAME0', '12', '12', 'guangzhou', 'zhongguo', '1', '12', 'zn', 50.2, '2021-07-05 17:45:06');
INSERT INTO warehouse_t1(W_WAREHOUSE_SK, W_WAREHOUSE_ID, W_WAREHOUSE_NAME, W_DATE) VALUES(1314, 123, 'name0', '2021-07-05 17:45:06');

Export the GaussDB(DWS) table data to a CSV file.
In Object Browser on the left of Data Studio, right-click the table to be exported and select Export Table Data. On the export page, select a specific output path, set Format to CSV and Delimiter to Comma (,), select I Agree under Security Disclaimer, and click OK. In this example, the data file warehouse_t1.csv of the warehouse_t1 table is exported.

Use WinSCP to upload the exported CSV file to the directory of the ClickHouse instance node. For example, upload the warehouse_t1.csv file to the /opt directory.
Log in to the node where the ClickHouse client is installed as the client installation user.
Run the following command to go to the client installation directory:

cd /opt/client
Run the following command to configure environment variables:

source bigdata_env
If Kerberos authentication is enabled for the current cluster, run the following command to authenticate the current user. The user must have the permission to create ClickHouse tables. Therefore, you need to bind the corresponding role to the user. For details, see ClickHouse User and Permission Management. If Kerberos authentication is disabled for the current cluster, skip this step.
1. Run the following command if it is an MRS 3.1.0 cluster:
  export CLICKHOUSE_SECURITY_ENABLED=true
2. kinit Component service user
  Example: kinit clickhouseuser
Run the following command to connect to the ClickHouse instance node to which data is to be imported:

clickhouse client --host IP address of the ClickHouse instance --user Login username --password --port ClickHouse port number --database Database name

Enter the user password.

Create a table with the same structure as the GaussDB(DWS) table on the ClickHouse instance node.

For example, run the following table creation statements to create a ReplicatedMergeTree table warehouse_t1 with the same table structure as that in 3 under the default database and user on the ClickHouse instance.

CREATE TABLE warehouse_t1
(
    `W_WAREHOUSE_SK` Int32 NOT NULL,
    `W_WAREHOUSE_ID` String NOT NULL,
    `W_WAREHOUSE_NAME` String,
    `W_WAREHOUSE_SQ_FT` Int32,
    `W_STREET_NUMBER` String,
    `W_STREET_NAME` String,
    `W_STREET_TYPE` String,
    `W_SUITE_NUMBER` String,
    `W_CITY` String,
    `W_COUNTY` String,
    `W_STATE` String,
    `W_ZIP` String,
    `W_COUNTRY` String,
    `W_GMT_OFFSET` Decimal(5, 2),
    `W_DATE` DateTime
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/warehouse_t1', '{replica}')
PARTITION BY toYear(W_DATE)
ORDER BY (W_DATE, W_WAREHOUSE_ID);

Exit the ClickHouse client.

exit;
Run the following command to import the data in the exported CSV file to the ClickHouse table:

clickhouse client --host ClickHouse instance IP address --database Database name --port Port number --format_csv_delimiter="CSV file delimiter" --query="INSERT INTO Table name FORMAT CSV" < Host path where the CSV file is stored

For example, import the comma-separated CSV file warehouse_t1.csv to the warehouse_t1 table under the default database and user.

clickhouse client --host 10.248.12.10 --format_csv_delimiter="," --query="INSERT INTO warehouse_t1 FORMAT CSV" < /opt/warehouse_t1.csv
After the import is complete, log in to the ClickHouse client, connect to the ClickHouse instance node where the data is imported, and run the query command to check the import result.

For example, query data in the warehouse_t1 table after the import is complete. The result is shown in the following figure.

clickhouse client --host ClickHouse instance IP address --user Login username --password Password --port ClickHouse port number --database Database name

Enter the user password.

select * from warehouse_t1;