SPEAKER: Analyzing your unstructured data,
such as images and freeform text,
has long had to take place outside of your data warehouse
and requiring complex machine learning expertise
in order to draw out insights.
However, BigQuery and Vertex AI are changing that.
In this video, we will show you how
you can use pretrained machine learning capabilities
of Vertex AI directly within BigQuery using its Inference
Engine.
You'll see a demo of how you can analyze and enhance
unstructured data all with the familiarity of SQL.
Before we jump into a demo, let's
walk through an overview of the capabilities built
into BigQuery that enable you to analyze
unstructured data in this way.
Firstly, BigQuery is Google Cloud's data warehouse
that allows you to not only store and analyze
structured data but also unstructured data
like freeform text and even images via object tables
that extend out to data in Google Cloud Storage.
BigQuery is highly scalable, so no matter how much data
you have, BigQuery can store it and help you analyze it.
Now when it comes to extracting meaning and gaining insights
from your unstructured data, it's
common to use machine learning models to analyze
this kind of data.
Vertex AI offers several pretrained machine
learning models targeting various use cases.
The three we will be highlighting
for analyzing unstructured data in BigQuery
are the Vision API for tests such as object detection, OCR,
and labeling and images; the Natural Language
API for extracting entities, classification,
or performing sentiment analysis on freeform text;
and the Translation API for language detection
and translation of freeform text for over 100 languages.
What ties the two together is BigQuery ML
and, more specifically, BigQuery ML's Inference Engine,
which allows you to connect and run predictions against Vertex
AI'S pre-trained ML models.
When called by the Inference Engine,
the pre-trained models return JSON responses
that can be stored and analyzed at scale in BigQuery.
Let's head over to the demo and analyze some unstructured data.
I'm starting here in my Colab notebook,
a hosted Jupyter Notebook that can connect to my GCP project
and run commands, code, and queries.
It's linked in the description so you can make a copy
and follow along.
We have a set of classic movie poster images
that we'd like to analyze, first by detecting and translating
text present on the posters and then running sentiment analysis
on reviews for those movies.
The first part of the notebook runs you
through several steps for configuring the notebook
environment, such as importing libraries and setting up
variables, as well as setting up your GCP project,
including authenticating, choosing your project,
enabling APIs, and more you can read about in the notebook.
The next thing you'll do is create a BigQuery data set
and populate an object table.
The object table provides a structured record interface
for the movie poster images that we have
stored in Google Cloud Storage.
Once created, it contains metadata
such as the URIs, content type, and size of the posters.
A quick note.
As I mentioned before, you'll also
be analyzing freeform text of associated movie
reviews on IMDb.
This data set is actually available publicly in BigQuery.
And this type of unstructured data
is stored entirely in a BigQuery table
and doesn't require an object table.
All right.
Now that we have our data, let's start
performing OCR on the movie poster
images to pull out the text.
First, you need to create the vision AI remote model,
which essentially enables the ML.ANNOTATE_IMAGE function,
a connection to the Vision API from BigQuery.
This model setup is one time and then
you can continue to use it in your project.
Now, you'll use that function in a SQL statement
to run the text detection feature over the entire object
table of images and return the JSON formatted results
of the ML API alongside the other columns of the table.
Querying that table, you can see the results
that were provided by the Vision API for each poster.
Now, the movie titles are in several languages
and so next you'll translate the text
to English so that you can join them with the English language
IMDb reviews.
For this you'll create the remote model
for the Translation API, which allows you to use ML.TRANSLATE
to both detect the language of the results from the OCR setup
and then translate it to English.
Here's a sampling of the results where the original language was
detected as French and then translated to English.
Finally, you'll create the remote model for the Natural
Language API and then use the ML.UNDERSTAND_TEXT feature
to run sentiment analysis of reviews from the IMDb public
data set in BigQuery.
Your first query will run sentiment analysis
on the reviews for one of the titles
from the results of the previous steps, "The Lost World," which
was released in 1925.
The response from the NLP API provides a sentiment score
from clearly negative to clearly positive and neutral
in between.
In the final query, you'll use sentiment analysis
to compare the IMDb reviews for other movies released
in 1925 against reviews for "The Last World."
Analyzing the average sentiment scores,
you can see that "Sally of the Sawdust"
eked out in first place.
Using the Vertex AI pretrained ML models via BQ ML's Inference
Engine is really a game changer for drawing insights
from unstructured data at scale without complex code,
or needing ML expertise, or ever leaving your data warehouse.
Try it for yourself.
Access the Colab notebook and check out the documentation.
Both are linked in the description below.