CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
Damian Gwirc
Damian Gwirc

Reputation: 21

BigQuery _TABLE_SUFFIX for ga_sessions

This query works fine...

SELECT page.* FROM `zentinel-datawarehouse.xxx.ga_sessions_20170601` ga,
UNNEST (hits) hits,
UNNEST (hits.page) page

but when i need use _table_suffix

SELECT page.* FROM `zentinel-datawarehouse.xxx.ga_sessions_*` ga,
UNNEST (hits) hits,
UNNEST (hits.page) page
WHERE _TABLE_SUFFIX>=20170601

Dont works any more...

This happened in thats date because hits.page is record repeteabled... in month 08 or 09 works fine because is record NULL ANY IDEA???

REGARDS

Upvotes: 0

Views: 2002

Answers (2)

Martin Weitzmann
Martin Weitzmann

Reputation: 4746

hits.page is not an array but a struct. You're already cross joining "hits" (which is an array), that should be sufficient. You can only cross join arrays or tables. You want to remove this statement as it only work if hits.page is null:

SELECT
  page.*
FROM
  `project.dataset.ga_sessions_201712*` t, t.hits h
LIMIT
  1000

Upvotes: 0

Pentium10
Pentium10

Reputation: 207992

if you get the below error message

ERROR:Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRUCT at [3:9]

I think some tables have different schema, try to locate when was the schema change applied it might be that jan-feb has one schema, and since march updated schema.

With Google Analytics export you encounter this schema change frequently.

What you can do here, is that you patch your tables, eg: fix the schema in a direction that will help you. Without doing the fix, you would need to have two different queries to target both schema(s) (and more schemas will follow if the GA team changes on the go).

You should have a script that constantly propagates to previous tables all the schema changes they introduce with newer updates.

Upvotes: 1

Related Questions