5

I am running the following query on Google BigQuery web interface, for data provided by Google Analytics:

SELECT *
FROM [dataset.table]
WHERE
  hits.page.pagePath CONTAINS "my-fun-path"

I would like to save the results into a new table, however I am obtaining the following error message when using Flatten Results = False:

Error: Cannot query the cross product of repeated fields customDimensions.value and hits.page.pagePath.

This answer implies that this should be possible: Is there a way to select nested records into a table?

Is there a workaround for the issue found?

Community
  • 1
  • 1
Silvia Pina
  • 323
  • 4
  • 21
  • 1
    related: http://stackoverflow.com/questions/25583878/flattening-google-analytics-data-with-repeated-fields-not-working-anymore – Pentium10 May 22 '15 at 16:50
  • Related but not helpful, unfortunately, since the purpose is to get unflattened results. – Silvia Pina May 22 '15 at 17:21

3 Answers3

6

Depending on what kind of filtering is acceptable to you, you may be able to work around this by switching to OMIT IF from WHERE. It will give different results, but, again, perhaps such different results are acceptable. The following will remove entire hit record if (some) page inside of it meets criteria. Note two things here:

  • it uses OMIT hits IF, instead of more commonly used OMIT RECORD IF).
  • The condition is inverted, because OMIT IF is opposite of WHERE

The query is:

SELECT *
FROM [dataset.table]
OMIT hits IF EVERY(NOT hits.page.pagePath CONTAINS "my-fun-path")
Mosha Pasumansky
  • 13,206
  • 5
  • 32
  • 55
  • This is probably suitable for my purposes for now, but I am not sure if for future queries it will be so. Would it be suitable to create a new feature request for this functionality, since the query I originally posted should return a subset of the original table instead of the error reported? And thank you very much! – Silvia Pina May 26 '15 at 10:43
3

Update: see the related thread, I am afraid this is no longer possible. It would be possible to use NEST function and grouping by a field, but that's a long shot.

Using flatten call on the query:

SELECT *
FROM flatten([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910],customDimensions)
WHERE
  hits.page.pagePath CONTAINS "m"

Thus in the web ui:

  • setting a destination table
  • allowing large results
  • and NO flatten results

does the job correctly and the produced table matches the original schema.

Community
  • 1
  • 1
Pentium10
  • 204,586
  • 122
  • 423
  • 502
  • Thanks for your reply, but this is not what is intended. I just want to subset the table into a new one, while keeping the original structure. The solution that you propose introduces flattening over the field inputted in the FLATTEN() function, regardless of the fact that "Flatten Results" is not ticked. – Silvia Pina May 22 '15 at 16:31
1

I know - it is old ask. But now it can be achieved by just using standard SQL dialect instead of Legacy

#standardSQL
SELECT t.*
FROM `dataset.table` t, UNNEST(hits.page) as page
WHERE
  page.pagePath CONTAINS "my-fun-path"
Alexey Maloletkin
  • 1,089
  • 7
  • 7