Automating Setup of Athena Queries of a DynamoDB Database

Athena is a great way to do occasional ad hoc queries of data from a DynamoDB database, but setting it up involves several steps. Here’s a script to automate that.

DynamoDB is a great database for various use cases, but it doesn’t lend itself to ad hoc queries the way an RDBM/SQL database does. One solution to that is to export your DynamoDB data to S3, and then set up AWS Athena to query it. There are several steps to do this, and it’s not something that’s easy to setup with say CloudFormation or some singular command in the AWS console. In this article I’ll cover my small shell script that uses the AWS CLI to set this up.

Note, there are many other solutions to querying your DynamoDB data using SQL/ad hoc queries. For example, you can leverage DynamoDB streams and get data into Redshift or Snowflake, or use Glue for that too. I myself am looking forward to trying out the DynamoDB Zero-ETL to Redshift solution that is coming soon (I played with the preview a bit, but it wasn’t quite there yet, and hasn’t been suggested for use on production DB’s yet). Our data is fairly large, and we infrequently need to do these queries, so I haven’t generally wanted to do use the Redshift setup as it’s considerably more expensive (to run a Redshift DB and keep the data in it, keeping it on, etc. as compared to this S3/Athena setup — see the Cost section below). You can also use AWS Glue to get your DynamoDB data into many other kinds of data sources, especially if you’re willing to do some…