使用OSS外表高速导出数据到OSS

功能介绍

目前gpossext支持读写TEXT、CSV格式的文件以及GZIP压缩格式的TEXT、CSV文件。

gpossext架构图如下。

TEXT和CSV格式说明

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

• TEXT和CSV行分割符号是\n，也就是换行符。

• DELIMITER用于定义列的分割符：

  • 当用户数据中包括DELIMITER时，则需要和QUOTE参数一同使用。

  • 推荐的列分割符有,、\t、|或一些不常见的字符。

• QUOTE用于包裹有特殊字符的用户数据（以列为单位）：

  • 包含有特殊字符的字符串会被QUOTE包裹，用于区分用户数据和控制字符。

  • 如果不必要，例如整数，基于优化效率的考虑，不必使用QUOTE包裹数据。

  • QUOTE不能和DELIMITER相同，默认QUOTE是双引号。

  • 当用户数据中包含了QUOTE字符，则需要使用转义字符ESCAPE加以区分。

• ESCAPE用于特殊字符转义：

  • 转义字符出现在需要转义的特殊字符前，表示它不是一个特殊字符。

  • ESCAPE默认和QUOTE相同，为双引号""。

  • 也支持设置成\（MySQL默认的转义字符）或别的字符。

注意事项

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

• 数据导入导出的性能和AnalyticDB PostgreSQL的资源（CPU、I/O、内存、网络等）有关，也和OSS相关。为了获取最优的导入导出性能，建议在创建表时，使用列式存储加压缩功能。例如，指定子句"WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, BLOCKSIZE=1048576)"，详细信息，请参见 Greenplum Database创建表官方文档。

• 为了保证数据导入导出的性能，请保证OSS与AnalyticDB PostgreSQL在同一地域下。

操作步骤

1. 创建OSS外部表插件。

   使用OSS外部表时，需要在AnalyticDB PostgreSQL中先创建OSS外部表插件（每个库中均需要单独创建）。创建命令如下：

   CREATE EXTENSION IF NOT EXISTS oss_ext;

2. 在AnalyticDB PostgreSQL中，创建WRITABLE外部表。

   创建OSS外部表语法如下。

   CREATE WRITABLE EXTERNAL TABLE table_name
   ( column_name data_type [, ...] | LIKE other_table )
   LOCATION ('ossprotocol')
   FORMAT 'TEXT'
                  [( [DELIMITER [AS] 'delimiter']
                  [NULL [AS] 'null string']
                  [ESCAPE [AS] 'escape' | 'OFF'] )]
             | 'CSV'
                  [([QUOTE [AS] 'quote']
                  [DELIMITER [AS] 'delimiter']
                  [NULL [AS] 'null string']
                  [FORCE QUOTE column [, ...]] ]
                  [ESCAPE [AS] 'escape'] )]
   [ ENCODING 'encoding' ]
   [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
   ossprotocol:
      oss://oss_endpoint [prefix=prefix_name|dir=[folder/[folder/]...]/file_name]
       id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]

   参数说明如下。

   参数	说明
   WRITABLE	WRITABLE是导出模式外部表的关键字，创建外部表时需要明确指明。
   FORMAT	支持文件格式，例如TEXT、CSV。
   ENCODING	文件中数据的编码格式，例如UTF8。
   DISTRIBUTED BY	DISTRIBUTED BY子句可以让数据节点（Segment）按指定的分布键将数据导出到OSS。
   oss://oss_endpoint	协议和Endpoint，格式为协议名://oss_endpoint，其中协议名为oss，oss_endpoint为OSS对应区域的域名。示例如下： oss://oss-cn-hangzhou.aliyuncs.com 重要 如果是从阿里云的主机访问数据库，应该使用内网域名（即带有internal的域名），避免产生公网流量。
   id	阿里云账号的AccessKey ID。获取AccessKey操作，请参见创建AccessKey。
   key	阿里云账号的AccessKey Secret。获取AccessKey操作，请参见创建AccessKey。
   bucket	指定数据文件所在的Bucket，需要在OSS上预先创建。
   prefix	指定数据文件对应路径名的前缀，不支持正则表达式，仅是匹配前缀。 说明 与dir互斥，两者只能设置其中一个。 WRITABLE外部表在导出数据时，会根据该前缀自动生成一个唯一的文件名来给导出文件命名。 如果指定prefix=osstest/exp/outfromhdb，则表示的导出路径为osstest/exp/，导出文件的名称都以outfromhdb开头。
   dir	OSS中的虚拟文件夹路径。 说明 与prefix互斥，两者只能设置其中一个。 文件夹路径需要以/结尾，如test/mydir/。 在导出数据时，使用此参数创建外部表，所有数据会导出到此目录下的多个文件中，输出文件名的形式为filename.x，x为数字，但可能不是连续的。
   compressiontype	导出文件的压缩格式。 none（默认值）：导入的文件未压缩。 gzip：导入的文件压缩格式为GZIP。 说明 目前仅支持GZIP压缩格式。
   num_parallel_worker	设置导出到OSS的压缩数据的并行压缩线程个数，默认值为3。取值范围为1~8。示例如下： num_parallel_worker=3
   oss_flush_block_size	单次导出数据到OSS的buffer大小，默认为32 MB。取值范围为1 MB~128 MB。示例如下： oss_flush_block_size=32
   oss_file_max_size	设置导出到OSS的最大文件大小，超出之后会切换到另一个文件继续写。默认为1024 MB。取值范围为8 MB~4000 MB。示例如下： oss_file_max_size=1024
   oss_connect_timeout	设置连接超时。单位为秒，默认为10秒。
   oss_dns_cache_timeout	设置DNS超时。单位为秒，默认为60秒。
   oss_speed_limit	设置触发超时的最小速率。默认为1024字节，即1 KB。 需要与oss_speed_time参数配合使用。 说明 如果使用默认值且连续15秒的传输速率小于1 KB，会触发超时。具体信息，请参见OSS SDK 错误处理。
   oss_speed_time	设置触发超时的最长时间。默认为15秒。 需要与oss_speed_limit参数配合使用。 说明 如果使用默认值且连续15秒的传输速率小于1 KB，会触发超时。具体信息，请参见OSS SDK 错误处理。

