Query Failed Error: Resources exceeded during query execution: The query could not be executed in the allotted memory

Question

I am using Standard SQL.Even though its a basic query it is still throwing errors. Any suggestions pls

SELECT 
  fullVisitorId,
  CONCAT(CAST(fullVisitorId AS string),CAST(visitId AS string)) AS session,
  date,
  visitStartTime,
  hits.time,
  hits.page.pagepath
FROM
  `XXXXXXXXXX.ga_sessions_*`,
  UNNEST(hits) AS hits
WHERE
  _TABLE_SUFFIX BETWEEN "20160801"
  AND "20170331"
ORDER BY
  fullVisitorId,
  date,
  visitStartTime

Solution 1

The only way for this query to work is by removing the ordering applied in the end:

SELECT 
  fullVisitorId,
  CONCAT(CAST(fullVisitorId AS string),CAST(visitId AS string)) AS session,
  date,
  visitStartTime,
  hits.time,
  hits.page.pagepath
FROM
  `XXXXXXXXXX.ga_sessions_*`,
  UNNEST(hits) AS hits
WHERE
  _TABLE_SUFFIX BETWEEN "20160801"
  AND "20170331"

ORDER BY operation is quite expensive and cannot be processed in parallel so try to avoid it (or try applying it in a limited result set)

Solution 2

Besides the accepted answer, you might want to partition your table by date to lessen the amount of memory used with an expensive query.

Comments

[HKE]

Thanks Willian. It's working, but can you tell me the reason why it was not working when I use order by.

[Elliott Brossard]

There were too many rows to hold in memory on a single node. If you look at the "Explanation" tab for the query, it will show where it ran out of memory.

[HKE]

Thanks @ElliottBrossard

[HKE]

The above query is to pull the GA data and by default it is partitioned by date. _TABLE_SUFFIX BETWEEN "20160801" AND "20170331" This is how I am pulling data from different date ranges

[Rutger Hofste]

I encountered the same issue. Even weirder, the query succeeded in the web UI but not in the python API. Removed ORDER BY clauses solved the issue but it's a bit weird to experience the discrepancy.

[Islam Azab]

I know this isa bit old, but does OVER (PARTITION BY ... has the same effect?