How do I resolve "Query timeout" errors in Athena?

Resolution

Athena queries fail with timeout errors when they exceed the DML query timeout quota. To resolve the error, use the following solutions.

Reduce or increase query runtime from Athena

Follow the instructions in How do I resolve "query timeout" errors when I import data from Athena to Quick Sight SPICE?

Set up notifications for Athena query timeouts

To receive a notification for queries that time out and fail, use an Amazon CloudWatch event rule to capture the event. Then, use Amazon Simple Notification Service (Amazon SNS) to set up notifications.

Query Amazon S3 access logs

If you run queries for Amazon Simple Storage Service (Amazon S3) access logs and the query times out, then use an AWS Glue ETL job to partition your Amazon S3 data. Then, run Athena queries on limited partitions. For instructions, see How do I use a partitioned Amazon S3 access log to prevent an Athena query timeout?

Query CloudTrail data

If you use Athena to query AWS CloudTrail data, then queries might take a long time to run or time out. This is because CloudTrail logs grow in size over time even if you partition the CloudTrail table to reduce the query runtime.

Use partition projection to manually create a CloudTrail table. This allows Athena to dynamically calculate the value of CloudTrail tables and reduce query runtime. Partition projection calculates partition values and locations from the configuration and this eliminates manual partition management.

Use CTAS queries

To improve query performance, use a CREATE TABLE AS SELECT (CTAS) query to create a new table in Athena from the results of a SELECT statement from another query. CTAS creates tables from query results in one step to make raw data sets easier to use. CTAS queries also improve query performance and reduce query costs. For examples, see Examples of CTAS queries.

Use EXPLAIN and EXPLAIN ANALYZE statements

Use EXPLAIN and EXPLAIN ANALYZE in Athena for complex queries. The EXPLAIN statement refines SQL statements and verifies partition pruning to optimize queries. It's a best practice review the considerations and limitations for EXPLAIN and EXPLAIN ANALYZE statements.

For more information, see EXPLAIN examples and EXPLAIN ANALYZE examples.

Related information

Top 10 performance tuning tips for Amazon Athena

How do I use the results of an Amazon Athena query in another query?