Search
Clear search
Close search
Google apps
Main menu

BigQuery connector

Connect Data Studio to BigQuery tables.

BigQuery is Google's fully managed, petabyte scale, low-cost analytics data warehouse. The Data Studio BigQuery connector allows you to access data from your BigQuery tables within Data Studio.

BigQuery is a paid product and you will incur BigQuery usage costs when accessing BigQuery through Data Studio.

 

In this article:

How to connect to BigQuery

You can can connect Data Studio to a single Google BigQuery table or view. When creating a BigQuery data source, you can choose from the following options:

My Projects

Use this option to select a full table in a project to which you have access. The data source editor allows you to traverse the Project, Data Set, and Table hierarchy.

Shared Projects

Use this to access a shared data set, and use a different project for billing purposes.

You can also use this option to access BigQuery public data sets. BigQuery offers a number of public samples, where the dataset is shared, but not the project. To query this data, you must specify your own project, which will be used to bill for processing costs on the shared data.

To access a shared project:

  1. Enter in the project ID. For example, BigQuery’s public data is in the project bigquery-public-data
  2. Select a data set and table.
  3. Specify a project that you have access to

Custom Query

Use this option to specify a BigQuery table using a SQL query. The results of the query will be in table format, which becomes the field definition (schema) for your data source.

The Custom Query option lets you leverage BigQuery’s full query capabilities, including joins, unions, and analytical functions.

How custom queries work

Data Studio generates SQL queries to access data from BigQuery tables. Data Studio uses your SQL as an inner select statement for each generated query to BigQuery.

To use Custom Query, first select a project you have access to. This will be used for billing purposes. Next, input your SQL query.

Custom Query Syntax

The custom query syntax should follow the BigQuery’s SQL dialect with the following caveats:

  1. Comments in the SQL statement are not supported
  2. Field names and aliases in the SELECT statement must not contain a period (.) To work around this you can do one of the following:
     
    1. Uses aliases to replace each period with an underscore (_)

      Example:
      SELECT category.product AS category_product FROM …
       
    2. Create a view within BigQuery and reference the view in the SQL statement or select the view under My Projects.

You can use the BigQuery user interface to test that your query works, then copy and paste that query into Data Studio.

Google data integrations

Some Google products export data directly into BigQuery tables. When recognized, Data Studio will automatically enrich the fields to include common aggregations, calculations, and field names.

Google Analytics BigQuery export

When Data Studio encounters a table generated by Google Analytics BigQuery Export, the table will have a Google Analytics icon next to it. When selected, many of the common metrics found in Google Analytics will automatically be created as Data Studio fields.

Notes

BigQuery is a paid product and you will incur BigQuery usage costs when accessing BigQuery through DataStudio. Data Studio will issue queries to BigQuery during report editing, report caching, and occasionally during report viewing.

Multi-day Tables

BigQuery supports querying across multiple tables, where each table has a single day of data. The tables have the format of YYYYMMDD. When Data Studio encounters a table that has the format of YYYYMMDD, the table will be marked as a multi-day table and only the name prefix_YYYYMMDD will be displayed in the table select.

When a chart is created to visualize this table, Data Studio will automatically create a default date range of the last 28 days, and properly query the last 28 tables. You can configure this setting by editing the report, selecting the chart, then adjust the Date Range properties in the chart's Properties Panel > DATA tab.

Caching

To speed up the user experience, Data Studio Reports will try to asynchronously fetch data from BigQuery every 12 hours by default. Each of these fetches can incur BigQuery costs. The cache can be disabled by editing the report and selecting File > Reports and Theme Settings > Data tab > Cache control.

Caching and Report Viewing

Once the data is cached, when a report is viewed, Data Studio will try to access the data from cache, and therefore minimize BigQuery costs.

Caching is a best effort feature and might not always be possible depending on query response size.

Viewing SQL issued to BigQuery

You can view all the BigQuery SQL Data Studio generates from within the BigQuery Query History user interface.

Was this article helpful?
How can we improve it?
What's new in Data Studio

Learn about new features and recent changes.