Amazon Relational Database Service (RDS) is a powerful tool for managing relational databases in the cloud. It simplifies database administration tasks and offers cost-efficient, scalable solutions. In this article, we will explore a hands-on demo of exporting an Amazon RDS snapshot to Amazon S3 and then using Amazon Athena to query the exported data. This enables us to perform data analytics and reporting without impacting the performance of our RDS database.
Exporting RDS Snapshot to Amazon S3
Step 1: Access RDS Console
Begin by navigating to the Amazon RDS console and access the list of available snapshots for your database.
Step 2: Choose Snapshot
Select the most recent automated system snapshot or any manually created snapshot.
Step 3: Export to Amazon S3
From the “Actions” dropdown menu, choose “Export to Amazon S3” and configure the export. Give it a name, such as “bank-demo-export-1”.
Step 4: Specify Data to Export
You can either export the entire database or specify specific tables by listing their schema and names. For this demo, let’s export everything.
Step 5: Create S3 Bucket
If you don’t have an S3 bucket, create one. Name it, leave default options, and ensure “Block all public access” is checked.
Step 6: Set up IAM Role
Create an IAM Role called “bank-demo-exports” with the necessary permissions to read, write, and manage snapshot exports.
Step 7: Configure Encryption
Create a customer-managed encryption key from the Amazon Key Management Service (KMS) and paste its ARN into the Encryption section in RDS.
Step 8: Start the Export
Initiate the export process. The time required depends on the snapshot size, but you can monitor the progress in the RDS Console or by using the aws rds describe-export-tasks command in the AWS CLI.
Export Progress
Monitor the export progress in the RDS Console or through the AWS CLI. The time taken depends on the snapshot’s size.
Export Report
After the export completes, check your S3 bucket. You’ll find two export info JSON files and a folder with the same name as your database. The JSON files provide a summary of the export, while the folder contains data in Apache Parquet format.
Setting up Athena
Step 1: Configure AWS Glue Crawler
Define an AWS Glue crawler to discover the schema of your exported data in S3. Provide the crawler with the path to your data, create an IAM Role, and organize schema information under a name like “bank_demo”.
Step 2: Grant IAM Role Access
Since the exported data is encrypted, grant the IAM Role used by the Glue crawler access to the KMS key.
Step 3: Run the Crawler
Execute the Glue crawler, which may take a few minutes depending on data size.
Running Athena Queries
Now, you can use Amazon Athena to query your exported data. Amazon Athena supports SQL queries, making it user-friendly for those familiar with SQL.
Automating the Export Process
To avoid manual exports, set up automation using AWS Simple Notification Service (SNS), AWS Lambda, and AWS Glue.
AWS Simple Notification Service (SNS)
RDS uses SNS to trigger Lambda functions when specific events occur.
AWS Lambda Function
Use the code provided in the aws-samples/rds-snapshot-export-to-s3-pipeline repository to deploy an automated export setup. This code will handle exporting snapshots to S3.
Amazon Cloud Development Kit (CDK)
CDK deploys the automation infrastructure. Follow the instructions in the repository for a step-by-step setup.
Conclusion
Exporting Amazon RDS snapshots to Amazon S3 and querying the data with Amazon Athena is a powerful way to perform analytics, generate reports, and maintain long-term data retention without impacting your RDS database’s performance. With automation, you can streamline the process for ongoing data analysis. This hands-on demo should help you get started on harnessing the full potential of your RDS data with Amazon Athena. Explore the links in the video description for more information on the services mentioned in this article.
Thanks for reading and watching this tutorial!