使用 oss_fdw 读写外部数据文本文件_问答-阿里云开发者社区

在阿里云上，支持通过 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，替换成空字符串。
compressiontype
设置读取在 oss 上的文件是否压缩和压缩的格式：none ：默认的文件类型，即不压缩的文本格式
gzip ：读取的文件为 gzip 压缩格式

需要注意，各参数的值使用’’引起来，不能包括无用的空格。
注意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用例

[backcolor=transparent]# 创建插件
[backcolor=transparent]create extension oss_fdw[backcolor=transparent];
[backcolor=transparent]# 创建 server
[backcolor=transparent]CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS
[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]host [backcolor=transparent]'oss-cn-hangzhou.aliyuncs.com'[backcolor=transparent] [backcolor=transparent]，[backcolor=transparent] id [backcolor=transparent]'xxx'[backcolor=transparent]，[backcolor=transparent] key [backcolor=transparent]'xxx'[backcolor=transparent]，[backcolor=transparent]bucket [backcolor=transparent]'mybucket'[backcolor=transparent]);
[backcolor=transparent]# 创建 oss 外部表
[backcolor=transparent]CREATE FOREIGN TABLE ossexample
[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]date text[backcolor=transparent]，[backcolor=transparent] time text[backcolor=transparent]，[backcolor=transparent] open [backcolor=transparent]float[backcolor=transparent]，
[backcolor=transparent] high [backcolor=transparent]float[backcolor=transparent]，[backcolor=transparent] low [backcolor=transparent]float[backcolor=transparent]，[backcolor=transparent] volume [backcolor=transparent]int[backcolor=transparent])[backcolor=transparent]
[backcolor=transparent] SERVER ossserver
[backcolor=transparent] OPTIONS [backcolor=transparent]([backcolor=transparent] filepath [backcolor=transparent]'osstest/example.csv'[backcolor=transparent]，[backcolor=transparent] delimiter [backcolor=transparent]'，'[backcolor=transparent] [backcolor=transparent]，
[backcolor=transparent] format [backcolor=transparent]'csv'[backcolor=transparent]，[backcolor=transparent] encoding [backcolor=transparent]'utf8'[backcolor=transparent]，[backcolor=transparent] PARSE_ERRORS [backcolor=transparent]'100'[backcolor=transparent]);
[backcolor=transparent]# 创建表，数据就装载到这张表中
[backcolor=transparent]create table example
[backcolor=transparent] [backcolor=transparent]([backcolor=transparent]date text[backcolor=transparent]，[backcolor=transparent] time text[backcolor=transparent]，[backcolor=transparent] open [backcolor=transparent]float[backcolor=transparent]，
[backcolor=transparent] high [backcolor=transparent]float[backcolor=transparent]，[backcolor=transparent] low [backcolor=transparent]float[backcolor=transparent]，[backcolor=transparent] volume [backcolor=transparent]int[backcolor=transparent]);
[backcolor=transparent]# 数据从 ossexample 装载到 example 中。
[backcolor=transparent]insert [backcolor=transparent]into[backcolor=transparent] example [backcolor=transparent]select[backcolor=transparent] [backcolor=transparent]*[backcolor=transparent] [backcolor=transparent]from[backcolor=transparent] ossexample[backcolor=transparent];
[backcolor=transparent]# 可以看到
[backcolor=transparent]# oss_fdw 能够正确估计 oss 上的文件大小，正确的规划查询计划。
[backcolor=transparent]explain insert [backcolor=transparent]into[backcolor=transparent] example [backcolor=transparent]select[backcolor=transparent] [backcolor=transparent]*[backcolor=transparent] [backcolor=transparent]from[backcolor=transparent] ossexample[backcolor=transparent];
[backcolor=transparent] QUERY PLAN
[backcolor=transparent]---------------------------------------------------------------------
[backcolor=transparent] [backcolor=transparent]Insert[backcolor=transparent] on example [backcolor=transparent]([backcolor=transparent]cost[backcolor=transparent]=[backcolor=transparent]0.00[backcolor=transparent]..[backcolor=transparent]1.60[backcolor=transparent] rows[backcolor=transparent]=[backcolor=transparent]6[backcolor=transparent] width[backcolor=transparent]=[backcolor=transparent]92[backcolor=transparent])
[backcolor=transparent] [backcolor=transparent]->[backcolor=transparent] [backcolor=transparent]Foreign[backcolor=transparent] [backcolor=transparent]Scan[backcolor=transparent] on ossexample [backcolor=transparent]([backcolor=transparent]cost[backcolor=transparent]=[backcolor=transparent]0.00[backcolor=transparent]..[backcolor=transparent]1.60[backcolor=transparent] rows[backcolor=transparent]=[backcolor=transparent]6[backcolor=transparent] width[backcolor=transparent]=[backcolor=transparent]92[backcolor=transparent])
[backcolor=transparent] [backcolor=transparent]Foreign[backcolor=transparent] [backcolor=transparent]OssFile[backcolor=transparent]:[backcolor=transparent] osstest[backcolor=transparent]/[backcolor=transparent]example[backcolor=transparent].[backcolor=transparent]csv[backcolor=transparent].[backcolor=transparent]0
[backcolor=transparent] [backcolor=transparent]Foreign[backcolor=transparent] [backcolor=transparent]OssFile[backcolor=transparent] [backcolor=transparent]Size[backcolor=transparent]:[backcolor=transparent] [backcolor=transparent]728
[backcolor=transparent]([backcolor=transparent]4[backcolor=transparent] rows[backcolor=transparent])
[backcolor=transparent]# 表 example 中的数据写出到 OSS 中。
[backcolor=transparent]insert [backcolor=transparent]into[backcolor=transparent] ossexample [backcolor=transparent]select[backcolor=transparent] [backcolor=transparent]*[backcolor=transparent] [backcolor=transparent]from[backcolor=transparent] example[backcolor=transparent];
[backcolor=transparent]explain insert [backcolor=transparent]into[backcolor=transparent] ossexample [backcolor=transparent]select[backcolor=transparent] [backcolor=transparent]*[backcolor=transparent] [backcolor=transparent]from[backcolor=transparent] example[backcolor=transparent];
[backcolor=transparent] QUERY PLAN
[backcolor=transparent]-----------------------------------------------------------------
[backcolor=transparent] [backcolor=transparent]Insert[backcolor=transparent] on ossexample [backcolor=transparent]([backcolor=transparent]cost[backcolor=transparent]=[backcolor=transparent]0.00[backcolor=transparent]..[backcolor=transparent]16.60[backcolor=transparent] rows[backcolor=transparent]=[backcolor=transparent]660[backcolor=transparent] width[backcolor=transparent]=[backcolor=transparent]92[backcolor=transparent])
[backcolor=transparent] [backcolor=transparent]->[backcolor=transparent] [backcolor=transparent]Seq[backcolor=transparent] [backcolor=transparent]Scan[backcolor=transparent] on example [backcolor=transparent]([backcolor=transparent]cost[backcolor=transparent]=[backcolor=transparent]0.00[backcolor=transparent]..[backcolor=transparent]16.60[backcolor=transparent] rows[backcolor=transparent]=[backcolor=transparent]660[backcolor=transparent] width[backcolor=transparent]=[backcolor=transparent]92[backcolor=transparent])
[backcolor=transparent]([backcolor=transparent]2[backcolor=transparent] rows[backcolor=transparent])

oss_fdw 使用注意

oss_fdw 是在 PostgreSQL FOREIGN TABLE 框架下开发的外部表插件。
数据导入的性能和 PostgreSQL 集群的资源（CPU IO MEM MET）相关，也和 OSS 相关。
为了保证数据导入的性能 ossprotocol 中 ossendpoint 的需要匹配 PostgreSQL 云上所在 Region。相关信息请参考下面的链接。

错误处理

当导入或导出操作出错时，错误日志会出现下列信息：

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那样，增加一个数据类型，因为会不兼容老实例。
最终的加密后的信息如下：

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