MINHAZ KAZI: Welcome to "DataVis DevTalk."
I'm your host, Minhaz Kazi, developer advocate
for Data Studio.
In this episode of date of "DataVis DevTalk,"
you will learn about different approaches
to build custom solutions with BigQuery and Data Studio.
Let's get started.
[MUSIC PLAYING]
BigQuery is Google's fully-managed petabytes scale
low cost data warehousing solution.
Data Studio natively integrates with BigQuery,
and can be used to analyze and visualize BigQuery data.
You can also use a new BigQuery BI engine with Data Studio
to build rich interactive dashboards and reports
without compromising performance, scale, security
or data freshness.
When you're trying to build a reporting solution
from your BigQuery data, there are several approaches
you can take.
In this video, we'll talk about the recommended approaches
for different use cases.
You can use the native BigQuery connector in Data Studio
to fetch and visualize BigQuery create data.
You can fetch entire cables from BigQuery
or run custom queries within Data Studio.
This approach is useful if your users
are doing exploratory analysis and are familiar with SQL
and can write their own queries, and are familiar with the data
and know how to visualize it from scratch.
When using the native BigQuery connector, you or your users
can also take advantage of the Explorer feature
indeed Data Studio to visually construct the queries
without writing custom SQL.
Keep in mind, that your users will need a BigQuery billing
account to use the native BigQuery
connector in Data Studio UI.
There are other use cases when you might need a more
customized solution.
One use case is where you want to 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.
Another use case is when you want
to use service accounts to centralize billing.
This means your users will not need access
to a GCB billing account to query and visualize their data.
Other use cases include getting your users started
with template reports populated with their own data,
and implementing your own cache in there
to control BigQuery cost.
In all these cases, it is useful to create a custom solution
with a community connector.
In a community connector, you can access BigQuery
in three separate ways--
Data Studio Advanced Services, BigQuery REST API,
and Apps Script BigQuery Service.
Most of the custom use cases can be solved with Data Studio
Advanced Services.
While using the service in your connector,
you provide a query configuration in the code.
Based on this configuration, Data Studio
will fetch the data for you using its native connection
to BigQuery.
This works as fast as the native BigQuery connector,
since the data does not pass through Apps Script
and does not require any additional transformation.
Any filters added to the dashboard
are automatically pushed down to BigQuery,
so the connector does not get bogged
down with handling excess data.
This approach requires the least amount
of code among the three separate ways,
since you're passing only configuration to Data Studio.
The World Bank example connector in our open source repo
uses Data Studio Advanced Services.
Link is in the description, if you want to see the code.
We will have a separate video showing
how you can use the Data Studio Advanced Services when
building your own connector.
When you use the Data Studio Advanced Services,
the connector code does not get access
to the data being fetched from BigQuery,
so it is not possible to implement your own caching
there.
You also cannot do additional transformations or calculations
with the data.
In these cases, you can use the App Script
URL fetch app service to access the BigQuery REST API.
This approach lets you send off your query to the BigQuery REST
endpoint and get back your query results in your connector.
You can then implement custom caching
or do additional transformation, as required.
For an example of BigQuery REST API implementation,
check out the [INAUDIBLE] connector.
Link is in the description.
Apps Script BigQuery Service is useful
when you need to do manual calculation or cross
formation in your connector, but are hitting the URL fetch app
limits due to large data sizes.
This approach does have its limitations,
since you cannot use the service account to access BigQuery this
way.
To quickly summarize, use the native BigQuery connector
to create dashboards when your users are exploring the data
and are familiar with the data set and SQL.
For custom solutions, create community connectors
to access BigQuery.
Data Studio Advanced Services should
address most of the issues, unless you need access
to the data in your code.
In such cases, use the BigQuery REST API or the Apps Script
BigQuery Service.
All of these approaches support calculated fields
in the schema, and can use templated reports
with the connector.
To learn more, check out the links in the description below.
And don't forget to subscribe to the GCB channel
for more great content on Data Studio.
Thanks for watching, and until next time,
keep building awesome dashboards.
[MUSIC PLAYING]