CODEWITHSUNDEEP
google-bigquery
Pentium10
Pentium10

Reputation: 207863

Workaround for Wildcard pattern hits.* cannot be used to refer to fields in a union

The data that comes out from the BigQuery implementation of GoogleAnalytics raw data has multiple repeated fields. To access a sample DB set on BigQuery read here it's free.

|- fullVisitorId
+- hits
    +- eCommerceAction
    +- customVariables
    +- customDimensions
    +- customMetrics
    +- product
        +- customDimensions
        +- customMetrics
    +- promotion
    +- experiment

when using this query: 

from flatten((select hits.*,fullVisitorId from (TABLE_DATE_RANGE([0.ga_sessions_], TIMESTAMP('2017-01-12'), TIMESTAMP('2017-01-13'))) ),hits)

we get:

Error: 0.0 - 0.0: Wildcard pattern hits.* cannot be used to refer to fields in a union

What would be an applicable workaround to overcome this nasty error.

Upvotes: 0

Views: 173

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

Some ideas:

  • Use standard SQL instead (this is probably the most maintainable option).
  • Enumerate all columns explicitly instead of using .* inside the FLATTEN.
  • Use filters such as OMIT RECORD IF or scoped aggregations rather than flattening.
  • Use a "flattening" operator such as ORDER BY or GROUP BY to remove the repetition. This will probably have a performance impact, though.

Upvotes: 2

Related Questions