SPEAKER 1: Throughout this series,
a lot of what we've shown you has been focused
on the BigQuery console, but when
you have large amounts of data or stakeholders,
manually running jobs and creating resources
won't be very scalable.
[MUSIC PLAYING]
In today's video, we're going to take a tour of the BigQuery
APIs so that you can get started creating programmatic data
workflows.
[MUSIC PLAYING]
The first API we'll discuss is BigQuery V2, which
is really the core of BigQuery.
Here, you can manage data warehousing resources
like data sets, tables, and routines.
You can also manage machine learning models as well
as execution resources or jobs.
Like most of the APIs we'll discuss today,
you can interact with the BigQuery API
through the various client libraries
that we have available.
I'm personally a Python fan, so having the Python library makes
it so much easier for me to programmatically
interact with BigQuery.
OK, so when might you want to actually use this API?
Well, first off, you may want to programmatically get
answers to business questions using data
that you have in BigQuery.
Let's say every week, you want each sales team's revenue
to be added to a slide deck so it can be presented
at your company's all hands.
Well, in a Google App Script, you
can write some JavaScript that loops through each of the team
names, and uses them as inputs for a parameterized query that
sums up their total revenue.
We can use this information to populate each team slide,
saving our analysts lots of time copying
and pasting into the deck.
Alternatively, you might want to use this API to add
some new data into your tables.
For example, maybe you have information
about your customers stored in your application's Cloud SQL
database.
You have your customer addresses,
but you really want to convert them
into lat/long coordinates so you can
leverage BigQuery's awesome geospatial capabilities.
Well, in this case, you can create a Google Cloud function
that runs each night, and grabs the new transactions
from the database using an external query,
then uses the Google Maps geocoding
API to grab customer coordinates,
like I'm showing in this Jupyter Notebook.
Finally, you'll create and run a load job
that appends the new data to your transactions table.
So cool.
And on the topic of analyzing data
from transactional databases, remember a few videos ago
when we talked about connections,
which allow you to write Federated queries against Cloud
SQL?
Well, there's an API for that too.
The Connections API allows you to programmatically create
and manage connections, which can be really useful if you
have multiple databases supporting your application,
like if you're a B2B organization with one database
for each of your accounts.
Here, you may want to write a program that
creates a new connection as part of the customer onboarding
process.
This way, your BigQuery users have access to the data
without needing to send a request to you.
You could even weave in the Google Cloud IAM API
to ensure that only users who need access
to the specified account's data are able to leverage
the connection.
Besides the BigQuery and the connections API,
what else is out there?
Well, we talked a bit about pushing new data into storage,
but BigQuery also has a dedicated storage API.
The storage API has two components.
The read client exposes a data stream
suitable for reading large volumes of data.
This includes features for parallelizing reads, performing
partial projections, filtering data,
and getting precise control over snapshot time.
At Google, we use the Snapchat API
to build out a series of Hadoop connectors
that make it easy to use BigQuery
as your source of truth data lake.
You could also use it to build out your own custom connector.
The Read Client, currently in preview,
is the successor to the streaming mechanism
found in the BigQuery V2 API.
It supports more advanced write patterns,
such as exactly one semantics for more robust streaming
inserts.
Keep an eye out for more info on this coming soon.
Continuing on the topic of ingesting data into BigQuery,
we also have the data transfer API.
BigQuery Data Transfer Service allows you to automate work
to ingest data from known sources,
like Cloud Storage, Google Marketing Platform, YouTube,
and lots of other third party data sources
on a scheduled basis.
With the API, you can do things like programmatically check
credentials, trigger a manual run of the transfer,
or get the responses from previous transfers.
Now we can't have a discussion about BigQuery APIs
without mentioning the Reservation API, which
allows you to manage reservations
and assignments like we discussed in one of our earlier
videos.
This is super useful for programmatic workload
management.
Let's say the dashboard for your CEO
needs to be run every morning at 8:00 AM.
Well, you want it to be super fast,
so you better make sure the project it runs in
will have access to enough slots.
With the Reservation Python SDK, you
can write a program that runs each morning
and creates a new slot reservation
by specifying the reservation ID and how many slots it contains.
Next, you'll assign the reservation to the designated
bi_project where the dashboard queries run,
and specify that this assignment is for query jobs.
Finally, you can delete the assignment and reservation
so that slots are released back to the organization.
The last API I'll mention is one of my favorites--
Data QnA, which is currently in preview.
Are business users at your organization
always pinging you to query data on their behalf?
Well, with the QnA API, you can convert natural language text
in queries into SQL, meaning you can
build a super powerful chatbot that
fulfills those query requests, or even give your business
users access to Connected Sheets so they
can ask analytics questions directly in a spreadsheet.
Wow, that's a lot of different APIs, all under the BigQuery
umbrella, and we didn't even talk
through other related products like Data Catalog.
[MUSIC PLAYING]
But don't worry, we talked about programmatic data governance
last week, so make sure to check it out.
As always, thanks for joining us for "BigQuery Spotlight,"
and make sure to look through the links
below for details on what we discussed today,
and code samples for the various APIs.
And remember, stay curious.
[MUSIC PLAYING]