147      阿裏雲

使用 oss_fdw 讀寫外部數據文本文件__快速入門（PostgreSQL）_雲數據庫 RDS 版-阿裏雲

在阿裏雲上，支持通過 oss_fdw 插件在 PostgreSQL 和 PPAS 中裝載 oss 上的數據到數據庫，同時也支持把數據庫中的數據寫到 oss 上。

oss_fdw 參數

oss_fdw 和其他 fdw 的接口一樣，提供對外部數據源 oss 的數據封裝，用戶可像使用數據表一樣通過 oss_fdw 讀取 oss 上存放的文件。和其他 fdw 一樣，oss_fdw 提供獨有的參數用於連接和解析 oss 上的文件數據。

CREATE SERVER 主要參數

• ossendpoint 參數，是內網訪問oss的地址，也叫 host
• id oss 賬號 id
• key oss 賬號 key
• bucket ossbucket，需要創建 oss 賬號後分配

需要注意，各參數的值使用’’引起來，不能包括無用的空格。

CREATE SERVER 輔助參數

• filepath oss 中帶路徑的文件名

  • 文件名包含文件路徑，但不包含 bucket。

  • 該參數匹配 oss 對應路徑上的多個文件，支持將他們裝載到數據庫。

  • 文件命名為 filepath 和 filepath.x 支持被導入到數據庫，x 要求從 1 開始，且是連續的。

    例 filepath filepath.1 filepath.2 filepath.3 filepath.5 前4個文件會被匹配和導入，但是 filepath.5 不會。

• dir oss 中的虛擬文件目錄

  • dir 需要以 / 結尾。

  • dir 製定的虛擬文件目錄中的所有文件（不包含子文件夾和子文件夾下的文件）都會被匹配和導入到數據庫。

• format

  指定文件的格式，目前隻支持 csv。

• encoding

  文件中數據的編碼格式，支持常見 pg 編碼，如 utf8。

• parse_errors

  容錯模式解析，按照行為單位，忽略文件分析過程中發生的錯誤。

• delimiter

  製定列的分割符。

• quote

  指定文件的引用字符。

• escape

  指定文件的逃逸字符。

• null

  指定匹配對應字符串的列為 null，例如 null ‘test’，即列值為 ‘test’ 的字符串為 null。

• force_not_null

  製定一列為多列的值不是 null，例如 force_not_null ‘id’，即表中 id 列如果是 null，替換成空字符串。

需要注意，各參數的值使用’’引起來，不能包括無用的空格。

• 注意1：filepath 和 dir 需要在 OPTIONS 參數參數中指定。
• 注意2：filepath 和 dir 必須指定兩個參數之一，且不能同時指定。
• 注意3：針對導出模式，需要注意，導出模式目前隻支持虛擬文件夾的匹配模式，即隻支持 dir，不支持 filepath。

CREATE FOREIGN TABLE 的導出模式參數

針對導出模式，新增下列兩個參數 oss_flush_block_size 和 oss_flush_block_size。

• oss_flush_block_size

  單詞刷出到 oss 的buffer大小，默認 32 MB，可選範圍 1 到 128 MB。

• oss_file_max_size

  寫到 oss 的最大文件大小，超出之後會切換到另一個文件再寫。默認 1024 MB，可選範圍 8 到 4000 MB。

注意：上述兩個參數對導入模式無效。

其它CREATE FOREIGN TABLE 的通用參數

針對導入模式和導出模式，還有下列容錯相關參數：

• oss_connect_timeout 設置鏈接超時，單位秒，默認是10秒
• oss_dns_cache_timeout 設置DNS超時，單位秒，默認是60秒
• oss_speed_limit 控製能容忍的最小速率，默認是1024，即1K
• oss_speed_time 控製能容忍的最長時間，默認是15秒

如果使用了上述默認值，表示如果連續15秒的傳輸速率小於1K，則超時詳細描述，請客參考文章末尾的鏈接，這4個參數需要在 server 對象中指定。

oss_fdw用例

# 創建插件
create extension oss_fdw;
# 創建 server 
CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS 
     (host 'oss-cn-hangzhou.aliyuncs.com' ， id 'xxx'， key 'xxx'，bucket 'mybucket');
# 創建 oss 外部表
CREATE FOREIGN TABLE ossexample 
    (date text， time text， open float，
     high float， low float， volume int) 
     SERVER ossserver 
     OPTIONS ( filepath 'osstest/example.csv'， delimiter '，' ，
         format 'csv'， encoding 'utf8'， PARSE_ERRORS '100');
# 創建表，數據就裝載到這張表中
create table example
        (date text， time text， open float，
         high float， low float， volume int);
# 數據從 ossexample 裝載到 example 中。
insert into example select * from ossexample;
# 可以看到
# oss_fdw 能夠正確估計 oss 上的文件大小，正確的規劃查詢計劃。
explain insert into example select * from ossexample;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Insert on example  (cost=0.00..1.60 rows=6 width=92)
   ->  Foreign Scan on ossexample  (cost=0.00..1.60 rows=6 width=92)
         Foreign OssFile: osstest/example.csv.0
         Foreign OssFile Size: 728
(4 rows)
# 表 example 中的數據寫出到 OSS 中。
insert into ossexample select * from example;
explain insert into ossexample select * from example;
                           QUERY PLAN
-----------------------------------------------------------------
 Insert on ossexample  (cost=0.00..16.60 rows=660 width=92)
   ->  Seq Scan on example  (cost=0.00..16.60 rows=660 width=92)
(2 rows)

oss_fdw 使用注意

1. oss_fdw 是在 PostgreSQL FOREIGN TABLE 框架下開發的外部表插件。
2. 數據導入的性能和 PostgreSQL 集群的資源（CPU IO MEM MET）相關，也和 OSS 相關。
3. 為了保證數據導入的性能 ossprotocol 中 ossendpoint 的需要匹配 PostgreSQL 雲上所在 Region。相關信息請參考下麵的鏈接。

錯誤處理

當導入或導出操作出錯時，錯誤日誌會出現下列信息：

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