The Easy Way to Query AWS DynamoDB Tables Using Amazon Athena

4 min readAug 7, 2024

Amazon DynamoDB is a powerful NoSQL database service, but querying it can sometimes be challenging, especially for users more comfortable with SQL. Enter Amazon Athena, a serverless query service that allows you to analyse data in Amazon S3 using standard SQL. By connecting Athena to DynamoDB, you can leverage the power of SQL to query your NoSQL data. This article will guide you through setting up Athena to query your DynamoDB tables.

Prerequisites

Before we begin, ensure you have:

An AWS account
Existing DynamoDB table(s) with data (I will post tutorial on how to create a DynamoDB and populate data)

Step-by-Step Guide

1. Set up Spill Bucket

First, we need to create a spill bucket in Amazon S3:

Navigate to S3 in the AWS Console.
Create a new bucket. This will be used as the spill bucket for Athena queries.
Note down the bucket name for later use.

2. Configure Athena Data Source Connection

Now, let’s set up the connection between Athena and DynamoDB:

Go to Athena in the AWS Console.

2. Select “Data sources” from the right menu tab.

3. Click on Create data source

4. Choose DynamoDB as the data source.

5. Click Next, Give your Data Source a name as shown below.

6. When you down scroll down and select on “Create Lambda Function”

3. Create and Configure Lambda Function

Athena uses a Lambda function to connect to DynamoDB:

In the Lambda function setup:

Paste the spill bucket name you created earlier under “SpillBucket”.

2. Scroll down to the connection settings.

Enter a name for your Lambda function under “AthenaCatalogName”.
Leave other settings as default.
Acknowledge and deploy the Lambda function.

4. Complete Athena Data Source Setup

After creating the Lambda function:

Go to the Lambda function in the AWS Console.
Copy the function ARN (Amazon Resource Name).

3. Return to the Athena tab.

4. Paste the ARN in the “Lambda function” field.

5. Click “Next” and then “Create data source”

5. Verify Data Source Creation

To ensure everything is set up correctly:

Go back to the Athena page.
Check the “Data Source” section to confirm your new data source is listed.

6. Query DynamoDB Tables

You’re now ready to query your DynamoDB tables using SQL:

Navigate to the Athena Query Editor.
Select your new data source.
You should now see all the tables from your DynamoDB.

Writing Queries

With the setup complete, you can now write SQL queries to analyze your DynamoDB data. Here’s a simple example to get you started:

SELECT * FROM your_dynamodb_table LIMIT 10;

Start with simple queries and gradually increase complexity.
Use appropriate WHERE clauses to filter data and improve query performance.
Be mindful of the data types in your DynamoDB tables when writing queries.

Limitations and Considerations

Athena queries on DynamoDB are eventually consistent.
Complex nested structures in DynamoDB may require careful query construction in Athena.
Be aware of the costs associated with Athena queries and Lambda invocations.

Conclusion

By following this guide, you’ve set up Amazon Athena to query your AWS DynamoDB tables. This powerful combination allows you to use familiar SQL syntax to analyze your NoSQL data, opening up new possibilities for data exploration and reporting.

Remember, while this setup provides great flexibility, it’s important to consider performance and cost implications for large-scale or frequent queries. Always optimize your queries and monitor your usage to ensure efficient operation.

Happy querying!