通過oss_fdw讀寫外部資料檔案 - ApsaraDB RDS

阿里雲支援通過oss_fdw外掛程式將OSS中的資料載入到RDS PostgreSQL資料庫中，也支援將RDS PostgreSQL資料庫中的資料寫入OSS中。

前提條件

RDS PostgreSQL執行個體大版本為10或以上。

說明

如果執行個體版本為PostgreSQL 14，需要核心小版本大於等於20220830。如需升級核心小版本，請參見升級核心小版本。

oss_fdw用例

建立oss_fdw外掛程式。詳細參數請參見oss_fdw參數。
```
CREATE EXTENSION oss_fdw; 
```

建立SERVER。詳細參數請參見CREATE SERVER參數。

CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS 
     (host 'oss-cn-hangzhou-internal.aliyuncs.com' , id 'access_id', key 'secrect_key',bucket 'mybucket');

建立OSS外部表格。詳細參數請參見CREATE FOREIGN TABLE參數。

CREATE FOREIGN TABLE ossexample 
    (date text, time text, open float,
     high float, low float, volume int) 
     SERVER ossserver 
     OPTIONS ( dir 'osstest/', delimiter ',' ,
         format 'csv', encoding 'utf8');

重要

建表語句中的表結構僅為樣本，實際情況下，外部表格的結構須與OSS檔案對應的表結構保持一致。

讀、寫OSS中的資料。

讀取OSS中的資料。
```
SELECT * FROM ossexample;
```

將OSS中的資料插入到RDS PostgreSQL執行個體中。

建立與外部表格ossexample結構相同的表example。

CREATE TABLE example
        (date text, time text, open float,
         high float, low float, volume int);

將OSS中的資料插入到表example中。

INSERT INTO example SELECT * FROM ossexample;

您可以通過EXPLAIN估算OSS上的檔案大小，正確的規劃查詢計劃。

EXPLAIN INSERT INTO example SELECT * FROM ossexample;
                              QUERY PLAN
----------------------------------------------------------------------
 Insert on example  (cost=0.00..1.10 rows=0 width=0)
   ->  Foreign Scan on ossexample  (cost=0.00..1.10 rows=1 width=998)
         Foreign OssDir: osstest/
         Number Of Ossfile: 2

您也可以將example表中的資料寫入到OSS中。
```
INSERT INTO ossexample SELECT * FROM example;
```

更多oss_fdw相關參數說明請參見下文。

oss_fdw參數

oss_fdw和其他fdw介面一樣，對外部資料OSS中的資料進行封裝。使用者可以像使用資料表一樣通過oss_fdw讀取OSS中存放的資料。oss_fdw提供相應參數用於串連和解析OSS上的檔案資料。

說明

目前oss_fdw支援讀取和寫入OSS中檔案的格式為：csv或者gzip格式的csv檔案。

CREATE SERVER參數

參數	說明
host	內網訪問OSS的Endpoint（地區節點）。
id	帳號的AccessKey ID，具體請參見建立AccessKey。
key	帳號的AccessKey Secret，具體請參見建立AccessKey。
bucket	OSS的bucket名稱，需要先建立OSS帳號再設定該參數。

針對匯入模式和匯出模式，提供下列容錯相關參數。網路條件較差時，可以調整以下參數，以保障匯入和匯出成功。

參數	說明
oss_connect_timeout	設定連線逾時，單位秒，預設是10秒。
oss_dns_cache_timeout	設定DNS逾時，單位秒，預設是60秒。
oss_speed_limit	設定能容忍的最小速率，預設是1024，即1K。
oss_speed_time	設定能容忍最小速率的最長時間，預設是15秒。

說明

如果使用了oss_speed_limit和oss_speed_time的預設值，表示如果連續15秒的傳輸速率小於1K，則逾時。

CREATE FOREIGN TABLE參數

