通过外表访问OSS数据

企业版或版本大于等于22.8的社区兼容版

版本小于等于21.8的社区兼容版

1. 登录数据库。

   1. 登录云数据库ClickHouse控制台。

   2. 在集群列表页面，选择社区版实例列表，单击目标集群ID。

   3. 在集群信息页面，单击右上方导航栏的登录数据库。

   4. 在登录实例页面，输入数据库账号和密码，单击登录。

2. 创建OSS外表。

   建表语法如下。

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

   参数说明如下。

   参数名	描述	示例
   table_name	表名。	oss_test_tb
   col_name1,col_name2	列名。	user_name
   col_type1,col_type2	列类型。 重要 OSS外表的结构类型需与OSS数据对应。	String
   oss-endpoint	ECS的VPC网络访问（内网）的Endpoint。如何获取，请参见OSS地域和访问域名。 重要 请确保OSS存储空间（Bucket）与您的云数据库ClickHouse集群在同一地域。	oss-cn-hangzhou-internal.aliyuncs.com
   access-key-id	访问OSS数据的RAM用户的AccessKey ID。	LTAI****************
   access-key-secret	访问OSS数据的RAM用户的AccessKey Secret。 如何查看RAM用户的AccessKey ID以及AccessKey Secret，请参见查看RAM用户的AccessKey信息。	yourAccessKeySecret
   oss-file-path	文件的存储路径。一般格式为oss://<bucket-name>/<path-to-file>。 说明 oss-file-path参数支持通过通配符进行模糊匹配。更多信息，请参见通配符模糊匹配OSS的存储路径。	oss://ck-test-oss/test.csv
   file-format-name	文件的格式。 重要 此参数需严格按照ClickHouse支持的文件格式命名填写，不要随意更改文件格式命名的大小写。	CSV

   示例如下。

   CREATE TABLE oss_test_tb ON cluster default
   (
   id UInt8,
   user_name String,
   age UInt16,
   city String,
   access_url String
   )
   ENGINE = OSS('oss-cn-hangzhou-internal.aliyuncs.com', 'LTAI****************', 'yourAccessKeySecret', 'oss://ck-test-oss/test.csv', 'CSV');

3. 查询OSS数据。

   SELECT * FROM oss_test_tb;

   结果如下。

   +--------------+---------------------+---------------+----------------+----------------------+
   | id           | user_name           | age           | city           | access_url           |
   +--------------+---------------------+---------------+----------------+----------------------+
   | 1           | tick                | 32            | shanghai       | http://example.com   |
   +--------------+---------------------+---------------+----------------+----------------------+
   返回行数: [1], 耗时: [183ms]

企业版或版本大于等于22.8的社区兼容版

1. 登录数据库。

   1. 登录云数据库ClickHouse控制台。

   2. 在集群列表页面，选择社区版实例列表，单击目标集群ID。

   3. 在集群信息页面，单击右上方导航栏的登录数据库。

   4. 在登录实例页面，输入数据库账号和密码，单击登录。

2. 使用表函数查询数据。

   语法如下。

   SELECT * FROM oss('https://<bucket-name>.<oss-endpoint>/<file-name>','<access-key-id>', '<access-key-secret>', '<file-format-name>', '<col_name> <col_type>(,...)');

   参数详细与创建OSS外表的参数一致，请参见参数说明。

   示例如下。

   SELECT * FROM oss('http://testBucketName.oss-cn-shanghai-internal.aliyuncs.com/test.csv', 'LTAI****************', 'yourAccessKeySecret', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String')

   结果如下。

   +--------------+---------------------+---------------+----------------+----------------------+
   | id           | user_name           | age           | city           | access_url           |
   +--------------+---------------------+---------------+----------------+----------------------+
   | 1           | tick                | 32            | shanghai       | http://example.com   |
   +--------------+---------------------+---------------+----------------+----------------------+
   返回行数: [1], 耗时: [183ms]

版本小于等于21.8的社区兼容版

1. 登录数据库。

   1. 登录云数据库ClickHouse控制台。

   2. 在集群列表页面，选择社区版实例列表，单击目标集群ID。

   3. 在集群信息页面，单击右上方导航栏的登录数据库。

   4. 在登录实例页面，输入数据库账号和密码，单击登录。

2. 使用表函数查询数据。

   语法如下。

   SELECT * FROM oss('<oss-endpoint>', '<access-key-id>', '<access-key-secret>', '<oss-file-path>', '<file-format-name>', '<col_name> <col_type>(,...)');

   参数详细与创建OSS外表的参数一致，请参见参数说明。

   示例如下。

   SELECT * FROM oss('oss-cn-hangzhou-internal.aliyuncs.com', 'LTAI****************', 'yourAccessKeySecret', 'oss://ck-test-oss/test.csv', 'CSV', 'id UInt8, user_name String, age UInt16, city String, access_url String');

   结果如下。

   +--------------+---------------------+---------------+----------------+----------------------+
   | id           | user_name           | age           | city           | access_url           |
   +--------------+---------------------+---------------+----------------+----------------------+
   | 1           | tick                | 32            | shanghai       | http://example.com   |
   +--------------+---------------------+---------------+----------------+----------------------+
   返回行数: [1], 耗时: [183ms]

企业版或版本大于等于22.8的社区兼容版

无论是导出ClickHouse数据至OSS还是通过ClickHouse直接写入数据至OSS，操作时均需设置s3_truncate_on_insert=1或者s3_create_new_file_on_insert=1参数，如果不设置或将它们设置为0，当目标路径上的文件已存在时，导出或写入数据至OSS的操作会失败。

• s3_truncate_on_insert=1：云数据库ClickHouse在导出或者写入数据至OSS时会检查目标路径上是否已存在文件。

  • 如果文件已存在，ClickHouse会覆盖原有文件并写入新数据。

  • 适用于全量覆盖导出或写入场景，但需谨慎操作，因为这会导致原有数据被删除。

• s3_create_new_file_on_insert=1：ClickHouse 在导出或者写入数据至OSS时会检查目标路径上是否已存在文件。

  • 如果文件已存在，ClickHouse会在对应目录下创建一个新的文件，并将数据写入新文件中。新文件命名为：目标文件名+从0开始，数字以1为步长递增+文件格式名。

    例如，创建OSS外表时指定的目标路径上的文件名为test.csv，则第一次执行该操作后，新的文件名则为test1.csv。第二次执行该操作后，新的文件名则为test2.csv。

  • 这种方式适用于增量导出或写入场景，避免覆盖已有数据。

版本小于等于21.8的社区兼容版

无论是导出ClickHouse数据至OSS还是通过ClickHouse直接写入数据至OSS，OSS文件只会有一个。导出或者写入的数据会覆盖原始文件的内容，谨慎操作。

企业版或版本大于等于22.8的社区兼容版

CREATE TABLE <table_name> [ON cluster default]
(
'col_name1' col_type1,
'col_name2' col_type2,
...
)
ENGINE = OSS('https://<bucket-name>.<oss-endpoint>/<file-name>', '<access-key-id>', '<access-key-secret>', '<file-format-name>');

CREATE TABLE oss_test_tb ON cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = OSS('http://ck-test-oss.oss-cn-hangzhou-internal.aliyuncs.com/test.csv', 'LTAI****************', 'yourAccessKeySecret','CSV')

版本小于等于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>');

CREATE TABLE oss_test_tb ON cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = OSS('oss-cn-hangzhou-internal.aliyuncs.com', 'LTAI****************', 'yourAccessKeySecret', 'oss://ck-test-oss/test.csv', 'CSV');