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

使用 OSS 外部表同步数据

更新时间:2018-07-17 15:09:14

云数据库 HybridDB for PostgreSQL 支持通过 OSS 外部表(即 gpossext 功能),将数据并行从 OSS 导入或导出到 OSS,并支持通过 gzip 进行 OSS 外部表文件压缩,大量节省存储空间及成本。

目前的 gpossext 支持读写text/csv格式的文件或者gzip 压缩格式的 text/csv 文件。

本文内容包括:

操作说明

通过 HybridDB for PostgreSQL 使用 OSS 外部表,主要涉及以下操作。

创建 OSS 外部表插件(oss_ext)

使用 OSS 外部表时,需要在 HybridDB for PostgreSQL 中先创建 OSS 外部表插件(每个数据库需要单独创建)。

  • 创建命令为:CREATE EXTENSION IF NOT EXISTS oss_ext;
  • 删除命令为:DROP EXTENSION IF EXISTS oss_ext;

并行导入数据

导入数据时,请执行如下步骤:

  1. 将数据均匀分散存储在多个 OSS 文件中,文件的数目最好为 HybridDB for PostgreSQL 数据节点数(Segment 个数)的整数倍。

  2. 在 HybridDB for PostgreSQL 中,创建 READABLE 外部表。

  3. 执行如下操作,并行导入数据。

    INSERT INTO <目标表> SELECT * FROM <外部表>

并行导出数据

导出数据时,请执行如下步骤:

  1. 在 HybridDB for PostgreSQL 中,创建 WRITABLE 外部表。

  2. 执行如下操作,并行把数据导出到 OSS 中。

    INSERT INTO <外部表> SELECT * FROM <源表>

创建 OSS 外部表语法

创建 OSS 外部表语法,请执行如下命令:

  1. CREATE [READABLE] EXTERNAL TABLE tablename
  2. ( columnname datatype [, ...] | LIKE othertable )
  3. LOCATION ('ossprotocol')
  4. FORMAT 'TEXT'
  5. [( [HEADER]
  6. [DELIMITER [AS] 'delimiter' | 'OFF']
  7. [NULL [AS] 'null string']
  8. [ESCAPE [AS] 'escape' | 'OFF']
  9. [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
  10. [FILL MISSING FIELDS] )]
  11. | 'CSV'
  12. [( [HEADER]
  13. [QUOTE [AS] 'quote']
  14. [DELIMITER [AS] 'delimiter']
  15. [NULL [AS] 'null string']
  16. [FORCE NOT NULL column [, ...]]
  17. [ESCAPE [AS] 'escape']
  18. [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
  19. [FILL MISSING FIELDS] )]
  20. [ ENCODING 'encoding' ]
  21. [ [LOG ERRORS [INTO error_table]] SEGMENT REJECT LIMIT count
  22. [ROWS | PERCENT] ]
  23. CREATE WRITABLE EXTERNAL TABLE table_name
  24. ( column_name data_type [, ...] | LIKE other_table )
  25. LOCATION ('ossprotocol')
  26. FORMAT 'TEXT'
  27. [( [DELIMITER [AS] 'delimiter']
  28. [NULL [AS] 'null string']
  29. [ESCAPE [AS] 'escape' | 'OFF'] )]
  30. | 'CSV'
  31. [([QUOTE [AS] 'quote']
  32. [DELIMITER [AS] 'delimiter']
  33. [NULL [AS] 'null string']
  34. [FORCE QUOTE column [, ...]] ]
  35. [ESCAPE [AS] 'escape'] )]
  36. [ ENCODING 'encoding' ]
  37. [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
  38. ossprotocol:
  39. oss://oss_endpoint prefix=prefix_name
  40. id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]
  41. ossprotocol:
  42. oss://oss_endpoint dir=[folder/[folder/]...]/file_name
  43. id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]
  44. ossprotocol:
  45. oss://oss_endpoint filepath=[folder/[folder/]...]/file_name
  46. id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]

