Amazon Athena, a serverless interactive query service, allows users to analyze data stored in S3 using standard SQL. One of its most powerful features is federated querying, which lets you extend Athena’s capabilities beyond S3 and into relational databases like RDS PostgreSQL. This guide will walk you through implementing federated queries between Athena and an RDS PostgreSQL instance, enabling seamless integration and enhanced data analytics capabilities.

Introduction to Federated Querying with Amazon Athena and RDS PostgreSQL

Federated querying in Amazon Athena allows you to run SQL queries across different data sources without moving or copying the data. By connecting Athena to RDS PostgreSQL, you can combine Athena’s analytical capabilities with relational data stored in PostgreSQL. This is particularly useful for data lakes where structured and unstructured data are combined for comprehensive analysis.

Preparing the Environment: S3 Bucket Configuration and Subnet Setup

Before configuring the federated query environment, ensure that you have an Amazon S3 bucket set up where query results from Athena will be stored. You’ll also need a VPC subnet configured to host your RDS PostgreSQL instance.

  • S3 Bucket Configuration: Set up a dedicated S3 bucket where Athena will write query results. Make sure to define appropriate permissions for Athena to access this bucket.
  • Subnet Setup: Ensure your RDS PostgreSQL instance is in a private subnet within your VPC. Configure security groups to allow access to the example from AWS Lambda (which will serve as the PostgreSQL connector) and Athena.

Establishing Security with AWS Secrets Manager and Security Groups

Security is critical when integrating services. Use AWS Secrets Manager to securely store your PostgreSQL credentials and Security Groups to control access between different AWS resources.

  1. AWS Secrets Manager: Securely stores your RDS PostgreSQL username and password in Secrets Manager. This ensures that sensitive information is not exposed in your code.
    Example of the secret JSON structure:
    {

  “username”: “your_db_user”,

  “password”: “your_db_password”,

  “host”: “your_db_host”,

  “dbname”: “your_db_name”,

  “port”: 5432

}

  1. Security Groups: Create security groups for your PostgreSQL instance that allow inbound traffic from AWS Lambda—Configure outbound rules to enable access to your VPC and S3 bucket.

Crafting the Connection String for Seamless Integration

The connection string plays a vital role in federating Athena with RDS PostgreSQL. It must be crafted carefully to include the database name, hostname, port, username, and password stored in Secrets Manager.

For PostgreSQL, the connection string will look something like this:

jdbc:postgresql://<rds-hostname>:5432/<db-name>?user=<username>&password=<password>

Make sure to replace placeholders with values fetched securely from Secrets Manager.

Creating a PostgreSQL Data Source in Amazon Athena

Once security configurations are in place, the next step is to create a data source for PostgreSQL in Athena. This is done using AWS Lambda, which acts as the data source connector for PostgreSQL.

To create the data source:

  1. Open the Amazon Athena console.
  2. Navigate to Data Sources and choose Connect data source.
  3. Select PostgreSQL from the list of supported connectors.
  4. Follow the prompts to connect your RDS PostgreSQL instance to Athena.

Deploying the PostgreSQL Connector via AWS Lambda

AWS Lambda deploys the PostgreSQL connector, allowing Athena to communicate with the RDS PostgreSQL instance. Amazon provides a pre-built Lambda connector for PostgreSQL, which you can directly deploy via the Athena console or AWS Lambda.

Steps for deploying the connector:

  1. Go to the AWS Lambda Console.
  2. Use the pre-built Amazon Athena PostgreSQL Connector.
  3. Assign the appropriate IAM roles and permissions to allow access to your RDS PostgreSQL instance and Secrets Manager.
  4. Ensure the Lambda function has network access to the VPC and subnets where your RDS PostgreSQL instance resides.

Verifying Connectivity and Executing SQL Queries

After setting up the Lambda function, you should execute some SQL queries to test the connection between Athena and your PostgreSQL database.

  1. Open the Athena Query Editor.
  2. Write a simple SQL query that pulls data from a PostgreSQL table:
    SELECT * FROM “postgres_data_source”.”schema_name”.”table_name” LIMIT 10;
  3. Run the query and check if Athena successfully retrieves the data from RDS PostgreSQL.

If the query executes without errors, you’ve successfully implemented federated querying.

Conclusion: Enhancing Data Analytics through Federated Querying

Implementing federated queries in Amazon Athena with RDS PostgreSQL opens up powerful new opportunities for analyzing structured and unstructured data across diverse sources. Organizations can enhance their data analytics efforts with minimal overhead by leveraging S3 for data storage, PostgreSQL for relational data, and Athena’s query capabilities.

References

Amazon Athena PostgreSQL connector

Creating and connecting to a PostgreSQL DB instance