在EMR ClickHouse集群,您可以在通过OSS表引擎读写数据,或者通过OSS表函数读数据。本文为您介绍如何将OSS中的数据导入至ClickHouse集群。

前提条件

使用限制

EMR-3.38.0及后续版本,EMR-5.4.0及后续版本的ClickHouse集群,支持从OSS导入数据至ClickHouse集群。

使用OSS表引擎读写数据

语法

创建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 = OSS(path, [access_key_id, access_key_secret,] format, [compression]);
其中,涉及参数描述如下表所示。
参数 描述
db 数据库名。
table_name 表名。
name1/name2 列名。
tyep1/type2 列的类型。
path OSS路径。

ClickHouse集群访问OSS使用地址详情,请参见ECS实例通过OSS内网地址访问OSS资源

path支持virtual hosted style和path style两种形式。推荐您使用virtual hosted style。

path支持使用以下通配符:
  • *表示除了 '/' 以外任意个字符,包括空字符串。
  • ?表示单个字符。
  • {str1,str2,...,strn}表示str1/str2/.../strn中任意一个字符串。
  • {N..M}表示从N到M的任意数字。N和M可以包含前导0,例如 {001..099}。
    注意 根据通配符来决定哪些文件会被使用在 SELECT时而非创建表时,因此如果是为了写入数据至OSS,不应该使用通配符。
access_key_id 阿里云账号的AccessKey ID。
access_key_secret 阿里云账号的AccessKey Secret。
format path所指向的对象(文件)的格式。例如,CSV和XML等类型,详细信息请参见Formats for Input and Output Data
compression 压缩类型。

该参数为可选参数,默认会根据文件扩展选择合适的压缩类型。

根据您创建的集群版本,设置压缩类型:
  • EMR-3.x系列版本:支持nonegzip/gzbrotli/bdeflateauto
  • EMR-5.x系列版本:支持nonegzip/gzbrotli/blzma(xz)autozstd/zst

示例:使用OSS表将数据导入至ClickHouse集群

  1. 下载并上传示例数据orders.csv至OSS。

    上传文件至OSS的详细操作,请参见上传文件

  2. 使用SSH方式登录ClickHouse集群,详情请参见登录集群
  3. 执行以下命令,进入ClickHouse客户端。
    clickhouse-client -m
  4. 执行以下命令,创建数据库test。
    CREATE DATABASE IF NOT EXISTS test ON CLUSTER cluster_emr;
  5. 执行以下命令,创建业务表。
    1. 创建复制表。
      CREATE TABLE test.orders ON CLUSTER cluster_emr
      (
        uid UInt32,
        date DateTime,
        skuId UInt32,
        order_revenue UInt32
      ) ENGINE = ReplicatedMergeTree('/clickhouse/cluster_emr/test/orders/{shard}', '{replica}')
      PARTITION BY toYYYYMMDD(date)
      ORDER BY toYYYYMMDD(date);
    2. 创建分布式表。
      CREATE TABLE test.orders_all ON CLUSTER cluster_emr
      (
        uid UInt32,
        date DateTime,
        skuId UInt32,
        order_revenue UInt32
      ) ENGINE = Distributed(cluster_emr, test, orders, rand());
  6. 执行以下命令,创建表orders_oss。
    CREATE TABLE test.orders_oss
    (
      uid UInt32,
      date DateTime,
      skuId UInt32,
      order_revenue UInt32
    ) ENGINE = OSS('http://test.oss-cn-beijing.aliyuncs.com/orders.csv', '<access_key_id>', '<access_key_secret>', 'CSV');
    说明 示例中的数据目录http://test.oss-cn-beijing.aliyuncs.com/orders.csv,表示cn-beijing地域下名称为test的Bucket中的orders.csv文件。
  7. 将OSS上的数据写入业务表orders_all。
    INSERT INTO test.orders_all
    SELECT
      uid,
      date,
      skuId,
      order_revenue
    FROM
      test.orders_oss;
    您可以通过以下命令检查数据一致性:
    • 查看表orders_all的数据。
      SELECT count(1) FROM test.orders_all;
    • 查看表orders_oss的数据。
      SELECT count(1) FROM test.orders_oss;

示例:将ClickHouse集群数据导出至OSS

  1. 使用SSH方式登录ClickHouse集群,详情请参见登录集群
  2. 执行以下命令,进入ClickHouse客户端。
    clickhouse-client -m
  3. 执行以下命令,创建要写入数据的OSS表。
    CREATE TABLE test.orders_oss
    (
      uid UInt32,
      date DateTime,
      skuId UInt32,
      order_revenue UInt32
    ) ENGINE = OSS('http://test.oss-cn-beijing.aliyuncs.com/orders.csv', '<access_key_id>', '<access_key_secret>', 'CSV');
  4. 执行以下命令,向表中写入数据。
    --假设业务表为test.orders_all
    INSERT INTO test.orders_oss
    SELECT
      uid,
      date,
      skuId,
      order_revenue
    FROM
      test.orders_all;
  5. OSS管理控制台上查看数据。

使用OSS表函数读数据

语法

创建OSS表的语法如下所示。

oss(path, [access_key_id, access_key_secret,] format, structure, [compression])
其中,涉及参数描述如下表所示。
参数 描述
path OSS路径。

ClickHouse集群访问OSS使用地址详情,请参见ECS实例通过OSS内网地址访问OSS资源

path支持virtual hosted style和path style两种形式。推荐您使用virtual hosted style。

path支持使用以下通配符:
  • *表示除了 '/' 以外任意个字符,包括空字符串。
  • ?表示单个字符。
  • {str1,str2,...,strn}表示str1/str2/.../strn中任意一个字符串。
  • {N..M}表示从N到M的任意数字。N和M可以包含前导0,例如 {001..099}。
    注意 根据通配符来决定哪些文件会被使用在 SELECT时而非创建表时,因此如果是为了写入数据至OSS,不应该使用通配符。
