OSS 裝載資料到 PostgreSQL

oss_fdw

在阿里雲上，支援通過 oss_fdw 並行裝載資料到 PostgreSQL 和 PPAS 中

oss_fdw 參數

oss_fdw 和其他 fdw 的介面一樣，提供對外部資料源 oss 的資料封裝，使用者可以使用 oss_fdw 像一張表一樣讀取 oss 上的存放的檔案。
和其他 fdw 一樣，oss_fdw 提供專屬的數個參數用於串連和解析 oss 上的檔案資料。

和 oss 相關參數有

1. ossendpoint 參數,是內網訪問oss的地址，也叫 host

2. id oss 帳號 id

3. key oss 帳號 key

4. bucket ossbucket,需要建立 oss 帳號後分配

5. filepath oss 中帶路徑的檔案名稱
  5.1 檔案名稱包含檔案路徑，但不包含 bucket
  5.2 該參數匹配 oss 對應路徑上的多個檔案，支援將他們裝載到資料庫
  5.3 檔案命名為 filepath 和 filepath.x 支援被匯入到資料庫,x 要求從 1 開始，且是連續的
  5.4 例 filepath filepath.1 filepath.2 filepath.3 filepath.5 前4個檔案會被匹配和匯入，但是 filepath.5 不會。

6. dir oss 中的虛擬檔案目錄
    6.1 dir 需要以 / 結尾
    6.2 dir 制定的虛擬檔案目錄中的所有檔案（不包含子檔案夾和子檔案夾下的檔案）都會被匹配和匯入到資料庫。

需要注意

1. 前4個參數 ossendpoint id key bucket 放在server對象中
2. filepath 和 dir 需要在 FDW 的 OPTIONS 參數參數中指定
3. filepath 和 dir 必須指定兩個參數之一，且不能同時指定
4. 各參數的值使用‘’引起來，不能包括無用的空格

其他參數

1. format
    指定檔案的格式,目前只支援 csv

2. encoding
    檔案中資料的編碼格式，支援常見 pg 編碼，如 utf8

3. parse_errors
    容錯模式解析，按照行為單位，忽略檔案分析過程中發生的錯誤

4. delimiter
   制定列的分割符

5. quote
   指定檔案的引用字元

6. escape
    指定檔案的逃逸字元

7. null
    指定匹配對應字串的列為 null
    例如 null 'test',即列值為 ‘test’ 的字串為 null

8. force_not_null
    制定一列為多列的值不是 null
    例 force_not_null ‘id’,即表中 id 列如果是 null,替換成Null 字元串

用例

# 建立外掛程式
create extension oss_fdw;

# 建立 server
CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS
     (host 'oss-cn-hangzhou-zmf.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)

oss_fdw 使用注意
oss_fdw 開啟了 oss 到 PostgreSQL 和 PPAS 的資料通道，使用者可以把資料放到廉價的oss中，再匯入到 PostgreSQL 或 PPAS 中。

1. oss_fdw 是在 PostgreSQL FOREIGN TABLE 架構下開發的外部表格外掛程式。
2. 資料匯入的效能和 PostgreSQL 叢集的資源（CPU IO MEM MET）相關，也和 OSS 相關。
3. 為了保證資料匯入的效能 ossprotocol 中 ossendpoint 的需要匹配 PostgreSQL 雲上所在 Region。相關資訊請參考下面的連結。

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=MD5xxxxxxxx,key=MD5xxxxxxxx,bucket=067862}

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