Working with foreign tables

Foreign tables give your Aurora PostgreSQL database read-only access to Apache Parquet and Apache Iceberg data stored in Amazon S3, without copying it into Aurora. You query a foreign table with standard SQL, on its own or joined with your local PostgreSQL tables, and the data stays in place in Amazon S3.

Supported data formats

Aurora PostgreSQL supports the following open data formats for foreign tables:

Foreign table syntax

The following is the syntax for creating a foreign table:

CREATE FOREIGN TABLE [ IF NOT EXISTS ] [schema_name.]table_name ( [
    column_name data_type [, ... ]
] )
SERVER aurora_analytics_server
OPTIONS (
    location 'location'
    [, format 'parquet' | 'iceberg' ]
    [, region 'region' ]
    [, snapshot 'snapshot_id' ]
    [, timestamp 'timestamp' ]
);

The following parameters are accepted.

Creating a foreign table for Parquet data

The following example creates a foreign table with explicit column definitions for your data stored in Parquet files in Amazon S3:

CREATE FOREIGN TABLE ft_orders (
    order_id INT,
    customer_id INT,
    order_date TIMESTAMP,
    total_amount DECIMAL(10,2),
    status TEXT,
    payment_method TEXT,
    shipping_address TEXT,
    items_count INT
)
SERVER aurora_analytics_server
OPTIONS (
    location 's3://mybucket/data/orders/',
    format 'parquet'
);

To query the table:

SELECT COUNT(*) FROM ft_orders;

Creating a foreign table for Iceberg data

The following example creates a foreign table that uses schema auto-inference for an Iceberg table registered in the AWS Glue Data Catalog:

CREATE FOREIGN TABLE ft_orders ()
SERVER aurora_analytics_server
OPTIONS (
    location 'arn:aws:glue:us-east-1:123456789012:table/my_database/orders'
);

To query the table:

SELECT COUNT(*) FROM ft_orders;

Querying a specific Iceberg snapshot

To query a specific point-in-time snapshot of an Iceberg table, specify the snapshot version you want to query with the snapshot option:

CREATE FOREIGN TABLE ft_orders_snapshot ()
SERVER aurora_analytics_server
OPTIONS (
    location 'arn:aws:glue:us-east-1:123456789012:table/my_database/orders',
    snapshot '3847291056291847'
);

Querying an Iceberg table at a specific timestamp

To query an Iceberg table as of a specific point in time, specify the timestamp option:

CREATE FOREIGN TABLE ft_orders_timetravel ()
SERVER aurora_analytics_server
OPTIONS (
    location 'arn:aws:glue:us-east-1:123456789012:table/my_database/orders',
    timestamp '2024-06-15 12:00:00'
);

The timestamp value is resolved at table creation time. You can use special keywords such as 'yesterday' or 'now', or epoch milliseconds such as '1704110400000'.

Bulk table creation (IMPORT FOREIGN SCHEMA)

To register multiple tables at once from an AWS Glue database or Amazon S3 Tables namespace, use IMPORT FOREIGN SCHEMA:

IMPORT FOREIGN SCHEMA remote_schema
    [ { LIMIT TO | EXCEPT } ( table_name [, ... ] ) ]
    FROM SERVER aurora_analytics_server
    INTO local_schema
    OPTIONS (
        location 'catalog_arn'
    );

This command queries the remote catalog, generates a CREATE FOREIGN TABLE statement for each discovered table, and executes them within a single transaction. Column definitions, format, and region are auto-resolved for each table using the same logic as CREATE FOREIGN TABLE with an empty column list.

Only catalog-based sources (AWS Glue, Amazon S3 Tables) are supported. Raw Amazon S3 URI locations can't be imported because they lack the catalog metadata needed to enumerate tables. The entire operation is transactional: if any individual table creation fails (for example, an unsupported format or a permission denied error), the entire import rolls back.

To import all tables from an AWS Glue database:

IMPORT FOREIGN SCHEMA my_analytics_db
    FROM SERVER aurora_analytics_server
    INTO analytics
    OPTIONS (location 'arn:aws:glue:us-east-1:123456789012:catalog');

To import all tables from an Amazon S3 Tables namespace:

IMPORT FOREIGN SCHEMA my_namespace
    FROM SERVER aurora_analytics_server
    INTO sales_data
    OPTIONS (location 'arn:aws:s3tables:us-east-1:123456789012:bucket/my-bucket');

To import only specific tables:

IMPORT FOREIGN SCHEMA my_analytics_db
    LIMIT TO (orders, customers, products)
    FROM SERVER aurora_analytics_server
    INTO analytics
    OPTIONS (location 'arn:aws:glue:us-east-1:123456789012:catalog');

To exclude specific tables:

IMPORT FOREIGN SCHEMA my_analytics_db
    EXCEPT (staging_temp, debug_logs)
    FROM SERVER aurora_analytics_server
    INTO analytics
    OPTIONS (location 'arn:aws:glue:us-east-1:123456789012:catalog');

Column name restrictions

Aurora PostgreSQL rejects foreign table definitions that contain columns whose names differ only in ASCII letter case. For example, you can't have both "Foo" and "foo" as column names on the same foreign table. If the underlying data contains such columns, rename them in the source data before creating the foreign table.

Modifying foreign tables

You can modify a foreign table with ALTER FOREIGN TABLE, for example, to rename it, move it to another schema, or point it at a new location, format, or Region. Because a foreign table is a definition rather than a copy of your data, these changes update only how PostgreSQL interprets the external data; the underlying data in Amazon S3 is untouched.

To keep queries returning correct results, keep the foreign table definition in sync with the actual schema of your Amazon S3 data. If the source schema changes, update the table (or re-infer it) to match. For an automated way to do this, see aurora_analytics_refresh_foreign_table.

The following characteristics apply to these foreign tables:

Supported ALTER operations:

The following operations are accepted by PostgreSQL but have no practical effect on these foreign tables:

The following operations are not supported because these foreign tables are read-only:

Managing permissions for foreign tables

Foreign tables follow standard PostgreSQL permission controls. Use GRANT and REVOKE to manage which roles can query specific foreign tables or entire schemas.

Querying foreign tables

-- Grant SELECT on a specific foreign table
GRANT SELECT ON TABLE ft_orders TO analyst_role;

-- Grant SELECT on all tables in a schema
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO analyst_role;

Creating foreign tables

To execute CREATE FOREIGN TABLE, a user needs both:

-- Allow a role to create foreign tables
GRANT CREATE ON SCHEMA analytics TO data_engineer_role;
GRANT USAGE ON FOREIGN SERVER aurora_analytics_server TO data_engineer_role;

Modifying foreign tables

Only the table owner can execute ALTER FOREIGN TABLE or DROP FOREIGN TABLE. However, changing any option requires USAGE privilege on aurora_analytics_server, because pointing a foreign table at a new data source is functionally equivalent to creating a new one.

-- Table owner with USAGE on server — can change location
ALTER FOREIGN TABLE ft_orders OPTIONS (SET location 's3://new-bucket/orders/');

-- Table owner without USAGE on server — can rename, add/drop columns, and so on
ALTER FOREIGN TABLE ft_orders RENAME TO ft_orders_archive;
ALTER FOREIGN TABLE ft_orders ADD COLUMN new_col TEXT;

To transfer ownership:

ALTER FOREIGN TABLE ft_orders OWNER TO new_owner_role;

For more information about PostgreSQL privilege management, see GRANT in the PostgreSQL documentation.