Bigquery: BAD_QUERY (The query requires too many resources to parse)

Question

I have a programmatically generated query for BigQuery. It has part like

select *,
       IF(cond1, val1, IF(cond2, val2, IF (cond3, val3, ...))) as x

But when the number of IFs grows, I start getting Fatal error: Reason: invalidQuery, message: BAD_QUERY (The query requires too many resources to parse)

I would like to know how many resources are available to parse the query and where those limits are documented. The query size itself is well under 256KB.

I didn't try yet, but wondering if rewriting query with

CASE WHEN THEN

might help here to stay under the limit.

Thank you.

FWIW, rewriting query with CASE/WHEN/THEN helped in my particular case, but I didn't check whether it can be as long as 256KB. — Timmy
– Timmy, Commented Jul 10, 2015 at 7:45

Mosha Pasumansky · Accepted Answer · 2015-07-10 15:10:50Z

3

The limitation in this case is not static, and not on the length of the query, but rather on number of function nesting levels. BigQuery checks stack depth during parsing, and if it goes too deep - the error is raised. Every function that could go deep, has variadic version, i.e. CONCAT with multiple arguments, CASE instead of IF - and this is a preferred solution for cases like that.

answered Jul 10, 2015 at 15:10

Mosha Pasumansky

14.1k5 gold badges36 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Bigquery: BAD_QUERY (The query requires too many resources to parse)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related