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 withdynamo-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
. ClickReview 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!