Connect to Google BigQuery

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 connect Data Studio to a single Google BigQuery table or view, or to a custom query.

  1. Sign in to Data Studio.
  2. In the top left, click Create, then select Data Source.
  3. Select the BigQuery connector.
  4. Provide your connection details (see below).
  5. In the upper right, click CONNECT.
    1. The data source fields panel appears.
    2. The data source is now connected to your data set.

 

Choose a billing project

Select the BigQuery billing project to be charged if your reports exceed BigQuery's free quotas.

Recent projects

Use this option to find a project you've accessed recently in the GCP Cloud Console.

My projects

Use this option to select any project to which you have access. You can also enter the project ID manually.

Shared projects

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

Choose a dataset and table or view

Datasets are top-level containers that are used to organize and control access to your tables and views. Select a dataset from the list, or search for a dataset by name.

Public datasets

You can use this option to access BigQuery public datasets. 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 billing project, which will be used to bill for processing costs on the shared data.

Connect to a date partitioned table

This option appears in the configuration column when you select a date-partitioned table. By default, the data source uses the table's partition time as the date range dimension. If the table contains one or more valid Date or Datetime fields, you can choose one to serve as the date range dimension in the data source. Learn more about date-partitioned tables in BigQuery.

Custom query

Select the CUSTOM QUERY option to provide a SQL query instead of connecting to a single table. Data Studio uses this custom SQL as an inner select statement for each generated query to the database.

To write your own 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 follows the Standard SQL dialect.

To use the legacy BigQuery SQL dialect, check Use Legacy SQL.

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

Query parameters

Parameters let you build more responsive, customizable reports. You can pass parameters in a BigQuery data source back to the underlying query. To use a parameter in your custom query, follow the syntax guidelines described for running parameterized queries in BigQuery.

Learn more about using parameters in custom queries.

Advanced options

Use the Override Billing Project ID option to specify a different Billing Project ID for your custom query.

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.

BigQuery BI Engine

BigQuery BI Engine is a fast, in-memory analysis service. By using BI Engine, you can analyze data stored in BigQuery with sub-second query response time and with high concurrency. You can accelerate your reports and explorations by connecting Data Studio to a BigQuery table managed by BI Engine.

BI Engine offers up to 1 GB of free capacity to Data Studio users.

Get started using Data Studio with BI Engine.

Notes

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

Learn more about setting up a BigQuery billing account.

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 DATA tab.

Caching

To speed up the user experience, Data Studio reports will try to fetch data from the cache. The BigQuery cache expires every 12 hours by default. Refreshing the cache can incur BigQuery costs.

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.

Learn more about the cache.

Viewing SQL issued to BigQuery

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

Record Count metric

BigQuery data sources automatically provide a default Record Count metric. You can use this to breakdown your dimensions to show the number of records being aggregated by your charts.

Quotas and limits

BigQuery data sources are subject to the same rate limits and quota limits as BigQuery itself.

Support for VPC Service Controls

Data Studio can connect to BigQuery projects protected by VPC Service Control perimeters via viewer IP-based access levels.

Visualize BigQuery GEOGRAPHY polygons

You can display GEOGRAPHY polygons using a Google Maps visualization in your report. Click here for a tutorial.

Was this helpful?
How can we improve it?