access_key_id 阿里云账号的AccessKey ID。
access_key_secret 阿里云账号的AccessKey Secret。
format path所指向的对象(文件)的格式。例如,CSV和XML等类型,详细信息请参见Formats for Input and Output Data
structure 表中字段的类型。例如,column1 UInt32、column2 String。
compression 压缩类型。

该参数为可选参数,默认会根据文件扩展选择合适的压缩类型。

根据您创建的集群版本,设置压缩类型:
  • EMR-3.x系列版本:支持nonegzip/gzbrotli/bdeflateauto
  • EMR-5.x系列版本:支持nonegzip/gzbrotli/blzma(xz)autozstd/zst

示例

  1. 使用SSH方式登录ClickHouse集群,详情请参见登录集群
  2. 执行以下命令,进入ClickHouse客户端。
    clickhouse-client -m
  3. 执行以下命令,查询OSS表数据。
    SELECT * FROM oss(
      'http://test.oss-cn-beijing.aliyuncs.com/orders.csv',
      '<your-access-key>',
      '<your-access-key-secret>',
      'CSV',
      'uid UInt32, date DateTime, skuId UInt32, order_revenue UInt32');
    返回信息如下。
    ┌───uid┬────────date─┬─skuId─┬─order_revenue─┐
    │ 60333391 │ 2021-08-04 11:26:01 │ 49358700 │             89 │
    │ 38826285 │ 2021-08-03 10:47:29 │ 25166907 │             27 │
    │ 10793515 │ 2021-07-31 02:10:31 │ 95584454 │             68 │
    │ 70246093 │ 2021-08-01 00:00:08 │ 82355887 │             97 │
    │ 70149691 │ 2021-08-02 12:35:45 │ 68748652 │              1 │
    │ 87307646 │ 2021-08-03 19:45:23 │ 16898681 │             71 │
    │ 61694574 │ 2021-08-04 23:23:32 │ 79494853 │             35 │
    │ 61337789 │ 2021-08-02 07:10:42 │ 23792355 │             55 │
    │ 66879038 │ 2021-08-01 16:13:19 │ 95820038 │             89 │
    └─────┴──────────┴─────┴─────────┘
  4. 使用OSS表函数将数据导入至ClickHouse集群。
    INSERT INTO test.orders_all
    SELECT
      uid,
      date,
      skuId,
      order_revenue
    FROM
      oss('http://test.oss-cn-beijing.aliyuncs.com/orders.csv',
          '<your-access-key>',
          '<your-access-secret>',
          'CSV',
          'uid UInt32, date DateTime, skuId UInt32, order_revenue UInt32');

OSS相关配置

profile

  1. 支持的profile

    如果使用MultipartUpload上传文件到OSS,则可以设置oss_min_upload_part_size 参数以指定每个part最小的大小,默认值为512 MB,必须使用UInt64范围内的整数。

  2. 设置方法
    • 在一次SQL中,代码设置如下。
      INSERT INTO OSS_TABLE
      SELECT
        ...
      FROM
        ...
      SETTINGS
        oss_min_upload_part_size=1073741824;
    • 在一次Session中,代码设置如下。
      SET oss_min_upload_part_size=1073741824;
      INSERT INTO OSS_TABLE
      SELECT
        ...
      FROM
        ...
      ;
    • 针对某一个表,代码设置如下。
      CREATE TABLE OSS_TABLE
      (
        ...
      ) ENGINE = OSS(...)
      SETTINGS
        oss_min_upload_part_size=1073741824;
    • 针对某一个用户,设置如下。

      在EMR控制台ClickHouse服务的配置页面,单击server-users页签,新增参数为users.<YourUserName>.oss_min_upload_part_size,参数值为1073741824的配置项。

configuration

EMR中的ClickHouse支持使用如下参数配置OSS,代码示例如下。
<oss>
    <endpoint-name>
        <endpoint>https://oss-cn-beijing.aliyuncs.com/bucket</endpoint>
        <access_key_id>ACCESS_KEY_ID</access_key_id>
        <secret_access_key>ACCESS_KEY_SECRET</secret_access_key>
    </endpoint-name>
</oss>
其中,相关参数描述如下。
参数 描述
endpoint-name Endpoint的名称。
endpoint OSS的访问域名,详情请参见OSS访问域名使用规则
access_key_id 阿里云账号的AccessKey ID。
secret_access_key 阿里云账号的AccessKey Secret。
您也可以在EMR控制台ClickHouse服务的配置页面,单击server-config页签,通过以下两个方式新增自定义配置。
方式 操作
方法一 新增参数oss.<endpoint-name>.endpointoss.<endpoint-name>.access_key_idoss.<endpoint-name>.secret_access_key及其对应的参数值。
说明 参数中的<endpoint-name>需要替换为Endpoint的名称。
方法二 新增参数为oss,参数值如下的配置项。
<endpoint-name>
  <endpoint>https://oss-cn-beijing.aliyuncs.com/bucket</endpoint>
  <access_key_id>ACCESS_KEY_ID</access_key_id>
  <secret_access_key>ACCESS_KEY_SECRET</secret_access_key>
</endpoint-name>
说明 参数值请替换为您实际的值。
如果您已进行如上配置,则在创建OSS表或使用OSS表函数时,可以使用如下命令。
  • OSS表
    CREATE TABLE OSS_TABLE
    (
      column1 UInt32,
      column2 String
      ...
    )
    ENGINE = OSS(path, format, [compression]);
  • OSS表函数
    oss(path, format, structure, [compression]);