View a markdown version of this page

Getting started - Amazon Aurora
Installing the extensionRunning your first queryNext steps

Getting started

Aurora PostgreSQL includes the analytics feature by default on supported engine versions. To use it, you enable the feature through a DB cluster parameter group setting, then install the aurora_analytics extension in each database where you want to use it.

The extension creates a foreign data wrapper and a foreign server named aurora_analytics_server. You then create PostgreSQL foreign tables linked to this server that point to your data in Amazon S3 or Amazon S3 Tables.

Topics

Installing the extension

After aurora_analytics.enabled is set to true, install the extension in each database where you want to use it.

To install the extension

  1. Connect to your Aurora PostgreSQL DB cluster using psql, another PostgreSQL client, or the RDS Data API.

    psql --host=your-cluster-endpoint.region.rds.amazonaws.com --port=5432 --username=postgres --password

  2. Install the extension:

    CREATE EXTENSION aurora_analytics;
Note

To create the extension, you must have the rds_superuser role, or be a user that has been granted the rds_extension role and delegated this extension. To delegate the extension to a non-superuser, run the following as rds_superuser:

GRANT rds_extension TO your_user;
ALTER USER your_user SET rds.allowed_delegated_extensions = 'aurora_analytics';

For more information, see Using Amazon Relational Database Service delegated extension support for PostgreSQL.

You must install the extension separately in each database where you want to use it.

Running your first query

After the extension is installed, you can create a foreign table and query your Amazon S3 data using standard SQL. For detailed information about foreign table syntax and options, see Working with foreign tables.

The following example creates a foreign table that points to Parquet files in Amazon S3 and queries it:

-- Create a foreign table pointing to Parquet data in S3
CREATE FOREIGN TABLE ft_orders ()
SERVER aurora_analytics_server
OPTIONS (
    location 's3://my-bucket/data/orders/',
    format 'parquet',
    region 'us-east-1'
);

-- Query the foreign table
SELECT COUNT(*) FROM ft_orders;

When you specify an empty column list (()), the extension automatically infers column names and data types from the remote source.

Next steps

After you verify that the extension is installed, you can create foreign tables that reference your data in Amazon S3. For more information, see Working with foreign tables.