Athena query error: Query exhausted resources at this scale factor.

The "Query exhausted resources at this scale factor" error in Amazon Athena typically occurs when the demands of your query exceed the resources available to the cluster running the query. This can happen due to various reasons, such as the complexity of the query, the amount of data being processed, or the current resource utilization of the Athena service.

To resolve this issue and prevent it from happening in future scheduled runs, you can try the following approaches:

1. Optimize your query:

   • Use partition pruning to reduce the amount of data scanned.
   • Compress your data files to reduce the amount of data transferred.
   • Optimize file sizes for better query performance.
   • Reduce the number of API requests by using fewer, larger files instead of many small files.

2. Implement query performance tuning:

   • Use the EXPLAIN and EXPLAIN ANALYZE statements to understand query execution plans and optimize your SQL statements.
   • Verify that partition pruning is working correctly for your queries.

3. Adjust query execution:

   • Implement longer wait times between query retries to allow for resource availability.
   • Consider breaking down large queries into smaller, more manageable parts.

4. Use Athena workgroups:

   • Set up workgroups with appropriate query limits and controls to manage resource usage.

5. Consider using Athena's Provisioned Capacity:

   • If your workload is consistent and predictable, you might benefit from using Athena's Provisioned Capacity to ensure dedicated resources for your queries.

6. Check for data skew:

   • Ensure that your data is evenly distributed across partitions to avoid overloading specific workers.

7. Monitor and analyze:

   • Keep track of query patterns and resource usage to identify trends or specific queries that consistently cause issues.

If the problem persists after implementing these optimizations, you may need to contact AWS support for further assistance, providing them with the Query ID mentioned in the error message for detailed investigation.

Remember that the absence of a manifest file in the specified S3 location is normal in this case, as it's only generated when partial results are available. Focus on optimizing your query and data structure to resolve the resource exhaustion issue.
Sources
Resolve the Athena error "query exhausted resources" | AWS re:Post
Athena error catalog - Amazon Athena
Community | What happens when you run a query in Amazon Athena?
Community | What happens when you run a query in Amazon Athena?

Sorting is a common culprit for this kind of error. Sorting is done on a single node, and your query results are too big to sort on a single node.

To confirm, see if the query will complete when you remove the ORDER BY clause.

Hi,

the error "Query exhausted resources at this scale factor" is generally indicates that you hit memory limits on the Athena cluster that runs your query. So, ORDER BY (if you have it in your query) may be the cause but also other directives in your queries

See https://docs.aws.amazon.com/athena/latest/ug/performance-tuning-service-level-considerations.html#performance-tuning-resource-limits for more details.

Best,

Didier