Build awareness and adoption for your software startup with Circuit.

How to query DynamoDB using SQL?

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

A MacBook with lines of code on its screen on a busy desk

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!

Continue Learning