[AUDIO LOGO]
ABIRAMI SUKUMARAN: Do you know that one
of the important reasons most organizations are not
able to be fully data driven is that a major portion of data
generated today is unstructured?
Data in the form of videos, images, documents
remain untapped by organizations.
And one of the goals for Google Cloud
is to help organizations realize value from data of all formats
and types.
I'm Abirami Sukumaran, developer advocate at Google.
And today I'll demonstrate how we can store and process
image data with BigQuery.
Late last year, we announced a preview
of this capability that is to be able to store unstructured data
in BigQuery and perform analytics and ML directly
on those data using BQML with existing frameworks
like SQL and remote functions.
In this session today, we'll see a quick demo of this feature.
So what's unique about processing unstructured data
with BigQuery, you ask?
You can store unstructured data in object tables
just like you would with regular tabular data,
perform analytics and ML on this data only using SQL queries,
unify structured and unstructured data at scale,
and query them together.
[AUDIO LOGO]
There are five steps in achieving image data
classification with BigQuery ML--
create data set and BigLake connection,
create a Cloud Storage Bucket and grant permissions,
create an object table, create model and upload
to Google Cloud Storage, load the model into BQML
and start inferring.
Let's start the demo.
Log into Google Cloud Console.
And in the search bar, type in BigQuery.
If you haven't already enabled the API,
you will be prompted to do so.
Once that is done, you should be able to see the Explorer
pane on the left side and the Query Editor
on the right in the BigQuery Console.
The first step is to create data set.
For that, start typing in the query,
create schema, project ID, dot data set name.
In this case, I've chosen yoga_set_io as a data set name.
For this demo, I've already created this.
Once this is created, you should be
able to navigate to the created data set
and take a look at the configuration.
Moving on to the next step, create connection.
This is the BigLake connection we'd be creating.
For this, click on the ad icon on top of the Explorer pane.
From the list of popular sources,
select connections to external data sources.
Select connection type as BigLake and remote functions
cloud resource.
Provide a connection ID.
Make sure the region that you choose here in location type
is same as the one that you've created for BigQuery data set,
and create connection.
Once the connection is created, you
should be able to see that from the list available here.
Make a note of the service account
ID for the BigLake connection that is just created.
Next step is to create Google Cloud Storage
bucket, upload the images, and grant permissions.
For this, go to Google Cloud Console.
And on the search bar, type Google
Cloud Storage or Cloud Storage.
Once you select that, you should be
able to see the bucket's page with a list of already created
buckets if you have any.
You can choose to create a new bucket for this use case,
provide the name of the bucket, and select the region.
Make sure it is the same region that you
have chosen for the BigQuery data
set and the BigLake connection.
We leave the rest of the options set to default values for now
and hit Create.
I've already created the bucket that's required for the demo.
And I've uploaded the images.
For this demo, we have chosen the use case
of recognizing yoga poses.
So I have taken a few yoga poses images
that are available publicly.
And I've uploaded those folders into the yoga_images bucket
that I had created in Google Cloud Storage.
Once you create the bucket, uploading the files
into the bucket is fairly straightforward.
You see the upload files and upload folder options
right here on the yoga images bucket.
Click that and upload the files into this bucket.
Once everything is done, go to the configuration page
of Cloud Storage Bucket that you've just created
and make sure you copy the gsutil URI because this
is the URL that we'll be using in our SQL
to reference this Google Cloud Storage bucket.
And now let's grant permissions to the service account
so BigQuery can access the images stored in Cloud Storage
bucket.
For this, I'm going to copy these two commands
and run it in Cloud Shell terminal.
Replace the service account placeholder
and the gsutil delivery of the Cloud Storage bucket
placeholder.
Let's run the two commands from Cloud Shell terminal.
Authorize.
And we should be all set to move on
to the next step, which is creating an external object
table.
The external object table is the table in BigQuery
that will contain the images that we currently have
stored in Cloud Storage bucket.
For this, we will use the create or replace external table
query, create or replace external table data set ID
dot table name of your choice.
In this case, I've chosen yoga_poses_io,
with connection, connection name for the connection we just
created.
Options, object metadata remains simple.
And URIs, paste the gsutil URL of the Cloud Storage bucket
that we created.
I've already ran this query.
The table is created.
Once the table is created, you will
be able to navigate to the table and look at the schema.
Now that the table is created, let's
quickly query this table to make sure the data is copied over
from Cloud Storage to BigQuery external table.
The data field contains the reference to the image.
And you can see other fields, the URI, content type, updated,
and other metadata fields.
You can also notice that I've limited the results set to one.
And I've chosen an image from the blank folder.
Now let's quickly visualize the result
that we obtained in an image format.
To do that, you can export the result into a local CSV file,
open the Colab notebook, upload the CSV
that we've just exported from the query editor in BigQuery,
and run the small snippet.
This step is totally optional.
And there are several ways to do it.
You can also programmatically achieve
this using your favorite programming language.
Now that we've created the external table
and stored images in BigQuery, the next step
is to create the model and upload it
in Google Cloud Storage.
For this implementation, I'm going
to use an existing pretrained model.
You can use a pretrained model or create your own model
and upload it in TensorFlow Hub.
And it should unpackage into saved_model.pb and a variables
folder in your local.
Upload the saved_model.pb file and the variables folder
into the same bucket in Cloud Storage where we have already
stored our images.
That's it.
Now we can load that model into BigQuery ML
and start predicting.
For this, we are going to use the Create model query.
Create model data set name dot model name of your choice.
In this case, its resnet_io.
Options, model type remains TensorFlow.
And model path is your gsutil URI path of the Cloud Storage
bucket that we've created.
Run this.
In this case, I've already created the model.
Once the model is created, you should
be able to navigate to the model and take a look
at the configuration details.
Click Schema tab, and you should be
able to see two sections, labels and features.
Labels section contains activation_49 field.
It contains a batch of vectors that
represents the likelihood that a specific image belongs
to a particular category.
Input_1 is the independent variable or the input
that we'll be passing to the prediction query
in order to deliver the target result. All right, now
it's time to use the model that we've
created to start predicting.
For this, we're going to use the element of predict SQL, select
Start from ML.PREDICT, provide the model
name in the parameter, and the subquery that
represents the image for what you want to do the prediction.
In this case, the yoga pose that you want to label.
Let's run the query.
When the query execution is complete,
you can see that the result set has
activation_49, which is the prediction variable result,
and input_1.
This result is highly nested.
So I've written a simple flatten query
to translate this into a more relatable format or structure
that we will have to label in the form of an index
or in the form of actual yoga pose name.
Let's run this query.
There you go.
We have now obtained a flattened version of the same result
we got in the prediction query.
In this case, the image 1.jpg that I've
passed in as input for prediction
is identified as downward dog.
To validate this, go ahead and export the result
to a local CSV file and go back to the same Colab notebook
where you have the Python snippet and pass in the CSV
that you have just exported.
And you can see that the pose that we
have parsed for prediction is actually downward dog pose.
Now that we have created the model,
loaded the model in BigQuery ML, and predicted
using the model that we've just created,
the last step here is my favorite part,
which is unifying structured and unstructured data
and querying them together.
In order to do this, I have already
created yoga_health structure table in preparation
for this demo.
This is a simple structured relational table,
which has full fields.
So I'm just going to execute this select query that
combines unstructured data and structured data
and run it from BigQuery editor.
And there it is.
We have successfully unified structured and unstructured
data.
As you can see, pose, health benefit,
breath, focus are all from the relational table.
And data field is from the unstructured external table
in BigQuery.
[AUDIO LOGO]
Congratulations.
We have successfully stored, queried,
and classified unstructured data with BigQuery only
using SQL and also unified structured and unstructured
data in a single query as if they coexisted.
Try out SQL only ML with BigQuery to begin with
and come up with innovative solutions
for your organization.
Become a Google Cloud innovator and innovator champion today.
Read about it in the description.
[AUDIO LOGO]