Resources exceeded BigQuery

Question

When running the following query I got the error:

Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: 158% of limit. Top memory consumer(s): sort operations used for analytic OVER() clauses: 98% other/unattributed: 2%

select *, row_number() over(PARTITION BY Column_A ORDER BY Column_B)
from
(SELECT
*
FROM
  Table_1 UNION ALL
SELECT
  *
FROM
  Table_2 UNION ALL
SELECT
  *
FROM
  Table_3
)

Can someone help me how to change this query or is there possibility that we can change the memory limit in bigquery?

Answer 1

Welcome Aaron,

This error means BigQuery is unable to process the whole query due to memory limits, the ORDER BY function is pretty memory intensive, try removing it and I would expect your query to run fine.

If you need results ordered, try writing the unordered query out to a table then running a new query on this table to order the results.

If you're interested. here's an interesting article on how and BigQuery executes in memory: https://cloud.google.com/blog/products/gcp/in-memory-query-execution-in-google-bigquery

I don't believe you can override or change this memory limit, but happy to be proven wrong.

Answer 2

Make sure your ORDER BY is being executed in real last step, additionally, consider to use a LIMIT clause to avoid “Resources Exceeded” or “Response too large” fails.

Answer 3

You can try OVER without using ORDER BY