OSS 为阿里云对象存储服务，AnalyticDB for PostgreSQL 支持通过 OSS 外部表（即 gpossext 功能），将数据并行从 OSS云存储 导入或导出到 OSS云存储，并支持通过 gzip 进行 OSS 外部表文件压缩，大量节省存储空间及成本。

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

本文内容包括：

操作说明

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

创建 OSS 外部表插件（oss_ext）

使用 OSS 外部表时，需要在 AnalyticDB for PostgreSQL 中先创建 OSS 外部表插件（每个数据库需要单独创建）。

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

并行导入数据

导入数据时，请执行如下步骤：

  1. 将数据均匀分散存储在多个 OSS 文件中。

    注意

    AnalyticDB for PostgreSQL的每个数据分区（segment）将按轮询方式并行对OSS上的数据文件进行读取，文件的数目建议为 数据节点数（Segment 个数）的整数倍，从而提升读取效率。

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

  3. 执行如下操作，并行导入数据。 
    INSERT INTO <目标表> SELECT * FROM <外部表>

并行导出数据

导出数据时，请执行如下步骤：

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

  2. 执行如下操作，并行把数据导出到 OSS 中。 
    INSERT INTO <外部表> SELECT * FROM <源表>

创建 OSS 外部表语法

创建 OSS 外部表语法，请执行如下命令：

