Bigquery union/join error

Question

I am getting an error when trying to pull from my google analytics bigquery export tables... I want to look at a month's worth of data with some filters (including one that narrows it down to a list of specific fullvisitorids of interest). However, when I run the following query, I get this error:

Error: (L2:1): JOIN (including semi-join) and UNION ALL (comma) may not be combined in a single SELECT statement. Either move the UNION ALL to an inner query or the JOIN to an outer query.

select date, fullvisitorid, visitid, visitstarttime, visitnumber, hits.hitNumber, hits.page.pagePath, hits.page.pageTitle, hits.type --and other columns
FROM (TABLE_DATE_RANGE([mydata.ga_sessions_],TIMESTAMP('2015-02-01'),TIMESTAMP('2015-02-28')))
where fullvisitorid in (select * from [mydata.visitorid_lookup]) --table includes a list of fullvisitorids I am interested in
and device.browser!='Internet Explorer'
and lower(hits.page.pagePath) not like '%refer%'
and lower(hits.page.pagePath) like '%sample%'

So I change my query to this:

select * from (
   select date, fullvisitorid, visitid, visitstarttime, visitnumber, hits.hitNumber, hits.page.pagePath, hits.page.pageTitle, hits.type
   FROM (TABLE_DATE_RANGE([mydata.ga_sessions_],TIMESTAMP('2015-02-01'),TIMESTAMP('2015-02-28')))
   where device.browser!='Internet Explorer'
   and lower(hits.page.pagePath) not like '%refer%'
   and lower(hits.page.pagePath) like '%sample%')
where fullvisitorid in (select * from [mydata.visitorid_lookup_test])

Which then gives me an error saying response is too large to return. It would be cut down significantly if the where statement for fullvisitorid was being executed within the subquery, but of course that doesn't seem possible. So I feel like I'm between a rock and a hard place on this... Is there another way I am missing? Thanks!

Mosha Pasumansky Mosha Pasumansky · Accepted Answer · 2015-03-04T17:17:12

The "result is too large" error applies to the final result of the query, which means that the result is too large even after semijoin in WHERE is applied. This should work though if you use "Allow Large Results" setting.

Bigquery union/join error

1 Answers