Tutorial: Querying Amazon S3 data

This tutorial walks you through setting up the analytics feature from scratch and running your first analytical query against Parquet data in Amazon S3. By the end, you will have an Aurora PostgreSQL DB cluster querying Amazon S3 data through standard SQL.

Prerequisites

Before you begin, make sure you have the following:

Step 1: Create the IAM policy and role

Create an IAM policy that grants the extension read access to your Amazon S3 bucket, then create a role and attach the policy.

To create the IAM policy:

aws iam create-policy \
  --policy-name AuroraAnalyticsReadAccess \
  --policy-document '{
    "Version": "2012-10-17",
    "Statement": [
      {
        "Sid": "S3Access",
        "Effect": "Allow",
        "Action": ["s3:ListBucket", "s3:GetObject", "s3:GetBucketLocation"],
        "Resource": ["arn:aws:s3:::your-data-bucket", "arn:aws:s3:::your-data-bucket/*"]
      }
    ]
  }'

To create the IAM role and attach the policy:

aws iam create-role \
  --role-name AuroraAnalyticsRole \
  --assume-role-policy-document '{
    "Version": "2012-10-17",
    "Statement": [
      {
        "Effect": "Allow",
        "Principal": {"Service": "rds.amazonaws.com"},
        "Action": "sts:AssumeRole"
      }
    ]
  }'

aws iam attach-role-policy \
  --role-name AuroraAnalyticsRole \
  --policy-arn arn:aws:iam::<account-id>:policy/AuroraAnalyticsReadAccess

Step 2: Create the Aurora PostgreSQL DB cluster

Create a DB cluster parameter group, then create the DB cluster and a DB instance.

To create the parameter group with the feature enabled:

aws rds create-db-cluster-parameter-group \
  --db-cluster-parameter-group-name my-analytics-params \
  --db-parameter-group-family aurora-postgresql17 \
  --description "Parameter group with aurora_analytics enabled"

aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name my-analytics-params \
  --parameters "ParameterName=aurora_analytics.enabled,ParameterValue=true,ApplyMethod=immediate"

To create the DB cluster:

aws rds create-db-cluster \
  --db-cluster-identifier my-analytics-cluster \
  --engine aurora-postgresql \
  --engine-version 17.11 \
  --master-username postgres \
  --master-user-password <password> \
  --db-cluster-parameter-group-name my-analytics-params \
  --db-subnet-group-name <subnet-group> \
  --vpc-security-group-ids <security-group-id>

To create a DB instance:

aws rds create-db-instance \
  --db-instance-identifier my-analytics-instance-1 \
  --db-cluster-identifier my-analytics-cluster \
  --db-instance-class db.r8gd.xlarge \
  --engine aurora-postgresql

Wait for the DB instance to become available:

aws rds wait db-instance-available \
  --db-instance-identifier my-analytics-instance-1

Step 3: Attach the IAM role to the DB cluster

aws rds add-role-to-db-cluster \
  --db-cluster-identifier my-analytics-cluster \
  --role-arn arn:aws:iam::<account-id>:role/AuroraAnalyticsRole \
  --feature-name AuroraAnalytics

Step 4: Set up the Amazon S3 gateway VPC endpoint

If your Aurora DB cluster is in a private subnet, create an Amazon S3 gateway endpoint:

aws ec2 create-vpc-endpoint \
  --vpc-id <vpc-id> \
  --service-name com.amazonaws.<region>.s3 \
  --route-table-ids <route-table-id>

Step 5: Connect and install the extension

Connect to your Aurora PostgreSQL DB cluster using psql:

psql --host=my-analytics-cluster.<region>.rds.amazonaws.com \
     --port=5432 --username=postgres --password

Install the aurora_analytics extension:

CREATE EXTENSION aurora_analytics;

Verify the installation:

SELECT extname, extversion FROM pg_extension WHERE extname = 'aurora_analytics';

Expected output:

     extname      | extversion
------------------+------------
 aurora_analytics | 1.0

Verify the foreign server was created:

SELECT srvname FROM pg_foreign_server WHERE srvname = 'aurora_analytics_server';

Step 6: Create a foreign table

Create a foreign table that points to your Parquet data in Amazon S3. Using empty parentheses () lets Aurora PostgreSQL auto-infer the schema from the Parquet metadata:

CREATE FOREIGN TABLE ft_orders ()
SERVER aurora_analytics_server
OPTIONS (
    location 's3://your-data-bucket/data/orders/',
    format 'parquet',
    region 'us-east-1'
);

Check the inferred schema:

\d ft_orders

Step 7: Run your first query

-- Count all rows
SELECT COUNT(*) FROM ft_orders;

-- Aggregation with filter
SELECT
    status,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value
FROM ft_orders
WHERE order_date >= '2024-01-01'
GROUP BY status
ORDER BY total_revenue DESC;

Step 8: Join with a local PostgreSQL table

Create a local table and join it with your foreign table:

-- Create a local dimension table
CREATE TABLE customer_segments (
    customer_id BIGINT PRIMARY KEY,
    segment TEXT,
    region TEXT
);

INSERT INTO customer_segments VALUES
    (1, 'VIP', 'US-West'),
    (2, 'Premium', 'US-East'),
    (3, 'Regular', 'EU-West');

-- Join local table with S3 data
SELECT
    cs.segment,
    cs.region,
    COUNT(*) AS order_count,
    SUM(o.total_amount) AS total_revenue
FROM ft_orders o
JOIN customer_segments cs ON o.customer_id = cs.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY cs.segment, cs.region
ORDER BY total_revenue DESC;

Step 9: Materialize results into PostgreSQL

Store query results from Amazon S3 as a local PostgreSQL table for fast repeated access:

CREATE TABLE monthly_revenue AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value
FROM ft_orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Query the materialized result (fast, local PostgreSQL)
SELECT * FROM monthly_revenue;

Step 10: Check cache and performance

After running queries, verify that the cache is working:

-- Check cache size (should be non-zero after queries)
SELECT pg_size_pretty(aurora_analytics_cache_size()) AS cache_size;

-- Check per-query statistics
SELECT query,
       pg_size_pretty(analytics_cache_hit_bytes) AS cache_hits,
       pg_size_pretty(analytics_remote_read_bytes) AS s3_reads
FROM aurora_analytics_stat_statements()
WHERE analytics_cache_hit_bytes > 0 OR analytics_remote_read_bytes > 0;

Run the same query again and notice the improvement: the second execution reads from cache instead of Amazon S3.

Clean up

To avoid ongoing charges, delete the resources you created:

-- Drop the foreign table
DROP FOREIGN TABLE ft_orders;
DROP TABLE customer_segments;
DROP TABLE monthly_revenue;

aws rds delete-db-instance --db-instance-identifier my-analytics-instance-1 --skip-final-snapshot
aws rds delete-db-cluster --db-cluster-identifier my-analytics-cluster --skip-final-snapshot
aws iam detach-role-policy --role-name AuroraAnalyticsRole --policy-arn arn:aws:iam::<account-id>:policy/AuroraAnalyticsReadAccess
aws iam delete-role --role-name AuroraAnalyticsRole
aws iam delete-policy --policy-arn arn:aws:iam::<account-id>:policy/AuroraAnalyticsReadAccess

Next steps

Now that you've completed the tutorial, explore the following topics: