云数据库 ClickHouse：访问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  │
    └────┴───────────┴───────┴────────────┴───────────────────────┘