Create and use a foreign table-PolarDB(PolarDB)-阿里云帮助中心

Ganos_FDW is a foreign data wrapper (FDW) extension for PolarDB for PostgreSQL. It lets you query external data sources — such as data files stored in Object Storage Service (OSS) — directly from your database using standard SQL, without importing the data.

How it works

Ganos_FDW maps an external data source (a file in OSS) into a queryable foreign table through three database objects:

Object	Purpose
Server	Points to the external data source and specifies how to access it
User mapping	Stores the credentials used to authenticate with the data source
Foreign table	Defines the column structure that maps to the data

Once these three objects are in place, query the foreign table with SELECT just like any local table.

Prerequisites

Before you begin, ensure that you have:

A PolarDB for PostgreSQL cluster with the Ganos_FDW extension enabled
An OSS bucket containing the data files you want to query
The OSS endpoint for your bucket
An AccessKey ID and AccessKey secret with read access to the OSS bucket (see Obtain an AccessKey pair)

Create a foreign table

Step 1: Define a server

Create a server object that points to your data source in OSS.

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>]');

Parameter	Description
`datasource`	Path to the data source. For supported path formats, see Object storage paths.
`format`	Driver used to read the data source. If omitted, the default driver is used. For supported drivers, see ST_FDWDrivers.
`open_options`	Options for accessing the data source, specified as space-separated `<config>=<value>` pairs.
`config_options`	Environment variables to configure, specified as space-separated `<config>=<value>` pairs.

For the full syntax reference, see CREATE SERVER.

Example: Create a server that reads an ESRI Shapefile stored 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 '');

Expected output:

CREATE SERVER

Step 2: Create a user mapping

Create a user mapping to associate your database user with the OSS credentials used to access the server.

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

Parameter	Description
`SERVER`	Name of the server created in step 1.
`user`	AccessKey ID of the AccessKey pair.
`password`	AccessKey secret of the AccessKey pair.

For the full syntax reference, see CREATE USER MAPPING.

Example: Map the current database user to OSS credentials.

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

Expected output:

CREATE USER MAPPING

Replace 'id' and 'secret' with your actual AccessKey ID and AccessKey secret.

Step 3: Create a foreign table

Define a foreign table by mapping columns to the structure of the external data source.

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

Parameter	Description
`SERVER`	Name of the server created in step 1.
`layer`	Name of the layer in the data source to map to this table.

For the full syntax reference, see CREATE FOREIGN TABLE.

Example: Create a foreign table that maps to the poly layer in the Shapefile.

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

Expected output:

CREATE FOREIGN TABLE

Step 4: Import foreign table definitions

Instead of defining each foreign table manually, import table definitions from the server in bulk using IMPORT FOREIGN SCHEMA.

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

When using a remote schema with Ganos_FDW, the schema name is fixed as ganos_fdw. Create the target local schema first with CREATE SCHEMA if it does not already exist.

Example: Import all table definitions from myserver into a local schema named imp.

CREATE SCHEMA imp;

IMPORT FOREIGN SCHEMA ganos_fdw
  FROM SERVER myserver
  INTO imp;

Expected output:

CREATE SCHEMA
IMPORT FOREIGN SCHEMA

For the full syntax reference, including the LIMIT TO and EXCEPT clauses, see IMPORT FOREIGN SCHEMA.

Verify the setup

After completing the steps above, run a query against the foreign table to confirm it is accessible.

SELECT * FROM example_table LIMIT 10;

If the foreign table is set up correctly, the query returns rows from the external data source. If you get an authentication error, check that the AccessKey ID and AccessKey secret in the user mapping are correct and have read access to the OSS bucket.