全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网
云数据库 RDS 版

使用 oss_fdw 读写外部数据文本文件

更新时间:2017-06-12 14:50:00

在阿里云上,支持通过 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用例

  1. # 创建插件
  2. create extension oss_fdw;
  3. # 创建 server
  4. CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS
  5. (host 'oss-cn-hangzhou.aliyuncs.com' id 'xxx' key 'xxx'bucket 'mybucket');
  6. # 创建 oss 外部表
  7. CREATE FOREIGN TABLE ossexample
  8. (date text time text open float
  9. high float low float volume int)
  10. SERVER ossserver
  11. OPTIONS ( filepath 'osstest/example.csv' delimiter ','
  12. format 'csv' encoding 'utf8' PARSE_ERRORS '100');
  13. # 创建表,数据就装载到这张表中
  14. create table example
  15. (date text time text open float
  16. high float low float volume int);
  17. # 数据从 ossexample 装载到 example 中。
  18. insert into example select * from ossexample;
  19. # 可以看到
  20. # oss_fdw 能够正确估计 oss 上的文件大小,正确的规划查询计划。
  21. explain insert into example select * from ossexample;
  22. QUERY PLAN
  23. ---------------------------------------------------------------------
  24. Insert on example (cost=0.00..1.60 rows=6 width=92)
  25. -> Foreign Scan on ossexample (cost=0.00..1.60 rows=6 width=92)
  26. Foreign OssFile: osstest/example.csv.0
  27. Foreign OssFile Size: 728
  28. (4 rows)
  29. # 表 example 中的数据写出到 OSS 中。
  30. insert into ossexample select * from example;
  31. explain insert into ossexample select * from example;
  32. QUERY PLAN
  33. -----------------------------------------------------------------
  34. Insert on ossexample (cost=0.00..16.60 rows=660 width=92)
  35. -> Seq Scan on example (cost=0.00..16.60 rows=660 width=92)
  36. (2 rows)

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那样,增加一个数据类型,因为会不兼容老实例。

最终的加密后的信息如下:

  1. postgres=# select * from pg_foreign_server ;
  2. srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions
  3. -----------+----------+--------+---------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------
  4. ----------------------------------
  5. ossserver | 10 | 16390 | | | | {host=oss-cn-hangzhou-zmf.aliyuncs.comid=MD5xxxxxxxxkey=MD5xxxxxxxxbucket=067862}

加密后的信息将会以MD5开头(总长度为len%8==3),这样导出之后再导入不会再次加密,但是用户不能创建MD5开头的key和id。

参考链接

本文导读目录