通過ClickHouse訪問OSS資料或匯出ClickHouse資料至OSS - ApsaraDB for ClickHouse

本文介紹如何通過雲資料庫ClickHouse查詢阿里雲Object Storage Service（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的社區相容版

登入資料庫。
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。如何擷取，請參見地區和Endpoint。重要請確保OSS儲存空間（Bucket）與您的雲資料庫ClickHouse叢集在同一地區。	oss-cn-hangzhou-internal.aliyuncs.com
file-name	OSS的Bucket中，檔案的名稱。	test.csv
access-key-id	訪問OSS資料的RAM使用者的AccessKey ID。	yourAccessKeyID
access-key-secret	訪問OSS資料的RAM使用者的AccessKey Secret。請使用已有AccessKey或者參考建立AccessKey重新建立。重要為降低AccessKey泄露的風險，AccessKey Secret 只在建立時顯示一次，後續無法查看，請務必妥善保管。	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]

版本小於等於21.8的社區相容版

登入資料庫。
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。如何擷取，請參見地區和Endpoint。重要請確保OSS儲存空間（Bucket）與您的雲資料庫ClickHouse叢集在同一地區。	oss-cn-hangzhou-internal.aliyuncs.com
access-key-id	訪問OSS資料的RAM使用者的AccessKey ID。	yourAccessKeyID
access-key-secret	訪問OSS資料的RAM使用者的AccessKey Secret。請使用已有AccessKey或者參考建立AccessKey重新建立。重要為降低AccessKey泄露的風險，AccessKey Secret 只在建立時顯示一次，後續無法查看，請務必妥善保管。	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的社區相容版

登入資料庫。
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]

版本小於等於21.8的社區相容版

登入資料庫。
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的社區相容版

無論是匯出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，則第一次執行該操作後，新的檔案名稱則為test0.csv。第二次執行該操作後，新的檔案名稱則為test1.csv。
- 這種方式適用於增量匯出或寫入情境，避免覆蓋已有資料。

版本小於等於21.8的社區相容版

無論是匯出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的社區相容版

建表文法如下。

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。如何擷取，請參見地區和Endpoint。重要請確保OSS儲存空間（Bucket）與您的雲資料庫ClickHouse叢集在同一地區。	oss-cn-hangzhou-internal.aliyuncs.com
file-name	OSS的Bucket中，檔案的名稱。	test.csv
access-key-id	訪問OSS資料的RAM使用者的AccessKey ID。	yourAccessKeyID
access-key-secret	訪問OSS資料的RAM使用者的AccessKey Secret。請使用已有AccessKey或者參考建立AccessKey重新建立。重要為降低AccessKey泄露的風險，AccessKey Secret 只在建立時顯示一次，後續無法查看，請務必妥善保管。	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')

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

參數說明如下。

參數名	描述	樣本
table_name	表名。	oss_test_tb
col_name1,col_name2	列名。	user_name
col_type1,col_type2	列類型。重要 OSS外表的結構類型需與OSS資料對應。	String
oss-endpoint	ECS的VPC網路訪問（內網）的Endpoint。如何擷取，請參見地區和Endpoint。重要請確保OSS儲存空間（Bucket）與您的雲資料庫ClickHouse叢集在同一地區。	oss-cn-hangzhou-internal.aliyuncs.com
access-key-id	訪問OSS資料的RAM使用者的AccessKey ID。	yourAccessKeyID
access-key-secret	訪問OSS資料的RAM使用者的AccessKey Secret。請使用已有AccessKey或者參考建立AccessKey重新建立。重要為降低AccessKey泄露的風險，AccessKey Secret 只在建立時顯示一次，後續無法查看，請務必妥善保管。	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的社區相容版

全量覆蓋匯出（資料匯出至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控制台查看新增的檔案。

版本小於等於21.8的社區相容版

重要

匯出ClickHouse資料至OSS會覆蓋已有資料，謹慎操作。

INSERT INTO oss_test_tb  SELECT * FROM test_tb_local;

寫入資料

企業版或版本大於等於22.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控制台查看新增的檔案。

版本小於等於21.8的社區相容版

重要

通過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匯入資料。