通过ClickHouse访问OSS数据或导出ClickHouse数据至OSS_云数据库 ClickHouse(ClickHouse)-阿里云帮助中心

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

前提条件

OSS服务条件：
- 已开通OSS服务。如何开通，请参见开通OSS服务。
- 访问OSS的RAM用户已具备对OSS对象的读写权限。如何设置权限，请参见权限控制。
集群条件：
已创建数据库账号。如何创建，请参见账号管理。

准备工作

在OSS服务创建存储空间（Bucket）。如何创建，请参见控制台创建存储空间。
重要
确保OSS存储空间（Bucket）与您的云数据库ClickHouse集群在同一地域。
本文示例中Bucket的名为ck-test-oss。
上传文件。
本文以test.csv文件为例，开始操作前，您需将此文件上传至OSS。如何上传，请参见控制台上传文件。
如果您已有目标数据，您需注意云数据库ClickHouse支持访问OSS文件的格式。其除了不支持Protobuf和CapnProto格式外，其他均支持。更多详情，请参见ClickHouse支持的文件格式。

查询OSS数据

方式一：通过OSS外表

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

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

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

创建OSS外表。

OSS外表是云数据库ClickHouse的一种特殊表类型，允许直接查询存储在OSS中的文件，无需将数据导入数据库本地存储，实现存储与计算分离。

建表语法如下。

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

参数说明如下。

参数名	描述	示例
table_name	表名。	oss_test_tb
col_name1,col_name2	列名。	user_name
col_type1,col_type2	列类型。重要 OSS外表的结构类型需与OSS数据对应。	String
bucket-name	OSS的Bucket名称。	ck-test-oss
oss-endpoint	ECS的VPC网络访问（内网）的Endpoint。如何获取，请参见OSS地域和访问域名。重要请确保OSS存储空间（Bucket）与您的云数据库ClickHouse集群在同一地域。	oss-cn-hangzhou-internal.aliyuncs.com
file-name	OSS的Bucket中，文件的名称。	test.csv
access-key-id	访问OSS数据的RAM用户的AccessKey ID。	LTAI****************
access-key-secret	访问OSS数据的RAM用户的AccessKey Secret。如何查看RAM用户的AccessKey ID以及AccessKey Secret，请参见查看RAM用户的AccessKey信息。	yourAccessKeySecret
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('http://ck-test-oss.oss-cn-hangzhou-internal.aliyuncs.com/test.csv', 'LTAI****************', 'yourAccessKeySecret','CSV')

查询OSS数据。

SELECT * FROM oss_test_tb;

结果如下。

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

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

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

查询OSS数据。

SELECT * FROM oss_test_tb;

结果如下。

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

方式二：通过表函数

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

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

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

使用表函数查询数据。

语法如下。

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]

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

使用表函数查询数据。

语法如下。

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]

导出ClickHouse数据或写入数据至OSS

注意事项

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

版本小于等于21.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。
- 这种方式适用于增量导出或写入场景，避免覆盖已有数据。

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

步骤一：登录数据库

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

步骤二：（可选）准备需要导出的数据

如果您是导出ClickHouse数据至OSS，并且没有目标导出数据，可参见以下步骤，准备导出数据。

如果您已有目标导出数据，可跳过此步骤。

根据集群版本建表。
此处建表集群为社区兼容版的单副本集群，如果是企业版或者社区兼容版的双副本集群建表，请参见建表。
```
 CREATE TABLE test_tb_local ON cluster default
(
id UInt8,
user_name String,
age UInt16,
city String,
access_url String
)
ENGINE = MergeTree()
ORDER BY id;
```

写入示例数据。

INSERT INTO test_tb_local VALUES (13, 'tick', 25, 'shanghai', 'http://example.com');

（可选）查看数据。
您可通过以下语句，查看数据是否写入成功。
```
SELECT * FROM test_tb_local;
```

步骤三：创建OSS外表

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

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

参数说明如下。

参数名	描述	示例
table_name	表名。	oss_test_tb
col_name1,col_name2	列名。	user_name
col_type1,col_type2	列类型。重要 OSS外表的结构类型需与OSS数据对应。	String
bucket-name	OSS的Bucket名称。	ck-test-oss
oss-endpoint	ECS的VPC网络访问（内网）的Endpoint。如何获取，请参见OSS地域和访问域名。重要请确保OSS存储空间（Bucket）与您的云数据库ClickHouse集群在同一地域。	oss-cn-hangzhou-internal.aliyuncs.com
file-name	OSS的Bucket中，文件的名称。	test.csv
access-key-id	访问OSS数据的RAM用户的AccessKey ID。	LTAI****************
access-key-secret	访问OSS数据的RAM用户的AccessKey Secret。如何查看RAM用户的AccessKey ID以及AccessKey Secret，请参见查看RAM用户的AccessKey信息。	yourAccessKeySecret
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('http://ck-test-oss.oss-cn-hangzhou-internal.aliyuncs.com/test.csv', 'LTAI****************', 'yourAccessKeySecret','CSV')

建表语法如下。

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

步骤四：导出或写入数据至OSS

导出数据至OSS或写入数据至OSS，均是通过OSS外表写入，使用的是INSERT INTO语句。具体语法，请参见INSERT INTO。各场景示例语句如下。

导出ClickHouse数据

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

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

全量覆盖导出（数据导出至OSS并覆盖已有数据）。

INSERT INTO oss_test_tb SETTINGS s3_truncate_on_insert = 1 SELECT * FROM test_tb_local;

增量导出（数据导出至OSS并不覆盖已有数据）。
```
INSERT INTO oss_test_tb SETTINGS s3_create_new_file_on_insert = 1 SELECT * FROM test_tb_local;
```
此操作完成后，OSS的Bucket中，会新增一个文件。新文件命名为：目标文件名+从0开始，数字以1为步长递增+文件格式名。
例如，创建OSS外表时指定的目标路径上的文件名为test.csv，则新的文件名则为test1.csv。再次执行该操作，新的文件名则为test2.csv。您可使用OSS控制台查看新增的文件。

重要

导出ClickHouse数据至OSS会覆盖已有数据，谨慎操作。

INSERT INTO oss_test_tb  SELECT * FROM test_tb_local;

写入数据

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

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

写入新数据至OSS并覆盖已有数据。

INSERT INTO oss_test_tb SETTINGS s3_truncate_on_insert = 1 VALUES (14, 'tick', 25, 'shanghai', 'http://example.com');

写入新数据至OSS并不覆盖已有数据。
```
INSERT INTO oss_test_tb SETTINGS s3_create_new_file_on_insert = 1 VALUES (14, 'tick', 25, 'shanghai', 'http://example.com');
```
此操作完成后，OSS的Bucket中，会新增一个文件。新文件命名为：目标文件名+从0开始，数字以1为步长递增+文件格式名。
例如，创建OSS外表时指定的目标路径上的文件名为test.csv，则新的文件名则为test1.csv。再次执行该操作，新的文件名则为test2.csv。您可使用OSS控制台查看新增的文件。

重要

通过ClickHouse写入数据至OSS会覆盖已有数据，谨慎操作。

INSERT INTO oss_test_tb SETTINGS s3_truncate_on_insert = 1 VALUES (14, 'tick', 25, 'shanghai', 'http://example.com');

更多操作：导入OSS数据至ClickHouse

如果您是通过OSS将源数据中转并迁移至ClickHouse中，您还需将导出至OSS的数据导入至目标ClickHouse中。如何操作，请参见从OSS导入数据。