Google BigQuery

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 are not 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

Make the connection and set up the data source

  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 will be prompted to close the browser.



    Option 2:

    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 will be prompted to close the browser.

  3. 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 drop-down list, select a billing project. If you don't select a billing project, EmptyProject appears in the field after you have 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.

- 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 is not 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 Online, 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 Online 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 performance of large result sets:

bq-fetch-tasks Number of parallel background tasks to use when fetching data using HTTP. The default is 10.

Number of rows to fetch in each batch for spool queries. The default is 50000.

The following attributes are also available and are primarily 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 this attribute on.

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 file are read and included by Tableau Desktop when the data source or workbook is published to Tableau Online or Tableau Server.

Important: Tableau does not 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 is 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' />
    <customization name='bq-fetch-tasks' value='10' />
    <customization name='bq-large-fetch-rows' value='10000' />

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='' 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='' 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 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 in order to write level of detail expressions or to take advantage of other improvements. In this case, make sure that the Use Legacy SQL option is not selected.

  • You are creating a new workbook that needs to connect to a legacy SQL view. You can’t mix legacy SQL with standard SQL, so you need to 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 are 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.

Troubleshoot Google BigQuery issues

Connections to multiple accounts

When using web authoring or publishing to the web, you cannot 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 Online. 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 Online to match those credentials.

Web authoring with Internet Explorer 11 and Edge

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

See also

Thanks for your feedback!