AnalyticDB:Use an OSS foreign table to export data to OSS at high speed

How it works

Each compute node exports data directly to OSS in parallel without redistributing data across nodes. The export follows a three-step pattern: install the oss_ext extension once per database, create a writable external table, then run an INSERT INTO ... SELECT statement.

Prerequisites

Before you begin, make sure you have:

• An AnalyticDB for PostgreSQL V6.0 instance

• An OSS bucket in the same region as your instance

• An AccessKey ID and AccessKey secret with write access to the bucket. To create an AccessKey pair, see Create an AccessKey pair

Set up the extension

Run the following statement once per database to install the oss_ext extension before exporting data:

CREATE EXTENSION IF NOT EXISTS oss_ext;

You only need to run this once per database—not before each export.

Export data to 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]

INSERT INTO <external_table> SELECT * FROM <source_table>;

Example

This example exports data from a local table named example to an OSS bucket.

1. Create the source table:

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

2. Create the writable external table.

Using prefix to specify the output path:

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=<your-access-key-id>
    key=<your-access-key-secret> bucket=testbucket')
    FORMAT 'csv'
    DISTRIBUTED BY (date);

Using dir to specify the output path:

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=<your-access-key-id>
    key=<your-access-key-secret> bucket=testbucket')
    FORMAT 'csv'
    DISTRIBUTED BY (date);

Replace the placeholders with your actual values:

3. Export the data:

INSERT INTO ossexample_exp SELECT * FROM example;

4. Verify the export:

Run EXPLAIN to confirm that each compute node exports data directly to OSS:

EXPLAIN INSERT INTO ossexample_exp SELECT * FROM example;

Expected output:

                          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)

The segments: 3 indicates that all three compute nodes export data in parallel. After the export completes, verify that the files appear in the OSS console under the specified path.

Performance considerations

To maximize export throughput:

• Use column-oriented storage with compression when creating source tables. For example:

  WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, COMPRESSLEVEL=5, BLOCKSIZE=1048576)

  For more information, see CREATE TABLE.

• Keep the OSS bucket and the AnalyticDB for PostgreSQL instance in the same region.

• Tune num_parallel_worker, oss_flush_block_size, and oss_file_max_size based on your data volume and network conditions.

Limitations

• Only V6.0 instances are supported. For V7.0, use OSS foreign tables.

• Only TEXT and CSV formats are supported.

• Only GZIP compression is supported (compressiontype=gzip).

• prefix and dir are mutually exclusive—specify only one per external table.

• Regular expressions are not supported in prefix.

• All format control characters (DELIMITER, QUOTE, ESCAPE, NULL) must be single-byte characters.

• The external table syntax follows Greenplum Database conventions, except for location-related parameters.

Troubleshooting

When an export error occurs, the error log includes:

For a full list of error codes, see Error responses. To fix timeout-related errors, adjust the oss_speed_limit and oss_speed_time parameters.OSS API error responsesOSS API error responses

See also

• OSS domain names

• OSS documentationOSS endpoint information

• Error handling