Tableau Desktop and Web Authoring Help

This article describes how to connect Tableau to Google BigQuery and set up the data source.

Note: Some of the features and customisations listed in this article aren't supported in Tableau Prep Builder. For more information about connecting your data in Prep Builder, see Connect to Data(Link opens in a new window).

Before you begin

Before you begin, gather this connection information:

• Google BigQuery email or phone, and password

Recommendation

Set up a custom OAuth Client to use your company's IT policies

You can fully control your OAuth configuration according to your own IT policies with a custom OAuth client. The option to use your own OAuth client frees you from being tied to Tableau release cycles and Tableau’s OAuth client rotation schedule. For information on how to configure your own OAuth client, see Configure Custom OAuth for a site.

Make the connection and set up the data source

Note:Note: To use service account credentials for a new Google BigQuery data source, the connection must be created in Tableau Desktop.

1. Start Tableau and under Connect, select Google BigQuery.

   Complete one of the following 2 options to continue.

    

   Option 1:

   1. In Authentication, select Sign In Using OAuth.
   2. Click Sign In.
   3. Enter your password to continue.
   4. Select Accept to allow Tableau to access your Google BigQuery data. You’ll be prompted to close the browser.

   

    

    

   Option 2:

   Tip: If you’re using Tableau Server or Tableau Cloud for publishing, you have to start with logging in to the product you’re using, and then add your saved Google BigQuery credentials under account settings. This will prevent an “Invalid credentials error”.

   1. In Authentication, select Sign In Using Service Account (JSON) file.
   2. Enter the file path or use the Browse button to search for it.
   3. Click Sign In.
   4. Enter your password to continue.
   5. Select Accept to allow Tableau to access your Google BigQuery data. You’ll be prompted to close the browser.



 

2. On the data source page, do the following:

   1. (Optional) Select the default data source name at the top of the page, and then enter a unique data source name for use in Tableau. For example, use a data source naming convention that helps other users of the data source figure out which data source to connect to.

   2. (Optional) From the Invoicing Project drop-down list, select an invoicing project. If you don't select a billing project, EmptyProject appears in the field after you’ve selected the remaining fields.

   3. From the Project drop-down list, select a project. Alternatively, select publicdata to connect to sample data in BigQuery.

   4. From the Dataset drop-down list, select a data set.

   5. Under Table, select a table.

      Use custom SQL to connect to a specific query rather than the entire data source. For more information, see Connect to a Custom SQL Query.

Notes:
- Google BigQuery has changed support from BigQuery legacy SQL (BQL) to standard SQL. Your workbooks will upgrade to support standard SQL when you open them in Tableau.
- Because of the large volume of data in BigQuery, Tableau recommends that you connect live.
- The Radians() function isn’t supported in Google BigQuery.

Use customisation attributes to improve query performance

Note: Customisation attributes aren't currently supported in Tableau Prep Builder.

You can use customisation attributes to improve the performance of large result sets returned from BigQuery to Tableau Cloud, Tableau Server and on Tableau Desktop.

You can have the customisation attributes included in your published workbook or data source, as long as you specify the attributes before you publish the workbook or data source to Tableau Cloud or Tableau Server.

Use Google BigQuery customisation attributes

Customisation attributes accept integer values and affect both live queries and extract refreshes for the specified connection.

The following attributes help the most to increase performance of large result sets:

The following attributes are also available and are mainly used for small queries:

This capability setting accepts yes or no values and can be useful when testing:

How Tableau returns rows from Google BigQuery

Tableau uses two approaches to return rows from BigQuery: the default non-spool approach, or the temp table (spool) approach:

• On the first attempt, queries are executed using the default, non-spool query, which uses the bq-fetch-rows setting.

• If the result set is too large, the BigQuery API returns an error and the Tableau BigQuery connector retries the query by saving the results into a BigQuery temp table. The BigQuery connector then reads from that temp table, which is a spool job that uses the bq-large-fetch-rows setting.

How to specify the attributes

You can specify attributes in one of two ways: in a Tableau Datasource Customisation .tdc file, or in the workbook or data source XML.

Specify attributes in a .tdc file

To specify customisation attributes during a publish workbook or publish data source operation from Tableau Desktop, follow these steps:

1. Create an XML file that contains the customisation attributes.

2. Save the file with a .tdc extension, for example, BigQueryCustomization.tdc.

3. Save the file to the My Tableau Repository\Datasources folder.

The customisation attributes in the .tdc file are read and included by Tableau Desktop when the data source or workbook is published to Tableau Cloud or Tableau Server.

Important: Tableau doesn’t test or support TDC files. These files should be used as a tool to explore or occasionally address issues with your data connection. Creating and maintaining TDC files requires careful manual editing, and there’s no support for sharing these files.