參數	說明
filepath	OSS中帶路徑的檔案名稱。與dir任選一個即可，配置為此參數時，只支援從OSS將資料匯入到RDS PostgreSQL中。檔案名稱包含檔案路徑，但不包含bucket。該參數匹配OSS對應路徑上的多個檔案，支援將多個檔案載入到資料庫。檔案命名為filepath和filepath.x 支援被匯入到資料庫，x要求從1開始，且連續。例如，filepath、filepath.1、filepath.2、filepath.3、filepath.5 ，前4個檔案會被匹配和匯入，但是 filepath.5將無法匯入。
dir	OSS中的虛擬檔案目錄。與filepath任選一個即可，配置為此參數時，支援從OSS與RDS PostgreSQL互相匯入匯出資料。 dir需要以（/）結尾。 dir指定的虛擬檔案目錄中的所有檔案（不包含子檔案夾和子檔案夾下的檔案）都會被匹配和匯入到資料庫。
prefix	指定資料檔案對應路徑名的首碼，不支援Regex，且與 filepath、dir 互斥，三者只能設定其中一個。
format	指定檔案的格式，目前只支援csv。
encoding	檔案中資料的編碼格式，支援常見的pg編碼，如utf8。
parse_errors	讀取OSS中資料時的容錯模式解析，以行為單位，忽略檔案分析過程中發生的錯誤。重要本參數不支援RDS PostgreSQL向OSS寫入資料的情境。如果您需要將RDS PostgreSQL的資料寫入到OSS中，請不要配置該參數。
delimiter	指定列的分割符。
quote	指定檔案的引用字元。
escape	指定檔案的逃逸字元。
null	指定匹配對應字串的列為null，例如null ‘test’，即列值為’test’的字串為null。
force_not_null	指定某些列的值不為null。例如，`force_not_null ‘id’`表示：如果ID列的值為空白，則該值為空白字串，而不是null。
compressiontype	設定讀取和寫入OSS上檔案的格式： none：預設的檔案類型，即沒有壓縮的文字格式設定。 gzip：讀取檔案的格式為gzip壓縮格式。
compressionlevel	設定寫入OSS的壓縮格式的壓縮等級，範圍1到9，預設為6。

說明

filepath和dir需要在OPTIONS參數中指定。
filepath和dir必須指定兩個參數中的其中一個，且不能同時指定。
匯出模式目前只支援虛擬資料夾的匹配模式，即只支援dir，不支援filepath。

CREATE FOREIGN TABLE的匯出模式參數

oss_flush_block_size：單次刷出到OSS的buffer大小，預設32MB，可選範圍1到128MB。
oss_file_max_size：寫入OSS的最大檔案大小，超出之後會切換到另一個檔案續寫。預設1024MB，可選範圍8到4000 MB。
num_parallel_worker：寫OSS資料的壓縮模式中並行壓縮線程的個數，範圍1到8，預設並發數3。

輔助函數

FUNCTION oss_fdw_list_file (relname text, schema text DEFAULT 'public')

用於獲得某個外部表格所匹配的OSS上的檔案名稱和檔案的大小。
檔案大小的單位是位元組。

SELECT * FROM oss_fdw_list_file('ossexample');
              name              |   size    
--------------------------------+-----------
 osstest/test.gz.1  | 739698350
 osstest/test.gz.2  | 739413041
 osstest/test.gz.3  | 739562048
(3 rows)

協助工具功能

oss_fdw.rds_read_one_file：在讀模式下，指定某個外表匹配的檔案。設定後，該外部表格在資料匯入中只匹配這個被設定的檔案。

SET oss_fdw.rds_read_one_file = 'osstest/test.gz.2';
SELECT * FROM oss_fdw_list_file('ossexample');
              name              |   size    
--------------------------------+-----------
  oss_test/test.gz.2  | 739413041
(1 rows)

oss_fdw注意事項

oss_fdw是在PostgreSQL FOREIGN TABLE架構下開發的外部表格外掛程式。
資料匯入的效能和PostgreSQL叢集的資源（CPU、IO、MEM）相關，也和OSS相關。
為保證資料匯入的效能，請確保ApsaraDB for PostgreSQL與OSS所在Region相同，相關資訊請參見OSS endpoint 資訊。
如果讀取外表的SQL時觸發ERROR: oss endpoint userendpoint not in aliyun white list，建議使用阿里雲各可用性區域公用endpoint，詳情請參見訪問網域名稱和資料中心。

錯誤處理

匯入或匯出出錯時，日誌中會出現下列錯誤提示資訊：

code：出錯請求的HTTP狀態代碼。
error_code：OSS的錯誤碼。
error_msg：OSS的錯誤資訊。
req_id：標識該次請求的UUID。當您無法解決問題時，可以憑req_id來請求OSS開發工程師的協助。

請參見以下連結中的文檔瞭解和處理各類錯誤，逾時相關的錯誤可以使用oss_ext相關參數處理。

ID和Key隱藏

CREATE SERVER中的ID和Key資訊如果不做任何處理，使用者可以使用select * from pg_foreign_server看到明文資訊，會暴露使用者的ID和Key。我們通過對ID和Key進行對稱式加密實現對ID和Key的隱藏（不同的執行個體使用不同的密鑰，最大限度保護使用者資訊），但無法使用類似GP一樣的方法，增加一個資料類型，會導致老執行個體不相容。

最終加密後的資訊如下：

postgres=# SELECT * FROM pg_foreign_server ;
  srvname  | srvowner | srvfdw | srvtype | srvversion | srvacl |                                                                              srvoptions
-----------+----------+--------+---------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------
----------------------------------
 ossserver |       10 |  16390 |         |            |        | {host=oss-cn-hangzhou-zmf.aliyuncs.com，id=MD5****，key=MD5****，bucket=067862}

加密後的資訊將會以MD5開頭（總長度為len，len%8==3），這樣匯出之後再匯入不會再次加密，但是使用者不能建立MD5開頭的Key和ID。