DynamoDB is a NoSQL database offered by AWS. It’s designed for low latency and high availability, making it an optimal choice for applications that demand seamless scaling. With DynamoDB, AWS provides a feature-rich solution that equips you to handle growing workloads effortlessly.
Athena is a robust data analytics tool within the AWS ecosystem. It empowers users with the ability to query a variety of data sources using familiar SQL language. Whether you’re diving into business intelligence tasks or other analytical endeavours, Athena simplifies the process and enables you to derive meaningful insights from your data.
Connecting Amazon Athena to Amazon DynamoDB offers a powerful way to analyze data stored in DynamoDB using SQL queries. This integration can be valuable in various real-world scenarios:
There are many more you can do with both tools, you get the idea. In order to connect DynamoDB as Athena’s data source you need to create a lambda function to be a connector between both tools but don’t worry AWS Console is pretty much telling you what to do.
Let’s get into the hands-on
Prerequisite
Athena Part 1
2. Click on Create data source
3. Choose Amazon DynamoDB as the data source
4. Fill in the form — Add the Data source name that you’d like.
5. Scroll down and you will see Connection details This is where you choose or create the Lambda function you’d like to use as a connector to your DynamoDB.
In case you don’t have it click the Create Lambda function This will open the AWS Lambda page with the Application configuration that is almost ready for you to deploy
Lambda Function
3. Next ConnectorConfig
4. Click Deploy
5. After deployment you can check the result at the Lambda Dashboard, Applications, Functions
Athena Part 2
Athena Query Editor
SELECT count(*)
FROM “table_name”
;
This will count how many rows your database has(In this case my table is named coffeemagic)
Run the query
4. You will see this error If you haven’t created S3 bucket with the same name yet. Go to S3 and create it.
5. You can check bucket name in Lambda>Functions>[Your function name for DynamoDB connector] and go to tab Configuration>Environment variables>spill_bucket
6. Run the Query again, this time you should see the desired result
Note: If you have a permission issue please check your IAM user policy or Lambda>Configuration>Permissions
Conclusion
There you have it. Let’s have fun with DynamoDB with SQL in Athena Query Editor. Elevate your data analytics game, streamline operations, and make informed, real-time decisions.
Contact us now to revolutionize your data infrastructure and propel your business towards unparalleled efficiency and success!