# Query Amazon DynamoDB tables with SQL by using Amazon Athena
*Gavin Perrie, Ajit Ambike, and Brad Yates, Amazon Web Services*
## Summary
If your data includes sources other than Amazon Simple Storage Service (Amazon S3), you can use federated queries to access those relational, non-relational, object, or custom data sources. This pattern shows how to configure federated query access through Amazon Athena to Amazon DynamoDB by using an SQL data source connector.
Using this pattern you can do the following:
+ Query DynamoDB tables by using SQL.
+ Run federated SQL queries in Athena and join DynamoDB tables with other supported data sources.
## Prerequisites and limitations
**Prerequisites**
+ A DynamoDB table.
+ An Athena workgroup set to use Athena engine version 2. For instructions, see the [Athena documentation](https://docs.aws.amazon.com/athena/latest/ug/engine-versions-changing.html).
+ An S3 bucket where the `AthenaDynamoDBConnector` AWS Lambda function can spill the data. The S3 bucket and the Lambda function must be in the same AWS Region.
If this is your first time accessing Athena, you will need an additional S3 bucket to use as the query result location. For instructions, see the [Athena documentation](https://docs.aws.amazon.com/athena/latest/ug/querying.html#query-results-specify-location-console).
**Limitations**
+ Write operations such as [INSERT INTO](https://docs.aws.amazon.com/athena/latest/ug/insert-into.html) are not supported.** **
**Product versions**
+ [Athena Query Federation releases on GitHub](https://github.com/awslabs/aws-athena-query-federation/releases)
## Architecture
**Target architecture**
The following diagram shows the connection flow after the pattern is established. The user connects to Amazon Athena to provide the query. Athena passes the query and target to the DynamoDB data source connector Lambda function, which retrieves and returns the data to Athena. If large amounts of data are returned, Athena stores the temporary results in the spill bucket before packaging and returning the complete dataset.
## Tools
**AWS services**
+ [Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/what-is.html) is an interactive query service that helps you analyze data directly in Amazon Simple Storage Service (Amazon S3) by using standard SQL. This pattern uses [Amazon Athena DynamoDB Connector](https://github.com/awslabs/aws-athena-query-federation/tree/master/athena-dynamodb), a tool built using the Amazon Athena Query Federation SDK and installed as an AWS Lambda application through the AWS Serverless Application Repository.
+ [Amazon DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Introduction.html) is a fully managed NoSQL database service that provides fast, predictable, and scalable performance.
+ [AWS Lambda](https://docs.aws.amazon.com/lambda/latest/dg/welcome.html) is a compute service that helps you run code without needing to provision or manage servers. It runs your code only when needed and scales automatically, so you pay only for the compute time that you use.
+ [Amazon Simple Storage Service (Amazon S3)](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html) is a cloud-based object storage service that helps you store, protect, and retrieve any amount of data.
**Code repository**
The code for this pattern is available in the GitHub [Athena Query Federation](https://github.com/awslabs/aws-athena-query-federation/releases) repository.
## Epics
### Set up and test the DynamoDB data source connector
| Task | Description | Skills required |
| --- | --- | --- |
| Deploy the AthenaDynamoDBConnector application. | To deploy AthenaDynamoDBConnecter, do the following:[See the AWS documentation website for more details](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/query-amazon-dynamodb-tables-sql-amazon-athena.html) | AWS DevOps |
| Create a data source for Athena. | To create the data source, do the following:[See the AWS documentation website for more details](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/query-amazon-dynamodb-tables-sql-amazon-athena.html) | AWS DevOps |
| Use Athena to query the DynamoDB table. | To query the DynamoDB table, do the following:[See the AWS documentation website for more details](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/query-amazon-dynamodb-tables-sql-amazon-athena.html) | App developer |
## Troubleshooting
| Issue | Solution |
| --- | --- |
| Query fails with `GENERIC_INTERNAL_ERROR: The bucket is in this region: `*.* | Make sure that the Athena spill bucket and Lambda function are created in the same AWS Region. |
| The newly created data source isn't visible on the Athena console. | Athena data catalogs are regional. Ensure the `AthenaDynamoDBConnector` has been deployed in the Region where you are trying to use Athena. |
| You're unable to run the query against the newly created data source. | Check that the **Query Result Location** has been set. |
## Related resources
+ [Amazon Athena DynamoDB Connector](https://github.com/awslabs/aws-athena-query-federation/tree/master/athena-dynamodb)
+ [Amazon Athena Federated Query](https://docs.aws.amazon.com/athena/latest/ug/connect-to-a-data-source.html)