3. 并行导出数据。

   在AnalyticDB PostgreSQL数据库中执行如下命令，并行将数据导出到OSS。

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

操作示例

本文以源表example为例，介绍将源表example的数据导出到OSS。

1. 创建OSS外部表插件。

   创建命令如下：

   CREATE EXTENSION IF NOT EXISTS oss_ext;

2. 创建源表，用于装载待导出的数据。

   源表example的建表语句如下：

   CREATE TABLE example
           (date text, time text, open float,
            high float, low float, volume int)
            DISTRIBUTED BY (date);

3. 创建OSS导出外部表。

   • 创建外部表时，使用prefix参数指定导出路径。示例如下：

     CREATE WRITABLE EXTERNAL TABLE ossexample_exp
             (date text, time text, open float, high float,
             low float, volume int)
             location('oss://oss-cn-hangzhou.aliyuncs.com
             prefix=osstest/exp/outfromhdb id=XXX
             key=XXX bucket=testbucket') FORMAT 'csv'
             DISTRIBUTED BY (date);

   • 创建外部表时，使用dir参数指定导出路径。示例如下：

     CREATE WRITABLE EXTERNAL TABLE ossexample_exp
             (date text, time text, open float, high float,
             low float, volume int)
             location('oss://oss-cn-hangzhou.aliyuncs.com
             dir=osstest/exp/ id=XXX
             key=XXX bucket=testbucket') FORMAT 'csv'
             DISTRIBUTED BY (date);

4. 将数据并行地从example表导出到OSS。

   INSERT INTO ossexample_exp SELECT * FROM example;

执行如下查询计划，可以看到Segment节点直接将本地数据导出到OSS，没有进行数据重分布。

EXPLAIN INSERT INTO ossexample_exp SELECT * FROM example;

返回信息如下：

                          QUERY PLAN
---------------------------------------------------------------
 Insert (slice0; segments: 3)  (rows=1 width=92)
   ->  Seq Scan on example  (cost=0.00..0.00 rows=1 width=92)
(2 rows)

SDK错误处理

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

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

• error_code：OSS的错误码。

• error_msg：OSS的错误信息。

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

具体信息，请参见OSS API 错误响应，超时相关的错误可以使用oss_ext相关参数处理。

参考文档

• OSS Endpoint 信息

• OSS帮助文档

• OSS SDK错误处理

• OSS API错误响应

• Greenplum Database创建外部表官方文档

• Greenplum Database创建表官方文档