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}