Skip to content
By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post
About re:Post

Error "Query exhausted resources at this scale factor" in Athena trying to get data to quicksight

0

Hi,

Context and issue description

I have an aws account that containg data stored in S3 Bucket in parquet format. I have shared this data with other account to create quicksight dashboards. I am using Athena to import this data (as direct query) to quicksight, It generate almost all charts I need just one chart rise me the error "The data source's limits on server resources are temporarily exceeded by this query's execution. Try to simplify the query or reduce the size of the dataset".

I have opened the query in Athena and when I try to run it manually it rise the error "Query exhausted resources at this scale factor" (running statistics: Run time:31.281 sec, Data scanned:10.34 GB)

When I perform a "select * from my_database.my_table" it run in less than 2 minutes and scan 514MB of data. The queryes executed by athena to create other charts (sucessfully) give me running statistics like Run time:18.914 sec, Data scanned: 71.79 GB.

When I try ti import the data to quicksight using SPICE method it rise me the error "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."

More informations about the setup below. Do you guys know which can be the cause and how to solve it ?

Cross account share method

To share the data with the other account I have:

  • Allowed access from consumer account to the Glue catalog in provider account

Provider account glue settings

  • Allowed access to the S3 bucket of the provider account
    • Bucket policy set as:
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::<<CONSUMER-ACCOUNT-ID>>:root"
            },
            "Action": "s3:GetObject",
            "Resource": "<<YOUR-BUCKET-ARN>>"
        }
    ]
}

  • Consumer account added to the Access control list (ACL) of the bucket in provider account with the rights to list objects and read for bucket ACL

  • Created a data source in consumer account as a "S3 - AWS Glue data catalogue" filled the form with data source name and provider account ID

Athena workgroup setup and query

  • The workgroup settings can be found in the next image (already tried with the maximum value of 7PB)

Enter image description here

  • Query automatic generated by quicksight:
/* QuickSight cf90002b-7ab4-4af3-afda-8c650a992256 
 {"partner":"QuickSight","entityId":"c6b445ed-6b03-4d4d-a9a2-d9d90778494e","sheetId":"80b03065-2e67-43ca-96e2-3476621b14ee","visualId":"b6900a2d-5d49-484e-a102-99cba6c947b8"} */
