"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