AnalyticDB:Use an OSS foreign table for data lake analytics

Supported formats

Limitations

• The AnalyticDB for PostgreSQL instance and the OSS bucket must be in the same region.

• The filetype parameter applies only to CSV, TEXT, JSON, and JSON Lines objects. Snappy compression is not supported for JSON and JSON Lines.

• The log_errors and segment_reject_limit parameters apply only to CSV and TEXT objects.

• Only internal (VPC) endpoints are supported for the OSS server endpoint parameter.

Prerequisites

Before you begin, make sure you have:

• An AnalyticDB for PostgreSQL instance and an OSS bucket in the same region

• The OSS bucket name, an object path or directory, and the internal endpoint. See Get OSS bucket information below.

• An AccessKey ID and AccessKey secret with access to the OSS bucket. See Create an AccessKey pair.

• A sample data file. Download example.csv to follow the examples in this topic.

How it works

Setting up OSS FDW involves three DDL steps:

1. Create an OSS server — register the OSS endpoint and bucket.

2. Create a user mapping — bind your AnalyticDB for PostgreSQL database user to the OSS AccessKey credentials.

3. Create an OSS foreign table — define the table schema and map it to an OSS object path or directory.

After these steps, query the foreign table using standard SQL, just like a regular AnalyticDB for PostgreSQL table.

Step 1: Create an OSS server

Run CREATE SERVER to register the OSS endpoint. For the full PostgreSQL reference, see CREATE SERVER.

CREATE SERVER server_name
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ]

CREATE SERVER oss_serv
    FOREIGN DATA WRAPPER oss_fdw
    OPTIONS (
        endpoint 'oss-cn-********.aliyuncs.com',
        bucket 'adb-pg'
    );

-- Modify a parameter
ALTER SERVER oss_serv OPTIONS (SET endpoint 'oss-cn-********.aliyuncs.com');

-- Add a parameter
ALTER SERVER oss_serv OPTIONS (ADD connect_timeout '20');

-- Remove a parameter
ALTER SERVER oss_serv OPTIONS (DROP connect_timeout);

Step 2: Create a user mapping

Run CREATE USER MAPPING to bind a database user to the OSS AccessKey credentials. For the full reference, see CREATE USER MAPPING.

CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }
    SERVER server_name
    [ OPTIONS ( option 'value' [, ... ] ) ]

CREATE USER MAPPING FOR PUBLIC
    SERVER oss_serv
    OPTIONS (
        id 'LTAI****************',
        key 'yourAccessKeySecret'
    );

Step 3: Create an OSS foreign table

Run CREATE FOREIGN TABLE to define the table schema and map it to OSS objects. For the full reference, see CREATE FOREIGN TABLE.

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
    column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
      [, ... ]
] )
    SERVER server_name
  [ OPTIONS ( option 'value' [, ... ] ) ]

CREATE FOREIGN TABLE ossexample (
    date text,
    time text,
    open float,
    high float,
    low float,
    volume int
) SERVER oss_serv OPTIONS (dir 'dir_oss_adb/', format 'csv');

-- Method 1: Check the query plan and matched objects
EXPLAIN VERBOSE SELECT * FROM ossexample;

-- Method 2: List matched OSS objects
SELECT * FROM get_oss_table_meta('ossexample');

Query OSS data

Query OSS foreign tables using standard SQL, the same way you query native AnalyticDB for PostgreSQL tables.

Filter by column value:

SELECT * FROM ossexample WHERE volume = 5;

Aggregate with GROUP BY:

SELECT low, sum(volume)
FROM ossexample
GROUP BY low
ORDER BY low
LIMIT 5;

Count matching rows:

SELECT count(*) FROM ossexample WHERE volume = 5;

Join OSS foreign tables with AnalyticDB for PostgreSQL tables

1. Create a native AnalyticDB for PostgreSQL table and insert data:

   CREATE TABLE example (id int, volume int);
   INSERT INTO example VALUES(1,1), (2,3), (4,5);

2. Join the OSS foreign table with the native table:

   SELECT example.volume, min(high), max(low)
   FROM ossexample, example
   WHERE ossexample.volume = example.volume
   GROUP BY example.volume
   ORDER BY example.volume;

Fault tolerance

OSS FDW supports fault-tolerant scanning through the log_errors and segment_reject_limit parameters. When enabled, rows with parsing errors are logged instead of causing the entire scan to fail.

Create a fault-tolerant foreign table:

CREATE FOREIGN TABLE oss_error_sales (id int, value float8, x text)
    SERVER oss_serv
    OPTIONS (
        log_errors 'true',          -- Log error rows instead of failing
        segment_reject_limit '10',  -- Stop scanning if more than 10 rows have errors
        dir 'error_sales/',
        format 'csv',
        encoding 'utf8'
    );

Query error logs:

SELECT * FROM gp_read_error_log('oss_error_sales');

Clear error logs:

SELECT gp_truncate_error_log('oss_error_sales');

FAQ

Does deleting data from an OSS foreign table delete the data in OSS?

No. Data stored in OSS cannot be deleted by deleting data from an OSS foreign table.

What's next

• Use partitioned OSS foreign tables

• Use OSS foreign tables to import and export data

• Best practices for performing operations on OSS foreign tables

• Data type mappings for OSS foreign tables