CODEWITHSUNDEEP
sqlgoogle-bigquery
AnnaP
AnnaP

Reputation: 15

BigQuery error: "Resources exceeded during query execution" on one dataset, but the same query runs without issues on another dataset

I have a scheduled query running for about three months in BigQuery without any issues, but it stopped working a couple of days ago. It gives me the following error right now: "Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex". Even though the query is a bit complex (please see it below), the amount of data behind it is very minimum (14 rows).

So when I tried to run the same query under another dataset - it worked (and it didn't give any errors regarding lack of resources or complexity).

Are there any limits on the number of queries per dataset in BigQuery (Basic plan)? Or what can be causing such an issue?

   SELECT ___Day, Metric, Value_Label, Actual_Value 
   FROM(

   WITH temp_table AS(
   SELECT ___Day,
     CAST(SUM(Amount_Spent) AS INT64) AS Actual_spend,
     CAST(SUM(Amount_Spent)*0.9859 AS INT64) AS Target_spend,
     CAST(SUM(Impressions) AS INT64) AS Actual_impressions,
     CAST(SUM(Impressions)*1.105 AS INT64) AS Target_impressions,
     CAST(SUM(Clicks) AS INT64) AS Actual_clicks,
     CAST(SUM(Clicks)*0.8096 AS INT64) AS Target_clicks,
     CAST(SUM(App_Starts_) AS INT64) AS Actual_LPVs,
     CAST(SUM(App_Starts_)*0.7423 AS INT64) AS Target_LPVs

   FROM `..._all_data_performance_view` 
   WHERE ___Day = DATE_SUB(@run_date, INTERVAL 1 DAY)
   GROUP BY ___Day)

   SELECT ___Day, 'Spend' as Metric, 'Actual' as Value_Label, 
   Actual_spend as Actual_Value from temp_table
   UNION ALL
   SELECT ___Day, 'Spend' as Metric, 'Target' as Value_Label, 
   Target_spend as Actual_Value from temp_table
   UNION ALL
   SELECT ___Day, 'Impressions' as Metric, 'Actual' as Value_Label, 
   Actual_impressions as Actual_Value from temp_table
   UNION ALL
   SELECT ___Day, 'Impressions' as Metric, 'Target' as Value_Label, 
   Target_impressions as Actual_Value from temp_table
   UNION ALL
   SELECT ___Day, 'Clicks' as Metric, 'Actual' as Value_Label, 
   Actual_clicks as Actual_Value from temp_table
   UNION ALL
   SELECT ___Day, 'Clicks' as Metric, 'Target' as Value_Label, 
   Target_clicks as Actual_Value from temp_table
   UNION ALL
   SELECT ___Day, 'Landing Page Views' as Metric, 'Actual' as 
   Value_Label, Actual_LPVs as Actual_Value from temp_table
   UNION ALL
   SELECT ___Day, 'Landing Page Views' as Metric, 'Target' as 
   Value_Label, Target_LPVs as Actual_Value from temp_table

   )
   ORDER BY ___Day, Metric, Value_Label

Thank you in advance!

Upvotes: 0

Views: 1162

Answers (1)

99_m4n
99_m4n

Reputation: 1265

Without knowing your data (and even knowing it) is hard to know where exactly the reason for this error is.

Althought there is no limit of queries for a dataset (as long as your billing account allows it), this error appears because the query needs more slots (google's query processing units) than the available for your project at the time of processing the query.

As the error suggest the query is too complicated for one run with your available resources. Here you have to take into account that it is not just this query but also all the queries in every view, self joins and temporary tables/views running in parallel (in the background). Probably the same query works for other tables because with those tables there are not so many background processes and maybe the background views/tables have grown with the time.

As by google suggested, it is better to replace complex queries with multiple simple queries and materialized-views and/or tables (not views) in between with the partial results for each steps.

Upvotes: 0

Related Questions