OSS与ClickHouse间的数据导入与导出

通过S3表引擎导入数据

ClickHouse的HDFS表引擎能够从指定OSS地址读取特定格式的文件数据，语法如下：

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. 建表从OSS当中读取数据

   1. 下载并上传示例数据orders.csv至OSS中，本文将文件上传名为test的OSS Bucket的根目录下。

   2. 执行以下命令创建OSS表，设置表引擎为S3表引擎。

      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');

      说明

      示例中的数据目录http://test.oss-cn-beijing-internal.aliyuncs.com/orders.csv，表示cn-beijing地域下名称为test的Bucket中的orders.csv文件。

2. 执行以下命令将数据导入product.orders_all表中。

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

3. 您可以通过以下命令查看表中数据，验证数据一致性：

   • 查看表orders_all的数据。

     SELECT count(1) FROM product.orders_all;

   • 查看表orders_oss的数据。

     SELECT count(1) FROM oss.orders_oss;

通过S3表函数导入数据

ClickHouse的S3表函数能够从指定HDFS地址读取文件数据，返回指定结构的表，语法如下：

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

1. 使用S3表函数将数据导入至ClickHouse集群。

   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. 您可以通过以下命令查看表中数据，验证数据一致性：

   • 查看表orders_all的数据。

     SELECT count(1) FROM product.orders_all;

   • 查看表orders_oss的数据。

     SELECT count(1) FROM oss.orders_oss;

通过S3表引擎导出数据

1. 执行以下命令，创建S3表。

   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. 执行以下命令，向表中写入数据。

   --假设业务表为product.orders_all
   INSERT INTO oss.orders_oss
   SELECT
     uid,
     date,
     skuId,
     order_revenue
   FROM
     product.orders_all;

   说明

   ClickHouse在数据导出时会在相应地址上创建文件并写入数据，默认方式在文件已存在情况下导出失败。EMR-5.8.0、EMR-3.45.0之后的版本可通过配置参数来避免此问题。

3. 在OSS管理控制台上查看数据。

通过HDFS表函数导出数据

1. 执行以下命令导出数据。

   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;

   说明

   ClickHouse在数据导出时会在相应地址上创建文件并写入数据，默认方式在文件已存在情况下导出失败。EMR-5.8.0、EMR-3.45.0之后的版本可通过配置参数来避免此问题。

2. 在OSS管理控制台上查看数据。