Example of a .tdc file with recommended settings for large extracts

<connection-customization class='bigquery' enabled='true' version='8.0' >

  <vendor name='bigquery' />

  <driver name='bigquery' />

  <customizations>

    <customization name='bq-fetch-tasks' value='10' />

    <customization name='bq-large-fetch-rows' value='10000' />

  </customizations>

</connection-customization>

Manually embed attributes in the XML of the workbook or data source file

You can manually embed customisation attributes inside the 'connection' tag in the workbook .twb file or the data source .tds file. The BigQuery customisation attributes are bold in the following example to make them easier for you to see.

Example of manually embedded attributes

<connection CATALOG='publicdata' EXECCATALOG='some-project-123' REDIRECT_URI='some-url:2.0:oob' SCOPE='https://www.googleapis.com/auth/bigquery https://www.googleapis.com/auth/userinfo.profile https://www.googleapis.com/auth/userinfo.email' authentication='yes' bq-fetch-tasks='10' bq-large-fetch-rows='10000' bq_schema='samples' class='bigquery' connection-dialect='google-bql' connection-protocol='native-api' login_title='Sign in to Google BigQuery' odbc-connect-string-extras='' project='publicdata' schema='samples' server='googleapis.com/bigquery' server-oauth='' table='wikipedia' username=''>

Check if your workbook uses standard SQL or legacy SQL

In 2016, Google updated the BigQuery APIs to support standard SQL, in addition to still supporting BigQuery SQL (now called legacy SQL). Starting in Tableau 10.1, the Google BigQuery connector has been upgraded to support standard SQL, and also still supports legacy SQL. Standard SQL enables users of the BigQuery connector to use level of detail expressions, get faster metadata validation, and select an invoicing project with the connection.

Now, when you create a new workbook, Tableau supports standard SQL by default. Tableau also supports legacy SQL using the Use Legacy SQL option on the Data pane. For example, when you open a workbook that was created using a previous version of Tableau Desktop, and if your workbook uses legacy SQL, the Use Legacy SQL option is selected.

You might want to configure the Use Legacy SQL option for the following reasons:

• You have an existing workbook that you want to update to use standard SQL to write level of detail expressions or to take advantage of other improvements. In this case, make sure that the Use Legacy SQL option isn’t selected.

• You’re creating a new workbook that must connect to a legacy SQL view. You can’t mix legacy SQL with standard SQL, so you must select the Use Legacy SQL option for the workbook to function.

In Google BigQuery, views are written in standard SQL or legacy SQL. You can join views written in standard SQL to views written in standard SQL, or views written in legacy SQL to views written in legacy SQL, and you can join views written in either version of SQL to a table. But you can’t join views written in standard SQL and views written in legacy SQL in one workbook. When you join views, you must set the Use Legacy SQL tick box to correspond to the SQL type used in the view you’re connecting to.

Note: Tableau Desktop provides limited support for nested data when you use legacy SQL or standard SQL. For example, if a table contains nested data and you're using legacy SQL or standard SQL, on the data source page, Update Now won't work.

For more information about migrating from legacy SQL to standard SQL, see Migrating from legacy SQL(Link opens in a new window) on the Google Cloud Platform website.

Use BigQuery BI Engine to Analyse Data

You can use BigQuery BI Engine to perform fast, low-latency analysis services and interactive analytics with reports and dashboards backed by BigQuery. For more information, including instructions on how to integrate BigQuery BI Engine with Tableau, see Analyse BigQuery data by using BI Engine and Tableau in Google's documentation.

Troubleshoot Google BigQuery issues

Connections to multiple accounts

When using web authoring or publishing to the web, you can’t use multiple Google BigQuery accounts in the same workbook. You can have multiple Google BigQuery account connections in Desktop.

When publishing flows, the credentials that you use to connect to Google BigQuery in your Input step must match the credentials that are set up in the Settings tab in the My Account Settings page for Google BigQuery in Tableau Server or Tableau Cloud. If you select different credentials or no credentials in your authentication setting when publishing the flow, the flow will fail with an authentication error until you edit the connection for the flow in Tableau Server or Tableau Cloud to match those credentials.

Web authoring with Internet Explorer 11 and Edge

In Internet Explorer 11 and Edge, you can’t access a server using an unsecured connection (http). Use a secure connection (https) or switch to another browser.

See also

• Set Up Data Sources – Add more data to this data source or prepare your data before you analyse it.
• Build Charts and Analyse Data – Begin your data analysis.

• Set up Oauth for Google(Link opens in a new window) -Configure Oauth connections for Tableau Server.
• Oauth Connections(Link opens in a new window) - Configure Oauth connections for Tableau Cloud.
• Google BigQuery & Tableau: Best Practices(Link opens in a new window) - Read the Tableau white paper (registration or sign in required)