Athena QuickSight query timesout

Question

I am using Athena to connect to my DocumentDB database with the intention of visualising the data in QuickSight. I can see the connection is correct as the database name and table name load properly. When I go to create an analysis or even preview the data it timesout. A refresh just causes the same result. The table only contains 40 or so documents so its not an issue of the amount of data. I have tried with direct query and SPICE.

This is the error returned when attempting to reconnect from QuickSight:

```
Error Details
Error type: SQL_EXCEPTION Learn more

Error details: [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. GENERIC_USER_ERROR: Encountered an exception[com.amazonaws.SdkClientException] from your LambdaFunction[arn:aws:lambda:eu-west-1:717816899369:function:documentdb-connector] executed in context[retrieving meta-data] with message[Unable to execute HTTP request: Connect to s3.eu-west-1.amazonaws.com:443 [s3.eu-west-1.amazonaws.com/52.218.57.91] failed: connect timed out] [Execution ID: 56486351-0cba-4e81-9d9f-3ff4722c198f]

Ingestion Id: 061d9b7e-c7f3-49a7-88ba-531551f6cc9a
```

The SQL_EXCEPTION errors states that it can be caused by a timeout so I figure thats the issue.
`SQL_EXCEPTION – A general SQL error occurred. This error can be caused by query timeouts, resource constraints, unexpected data definition language (DDL) changes before or during a query, and other database errors. Check your database settings and your query, and try again.`

Could there be an issue with the Lambda function being used by Athena to connect to the DocumentDB? Looking at the logs the connection seems to be working.

I thought QuickSight would just be able to pull in the data now that everything is linked up. Any advice is appreciated.

Answer

Hello, 
     
I understand that you are using Athena connected to DocumentDB for the visualization of data in Quicksight but are experiencing a SQL_EXCEPTION error that you suspect is the result of a connection timeout between the two databases. 
     
For Athena, there is a soft-limit 30-minute timeout and the Amazon QuickSight timeout for generating any visual is 2 minutes [1]. The timeout exception you are seeing may be related to an issue with the connector between the Athena and DocumentDB databases. 
     
 In order to remediate this issue, check to make sure there are no issues with the networking configuration. As mentioned in this article [2]:
     
* The security group of the DocumentDB database must allow the traffic of the SG of the lambda function through port 27017

* The lambda must be in a private subnet and its routing table must send traffic to the internet through a NAT gateway
     
You might also want to refer directly to the documentation for the connector [3] and a tutorial on AWS Blogs that walks through the architecture of a similar use-case [4]. 
     
Links to documentation:

[1] Query timeout when using Athena with Amazon QuickSight - Amazon QuickSight (https://docs.aws.amazon.com/quicksight/latest/user/troubleshoot-athena-query-timeout.html)

[2] Get started with Athena federated queries and DocumentDB. | by Carlos Andres Zambrano Barrera | Globant | Medium (https://medium.com/globant/get-started-with-athena-federated-queries-and-documentdb-3ac66bef4ef)

[3] Amazon Athena DocumentDB connector - Amazon Athena (https://docs.aws.amazon.com/athena/latest/ug/athena-prebuilt-data-connectors-docdb.html)

[4] Visualize MongoDB data from Amazon QuickSight using Amazon Athena Federated Query | AWS Big Data Blog (https://aws.amazon.com/blogs/big-data/visualize-mongodb-data-from-amazon-quicksight-using-amazon-athena-federated-query/)
     
Please let me know if this response helps or if you have any other questions. If the above steps do not resolve your issue, I strongly encourage you to contact an AWS support engineer who will be able to assist you.

Athena QuickSight query timesout

Relevant content