全部产品
弹性计算 会员服务 网络 安全 移动云 数加·大数据分析及展现 数加·大数据应用 管理与监控 云通信 阿里云办公 培训与认证 更多
存储与CDN 数据库 域名与网站(万网) 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网 智能硬件
云数据库 RDS 版

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

更新时间:2018-04-27 14:05:51

阿里云支持通过oss_fdw插件将oss中的数据加载到PostgreSQL和PPAS数据库中,也支持将PostgreSQL和PPAS数据库中的数据写入OSS中。

oss_fdw 参数

oss_fdw和其他fdw接口一样,对外部数据OSS中的数据进行封装。用户可以像使用数据表一样通过oss_fdw读取OSS中存放的数据。oss_fdw提供独有的参数用于连接和解析OSS上的文件数据。

注意:

  • 目前oss_fdw支持读取和写入OSS中文件的格式为:text/csv、gzip格式的text/csv文件。
  • oss_fdw各参数的值需使用’’引起来,且不含无用空格。

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压缩格式。
  • compressionlevel:设置写入OSS的压缩格式的压缩等级,范围1到9,默认6。

注意:

  • filepath和dir需要在OPTIONS参数中指定。
  • filepath和dir必须指定两个参数中的其中一个,且不能同时指定。
  • 导出模式目前只支持虚拟文件夹的匹配模式,即只支持dir,不支持filepath。

CREATE FOREIGN TABLE 的导出模式参数

针对导出模式,新增下列两个参数oss_flush_block_size和oss_flush_block_size。

  • oss_flush_block_size:单次刷出到OSS的buffer大小,默认32MB,可选范围1到128MB。

  • oss_file_max_size:写入OSS的最大文件大小,超出之后会切换到另一个文件续写。默认1024MB,可选范围8到4000 MB。

  • num_parallel_worker:写OSS数据的压缩模式中并行压缩线程的个数,范围1到8,默认并发数3。

注意:oss_flush_block_size和oss_flush_block_size两个参数对导入模式无效。

其它CREATE FOREIGN TABLE 的通用参数

针对导入模式和导出模式,提供下列容错相关参数,且这4个参数需要在server对象中指定:

  • oss_connect_timeout:设置链接超时,单位秒,默认是10秒。

  • oss_dns_cache_timeout:设置DNS超时,单位秒,默认是60秒。

  • oss_speed_limit:控制能容忍的最小速率,默认是1024,即1K。

  • oss_speed_time:控制能容忍的最长时间,默认是15秒。

如果使用了上述参数的默认值,表示如果连续15秒的传输速率小于1K,则超时。

辅助函数

FUNCTION oss_fdw_list_file (relname text, schema text DEFAULT ‘public’)

  • 用于获得某个外部表所匹配的OSS上的文件名和文件的大小。

  • 文件大小的单位是字节。

  1. select * from oss_fdw_list_file('t_oss');
  2. name | size
  3. --------------------------------+-----------
  4. oss_test/test.gz.1 | 739698350
  5. oss_test/test.gz.2 | 739413041
  6. oss_test/test.gz.3 | 739562048
  7. (3 rows)

辅助参数

oss_fdw.rds_read_one_file:在读模式下,指定某个外表匹配的文件。设置后,该外部表在数据导入中只匹配被设置的一个文件。

例如,set oss_fdw.rds_read_one_file = ‘oss_test/example16.csv.1’;

  1. set oss_fdw.rds_read_one_file = 'oss_test/test.gz.2';
  2. select * from oss_fdw_list_file('t_oss');
  3. name | size
  4. --------------------------------+-----------
  5. oss_test/test.gz.2 | 739413041
  6. (1 rows)

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相关。

  • 为保证数据导入的性能,请确保云数据库PostgreSQL与OSS所在Region相同,相关信息请参考OSS endpiint 信息

错误处理

导入或导出出错时,日志中会出现下列错误提示信息:

  • 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。

本文导读目录