0 votes
1 view
in Big Data Hadoop & Spark by (11.5k points)

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

1 Answer

0 votes
by (24.8k points)

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.

...