本文介绍如何通过云数据库ClickHouse查询阿里云对象存储OSS（Object Storage Service）数据以及如何写入数据至OSS。

前提条件

  • 已开通OSS服务。如何开通，请参见开通OSS服务。
  • 已在云数据库ClickHouse所在地域创建存储空间（Bucket）。如何创建，请参见创建存储空间。
  • 已上传文件至OSS。如何上传，请参见上传文件。
    说明 本文以1,tick,32,shanghai,http://example.com为示例数据，存储为test.csv。
  • 访问OSS的账号已具备对OSS对象的读写权限。如何设置权限，请参见访问控制概述。

注意事项

  • 云数据库ClickHouse支持访问多种格式的OSS文件，除不支持Protobuf和CapnProto外，其他均支持，具体请参见ClickHouse支持的文件格式。
  • 请确认OSS存储空间（Bucket）与您的云数据库ClickHouse集群在同一地域，并使用阿里云VPC网络访问OSS。
  • 通过云数据库ClickHouse写入数据至OSS，会覆盖原来的OSS文件数据。

步骤一：登录数据库

  1. 登录云数据库ClickHouse控制台。
  2. 在集群列表页面，选择默认实例列表，单击目标集群ID。
  3. 在集群信息页面，单击右上方导航栏的登录数据库。
  4. 在登录实例页面，输入数据库账号和密码，单击登录。

步骤二：创建OSS外表

  1. 创建OSS外表。
    • 21.8及以下版本的建表语法：
      CREATE TABLE <table_name> [on cluster default]
(
'col_name1' col_type1,
'col_name2' col_type2,
...
)
ENGINE = OSS('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>');
    • 22.8版本的建表语法：
      CREATE TABLE <table_name> [on cluster default]
(
'col_name1' col_type1,
'col_name2' col_type2,
...
)
ENGINE = OSS('https://<bucketName>.<oss-endpoint>/<file-name>', '<access-key-id>', '<access-key-secret>', '<file-format-name>');
      参数说明如下：
      参数名 描述
      table_name 表名。
      col_name1,col_name2 列名。
      col_type1,col_type2 列类型。
      重要 OSS外表的结构类型需与OSS数据对应。
      BucketName Bucket的名称。
      oss-endpoint ECS的VPC网络访问（内网）的Endpoint。如何获取，请参见访问域名和数据中心。
      重要 请确保OSS存储空间（Bucket）与您的云数据库ClickHouse集群在同一地域。
      file-name 文件名称。
      access-key-id 访问OSS数据的AccessKey ID。
      access-key-secret 访问OSS数据的AccessKey Secret。
      oss-file-path 文件的存储路径。一般格式为oss://<bucket-name>/<path-to-file>。
      说明 oss-file-path参数支持通过通配符进行模糊匹配。更多信息，请参见通配符模糊匹配OSS的存储路径。
      file-format-name 文件的格式，本文为CSV。
    示例语句：
    • 21.8及以下版本的示例语句：
      CREATE TABLE oss_test_tbl on cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = OSS('oss-cn-shanghai-internal.aliyuncs.com', 'LTAI5tDVcUKu2CbcBwhr****', 'WAcroHBcL7IVOK8FIHzNJy91Lc****', 'oss://testBucketName/test.csv', 'CSV');
    • 22.8版本的示例语句：
      CREATE TABLE oss_test_tbl on cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = OSS('http://testBucketName.oss-cn-shanghai-internal.aliyuncs.com/test.csv', 'LTAI5tDVcUKu2CbcBwhr****', 'WAcroHBcL7IVOK8FIHzNJy91Lc****','CSV')
  2. 查询OSS外表的数据。
    select * from oss_test_tbl;
    返回结果：
    ┌─id─┬─user_name─┬──age──┬───city─────┬─────access_url────────┐
│  1 │  tick     │   32  │  shanghai  │   http://example.com  │
└────┴───────────┴───────┴────────────┴───────────────────────┘

步骤三：写入数据至OSS

insert into oss_test_tbl values(11, 'tick', 25, 'shanghai', 'http://example.com');

步骤四：查询数据

云数据库ClickHouse提供了表引擎和表函数两种方法查询OSS数据。

  • 通过表引擎查询OSS数据。
    查询OSS外表的数据：
    select * from oss_test_tbl;
    返回结果：
    ┌─id─┬─user_name─┬──age──┬───city─────┬─────access_url────────┐
│ 11 │  tick     │   25  │  shanghai  │   http://example.com  │
└────┴───────────┴───────┴────────────┴───────────────────────┘
  • 通过表函数查询OSS数据。
    • 21.8及以下版本的查询语法：
      SELECT * FROM oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>', '<col_name> <col_type>(,...)');
    • 22.8版本的查询语法：
      SELECT * FROM oss('https://<bucketName>.<oss-endpoint>/<file-name>','<access-key-id>', '<access-key-secret>', '<file-format-name>', '<col_name> <col_type>(,...)');

      参数的详细信息，请参见参数说明。

    示例语句：
    • 21.8及以下版本的查询语句：
      SELECT * FROM oss('oss-cn-shanghai-internal.aliyuncs.com', 'LTAI5tDVcUKu2CbcBwhr****', 'WAcroHBcL7IVOK8FIHzNJy91Lc****', 'oss://testBucketName/test.csv', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');
    • 22.8版本的查询语句：
      SELECT * FROM oss('http://testBucketName.oss-cn-shanghai-internal.aliyuncs.com/test.csv', 'LTAI5tDVcUKu2CbcBwhr****', 'WAcroHBcL7IVOK8FIHzNJy91Lc****', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String')
      返回结果：
      ┌─id─┬─user_name─┬──age──┬───city─────┬─────access_url────────┐
│ 11 │  tick     │   25  │  shanghai  │   http://example.com  │
└────┴───────────┴───────┴────────────┴───────────────────────┘