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

Use S3 table engines to import data

Hadoop Distributed File System (HDFS) table engines of ClickHouse can read file data in a specific format from a specified OSS address. Syntax:

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 object orders.csv and upload the object to OSS. In this example, the object is uploaded to the root directory of an OSS bucket named test.

   2. Execute the following statements to create an OSS table and use 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

      In the sample code, http://test.oss-cn-beijing-internal.aliyuncs.com/orders.csv is the path to the orders.csv object in an OSS bucket named test in the China (Beijing) region.

2. Execute the following statements to import data to the product.orders_all table:

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

3. Execute the following statements to view the data in the table and check data consistency:

   • Query the data in the orders_all table.

     SELECT count(1) FROM product.orders_all;

   • Query the data in the orders_oss table.

     SELECT count(1) FROM oss.orders_oss;

Use S3 table functions to import data

S3 table functions of ClickHouse can read file data from a specified HDFS address and return a table with a specified schema. Syntax:

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

1. Use S3 table functions to import data to 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. Execute the following statements to view the data in the table and check data consistency:

   • Query the data in the orders_all table.

     SELECT count(1) FROM product.orders_all;

   • Query the data in the orders_oss table.

     SELECT count(1) FROM oss.orders_oss;

Use S3 table engines to export data

1. Execute the following statement 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. Execute the following statements to write data to the table:

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

   Note

   During the data export, ClickHouse creates an object in the related path and writes data to the object. By default, if the object to which you want to write data already exists in OSS, the data export fails. You can configure parameters for EMR clusters of V5.8.0, V3.45.0, and their later minor versions to prevent the failure.

3. View data in the OSS console.

Use HDFS table functions to export data

1. Execute the following statements 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

   During the data export, ClickHouse creates an object in the related path and writes data to the object. By default, if the object to which you want to write data already exists in OSS, the data export fails. You can configure parameters for EMR clusters of V5.8.0, V3.45.0, and their later minor versions to prevent the failure.

2. View data in the OSS console.