1 2 Previous Next 17 Replies Latest reply on Feb 7, 2018 7:53 AM by Carlos Palminha

    BigQuery issue on Tableau

    Nuttakorn Rattanachaisit

      We can't get the BigQuery data that came from Google Analytics Premium on Tableau recently. It used to work.

      It returns the error message like this

      The Google BigQuery service was unable to compile the query. Unrecognized name: device.browser at [1:8]

      The Google BigQuery service was unable to compile the query. Unrecognized name: customDimensions.index; Did you mean customDimensions? at [1:80]

      As we haven't done on changes on BigQuery structure. Any idea to solve the issue?

        • 1. Re: BigQuery issue on Tableau
          Yuriy Fal

          Hi Nuttakorn,

           

          Is it a chance that you've updated Tableau to version 10.1 recently?

          There are significant changes in how Tableau behaves with BQ

          due to the recent changes in BQ engine which now supports ANSI SQL.

           

          Migrating from legacy SQL  |  BigQuery Documentation  |  Google Cloud Platform

           

          Tthe problem I've encountered when opening "old" workbooks in version 10.1

          is mainly about data types. Tableau detects some fields as strings now,

          though they were detected as integers when opening in Tableau 10.0.

          I outta create additional calculations to manually convert data type.

           

          So if possible, stay with version 10.0 for a while,

          and gradually move your content to version 10.1,

          since changes are inevitable.

           

          Yours,

          Yuri

          • 2. Re: BigQuery issue on Tableau
            Nuttakorn Rattanachaisit

            Hi Yuri,


            Thanks for your answer. We can pull data by using tableau 10.0 but when we try to use multiple table (as Google Analytics 360) they put each day as separate table. We can query only few dimension.

             

             

            We use this custom SQL to pull data

             

            SELECT   hits.page.pagePath

            FROM   (TABLE_DATE_RANGE([38110106.ga_sessions_],

                                TIMESTAMP('2016-11-1'),

                                TIMESTAMP('2016-11-27')))

             

            but it shows return error as

             

            • The Google BigQuery service was unable to compile the query.
            • Field 'hits_page_pagePath' not found.

             

            But we can pull data from fullvisitorid dimension

             

            SELECT  fullvisitorid

            FROM   (TABLE_DATE_RANGE([38110106.ga_sessions_],

                                TIMESTAMP('2016-11-1'),

                                TIMESTAMP('2016-11-27')))

            • 3. Re: BigQuery issue on Tableau
              Yuriy Fal

              Strange,

               

              Could you please try this:

              SELECT   hits.page.pagePath as hits_page_path

              FROM   (TABLE_DATE_RANGE([38110106.ga_sessions_],

                                  TIMESTAMP('2016-11-1'),

                                  TIMESTAMP('2016-11-27')))

               

              Yours,

              Yuri

              • 4. Re: BigQuery issue on Tableau
                Ken McDonald

                We are seeing something similar.  Everything worked until today.

                 

                We are getting this error - Expected "]" but got "ga_sessions_" at [1:501]

                 

                Query statement is below...

                 

                SELECT

                  cast(hits.transaction.transactionId as integer) as team_id,

                  lower(FIRST(hits.appInfo.landingScreenName)) as landing_page,

                  lower(FIRST(trafficSource.campaign)) as campaign,

                  lower(FIRST(trafficSource.source)) as source,

                  lower(FIRST(trafficSource.medium)) as medium,

                  lower(FIRST(trafficSource.keyword)) as keyword,

                  FIRST(device.deviceCategory) as device_category,

                  fullVisitorId as visitor_id

                FROM

                TABLE_DATE_RANGE([11759424.ga_sessions_],TIMESTAMP('2015-01-01'),CURRENT_TIMESTAMP())

                 

                where hits.transaction.transactionId is not null

                group by team_id, visitor_id

                • 5. Re: BigQuery issue on Tableau
                  Yuriy Fal

                  Hi Ken,

                   

                  Not able to test, so using a shotgun :-)

                   

                  Could you try this:

                   

                  SELECT

                    cast(hits.transaction.transactionId as integer) as team_id,

                    lower(FIRST(hits.appInfo.landingScreenName)) as landing_page,

                    lower(FIRST(trafficSource.campaign)) as campaign,

                    lower(FIRST(trafficSource.source)) as source,

                    lower(FIRST(trafficSource.medium)) as medium,

                    lower(FIRST(trafficSource.keyword)) as keyword,

                    FIRST(device.deviceCategory) as device_category,

                    fullVisitorId as visitor_id

                  FROM

                  ( TABLE_DATE_RANGE([11759424.ga_sessions_],TIMESTAMP('2015-01-01'),CURRENT_TIMESTAMP()) )

                   

                  where hits.transaction.transactionId is not null

                  group by team_id, visitor_id

                   

                  or this:

                   

                  SELECT

                    cast(hits.transaction.transactionId as integer) as team_id,

                    lower(FIRST(hits.appInfo.landingScreenName)) as landing_page,

                    lower(FIRST(trafficSource.campaign)) as campaign,

                    lower(FIRST(trafficSource.source)) as source,

                    lower(FIRST(trafficSource.medium)) as medium,

                    lower(FIRST(trafficSource.keyword)) as keyword,

                    FIRST(device.deviceCategory) as device_category,

                    fullVisitorId as visitor_id

                  FROM

                  TABLE_DATE_RANGE([11759424].[ga_sessions_],TIMESTAMP('2015-01-01'),CURRENT_TIMESTAMP())

                   

                  where hits.transaction.transactionId is not null

                  group by team_id, visitor_id

                   

                  or may be the combo of the two :-)

                   

                  SELECT

                    cast(hits.transaction.transactionId as integer) as team_id,

                    lower(FIRST(hits.appInfo.landingScreenName)) as landing_page,

                    lower(FIRST(trafficSource.campaign)) as campaign,

                    lower(FIRST(trafficSource.source)) as source,

                    lower(FIRST(trafficSource.medium)) as medium,

                    lower(FIRST(trafficSource.keyword)) as keyword,

                    FIRST(device.deviceCategory) as device_category,

                    fullVisitorId as visitor_id

                  FROM

                  ( TABLE_DATE_RANGE([11759424].[ga_sessions_],TIMESTAMP('2015-01-01'),CURRENT_TIMESTAMP()) )

                   

                  where hits.transaction.transactionId is not null

                  group by team_id, visitor_id

                   

                  Hope some would work for you.

                   

                  Yours,

                  Yuri

                  • 6. Re: BigQuery issue on Tableau
                    Ken McDonald

                    Sadly, no joy on all three options.  It is the same error message as before.  Anything else to try?

                    • 7. Re: BigQuery issue on Tableau
                      Yuriy Fal

                      Hi Ken,

                       

                      What version of Tableau are you on?

                      • 9. Re: BigQuery issue on Tableau
                        Yuriy Fal

                        Then use ` as a delimiter instead of [ and ]

                        • 10. Re: BigQuery issue on Tableau
                          Ken McDonald

                          When I use that character, I get the following error:

                           

                          Table-valued functions are not supported at [1:64]

                          1 of 1 people found this helpful
                          • 11. Re: BigQuery issue on Tableau
                            Yuriy Fal

                            Agh, yeah, TABLE_DATE_RANGE()

                            is a Legacy SQL (aka bQL) function,

                            and it is not supported in Standard SQL,

                            which is a language Tableau 10.1.x speaks with BQ.

                             

                            So you're opt to go with Tableau 10.0.x instead.

                            You may want to install both versions side-by-side

                            and use which one is appropriate to your tasks.

                             

                            If you have copies of your old workbooks, it's fine.

                            Just use them with 10.0.x. If not, you could open a wb

                            in any plain text editor (it's an XML after all) and change

                            any occurrence of  the string

                            version='10.1'

                            back to

                            version='10.0'

                             

                            Hope it works for you.

                             

                            Yours,

                            Yuri

                            1 of 1 people found this helpful
                            • 12. Re: BigQuery issue on Tableau
                              Ken McDonald

                              But that means we are going to be stuck on 10.0 forever.  That seems highly unpractical.  One of the main reasons people use BigQuery is to access Google Analytics data.  Please reconsider.

                              1 of 1 people found this helpful
                              • 13. Re: BigQuery issue on Tableau
                                Yuriy Fal

                                You may try to modify your query like this:

                                 

                                SELECT

                                  cast(hits.transaction.transactionId as integer) as team_id,

                                  lower(FIRST(hits.appInfo.landingScreenName)) as landing_page,

                                  lower(FIRST(trafficSource.campaign)) as campaign,

                                  lower(FIRST(trafficSource.source)) as source,

                                  lower(FIRST(trafficSource.medium)) as medium,

                                  lower(FIRST(trafficSource.keyword)) as keyword,

                                  FIRST(device.deviceCategory) as device_category,

                                  fullVisitorId as visitor_id

                                FROM

                                  `11759424.ga_sessions_*`

                                WHERE

                                _TABLE_SUFFIX >= '20150101'

                                AND hits.transaction.transactionId is not null

                                GROUP BY team_id, visitor_id

                                 

                                Yours,

                                Yuri

                                • 14. Re: BigQuery issue on Tableau
                                  Ken McDonald

                                  We had to tweak what you gave us a bit, but if you use a wildcard in the table name (e.g., SELECT fullVisitorId FROM `teamsnap-1300.11759424.ga_sessions_*` ), this works.  Thank you!

                                  1 2 Previous Next