CHRISTIAN SCHALK: Hello, and welcome to the Building
with AppSheet series, where I discuss building apps
with AppSheet and Google technologies.
I'm Christian Schalk, a Google Developer advocate.
And in this episode, we'll provide an introduction
on how to connect apps to BigQuery.
What is BigQuery?
BigQuery is a petabyte scale, cost-effective cloud data
warehouse technology that enables super-fast SQL queries
using the processing power of Google's Cloud Infrastructure.
BigQuery enables easy storage of large amounts
of data using common data transport,
such as CSV and so on.
BigQuery also supports real-time upload streaming
for immediate data analysis.
To query data, BigQuery support standard SQL.
So anyone familiar with databases
can easily query data.
BigQuery also supports the standard database
drivers, such as ODBC, JDBC, and so on.
BigQuery is also easily integrated with leading data
visualization tools such as DataStudio, Tableau,
and others.
BigQuery serves data from two types of data
sets, public and private.
Public data sets allow anyone to freely fetch
public data, such as weather, finance, news, and so on.
And private data sets can only be
accessed by the data set owner or to whom they explicitly
share the data with.
AppSheet provides direct access to BigQuery with its Connector
feature.
It's important to note that AppSheet's access to BigQuery
is read-only.
And more importantly, the data set
fetch size is currently limited to 100,000 rows maximum.
Before AppSheet can access a BigQuery data set,
you'll first need to prepare BigQuery a bit in advance.
To do this, you'll need to locate
a data set to query from.
It's easiest to start with a public data set.
Once you've found a data set, you'll
need to note its Data Set ID for later.
Next, you'll need to prepare a Cloud project to use BigQuery
by doing the following.
First, create a service account to query data from as a client.
Specify the role "BigQuery Job User" for the account.
And finally, download a JSON key for the service account
as you'll need this for later in AppSheet.
Here's how to prepare a BigQuery for AppSheet.
OK, so here we are in the Google Cloud platform console,
and I'm looking at my project AppSheet BigQuery.
And as you can see in the Resources down here,
we have BigQuery, and it's actually
already enabled by default.
So let's go ahead and take a look at that.
So now we're looking at BigQuery.
And as you can see in the Explorer here,
we have this bigquery-public-data option
here.
So I can open that up and see all of the data sets
that we can play with.
And so I happen to know that there's
some fun data associated with San Francisco.
And as you can see here, we have some film locations.
So let's take a look at that.
I'll open that up, and there's the table
that has our film locations.
I can look at some of the details
as well as preview the data.
So this is the data that we're going
to be using in our AppSheet app that we create.
So in order to set up a connection, what
I'll have to do actually is open up the data set itself.
And I would need to take note of the Data Set ID.
And I'll add that later to the AppSheet connection.
Before continuing, though, if you recall,
I actually have to add a service account to this project.
So I go down here to the IAM & Admin option,
and I select Service Accounts.
And incidentally, I happen to have an existing service
account that I created before.
But I'll just step through the steps just
to show you how it looks.
So I'll Create Service Account.
I'll give it a name.
So I'll just go something like BQ service.
I could add a description if I wanted to,
but I'll hit create and continue.
And then here, we can just select a role BigQuery Job
User.
So it's still there from my last usage.
And I will click Continue-- actually, I'll click Done.
And there we go.
Now we have a new service account here.
I'm not done yet, though, because I actually
need to create a key.
So let's click on Manage Keys.
Then I go over here under Add Key.
I select Create a New Key.
I'll accept the default for JSON and click Create.
And that actually creates a text file and downloads it
onto my local machine.
And this is actually what it looks like.
So this is essentially the JSON connection data.
And I'm going to be adding that to my connection
over an AppSheet when I continue.
And that's it.
My BigQuery environment is now prepped,
and I can switch over to AppSheet.
After setting up BigQuery, you'll switch over to AppSheet
and set it up to use BigQuery.
In the AppSheet My Account page, you'll
create a new data source.
Name the new data source and select Cloud Database
as the data source type.
Next, select BigQuery as the database type
and add the BigQuery Data Set ID that you located before,
the Google Cloud Project ID that you just
prepped, and then paste the JSON contents of the Service Account
key.
Once entered, you'll click Test and Authorize.
Let's switch to AppSheet now to set it up to use BigQuery.
So here we are in AppSheet, and I'm
going to create a new data source.
And that's done under the My Account page.
So I click on My Account, and then
I click on New Data Source.
And so this will be the data source
that I'm going to create.
I'll just say "SF Films" to keep it short.
And so I'm giving it a name, SF Films.
And I click on Cloud Database.
And here's where I select the database type,
and that's also where I would select the different Data Set
ID, Cloud Project ID, and so forth.
Now, I happen to have an existing data source
that I've already set up.
Let me just walk back here, and I'll select the San
Francisco filming locations.
And I'll switch back over to the form
here where I have all the information.
The only thing that I don't have that I
need to actually enter again is the JSON Service Account key.
So if you recall, I actually have that
saved off in a text file here.
So I would just add that.
Let's click Test and Authorize Access.
And that's it.
So now we're good to go.
We can actually create an application
to connect to that data source.
Once you've set up a BigQuery data source,
building AppSheet apps on it is trivial.
You'll simply create a new app and specify that you
wish to use your own data.
After naming your app, you'll click Choose your data.
Finally, you'll select the BigQuery data source
that you set up previously, and that's it.
Let's switch back to AppSheet and build
an app using our BigQuery data.
So here we are back in AppSheet, and so I'm
going to create a new app.
So I'll click Make a new app.
And I'm going to start with our own data.
So our data will be the BigQuery data that we set up before.
And let me just give it a name, SF Film Locations.
And I'm going to click Choose your data.
So that's going to be able to select the data from our data
sources.
And here's our SF Filming Locations data source
that we set up before.
So let's take a look at the tables, and there we go.
We have our film_locations table.
So let's click that.
So here is our app that we just created.
It's functional, but I'm going to go ahead and do
some updates.
So let's click on the Data tab here and pop open the table
and look at the columns.
And first off, as you can see, the years,
they look kind of odd with the commas there.
So I'm going to change the release year from number
to type text, and that will remove the comma.
And then I'm going to also, more importantly,
change the locations column to an address.
And so once the address is added there,
it will start putting maps and stuff inside of the app.
So let's go ahead and click Save.
And I'm going to go back over to the UX tab.
And as you can see here, I have this option
to add a Map View for the film locations.
So let's select that.
I'll just change that to map.
But there we go.
As you can see, we actually have data
showing the different filming locations
throughout San Francisco.
So that's kind of fun.
As you can see, I can select the different options here
and start looking at the different movies and stuff that
were filmed in San Francisco.
So as you can see, it was a rather trivial,
once I had set up the data source connection to BigQuery
to actually build or generate an app that shows the BigQuery
data inside of an app.
This concludes the BigQuery and AppSheet episode.
Stay tuned for more coverage of this important topic
in future episodes.
And for more info on AppSheet and Google Workspace,
check out the links in the description below.
And for more videos like this, make
sure you subscribe to this series so you don't miss out.
As always, thanks for watching.
[MUSIC PLAYING]