BigQuery is Google's petabyte scale data warehousing solution. Looker Studio natively integrates with BigQuery and can be used to analyze and visualize BigQuery data.
Implementation steps
There are multiple ways to bring your BigQuery data into Looker Studio:
- Using the native BigQuery connector in the Looker Studio UI
- Developing and using a Community Connector
Using the native BigQuery connector in the Looker Studio UI
Users can use the native BigQuery connector in Looker Studio to visualize BigQuery tables or specific queries. You can fetch entire tables or run custom queries on BigQuery from within Looker Studio. It is also possible to use the Looker Studio Explorer feature to complete exploratory analysis of your BigQuery data.
This approach is helpful if your users:
- are doing exploratory analysis.
- are familiar with SQL and can write their own queries.
- are familiar with the data and know how to visualize it from scratch.
Example: Querying birth-rate data from BigQuery
This guide shows how an end-user can use Looker Studio's native BigQuery connector from the Looker Studio UI to visualize BigQuery data. This example queries the BigQuery natality sample table and fetches the entire table into Looker Studio.
Example: Building a BI dashboard with BigQuery, App Engine, and Looker Studio
How to build a BI dashboard using Looker Studio and BigQuery shows how you can use App Engine to pre-aggregate BigQuery data and then visualize it with Looker Studio.
Developing and using a Community Connector
You can develop a Community Connector that fetches data from BigQuery. This approach gives you benefits over using the native connector:
- You can incorporate existing queries into your Connector. Your users won't have to write their own SQL or copy/paste SQL snippets to get the exact query. Additionally, you can parameterize your queries and let your users provide input via the connector configuration to customize the queries.
- You can use service accounts to centralize billing. Your users will not need access to a GCP billing account.
- Your users can start with ready made template reports with their own data.
- You can implemented your own caching layer to control BigQuery cost.
In a Community Connector, you can access BigQuery data in three separate ways:
This table summarizes the pros and cons:
| Looker Studio Advanced Services | Apps Script BigQuery Service | BigQuery REST API | |
|---|---|---|---|
| Reference | Looker Studio Advanced Services | Apps Script BigQuery Service | BigQuery REST API |
| Flow of data | BigQuery > Looker Studio | BigQuery > Apps Script > Looker Studio | BigQuery > Apps Script > Looker Studio |
Calculated
fields supported
via getschema |
Yes | Yes | Yes |
| Can be used with a service account/custom access control | Yes | No (effective user's credentials enforced) | Yes |
| Filters are automatically pushed down | Yes | No | No |
Additional data
transformation
needed in
getData |
No | Yes | Yes |
| Fetched data can
be accessed in
Apps Script (Lets you do additional transformation) |
No | Yes | Yes |
| Custom caching supported | No | Yes | Yes |
| UrlfetchApp Quota applied | No | No | Yes |
| Example implementation | World Bank data connector | Apps Script BigQuery Service | Chrome UX Connector |
Unless you need to transform the fetched data from BigQuery or need custom caching, in most use cases, you can use Looker Studio Advanced Services.