SELECT "a5d4a71c-5aaa-4114-953b-8ac51aa8b558.p_date_tg", "isotherrow_1", "a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_hub", "isotherrow_2", "a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_van1010_distinct_count", "count"
FROM (
SELECT CASE WHEN "t"."$RANK_1" > 50 THEN NULL ELSE "t"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.p_date_tg" END AS "a5d4a71c-5aaa-4114-953b-8ac51aa8b558.p_date_tg", CASE WHEN "t"."$RANK_1" > 50 THEN NULL ELSE "t"."$RANK_1" END AS "$f26", CASE WHEN "t"."$RANK_1" > 50 THEN 1 ELSE 0 END AS "isotherrow_1", CASE WHEN "t"."$RANK_1" > 50 OR "t"."$RANK_2" > 50 THEN NULL ELSE "t"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_hub" END AS "a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_hub", CASE WHEN "t"."$RANK_1" > 50 OR "t"."$RANK_2" > 50 THEN NULL ELSE "t"."$RANK_2" END AS "$f29", CASE WHEN "t"."$RANK_1" > 50 OR "t"."$RANK_2" > 50 THEN 1 ELSE 0 END AS "isotherrow_2", COUNT(DISTINCT "t0"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_van1010") AS "a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_van1010_distinct_count", COUNT(*) AS "count"
FROM (
SELECT "c_hub" AS "a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_hub", date_trunc('week', CAST(CAST(PARSE_DATETIME("p_date", 'yyyy-MM-dd') AS TIMESTAMP) AS TIMESTAMP)  + INTERVAL '1' day)  - INTERVAL '1' day AS "a5d4a71c-5aaa-4114-953b-8ac51aa8b558.p_date_tg", DENSE_RANK() OVER (ORDER BY date_trunc('week', CAST(CAST(PARSE_DATETIME("p_date", 'yyyy-MM-dd') AS TIMESTAMP) AS TIMESTAMP)  + INTERVAL '1' day)  - INTERVAL '1' day DESC) AS "$RANK_1", DENSE_RANK() OVER (PARTITION BY date_trunc('week', CAST(CAST(PARSE_DATETIME("p_date", 'yyyy-MM-dd') AS TIMESTAMP) AS TIMESTAMP)  + INTERVAL '1' day)  - INTERVAL '1' day ORDER BY COUNT(DISTINCT "c_van1010") DESC, "c_hub" NULLS FIRST) AS "$RANK_2"
FROM "KpieCatalog"."vehicle_ccpu_data_asset_sem"."t_session_level_header_drives_id8_global"
GROUP BY "c_hub", date_trunc('week', CAST(CAST(PARSE_DATETIME("p_date", 'yyyy-MM-dd') AS TIMESTAMP) AS TIMESTAMP)  + INTERVAL '1' day)  - INTERVAL '1' day
) AS "t"
INNER JOIN (
SELECT "c_van1010" AS "a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_van1010", "c_hub" AS "a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_hub", date_trunc('week', CAST(CAST(PARSE_DATETIME("p_date", 'yyyy-MM-dd') AS TIMESTAMP) AS TIMESTAMP)  + INTERVAL '1' day)  - INTERVAL '1' day AS "a5d4a71c-5aaa-4114-953b-8ac51aa8b558.p_date_tg"
FROM "KpieCatalog"."vehicle_ccpu_data_asset_sem"."t_session_level_header_drives_id8_global"
) AS "t0" ON ("t"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.p_date_tg" = "t0"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.p_date_tg" OR "t"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.p_date_tg" IS NULL AND "t0"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.p_date_tg" IS NULL OR "t"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.p_date_tg" IS NULL AND "t0"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.p_date_tg" IS NULL) AND ("t"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_hub" = "t0"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_hub" OR "t"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_hub" IS NULL AND "t0"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_hub" IS NULL OR "t"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_hub" IS NULL AND "t0"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_hub" IS NULL)
GROUP BY CASE WHEN "t"."$RANK_1" > 50 THEN NULL ELSE "t"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.p_date_tg" END, CASE WHEN "t"."$RANK_1" > 50 THEN NULL ELSE "t"."$RANK_1" END, CASE WHEN "t"."$RANK_1" > 50 THEN 1 ELSE 0 END, CASE WHEN "t"."$RANK_1" > 50 OR "t"."$RANK_2" > 50 THEN NULL ELSE "t"."a5d4a71c-5aaa-4114-953b-8ac51aa8b558.c_hub" END, CASE WHEN "t"."$RANK_1" > 50 OR "t"."$RANK_2" > 50 THEN NULL ELSE "t"."$RANK_2" END, CASE WHEN "t"."$RANK_1" > 50 OR "t"."$RANK_2" > 50 THEN 1 ELSE 0 END
ORDER BY CASE WHEN "t"."$RANK_1" > 50 THEN NULL ELSE "t"."$RANK_1" END NULLS FIRST, CASE WHEN "t"."$RANK_1" > 50 OR "t"."$RANK_2" > 50 THEN NULL ELSE "t"."$RANK_2" END NULLS FIRST
) AS "t1"
Topics
Analytics
Tags
Amazon Quick SightAWS GlueAmazon Athena
Language
English

asked a year ago215 views

1 Answer
0

The "Query exhausted resources at this scale factor" error occurs when the demands of your query exceed the resources available to the Athena cluster running the query. Athena runs queries in a distributed query engine that scales out (not up) to process more demanding queries. When a query's requirements exceed what the cluster can handle, this error occurs.

Looking at your specific case, there are several factors that could be causing this issue:

  1. Query Complexity: The automatically generated QuickSight query is quite complex, involving multiple operations that are resource-intensive:
  • It uses window functions (DENSE_RANK)
  • It has multiple GROUP BY clauses
  • It includes DISTINCT COUNT operations
  • It contains complex CASE statements and joins

  1. Data Volume: While some of your other queries scan more data (71.79 GB) successfully, this particular query might be processing the data in a way that requires more memory per node.

  2. Resource Limitations: Even though you've set the data usage control to the maximum value (7PB), this doesn't increase the memory available per node in Athena's distributed system.

Here are some approaches to resolve this issue:

  1. Optimize your query:
  • Simplify the query by removing some of the window functions or DISTINCT operations
  • Break down the complex query into smaller, more manageable parts
  • Consider removing or simplifying the ORDER BY clauses
  1. Use SPICE instead of direct query:
  • Since you mentioned SPICE import also fails, try importing a subset of the data first
  • Create a simplified view or table with pre-aggregated data that requires less processing
  1. Implement data optimization:
  • Ensure your data is properly partitioned to reduce the amount of data scanned
  • Optimize your Parquet files (right-size them, use appropriate compression)
  • Check for data skew that might be overloading specific workers
  1. Consider 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

Remember that QuickSight has a two-minute timeout for generating visuals, which might be contributing to the issue when using direct query mode. The complexity of the query combined with the data volume appears to be exceeding the resources Athena can allocate to a single query execution.
Sources
Athena query error: Query exhausted resources at this scale factor. | AWS re:Post
Optimize service use - Amazon Athena
Query timeout when using Athena with Amazon QuickSight - Amazon QuickSight

answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Relevant content