Search code examples
sqlgoogle-bigquerycustom-dimensions

Error in getting value in custom dimension in google bigquery

I got a question about extracting custom dimension in the google big query. This question is already asked by some people already, however, the solution is not working..

The question is, when I tried to extract the information for the custom dimension like this 

SELECT
fullvisitorId,
visitid,
hit.hitnumber,
(SELECT x.value FROM UNNEST(hit.customDimensions) x WHERE x.index = 1) as productCategory,
(SELECT x.value FROM UNNEST(hit.customDimensions) x WHERE x.index = 2) as loyaltyClass,
(SELECT x.value FROM UNNEST(hit.customDimensions) x WHERE x.index = 3) as existingCustomer
FROM [<id>.ga_sessions_20180805],UNNEST(hits) as hit
LIMIT 100

Then I got an error "Table name "hits" cannot be resolved: dataset name is missing."

I tried to use the solutions from others like this

SELECT
    fullvisitorId,
    visitid,
    hit.hitnumber,
    (SELECT x.value FROM UNNEST(hit.customDimensions) x WHERE x.index = 1) as productCategory,
    (SELECT x.value FROM UNNEST(hit.customDimensions) x WHERE x.index = 2) as loyaltyClass,
    (SELECT x.value FROM UNNEST(hit.customDimensions) x WHERE x.index = 3) as existingCustomer
FROM `<id>.ga_sessions_*`, UNNEST(hits) AS h
WHERE _TABLE_SUFFIX = '20180805'

Then I got another error Invalid table name: <id>.ga_sessions_* [Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)].

Update: I even tried the most basic query 

    SELECT
      *
    FROM [<id>.ga_sessions_20180805]
    LEFT JOIN UNNEST(hits) as hits
   LIMIT 10

Still returns the same error....

What is the error that I made for both script? And how can I get the custom dimension value?

Many thanks!

Solution

  • you can use case when which is supported by all 

        SELECT
    fullvisitorId,
    visitid,
    h.hitnumber,
    case when x.index = 1 then x.value end as productCategory,
    case when x.index = 2 then x.value end as loyaltyClass,
    case when x.index = 3 then x.value end as existingCustomer
    FROM [<id>.ga_sessions_20180805]
    LEFT JOIN UNNEST( hits ) as h
   WHERE _TABLE_SUFFIX = '20180805'

    Note: enable standard SQL for the query, or use the new BigQuery UI