如何使用OSS外表高速导出AnalyticDBPostgreSQL版数据到OSS_云原生数据仓库 AnalyticDB PostgreSQL版-阿里云帮助中心

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

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 在同一地域下。关于OSS Endpoint的相关信息，请参见 OSS endpoint 信息。

创建OSS外部表插件。

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

CREATE EXTENSION IF NOT EXISTS oss_ext;

在 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]

参数说明如下。

oss://oss_endpoint 协议和Endpoint，格式为


     协议名://oss_endpoint

，其中协议名为oss，oss_endpoint为OSS对应区域的域名。示例如下：

oss://oss-cn-hangzhou.aliyuncs.com

注意如果是从阿里云的主机访问数据库，应该使用内网域名（即带有


       internal

的域名），避免产生公网流量。 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 错误处理。

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

创建OSS外部表插件。

创建命令如下：

CREATE EXTENSION IF NOT EXISTS oss_ext;

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

源表example的建表语句如下：

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

创建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);

将数据并行地从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表创建语法官方文档