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,替换成空字符串

用例

# 创建插件
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 )
   ->  Foreign Scan on ossexample  (cost=0.00..1.60 rows=6 )
         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