ApsaraDB RDS:Create and use a foreign table

Prerequisites

Before you begin, ensure that you have:

• An ApsaraDB RDS for PostgreSQL instance with the Ganos_FDW extension enabled

• An OSS bucket containing the data files you want to query

• An AccessKey pair (AccessKey ID and AccessKey secret) with read access to the OSS bucket. To create one, see Obtain an AccessKey pair

Step 1: Define a server

A server object tells Ganos_FDW where your external data lives and how to access it.

CREATE SERVER <server_name>
FOREIGN DATA WRAPPER ganos_fdw
OPTIONS (
    datasource 'OSS://<endpoint>/path/file',
    format '<driver>',
    open_options '<config>=<value>[ <config>=<value>]',
    config_options '<config>=<value>[ <config>=<value>]');

For the full CREATE SERVER syntax, see the PostgreSQL documentation.

Example — define a server pointing to a Shapefile in OSS:

CREATE SERVER myserver
FOREIGN DATA WRAPPER ganos_fdw
OPTIONS (
  datasource 'OSS://<endpoint>/path/poly.shp',
  format 'ESRI Shapefile',
  open_options 'SHAPE_ENCODING=LATIN1',
  config_options '');

Step 2: Create a user mapping

A user mapping associates a database user with the OSS credentials used to access the server you defined in Step 1.

CREATE USER MAPPING
FOR <user_name>
SERVER <server_name>
OPTIONS (
  user '<oss_ak_id>',
  password '<oss_ak_secret>');

For the full CREATE USER MAPPING syntax, see the PostgreSQL documentation.

Example:

CREATE USER MAPPING
FOR CURRENT_USER
SERVER myserver
OPTIONS (
  user 'id',
  password 'secret');

Step 3: Create a foreign table

A foreign table maps OSS data to a queryable table in your database. Define the columns to match the structure of your data source.

CREATE FOREIGN TABLE <table_name> (
  column_name data_type
  [, ...]
) SERVER <server_name>
OPTIONS (layer '<layer_name>');

For the full CREATE FOREIGN TABLE syntax, see the PostgreSQL documentation.

Example — create a foreign table for a Shapefile layer named poly:

CREATE FOREIGN TABLE example_table (
  fid bigint,
  name varchar,
  age varchar,
  value varchar
) SERVER myserver
OPTIONS (layer 'poly');

After creating the table, run a query to verify the setup:

SELECT * FROM example_table LIMIT 10;

Step 4: Import foreign table definitions

Instead of defining each foreign table manually, use IMPORT FOREIGN SCHEMA to import all table definitions from a server at once.

IMPORT FOREIGN SCHEMA ganos_fdw
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER <server_name>
    INTO <local_schema>;

Example — import all foreign table definitions from myserver into a new local schema:

CREATE SCHEMA imp;

IMPORT FOREIGN SCHEMA ganos_fdw
  FROM SERVER myserver
  INTO imp;

When importing foreign tables, the remote schema name is always ganos_fdw. Create the local schema with CREATE SCHEMA before running the import. For the full IMPORT FOREIGN SCHEMA syntax, see the PostgreSQL documentation.