本文介紹如何使用OSS Foreign Table(OSS外表,簡稱OSS FDW)匯入OSS中的資料並進行資料分析。
功能介紹
OSS FDW基於PG FDW(PostgreSQL Foreign Data Wrapper)架構進行開發,通過OSS FDW,您可以進行如下操作:
將OSS資料匯入本地表(行存表或列存表)進行分析加速。
直接查詢分析OSS的海量資料。
OSS外表與本地表關聯分析。
OSS FDW支援多種格式的資料檔案,適用不同的業務情境,具體檔案格式如下:
支援訪問CSV、TEXT、JSON、JSONLINE格式的非壓縮文字檔。
支援訪問CSV、TEXT格式的GZIP壓縮、標準SNAPPY壓縮文字檔。
支援訪問JSON、JSONLINE格式的GZIP壓縮文字檔。
支援訪問ORC格式的二進位檔案。ORC資料類型與AnalyticDB PostgreSQL版資料類型的映射關係,請參見ORC檔案資料類型對照表。
支援訪問PARQUET格式的二進位檔案。PARQUET資料類型與AnalyticDB PostgreSQL版資料類型的映射關係,請參見Parquet檔案資料類型對照表。
支援訪問AVRO格式的二進位檔案。AVRO資料類型與AnalyticDB PostgreSQL版資料類型的映射關係,請參見Avro檔案資料類型對照表。
準備工作
準備OSS資料
準備樣本檔案example.csv。
擷取OSS Bucket資訊
以下內容將指導您擷取Bucket名稱、檔案的路徑、Endpoint(地區節點)和Bucket網域名稱。
登入OSS管理主控台。
在左側導覽列中,單擊目標Bucket列表。
在Bucket列表,單擊目標Bucket。
您可以在Bucket列表頁面,您可以擷取Bucket名稱。
在檔案管理頁面,您可以擷取的Bucket中檔案的路徑。
單擊左側概覽。
在概覽頁面的訪問連接埠地區,您可以擷取Endpoint(地區節點)和Bucket網域名稱。
建議使用ECS的VPC網路訪問(內網)的訪問網域名稱進行訪問。
擷取AccessKey ID和AccessKey Secret
擷取AccessKey ID和AccessKey Secret的具體操作,請參見建立AccessKey。
建立OSS Server
使用CREATE SERVER語句建立OSS Server,指定需要訪問的OSS服務端。更多關於CREATE SERVER的介紹,請參見CREATE SERVER。
文法
CREATE SERVER server_name
FOREIGN DATA WRAPPER fdw_name
[ OPTIONS ( option 'value' [, ... ] ) ]
參數選項
參數 | 類型 | 是否必填 | 說明 |
server_name | 字串 | 是 | OSS Server的名稱。 |
fdw_name | 字串 | 是 | 管理伺服器的外部資料容器的名稱,固定為oss_fdw。 |
OPTIONS參數選項請參見下表:
參數 | 類型 | 是否必填 | 說明 |
endpoint | 字串 | 是 | Bucket網域名稱,擷取方法請參見準備工作。 |
bucket | 字串 | 否 | 資料檔案所屬的Bucket的名稱,擷取方法請參見準備工作。 說明
|
speed_limit | 數值 | 否 | 觸發逾時的資料量。單位為位元組,預設值為1024位元組。 需要與speed_time參數配合使用。 說明 預設情況下,如果連續90秒傳輸的資料量少於1024位元組,則會觸發逾時。具體資訊,請參見OSS SDK 錯誤處理。 |
speed_time | 數值 | 否 | 觸發逾時的時間。單位為秒,預設值為90秒。 需要與speed_limit參數配合使用。 說明 預設情況下,如果連續90秒傳輸的資料量少於1024位元組,則會觸發逾時。具體資訊,請參見OSS SDK 錯誤處理。 |
connect_timeout | 數值 | 否 | 連線逾時時間。單位為秒,預設值為10秒。 |
dns_cache_timeout | 數值 | 否 | DNS逾時時間。單位為秒,預設值為60秒。 |
樣本
CREATE SERVER oss_serv
FOREIGN DATA WRAPPER oss_fdw
OPTIONS (
endpoint 'oss-cn-********.aliyuncs.com',
bucket 'adb-pg'
);
您也可以通過ALTER SERVER語句修改OSS Server的配置,使用方法,請參見ALTER SERVER。
修改OSS Server配置樣本如下:
修改OSS Server中的某個參數:
ALTER SERVER oss_serv OPTIONS(SET endpoint 'oss-cn-********.aliyuncs.com');
添加OSS Server中的某個參數:
ALTER SERVER oss_serv OPTIONS(ADD connect_timeout '20');
刪除OSS Server中的某個參數:
ALTER SERVER oss_serv OPTIONS(DROP connect_timeout);
您也可以通過DROP SERVER語句刪除該OSS Server,使用方法,請參見DROP SERVER。
建立OSS User Mapping
建立OSS Server後,您還需要建立一個訪問OSS Server的使用者。您可以使用CREATE USER MAPPING語句建立OSS User Mapping,用於定義AnalyticDB PostgreSQL版資料庫使用者與訪問OSS Server使用者的映射關係。更多介紹,請參見CREATE USER MAPPING。
文法
CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }
SERVER servername
[ OPTIONS ( option 'value' [, ... ] ) ]
參數選項
參數 | 類型 | 是否必填 | 說明 |
username | 字串 | 是,四選一 | 指定映射的AnalyticDB PostgreSQL版執行個體的使用者名稱。 |
USER | 字串 | 映射當前的AnalyticDB PostgreSQL版執行個體的使用者名稱。 | |
CURRENT_USER | 字串 | ||
PUBLIC | 字串 | 匹配所有AnalyticDB PostgreSQL版執行個體的使用者名稱,包括以後建立的使用者。 | |
servername | 字串 | 是 | OSS Server的名稱。 |
OPTIONS參數選項請參見下表:
參數 | 類型 | 是否必填 | 說明 |
id | 字串 | 是 | AccessKey ID,擷取方法,請參見建立AccessKey。 |
key | 字串 | 是 | AccessKey Secret,擷取方法,請參見建立AccessKey。 |
樣本
CREATE USER MAPPING FOR PUBLIC
SERVER oss_serv
OPTIONS (
id 'LTAI5t7Ge***************',
key 'FikziJd2La*******************'
);
您也可以通過DROP USER MAPPING語句刪除該使用者,使用方法,請參見DROP USER MAPPING。
建立OSS FDW
擁有OSS Server和訪問OSS Server的使用者後,您可以開始建立OSS FDW。您可以使用CREATE FOREIGN TABLE語句建立OSS FDW,更多資訊,請參見CREATE FOREIGN TABLE。
文法
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
[, ... ]
] )
SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]
參數選項
參數 | 類型 | 是否必填 | 說明 |
table_name | 字串 | 是 | OSS FDW名稱。 |
column_name | 字串 | 是 | 列名。 |
data_type | 字串 | 是 | 該列的資料類型。 |
OPTIONS參數選項請參見下表:
參數 | 類型 | 是否必填 | 說明 |
filepath | 字串 | 是,三選一 | OSS中包含路徑的檔案名稱。 如果使用filepath參數,則僅選擇指定檔案。 |
prefix | 字串 | 指定資料檔案對應路徑名的首碼,不支援Regex,僅是匹配首碼。 如果使用prefix參數,則會選擇含有這一首碼的所有OSS檔案,例如:
| |
dir | 字串 | OSS中的目錄路徑。目錄路徑需要以/結尾,例如test/mydir/。 如果使用dir參數,會選擇虛擬目錄下的所有檔案,但不包括它的子目錄和子目錄下的檔案。 | |
bucket | 字串 | 否 | 資料檔案所屬的Bucket的名稱,擷取方法請參見準備工作。 說明
|
format | 字串 | 是 | 檔案格式,取值範圍如下:
|
filetype | 字串 | 否 | 檔案類型,取值範圍如下:
說明
|
log_errors | 布爾型 | 否 | 是否將錯誤記錄到記錄檔。預設值為false。更多資訊,請參見容錯機制。 說明 該參數僅適用於CSV和TEXT格式的檔案。 |
segment_reject_limit | 數值 | 否 | 異常中止任務(error abort)的數量。 包含%時表示錯誤行百分比,不包含%時表示錯誤行數。例如:
說明 該參數僅適用於CSV和TEXT格式的檔案。 |
header | 布爾型 | 否 | 源檔案中欄位名是否包含header行,取值如下:
說明 該參數僅適用於CSV格式的檔案。 |
delimiter | 字串 | 否 | 欄位分隔符號,僅允許設定為單位元組字元。
說明 該參數僅適用於CSV和TEXT格式的檔案。 |
quote | 字串 | 否 | 欄位引號,僅允許設定為單位元組字元。預設為雙引號(")。 說明 該參數僅適用於CSV格式的檔案。 |
escape | 字串 | 否 | 聲明匹配quote參數的字串,只允許為單位元組的字元。預設為雙引號(")。 說明 該參數僅適用於CSV格式的檔案。 |
null | 字串 | 否 | 指定檔案中的NULL字串。
說明 該參數僅適用於CSV和TEXT格式的檔案。 |
encoding | 字串 | 否 | 指定資料檔案編碼。預設情況下為用戶端編碼。 說明 該參數僅適用於CSV和TEXT格式的檔案。 |
force_not_null | 布爾型 | 否 | 是否聲明欄位的值不匹配Null 字元串,取值如下:
說明 該參數僅適用於CSV和TEXT格式的檔案。 |
force_null | 布爾型 | 否 | Null 字元串處理方法,取值如下:
說明 該參數僅適用於CSV和TEXT格式的檔案。 |
樣本
CREATE FOREIGN TABLE ossexample (
date text,
time text,
open float,
high float,
low float,
volume int
) SERVER oss_serv OPTIONS (dir 'dir_oss_adb/', format 'csv');
建立OSS FDW完成後,您可以通過如下方式查看OSS FDW匹配的OSS檔案清單是否符合預期:
方法一:
EXPLAIN VERBOSE SELECT * FROM <OSS FDW表名>;
方法二:
SELECT * FROM get_oss_table_meta('<OSS FDW表名>');
您也可以通過DROP FOREIGN TABLE語句刪除該OSS FDW,使用方法,請參見DROP FOREIGN TABLE。
查詢分析OSS資料
查詢OSS FDW外表的資料與查詢本地表資料方法一樣,常見查詢如下:
索引值過濾查詢,樣本如下:
SELECT * FROM ossexample WHERE volume = 5;
彙總查詢,樣本如下:
SELECT count(*) FROM ossexample WHERE volume = 5;
過濾、分組和LIMIT查詢,樣本如下:
SELECT low, sum(volume) FROM ossexample GROUP BY low ORDER BY low limit 5;
OSS FDW外表與本地表關聯分析
建立用於關聯分析的本地表example,並插入測試資料,樣本如下:
CREATE TABLE example (id int, volume int); INSERT INTO example VALUES(1,1), (2,3), (4,5);
本地表example和OSS FDW外表ossexample執行關聯查詢,樣本如下:
SELECT example.volume, min(high), max(low) FROM ossexample, example WHERE ossexample.volume = example.volume GROUP BY(example.volume) ORDER BY example.volume;
容錯機制
OSS FDW通過log_errors和segment_reject_limit參數提供容錯功能,未經處理資料中的錯誤資料不會導致OSS外表掃描停止。
關於log_errors和segment_reject_limit參數的資訊,請參見建立OSS FDW。
建立支援容錯的OSS FDW外表,樣本如下:
CREATE FOREIGN TABLE oss_error_sales (id int, value float8, x text) SERVER oss_serv OPTIONS (log_errors 'true', -- 記錄錯誤行資訊 segment_reject_limit '10', -- 錯誤行數不得超過10行,否則會停止掃描。 dir 'error_sales/', -- 指定外表匹配的OSS檔案目錄 format 'csv', -- 指定按csv格式解析檔案 encoding 'utf8'); -- 指定檔案編碼
查看錯誤行的日誌,查看方式如下:
SELECT * FROM gp_read_error_log('oss_error_sales');
刪除錯誤行的日誌,刪除方式如下:
SELECT gp_truncate_error_log('oss_error_sales');
常見問題
Q:刪除OSS FDW上的資料時,能否同時刪除OSS上的資料。
A:刪除OSS FDW上的資料時,無法刪除OSS上的資料。