"teradata" avro "bigquery" "google analytics"
#standardSQL
CREATE TEMP FUNCTION customDimensionByIndex(indx INT64, arr ARRAY<STRUCT<index INT64, value STRING>>) AS (
(SELECT x.value FROM UNNEST(arr) x WHERE indx=x.index)
);
SELECT
fullvisitorId,
visitId,
hit.hitnumber,
customDimensionByIndex(1, hit.customDimensions),
customDimensionByIndex(2, hit.customDimensions),
customDimensionByIndex(3, hit.customDimensions)
FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`, UNNEST(hits) hit
LIMIT 1000