Athena version 3, error "Query exhausted resources at this scale factor"

Question

Hi,

I have an Athena query that worked fine on Athena engine 2, moving to version 3 we get this error

Query ID
b1831a04-13eb-4ed6-b407-ad8d873925a0
Error details
Query exhausted resources at this scale factor. You may need to manually clean the data at location 's3://xxxxxx' before retrying. Athena will not delete data in your account.

```
CREATE TABLE xxxxx WITH ( format = 'PARQUET', external_location = 's3://xxxxx/movement_123/year=2023/q=2/month=04/day=16/', partitioned_by = ARRAY['yyyyy']) 
AS 
SELECT *,from_base(substr(to_hex(md5(to_utf8(cast(xxxx AS varchar(xx))))),x,x),xx) % 100 AS xxxxx FROM xxxx.xxxxx WHERE xxxxx = 'xxxx' AND xxxx = 'xx' AND xx='xx' 
UNION ALL
SELECT *,from_base(substr(to_hex(md5(to_utf8(cast(xxxx AS varchar(10))))),1,8),16) % 100 AS xxxx FROM xxxx.xxxx WHERE xxx = 'xxx' AND xxx = 'xx' AND xx='xx' 
UNION ALL 
SELECT *,from_base(substr(to_hex(md5(to_utf8(cast(xxxx AS varchar(10))))),1,8),16) % 100 AS xxxx FROM xxxx.xxxx WHERE xxx = 'xxx' AND xxx = 'xx' AND xx='xx' 	
```

Answer

Hello,

As you may know  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".

This issue may sometime be transient and it may go away if you retry.

You can refer to below documentation which provides details on error message. 
********
1. https://repost.aws/questions/QUdYYEXbb0QMuKX6TwDl17GA/athena-query-exhausted-resources-at-this-scale-factor

2. https://docs.aws.amazon.com/athena/latest/ug/performance-tuning.html
********

However, you mentioned the Athena query is working fine on Athena v2.

Can you please confirm on Athena v2 you are running the same Query string, datasets, database, table, s3 location as identical to Athena v3.

If yes, then can you please create a support case and update the Query ID of Athena V2 and Athena v3, as this will help us to understand and troubleshoot the issue further.

If not, then please try running Athena query on Athena v2. Please make sure the everything is identical to Athena v3 with the only change to the version and let us know how it goes.

Thank you!!

Athena version 3, error "Query exhausted resources at this scale factor"

相關內容