How to query DynamoDB using SQL?

Alright, so you have a DynamoDB table with some data, and you want to query it with SQL?

That's cool! Bear with me, and we will set it up in a few minutes!

The trick is to use Athena SQL, but before using it we need to connect Athena with DynamoDB.

Creating a DynamoDB data source in Athena

Where to start?

In AWS Console go to Athena service

• Expand the left side panel
• Click on the "Data sources"
• Click the "Add data source" button

Configuring the data source

• Select "Amazon DynamoDB" as the data source type and click the Next button.

• Enter the Data source name.

Note: the field tip is confusing. At the end, your data source will contain all DynamoDB tables in the region, so choose wisely. I ended up with DynamoDBCatalog.

• Click Create Lambda function. You see another page in a new tab.

Creating the connector Lambda function

There are two important properties to set here:

• SpillBucket - a bucket where Athena will store query results. This bucket should exist, so create it if you don't have one.
• AthenaCatalogName - the field name is confusing, but the field tip is right - it is the name of the lambda function that will translate your Athena SQL to DynamoDB queries. I ended with dynamo-connector-for-athena.
• Click the Create Application button when done.

Finalizing your data source

After your connector lambda is created,

• Go back to your Data source creation page,
• Refresh the list of lambda functions
• Pick the one you created.
• Click Next. Click Review and create.

Querying the data

Now let's query our DynamoDB table using Athena SQL.

• Open Athena Query Editor.
• Change the Data source to the one you created.
• Write an SQL query for a Dynamo DB table from the Table list.
• Run it!