CODEWITHSUNDEEP
sqlgoogle-bigquerycustom-dimensions
Pak Hang Leung
Pak Hang Leung

Reputation: 389

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!

Upvotes: 1

Views: 964

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173056

In your first query - you should just fix table reference in below line 

FROM [<id>.ga_sessions_20180805],UNNEST(hits) as hit

to something like 

FROM `yourproject.yourdataset.ga_sessions_20180805`,UNNEST(hits) as hit

Similar fix for second query, but in addition - alias h should be replaced with hit as below 

FROM `yourproject.yourdataset.ga_sessions_*`, UNNEST(hits) AS hit

Note: above is for BigQuery Standard SQL - so you can add to the very top of your query below line as a first line 

#standardSQL

For example 

#standardSQL     
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 `yourproject.yourdataset.ga_sessions_20180805`,UNNEST(hits) as hit 
LIMIT 100

Upvotes: 4

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

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

Upvotes: 3

Related Questions