ARRAY_AGG leading to OOM

Question

I'm trying to run a pretty simple query but it's failing with an Resources exceeded error.
I read in another post that the heuristic used to allocate the number of mixers could fail from time to time.

SELECT
  response.auctionId,
  response.scenarioId,
  ARRAY_AGG(response) AS responses
FROM
  rtb_response_logs.2016080515
GROUP BY
  response.auctionId,
  response.scenarioId

Is there a way to fix my query knowing that:

• a response is composed of 38 fields (most of them being short strings)
• the max(count()) of a single response is kind of low (165)

Query Failed
Error: Resources exceeded during query execution.
Job ID: teads-1307:bquijob_257ce97b_1566a6a3f27

Answer 1

It's a current limitation that arrays (produced by ARRAY_AGG or other means) must fit in the memory of a single machine. We've made a couple of recent improvements that should help to reduce the resources required for queries such as this, however. To confirm whether this is the issue, you could try a query such as:

SELECT
  SUM(LENGTH(FORMAT("%t", response))) AS total_response_size
FROM
  rtb_response_logs.2016080515
GROUP BY
  response.auctionId,
  response.scenarioId
ORDER BY total_response_size DESC LIMIT 1;

This formats the structs as strings as a rough heuristic of how much memory they would take to represent. If the result is very large, then perhaps we can restructure the query to use less memory. If the result is not very large, then some other issue is at play, and we'll look into getting it fixed :) Thanks!