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.
How to connect to BigQuery
You can can connect Data Studio to a single Google BigQuery table or view:
- Sign in to Data Studio.
- In the top left, click , then select Data Source.
- Select the BigQuery connector.
- Select a connection option (described below) and provide your connection details.
- In the upper right, click CONNECT.
- The data source fields panel appears.
- The data source is now connected to your data set.
When creating a BigQuery data source, you can choose from the following options:
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.
Use this to access a shared data set, and use a different project for billing purposes.
To connect to a shared project:
- Select a billing project.
- Enter the shared project name.
- Select a data set and table.
- Click CONNECT.
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 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 SQL dialect with the following caveats:
- Comments in the SQL statement are not supported
- Field names and aliases in the SELECT statement must not contain a period (.) To work around this you can do one of the following:
- Below the query input field, uncheck Use Legacy SQL. This will cause your query to use standard SQL, which supports periods in field names and aliases. Learn more about the Standard SQL dialect.
- Uses aliases to replace each period with an underscore (_)
SELECT category.product AS category_product FROM …
- 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.
To add a query parameter:
In the body of your custom query, replace a hard-coded value with an identifier beginning with the @ character. For example: @param_name.
Below the custom query editor, click ADD PARAMETER. The parameters in your query are listed below the editor.
Click a parameter in the list to configure its options:
Name. This is the parameter identifier.
Display name. Determines how the parameter appears to report users.
Data type. Determines the kind of data expected for the parameter.
Input type. Determines how the parameter values are displayed in the data source's Parameters section.
Default value. Report users can override this. If modifying the parameter has been turned off then the default value will be used.
By default, parameters can be modified in reports. Uncheck the box to turn this off. Learn more about allowing data source parameters in reports.
Use the following reserved parameters to create more dynamic queries:
Sets the beginning of the query time frame.
Sets the end of the query time frame.
Passes in the email address of the logged-in user.
To use these parameters:
Below the query editor, turn on the parameters you want to use.
You'll be prompted grant access to your email address when you turn on the @DS_USER_EMAIL parameter.
In your query, be sure to use uppercase for the parameter names.
All parameter values are passed to BigQuery as strings. To handle them as dates, numbers, or other data types in BigQuery, be sure to use an appropriate conversion function, such as PARSE_DATE , PARSE_TIMESTAMP, or CAST.
Use date parameters to pass date ranges to the underlying query
If your report includes a date range control, viewers can use that to request different starting and ending dates from the BigQuery data.
Use the email parameter to provide row-level access to the data
The email parameter lets you show only the data associated with the logged-in user of a report, data source, or exploration. That user must be logged in to a Google account and must consent to providing their email address to Data Studio. If the viewer doesn't consent, all charts in the report based on this data source will display an authorization error.
Custom parameter example:
SELECT word FROM `TABLE` WHERE corpus = @corpus;
Use a string with contains and a number:
SELECT * FROM `bigquery-public-data.baseball.games_post_wide`
WHERE REGEXP_CONTAINS(gameId, @s)
AND attendance > @attendance LIMIT 100;
Use a multi-select string parameter. Note the use of UNNEST to flatten the list of values:
SELECT * from user.users as user WHERE display_name in UNNEST(@name);
Date parameter example:
SELECT creation_date, age, display_name from user.users as user
WHERE creation_date > PARSE_DATE('%Y%m%d', @DS_START_DATE)
AND creation_date < PARSE_DATE('%Y%m%d', @DS_END_DATE);
Email parameter example:
Select * from Sales WHERE sales-rep-email = @DS_USER_EMAIL;
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 project, which will be used to bill for processing costs on the shared data.
Override Billing Project ID
Specify a different Billing Project ID for this custom query.
Use _PARTITIONTIME as time dimension
This option appears when you select a date-partitioned table. By default, the data source uses the table's partition time as the date range dimension. Uncheck this option to use explicit date fields in the table as the date range dimension. Learn more about date-partitioned tables in BigQuery.
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.
Configure the data source
The data source fields panel is where you configure the data source by renaming fields and adding descriptions, adding calculated fields, and changing data types and aggregations. Learn more about working with data source fields.
Control who sees the data
At the top of the fields panel, you can change the data credentials. Credentials control who can see the data provided by this data source.
OWNER'S CREDENTIALS lets other people view or create reports that use this data without requiring them to have their own access to the data set.
VIEWER'S CREDENTIALS, on the other hand, requires each user of the data source to provide their own credentials to access the data set.
Learn more about data credentials.
Create a new report from the data source
- In the upper right, click CREATE REPORT.
- The report editor appears.
- Click ADD TO REPORT.
- This adds the data source to report.
- You can now create charts and controls that get their data from this data source.
New to Data Studio?
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.
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.
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.
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.
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.