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?

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

Answer 1

I think your query might work if you remove the ordering applied in the end.

Just try this:

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"

I would suggest you to avoid ORDER BY operation(or try applying it in a limited result set) in such cases because it is quite expensive and cannot be processed in parallel.