E-MapReduce:Import and export data between OSS and ClickHouse

Import with the S3 table engine

The ClickHouse S3 table engine reads data in a specific format from a specified OSS path. The syntax is as follows:

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
  name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
  name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|ALIAS expr2] [compression_codec] [TTL expr2],
  ...
)
ENGINE = S3(path, [access_key_id, access_key_secret,] format, [compression]);

1. Create a table to read data from OSS.

   1. Download the sample data file orders.csv and upload it to the root directory of an OSS bucket named test.

   2. Run the following command to create an OSS table that uses the S3 table engine.

      CREATE DATABASE IF NOT EXISTS oss ON CLUSTER cluster_emr;
      CREATE TABLE oss.orders_oss
      (
        uid UInt32,
        date DateTime,
        skuId UInt32,
        order_revenue UInt32
      ) ENGINE = S3('http://test.oss-cn-beijing-internal.aliyuncs.com/orders.csv', '<access_key_id>', '<access_key_secret>', 'CSV');

      Note

      The example data path http://test.oss-cn-beijing-internal.aliyuncs.com/orders.csv represents the orders.csv file in a bucket named test in the cn-beijing region.

2. Run the following command to import data into the product.orders_all table.

   INSERT INTO product.orders_all
   SELECT
     uid,
     date,
     skuId,
     order_revenue
   FROM
     oss.orders_oss;

3. Run the following commands to view the data in the tables and verify data consistency:

   • View the data in the orders_all table.

     SELECT count(1) FROM product.orders_all;

   • View the data in the orders_oss table.

     SELECT count(1) FROM oss.orders_oss;

Import with an S3 table function

The ClickHouse S3 table function reads data from a specified OSS path and returns a table with a defined structure. The syntax is as follows:

s3(path, [access_key_id, access_key_secret,] format, structure, [compression])

1. Use the S3 table function to import data into the ClickHouse cluster.

   INSERT INTO product.orders_all
   SELECT
     uid,
     date,
     skuId,
     order_revenue
   FROM
     s3('http://test.oss-cn-beijing-internal.aliyuncs.com/orders.csv',
         '<your-access-key>',
         '<your-access-secret>',
         'CSV',
         'uid UInt32, date DateTime, skuId UInt32, order_revenue UInt32');

2. Run the following commands to view the data in the tables and verify data consistency:

   • View the data in the orders_all table.

     SELECT count(1) FROM product.orders_all;

   • View the data in the orders_oss table.

     SELECT count(1) FROM oss.orders_oss;

Export with the S3 table engine

1. Run the following command to create an S3 table.

   CREATE TABLE oss.orders_oss
   (
     uid UInt32,
     date DateTime,
     skuId UInt32,
     order_revenue UInt32
   ) ENGINE = S3('http://test.oss-cn-beijing-internal.aliyuncs.com/orders.csv', '<access_key_id>', '<access_key_secret>', 'CSV');

2. Run the following command to write data to the table.

   -- Assume the business table is product.orders_all
   INSERT INTO oss.orders_oss
   SELECT
     uid,
     date,
     skuId,
     order_revenue
   FROM
     product.orders_all;

   Note

   When ClickHouse exports data, it creates a file at the specified path and writes data to it. By default, the export fails if the file already exists. On EMR-5.8.0, EMR-3.45.0, and later, you can configure parameters to change this behavior.

3. View the data in the OSS console.

Export with an S3 table function

1. Run the following command to export data.

   INSERT INTO FUNCTION
   s3('http://test.oss-cn-beijing-internal.aliyuncs.com/orders.csv',
         '<your-access-key>',
         '<your-access-secret>',
         'CSV',
         'uid UInt32, date DateTime, skuId UInt32, order_revenue UInt32')
   SELECT
     uid,
     date,
     skuId,
     order_revenue
   FROM
     product.orders_all;

   Note

   When ClickHouse exports data, it creates a file at the specified path and writes data to it. By default, the export fails if the file already exists. On EMR-5.8.0, EMR-3.45.0, and later, you can configure parameters to change this behavior.

2. View the data in the OSS console.