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 customizations 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 into the product you’re using, and then add your saved Google BigQuery credentials under account settings. This prevents an “Invalid credentials error”.

   1. In Authentication, select Sign In using the 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 Billing Project dropdown list, select a billing 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 dropdown list, select a project. Alternatively, select publicdata to connect to sample data in BigQuery.

   4. From the Dataset dropdown 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 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 customization attributes to improve query performance

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

You can use customization 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 customization 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 customization attributes

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

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

bq-fetch-tasks	Number of parallel background tasks to use when fetching data using HTTP. The default is 10.
bq-large-fetch-rows	Number of rows to fetch in each batch for spool queries. The default is 50000.

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

bq-fetch-rows	Number of rows to fetch in each batch for non-spool queries. The default is 10000.
bq-response-rows	Number of rows returned in non-spool non-batched queries. The default is 10000.

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

CAP_BIGQUERY_FORCE_SPOOL_JOB

Force all queries to use the temp table approach. The default value is “no.” Change the value to “yes” to turn on this attribute.

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 Customization .tdc file, or in the workbook or data source XML.

Specify attributes in a .tdc file

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

1. Create an XML file that contains the customization 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 customization attributes in the .tdc files are read and included by Tableau Desktop when the data source or workbook is published to Tableau Cloud or Tableau Server.

Important: TDC files can be utilized to customize the functionality of a connection or to diagnose and resolve issues related to connecting to your data. Tableau is unable to thoroughly test every potential modification you might make with these files. Creating and using TDC files requires manual editing and it is crucial to test them thoroughly before deploying them in a production environment.

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 customization attributes inside the 'connection' tag in the workbook.twb file or the data source .tds file. The BigQuery customization 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 a billing project with the connection.

Now, when you create a 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 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 check 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 Analyze 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 Analyze 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 fails 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 analyze it.
• Build Charts and Analyze 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)