Importing DWS Data to a ClickHouse Table

Prerequisites

• The ClickHouse cluster and instances are normal.
• You have created a 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 DWS Service Data to ClickHouse

1. Download Data Studio. For details, see "Data Studio GUI Client" in Downloading the Related Tools.
2. Connect to a DWS database using the username and password of the database in the DWS cluster. For details, see Adding a Connection.
3. Export table data from the DWS database to a CSV file.
   1. (Optional) If a table and data already exist in the DWS database, skip this step. The following demonstrates how to create a test table in 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');

   2. Export the 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.

      

4. 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.
5. Log in to the node where the ClickHouse client is installed as the client installation user.
6. Run the following command to go to the client installation directory:

   cd /opt/client

7. Run the following command to configure environment variables:

   source bigdata_env

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

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

10. Create a table with the same structure as the 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);

11. Exit the ClickHouse client.

    exit;

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

13. 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;