BigQuery is Google's petabyte scale data warehousing solution. Data Studio natively integrates with BigQuery and can be used to analyze and visualize BigQuery data.
There are multiple ways to bring your BigQuery data into Data Studio:
- Using the native BigQuery connector in the Data Studio UI
- Developing and using a Community Connector
Using the native BigQuery connector in the Data Studio UI
Users can use the native BigQuery connector in Data Studio to visualize BigQuery tables or specific queries. You can fetch entire tables or run custom queries on BigQuery from within Data Studio. It is also possible to use the Data 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 Data Studio's native BigQuery connector from the Data Studio UI to visualize BigQuery data. This example queries the BigQuery natality sample table and fetches the entire table into Data Studio.
Example: Building a BI dashboard with BigQuery, App Engine, and Data Studio
How to build a BI dashboard using Google Data Studio and BigQuery shows how you can use App Engine to pre-aggregate BigQuery data and then visualize it with Data 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:
|Data Studio Advanced Services||Apps Script BigQuery Service||BigQuery REST API|
|Reference||Data Studio Advanced Services||Apps Script BigQuery Service||BigQuery REST API|
|Flow of data||BigQuery > Data Studio||BigQuery > Apps Script > Data Studio||BigQuery > Apps Script > Data Studio|
|Calculated fields supported via
|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
|Fetched data can be accessed in Apps Script
(Lets you do additional transformation)
|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 Data Studio Advanced Services.