Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in Big Data Hadoop & Spark by (11.4k 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 (32.3k 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.

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.6k users

Browse Categories

...