参数释义

该部分介绍各操作中用到的参数定义,涉及到参数包括:

常用参数

  • 协议和 endpoint:格式为“协议名://oss_endpoint”,其中协议名为 oss,oss_endpoint 为 OSS 对应区域的域名。

    注意:如果是从阿里云的主机访问数据库,应该使用内网域名(即带有“internal”的域名),避免产生公网流量。

  • id:OSS 账号的 ID。

  • key:OSS 账号的 key。

  • bucket:指定数据文件所在的 bucket,需要通过 OSS 预先创建。

  • prefix:指定数据文件对应路径名的前缀,不支持正则表达式,仅是匹配前缀,且与 filepath、dir 互斥,三者只能设置其中一个。

    • 如果创建的是用于数据导入的 READABLE 外部表,则在导入时含有这一前缀的所有 OSS 文件都会被导入。

      • 如果指定 prefix=test/filename,以下文件都会被导入:
        • test/filename
        • test/filenamexxx
        • test/filename/aa
        • test/filenameyyy/aa
        • test/filenameyyy/bb/aa
      • 如果指定 prefix=test/filename/,只有以下文件会被导入(上面列的其他文件不会被导入):
        • test/filename/aa
    • 如果创建的是用于数据导出的 WRITABLE 外部表,在导出数据时,将根据该前缀自动生成一个唯一的文件名来给导出文件命名。

      注意:导出文件将不止有一个,每个数据节点都会导出一个或多个文件。导出文件名格式为 prefix_tablename_uuid.x,其中 uuid 是生成的 int64 整型值(精度为微秒的时间戳),x 为节点 ID。支持使用同一外部表多次导出,每次导出的文件将通过 uuid 区分,而同一次导出的文件 uuid 相同。

  • dir:OSS 中的虚拟文件夹路径,与 prefix、filepath 互斥,三者只能设置其中一个。

    • 文件夹路径需要以“/”结尾,如test/mydir/
    • 在导入数据时,使用此参数创建外部表,会导入指定虚拟目录下的所有文件,但不包括它子目录和子目录下的文件。与 filepath 不同,dir 下的文件没有命名要求。
    • 在导出数据时,使用此参数创建外部表,所有数据会导出到此目录下的多个文件中,输出文件名的形式为filename.x,x 为数字,但可能不是连续的。
  • filepath:OSS 中包含路径的文件名称,与 prefix、dir 互斥,三者只能设置其中一个,并且这个参数只能在创建 READABLE 外部表时指定(即只支持在导入数据时使用)。

    • 该文件名称包含该路径,但不包含 bucket 名。
    • 在导入数据时,文件命名方式必须为 filenamefilename.x,x 要求从 1 开始,且是连续的。例如,如果指定 filepath = filename,而 OSS 中含有如下文件:
      1. filename
      2. filename.1
      3. filename.2
      4. filename.4
      则将被导入的文件有 filename、filename.1 和 filename.2。而因为 filename.3 不存在,所以 filename.4 不会被导入。

导入模式参数

  • async:是否启用异步模式导入数据。

    • 开启辅助线程从 OSS 导入数据,加速导入性能。

    • 默认情况下异步模式是打开的,如果需要关掉,可以使用参数 async = false 或 async = f。

    • 异步模式和普通模式比,会消耗更多的硬件资源。

  • compressiontype:导入的文件的压缩格式。

    • 指定为 none(缺省值),说明导入的文件没经过压缩。

    • 指定为 gzip,则导入的格式为 gzip。目前仅支持 gzip 压缩格式。

  • compressionlevel:设置写入 OSS 的文件的压缩等级,取值范围为 1 - 9,默认值为 6

导出模式参数

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

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

  • num_parallel_worker: 设置写入 OSS 的压缩数据的并行压缩线程个数,取值范围为 1 - 8,默认值为3。

