Dashboarding BigQuery data

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.

Implementation steps

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:

  1. 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.
  2. You can use service accounts to centralize billing. Your users will not need access to a GCP billing account.
  3. Your users can start with ready made template reports with their own data.
  4. 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 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 Data Studio Advanced Services.