BigQuery is Google's fully managed, petabyte scale, low-cost analytics data warehouse. The Looker Studio BigQuery connector allows you to access data from your BigQuery tables within Looker Studio.
How to connect to BigQuery
You can connect Looker Studio to a single Google BigQuery table or view, or to a custom query. In the steps below, when instructed to select a connector, choose the BigQuery connector.
Steps to connect
To connect to your data, you can either create a new embedded data source, or select an existing (reusable) data source.
Embedded vs. reusable data sources explained
Data sources can be either embedded or reusable. Reports can include both embedded and reusable data sources.
Data sources that you create while editing a report are embedded in the report. To edit an embedded data source, you do so within that report. Embedded data sources make collaborating on reports and data sources easier. Anyone who can edit the report can also edit the data source, as well as modify its connection. When you share or copy the report, any embedded data sources are shared or copied as well.
Data sources that you create from the home page are reusable. You can reuse these data sources in different reports. Reusable data sources let you create and share a consistent data model across your organization. Only people with whom you share the reusable data source can edit it. Only the owner of the data source's credentials can modify the connection.
Learn more about data sources.
- Sign in to Looker Studio.
- In the top left, click
Create and then select Report.
- The report editor appears and the Add data to report panel opens.
- To create a new embedded data source, select a connector.
-
To select an existing reusable data source, click the My data sources tab, then select a data source that you've created previously or that has been shared with you. If needed, you can edit that data source;s connection to your dataset.
-
- Configure the connection to your dataset. The following sections provide more details on the connection options.
- In the bottom right, click Add.
Create and then select Report.In a moment, you'll see a table appear on the report canvas, containing fields from the data source. Use the properties panel on the right of the canvas to change the data and style of the table. Use the toolbar above the canvas to add more charts, controls, and other components to your report.
New to Looker Studio?
Take the Create a report tutorial. Or learn more about the report editor.
Configure the data source connection
Select a project
Projects organize your BigQuery resources and provide for billing if your reports exceed BigQuery's free quotas. You can use the same project for both billing and data management, or you can use one project for the data while billing another project. Learn more about Google Cloud Projects.
Choose one of the following options to select your project(s):
RECENT PROJECTS
Use this option to find a project you've accessed recently in the Google Cloud console. You can also enter the project ID manually. The project you choose is used both for billing and data access. After selecting a project, you'll select a dataset.
MY PROJECTS
Use this option to select any project to which you have access. You can also enter the project ID manually. The project you choose is used both for billing and data access. After selecting a project, you'll select a dataset.
SHARED PROJECTS
Use this option to access a shared project. You can select different projects for data and billing, if desired. After selecting a project, you'll select a dataset.
CUSTOM QUERY
Use this option to specify a billing project for a custom query. You can use the same project or a different one for access to your data. After selecting a billing project, you'll compose your query, in which you can specify a different project for the data in the FROM clause, as shown in the screenshot below:
PUBLIC DATASETS
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.
Select a dataset
Datasets are used to organize and control access to your data. Select a dataset from the list, or search for a dataset by name.
Connect to a table or view
A BigQuery tables contains individual records organized in rows. Each record is composed of columns (also called fields). A BigQuery views is a virtual table defined by a SQL query. You can connect a Looker Studio data source to a single table or view, or to a custom query (see below).
Connect to a date partitioned table
This option appears in the configuration column when you select a date-partitioned table. If the partition filter is required, the option is selected by default. If the partition filter is optional, the option is unselected by default. If the table contains one or more valid Date or Datetime fields, you can choose which field to use as the date range dimension in the data source. Learn more about date-partitioned tables in BigQuery.
Connect using a custom query
Select the CUSTOM QUERY option to provide a SQL query instead of connecting to a single table. Looker Studio uses this custom SQL as an inner select statement for each generated query to the database.
Custom SQL queries may only have a single statement.
For example, the following will not work because it has multiple SQL statements:
DECLARE cost_per_tb_in_dollar FLOAT64 DEFAULT 4.2;
SELECT total_bytes_billed / (1024 * 1024))* cost_per_tb_in_dollar)/(1024*1024))) FROM billing-table;
This is also true when exploring from BigQuery: your query result set may only be based on a single statement.
- Simplify the query so that it runs faster.
- Run the query in your database and store the results in a separate table. Then connect to that table in your data source.
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 Looker 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.
Edit the data source
The data source controls things like who can view the data and how often that data refreshes. You can also add calculated fields and parameters to the data source. To edit the data sources added to your report:
- In the menus, select Resource > Manage added data sources.
- Locate the data source in the list, then to the right, click Edit
.
.Learn more about editing a data source.
Notes
BigQuery is a paid product and you will incur BigQuery usage costs when accessing BigQuery through Looker Studio. Looker 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 Looker 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, Looker 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, Looker 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, Looker 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 Looker 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
Looker Studio can connect to BigQuery projects protected by VPC Service Control (VPC-SC) perimeters via viewer IP-based access levels. The BigQuery connector passes the report viewer's IP address to BigQuery, which can then enforce any IP-based access levels that have been set up.
Visualize BigQuery GEOGRAPHY polygons
You can display GEOGRAPHY polygons using a Google Maps visualization in your report. Click here for a tutorial.
Identify Looker Studio queries with job labels
All queries sent by Looker Studio to BigQuery have the BigQuery job label requestor:looker_studio. You can use this job label to identify BigQuery queries related to Looker Studio. See viewing job labels for instructions.