CREATE [READABLE] EXTERNAL TABLE tablename
( columnname datatype [, ...] | LIKE othertable )
LOCATION ('ossprotocol')
FORMAT 'TEXT'
            [( [HEADER]
               [DELIMITER [AS] 'delimiter' | 'OFF']
               [NULL [AS] 'null string']
               [ESCAPE [AS] 'escape' | 'OFF']
               [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
               [FILL MISSING FIELDS] )]
           | 'CSV'
            [( [HEADER]
               [QUOTE [AS] 'quote']
               [DELIMITER [AS] 'delimiter']
               [NULL [AS] 'null string']
               [FORCE NOT NULL column [, ...]]
               [ESCAPE [AS] 'escape']
               [NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
               [FILL MISSING FIELDS] )]
[ ENCODING 'encoding' ]
[ [LOG ERRORS [INTO error_table]] SEGMENT REJECT LIMIT count
       [ROWS | PERCENT] ]
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
    id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]
ossprotocol:
   oss://oss_endpoint dir=[folder/[folder/]...]/file_name
    id=userossid key=userosskey bucket=ossbucket compressiontype=[none|gzip] async=[true|false]
ossprotocol:
   oss://oss_endpoint filepath=[folder/[folder/]...]/file_name
    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 外部表，在导出数据时，将根据该前缀自动生成一个唯一的文件名来给导出文件命名。

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

    • 文件夹路径需要以“/”结尾，如test/mydir/。
    • 在导入数据时，使用此参数创建外部表，会导入指定虚拟目录下的所有文件，但不包括它子目录和子目录下的文件。与 filepath 不同，dir 下的文件没有命名要求。
    • 在导出数据时，使用此参数创建外部表，所有数据会导出到此目录下的多个文件中，输出文件名的形式为filename.x，x 为数字，但可能不是连续的。

  • filepath：OSS 中包含路径的文件名称，与 prefix、dir 互斥，三者只能设置其中一个，并且这个参数只能在创建 READABLE 外部表时指定（即只支持在导入数据时使用）。

    • 该文件名称包含该路径，但不包含 bucket 名。
    • 在导入数据时，文件命名方式必须为 filename 或 filename.x，x 要求从 1 开始，且是连续的。例如，如果指定 filepath = filename，而 OSS 中含有如下文件： 
      filename
filename.1
filename.2
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。

  • compressiontype：导出文件的压缩格式。

    • 指定为 none（缺省值），说明导出的文件没经过压缩。

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

另外，针对导出模式，有如下注意事项：

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

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

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

其他通用参数

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

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

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

  • oss_speed_limit：设置能容忍的最小速率，默认是 1024，即 1 KB。

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

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

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

  • FORMAT：支持文件格式，支持 text、csv 等。

  • ENCODING：文件中数据的编码格式，如 utf8。

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

    说明
    • 通过LOG ERRORS将错误行信息记录到内部关联文件。 
      create readable external table ossexample
        (date text, time text, open float, high float,
        low float, volume int)
        location('oss://oss-cn-hangzhou.aliyuncs.com
        prefix=osstest/example id=XXX
        key=XXX bucket=testbucket compressiontype=gzip')
        FORMAT 'csv' (QUOTE '''' DELIMITER E'\t')
        ENCODING 'utf8'
        LOG ERRORS SEGMENT REJECT LIMIT 5;
    • 通过函数gp_read_error_log('external_table_name')可以读取错误行信息。 
      select * from gp_read_error_log('external_table_name');
    • 内部文件随外表删除而删除，也可以通过函数gp_truncate_error_log('external_table_name')删除。 
      select gp_truncate_error_log('external_table_name');
    • 同时4.3版本也支持通过LOG ERRORS INTO error_table语法指定错误表，其他版本不再支持。 
      create readable external table ossexample
        (date text, time text, open float, high float,
        low float, volume int)
        location('oss://oss-cn-hangzhou.aliyuncs.com
        prefix=osstest/example id=XXX
        key=XXX bucket=testbucket compressiontype=gzip')
        FORMAT 'csv' (QUOTE '''' DELIMITER E'\t')
        ENCODING 'utf8'
        LOG ERRORS INTO my_error_rows SEGMENT REJECT LIMIT 5;

使用示例

#  创建 OSS 导入外部表
create readable external table ossexample
        (date text, time text, open float, high float,
        low float, volume int)
        location('oss://oss-cn-hangzhou.aliyuncs.com
        prefix=osstest/example id=XXX
        key=XXX bucket=testbucket compressiontype=gzip')
        FORMAT 'csv' (QUOTE '''' DELIMITER E'\t')
        ENCODING 'utf8'
        LOG ERRORS SEGMENT REJECT LIMIT 5;
create readable external table ossexample
        (date text, time text, open float, high float,
        low float, volume int)
        location('oss://oss-cn-hangzhou.aliyuncs.com
        dir=osstest/ id=XXX
        key=XXX bucket=testbucket')
        FORMAT 'csv'
        LOG ERRORS SEGMENT REJECT LIMIT 5;
create readable external table ossexample
        (date text, time text, open float, high float,
        low float, volume int)
        location('oss://oss-cn-hangzhou.aliyuncs.com
        filepath=osstest/example.csv id=XXX
        key=XXX bucket=testbucket')
        FORMAT 'csv'
        LOG ERRORS SEGMENT REJECT LIMIT 5;
# 创建 OSS 导出外部表
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);
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);
# 创建堆表，数据就装载到这张表中
create table example
        (date text, time text, open float,
         high float, low float, volume int)
         DISTRIBUTED BY (date);
# 数据并行地从 ossexample 装载到 example 中
insert into example select * from ossexample;
# 数据并行地从 example 导出到 oss
insert into ossexample_exp select * from example;
# 从下面的执行计划中可以看出，每个 Segment 都会参与工作。
# 每个 Segment 从 OSS 并行拉取数据，然后通过 Redistribution Motion 这个执行节点将拿到的数据 HASH 计算后分发给对应的 Segment，接受数据的 Segment 通过 Insert 执行节点进行入库。
explain insert into example select * from ossexample;
                                            QUERY PLAN
-----------------------------------------------------------------------------------------------
 Insert (slice0; segments: 4)  (rows=250000 width=92)
   ->  Redistribute Motion 4:4  (slice1; segments: 4)  (cost=0.00..11000.00 rows=250000 width=92)
         Hash Key: ossexample.date
         ->  External Scan on ossexample  (cost=0.00..11000.00 rows=250000 width=92)
(4 rows)
# 从下面的查询计划可以看到，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)

注意事项

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

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

  • 为了保证数据导入的性能，ossendpoint Region 需要匹配 AnalyticDB for PostgreSQL 云上所在 Region，建议 OSS AnalyticDB for PostgreSQL 在同一个 Region 内以获得最好的性能。

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 相关参数处理。

常见问题

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

参考文档