bigquery/ERR/2024_001

BigQuery query job do not encounter query is too complex

Product: BigQuery
Rule class: ERR - Something that is very likely to be wrong

Description

A BigQuery query has too many subqueries or query is too complex

resource.type="bigquery_resource"
severity=ERROR
protoPayload.methodName="jobservice.jobcompleted"
protoPayload.status.message:"Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex"

Remediation

This error occurs when a query is too complex. The primary causes of complexity are:

  • WITH clauses that are deeply nested or used repeatedly.
  • Views that are deeply nested or used repeatedly.
  • Repeated use of the UNION ALL operator.

To address this error, try the following options:

  • Split the query into multiple queries, then use procedural language to run those queries in a sequence with shared state.
  • Use temporary tables instead of WITH clauses.
  • Rewrite your query to reduce the number of referenced objects and comparisons.

Further information

Please follow the best practices mentioned here http://cloud/bigquery/docs/best-practices-performance-patterns

For additional information on how to resolve these issues, see Troubleshoot resources exceeded issues