FELIPE HOFFA: My name is Felipe Hoffa.
You are interested in visualizing data.
Prepare to rewind.
[MUSIC PLAYING]
For years, I've used BigQuery and public data
sets to publish interesting articles.
And I've used many different visualization tools.
But there was still a space in my life
until Data Studio showed up.
Google Data Studio is totally free for you and for me.
And I can use it to publish interactive visualizations
online.
And it's really easy to connect it to many different sources,
including BigQuery.
So that's what I did.
I analyzed data with BigQuery.
I connected it to Data Studio.
I made my dashboards interactive.
And I published them online.
You can embed a Data Studio dashboard anywhere,
including some of the most popular plugin platforms.
But there are some challenges.
As you know, BigQuery allows you to analyze huge amounts
of data in very few seconds.
And it only charge to you for each query you run.
And it has a free monthly tier.
So you might end up paying nothing at all.
But if you connect BigQuery to a popular dashboard,
then your users may end up generating a lot
of queries on your behalf.
And that means you can end up with a huge BigQuery bill.
We don't want that.
So I asked the developer advocate
for Data Studio, Minhaz Kazi, to join me on state
to share our best strategies for publishing dashboards with Data
Studio and BigQuery.
For example, the easiest way to avoid
pushing new queries from Data Studio to BigQuery
is to materialize the results of your queries into Data Studio.
That's right, once you have the BigQuery results
or table you want to visualize, you
can use the Extract button to do so.
And Data Studio will offer you an easy way to keep and refresh
a copy of this data.
But right now, that feature is still in development.
And it will be subject to certain limitations.
So we also shared some other options
to create an inexpensive layer between Data Studio
and BigQuery.
For example, you can use G Suite Apps Script
to schedule queries and have the results pushed to Google Cloud
Storage.
You can then connect Data Studio to that file, which
will be refreshed based on your schedule.
And your users will be able to quickly explore the results.
But this works only up to a certain size of results.
In other cases, it may make a lot of sense
to use a database that supports lookup by key.
Once you know that your visualization will all
require key lookups to retrieve data,
instead of scanning a table in BigQuery,
you can connect Data Studio to a traditional database
and just get the individual rows out of it.
In this case, I exported the BigQuery results
to a MySQL database hosted on Cloud SQL.
I added the required indexes.
And we got quick, inexpensive results.
But sometimes you might have more complex needs--
for example, when you want to make a data
source available to many users and let them
build their own customized visualizations.
In that case, you can create a Data Studio custom
connector with Apps Script and design your own caching logic
within it.
In the full talk, you can see this in action
with Minhaz's example, where he created a connector that anyone
can use with just a couple clicks.
And they get a custom and customizable visualization
with fresh data, hosted in BigQuery,
without any need of coding, SQL or otherwise.
In summary., if you want to connect BigQuery and Data
Studio to create an interactive dashboard,
and you want to make this dashboard
available for everyone, remember to think
about what kind of layer you want to have between BigQuery
and Data Studio.
And stay tuned.
Because at Google, we are always thinking
about how to make this connection even more powerful.
If you'd like to see the full talk,
check out the link in the description below.
And don't forget to subscribe to the Google Cloud Platform
YouTube channel for more great Next Rewind content.
Thanks for watching.
[MUSIC PLAYING]