ApsaraDB RDS:Use the oss_fdw extension to read and write foreign data text files

Prerequisites

Before you begin, make sure you have:

• An RDS instance running PostgreSQL 10 or later

• If your instance runs PostgreSQL 14: a minor engine version of 20220830 or later. To upgrade, see Update the minor engine version

• An OSS bucket in the same region as your RDS instance (recommended for performance). See Activate OSS and Create a bucket

• An AccessKey ID and AccessKey Secret with read/write access to the bucket. See Obtain an AccessKey pair

How it works

oss_fdw maps one or more OSS objects to a PostgreSQL foreign table. Queries against the foreign table trigger a scan of the matched objects in OSS — no data is copied to your RDS instance until you explicitly insert it. All OSS objects must be in CSV format, with optional gzip compression.

To use oss_fdw:

1. Install the extension.

2. Create a server that holds your OSS connection credentials.

3. Create a foreign table that maps to the OSS objects you want to read or write.

4. Query or write through the foreign table.

Set up oss_fdw

CREATE EXTENSION oss_fdw;

CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS (
    host   'oss-cn-hangzhou-internal.aliyuncs.com',
    id     '<your-access-key-id>',
    key    '<your-access-key-secret>',
    bucket '<your-bucket-name>'
);

CREATE FOREIGN TABLE ossexample (
    date   text,
    time   text,
    open   float,
    high   float,
    low    float,
    volume int
)
SERVER ossserver
OPTIONS (
    dir       'osstest/',
    delimiter ',',
    format    'csv',
    encoding  'utf8'
);

Import data from OSS

Read directly from OSS without loading data into your RDS instance:

SELECT * FROM ossexample;

Load data into a local table for faster subsequent queries:

1. Create a local table with the same schema:

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

2. Insert data from the foreign table:

    INSERT INTO example SELECT * FROM ossexample;

Before importing, run EXPLAIN to estimate the number of OSS objects matched and get the query plan:

EXPLAIN INSERT INTO example SELECT * FROM ossexample;

Expected output:

                                      QUERY PLAN
----------------------------------------------------------------------
 Insert on example  (cost=0.00..1.10 rows=0 width=0)
   ->  Foreign Scan on ossexample  (cost=0.00..1.10 rows=1 width=998)
         Foreign OssDir: osstest/
         Number Of Ossfile: 2

Export data to OSS

Write data from a local table to OSS:

INSERT INTO ossexample SELECT * FROM example;

For export, the foreign table must use the dir option (not filepath). Each export writes objects to the specified OSS folder.

To control output file size and parallelism, use the write-specific parameters in Foreign table parameters.

Server parameters

Use these parameters in the OPTIONS clause of CREATE SERVER.

Foreign table parameters

Use these parameters in the OPTIONS clause of CREATE FOREIGN TABLE.

Auxiliary tools

oss_fdw_list_file(relname text, schema text DEFAULT 'public')

SELECT * FROM oss_fdw_list_file('ossexample');

              name              |   size
--------------------------------+-----------
 osstest/test.gz.1              | 739698350
 osstest/test.gz.2              | 739413041
 osstest/test.gz.3              | 739562048
(3 rows)

SET oss_fdw.rds_read_one_file = 'osstest/test.gz.2';
SELECT * FROM oss_fdw_list_file('ossexample');

              name              |   size
--------------------------------+-----------
 oss_test/test.gz.2             | 739413041
(1 rows)

Protect your credentials

If the id and key values in CREATE SERVER are stored in plaintext, any database user with access to pg_foreign_server can read your AccessKey pair:

SELECT * FROM pg_foreign_server;

To protect your credentials, use symmetric encryption on the id and key values when creating the server. Use a different encryption key for each RDS instance. When encrypted, the values appear as MD5**** in pg_foreign_server:

 ossserver | 10 | 16390 | | | | {host=oss-cn-hangzhou-zmf.aliyuncs.com,id=MD5****,key=MD5****,bucket=067862}

Each encrypted value starts with MD5, and the total length divided by 8 equals 3. After encrypted values are exported, they are not re-encrypted. AccessKey IDs and secrets that start with MD5 cannot be created — the encryption prefix is reserved.

Unlike Greenplum, oss_fdw does not support adding data types during encryption, which preserves compatibility with earlier PostgreSQL versions.

Usage notes

• Place your RDS instance and OSS bucket in the same region to maximize import and export throughput. See OSS domain names for endpoint details.

• oss_fdw reads and writes only CSV format, including gzip-compressed CSV.

• Import performance depends on CPU, I/O, and memory available on your RDS instance.

• parse_errors is supported for reads only. Setting it on an export foreign table is not allowed.

• filepath is for import only. For export, use dir.

• The filepath and dir options are mutually exclusive. Specify exactly one.

Troubleshooting