另外,针对导出模式,有如下注意事项:

  • WRITABLE 是导出模式外部表的关键字,创建外部表时需要明确指明。

  • 导出模式目前只支持 prefix 和 dir 参数模式,不支持 filepath。

  • 导出模式的 DISTRIBUTED BY 子句可以使数据节点(Segment)按指定的分布键将数据写入 OSS。

其他通用参数

针对导入模式和导出模式,还有下列容错相关的参数:

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

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

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

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

上述参数如果使用默认值,则如果连续 15 秒的传输速率小于 1 K,就会触发超时。详细描述请参见 OSS SDK 错误处理

其他参数兼容 Greenplum EXTERNAL TABLE 的原有语法,具体语法解释请参见 Greenplum 外部表语法官方文档。这部分参数主要有:

  • FORMAT:支持文件格式,支持 text、csv 等。

  • ENCODING:文件中数据的编码格式,如 utf8。

  • LOG ERRORS:指定该子句可以忽略掉导入中出错的数据,将这些数据写入error_table,并可以使用count参数指定报错的阈值。

使用示例

  1. # 创建 OSS 导入外部表
  2. create readable external table ossexample
  3. (date text, time text, open float, high float,
  4. low float, volume int)
  5. location('oss://oss-cn-hangzhou.aliyuncs.com
  6. prefix=osstest/example id=XXX
  7. key=XXX bucket=testbucket compressiontype=gzip')
  8. FORMAT 'csv' (QUOTE '''' DELIMITER E'\t')
  9. ENCODING 'utf8'
  10. LOG ERRORS INTO my_error_rows SEGMENT REJECT LIMIT 5;
  11. create readable external table ossexample
  12. (date text, time text, open float, high float,
  13. low float, volume int)
  14. location('oss://oss-cn-hangzhou.aliyuncs.com
  15. dir=osstest/ id=XXX
  16. key=XXX bucket=testbucket')
  17. FORMAT 'csv'
  18. LOG ERRORS SEGMENT REJECT LIMIT 5;
  19. create readable external table ossexample
  20. (date text, time text, open float, high float,
  21. low float, volume int)
  22. location('oss://oss-cn-hangzhou.aliyuncs.com
  23. filepath=osstest/example.csv id=XXX
  24. key=XXX bucket=testbucket')
  25. FORMAT 'csv'
  26. LOG ERRORS SEGMENT REJECT LIMIT 5;
  27. # 创建 OSS 导出外部表
  28. create WRITABLE external table ossexample_exp
  29. (date text, time text, open float, high float,
  30. low float, volume int)
  31. location('oss://oss-cn-hangzhou.aliyuncs.com
  32. prefix=osstest/exp/outfromhdb id=XXX
  33. key=XXX bucket=testbucket') FORMAT 'csv'
  34. DISTRIBUTED BY (date);
  35. create WRITABLE external table ossexample_exp
  36. (date text, time text, open float, high float,
  37. low float, volume int)
  38. location('oss://oss-cn-hangzhou.aliyuncs.com
  39. dir=osstest/exp/ id=XXX
  40. key=XXX bucket=testbucket') FORMAT 'csv'
  41. DISTRIBUTED BY (date);
  42. # 创建堆表,数据就装载到这张表中
  43. create table example
  44. (date text, time text, open float,
  45. high float, low float, volume int)
  46. DISTRIBUTED BY (date);
  47. # 数据并行地从 ossexample 装载到 example 中
  48. insert into example select * from ossexample;
  49. # 数据并行地从 example 导出到 oss
  50. insert into ossexample_exp select * from example;
  51. # 从下面的执行计划中可以看出,每个 Segment 都会参与工作。
  52. # 每个 Segment 从 OSS 并行拉取数据,然后通过 Redistribution Motion 这个执行节点将拿到的数据 HASH 计算后分发给对应的 Segment,接受数据的 Segment 通过 Insert 执行节点进行入库。
  53. explain insert into example select * from ossexample;
  54. QUERY PLAN
  55. -----------------------------------------------------------------------------------------------
  56. Insert (slice0; segments: 4) (rows=250000 width=92)
  57. -> Redistribute Motion 4:4 (slice1; segments: 4) (cost=0.00..11000.00 rows=250000 width=92)
  58. Hash Key: ossexample.date
  59. -> External Scan on ossexample (cost=0.00..11000.00 rows=250000 width=92)
  60. (4 rows)
  61. # 从下面的查询计划可以看到,Segment 把本地数据直接导出到 OSS ,没有进行数据重分布
  62. explain insert into ossexample_exp select * from example;
  63. QUERY PLAN
  64. ---------------------------------------------------------------
  65. Insert (slice0; segments: 3) (rows=1 width=92)
  66. -> Seq Scan on example (cost=0.00..0.00 rows=1 width=92)
  67. (2 rows)

注意事项

  • 创建和使用外部表的语法,除了 location 相关的参数,其余部分和 Greenplum 相同。

  • 数据导入的性能和 HybridDB for PostgreSQL 集群的资源(CPU、IO、内存、网络等)相关,也和 OSS 相关。为了获取最大的导入性能,建议在创建表时,使用列式存储 + 压缩功能。例如,指定子句“WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, BLOCKSIZE=1048576)”,详情请参见 Greenplum Database 表创建语法官方文档

  • 为了保证数据导入的性能,ossendpoint Region 需要匹配 HybridDB for PostgreSQL 云上所在 Region,建议 OSS 和 HybridDB for PostgreSQL 在同一个 Region 内以获得最好的性能。相关信息请参见 OSS endpoint 信息

TEXT/CSV 格式说明

下列几个参数可以在外表 DDL 参数中指定,用于规定读写 OSS 的文件格式:

  • TEXT/CSV 行分割符号是 ‘\n’ ,也就是换行符。
  • DELIMITER 用于定义列的分割符:
    • 当用户数据中包括 DELIMITER 时,则需要和 QUOTE 参数一同使用。
    • 推荐的列分割符有 ‘,’、‘\t‘ 、‘|’ 或一些不常出现的字符。
  • QUOTE 以列为单位包裹有特殊字符的用户数据。
    • 用户包含有特殊字符的字符串会被 QUOTE 包裹,用于区分用户数据和控制字符。
    • 如果不必要,例如整数,基于优化效率的考虑,不必使用 QUOTE 包裹数据。
    • QUOTE 不能和 DELIMITER 相同,默认 QUOTE 是双引号。
    • 当用户数据中包含了 QUOTE 字符,则需要使用转义字符 ESCAPE 加以区分。
  • ESCAPE 特殊字符转义
    • 转义字符出现在需要转义的特殊字符前,表示它不是一个特殊字符。
    • ESCAPE 默认和 QUOTE 相同,也就是双引号。
    • 也支持设置成 ‘\’(MySQL 默认的转义字符)或别的字符。

典型的 TEXT/CSV 默认控制字符

控制字符 \ 格式 TEXT CSV
DELIMITER(列分割符) \t (tab) , (comma)
QUOTE(摘引) “ (double-quote) “(double-quote)
ESCAPE(转义) (不适用) 和 QUOTE 相同
NULL(空值) \N (backslash-N) (无引号的空字符串)

所有的控制字符都必须是单字节字符。

SDK 错误处理

当导入或导出操作出错时,错误日志可能会出现如下信息:

  • code:出错请求的 HTTP 状态码。

  • error_code:OSS 的错误码。

  • error_msg:OSS 的错误信息。

  • req_id:标识该次请求的 UUID。当您无法解决问题时,可以凭 req_id 来请求 OSS 开发工程师的帮助。

详情请参见 OSS API 错误响应,超时相关的错误可以使用 oss_ext 相关参数处理。

常见问题

如果导入过慢,请参见上面“注意事项”中关于导入性能的描述。

参考文档

本文导读目录