[MUSIC PLAYING]
SANDY GHAI: Hi, I'm Sandy Ghai, product manager for AlloyDB.
ABHINAV KHUSHRAJ: And I'm Abhinav Khushraj,
product manager for BigQuery.
SANDY GHAI: Today we're going to be talking about how databases
can help you more easily add ML to your applications.
We see a lot of app developers looking
to build better experiences using machine
learning, whether that's a mobile app developer looking
to use AI/ML to optimize a customer journey,
to a retailer looking to offer the right product
to the right customer, or a financial services user looking
to detect fraud within a transaction.
There are so many opportunities to build
more personal, more intelligent experiences
in your applications.
ABHINAV KHUSHRAJ: But you know, Sandy,
while people see a lot of promise in what AI and ML can
do for their business, they are struggling to operationalize
AI initiatives.
You know what happens typically?
Typically, enterprises will spend a lot of time and energy
to build data pipelines to bring data into a warehouse.
They'll build governance models around it and so on.
However, when they want to do an ML project,
they have to take that data out again.
Not only do they have to build and maintain new pipelines
to export the data, it often results in loss of governance
and can lead to security issues.
And finally, there's the headache
of figuring out and maintaining infrastructure,
both for training and inference.
Either they're underserved, creating a bad experience,
or overserved, resulting in huge costs.
So the traditional approach results
in skill gaps, significant cost, and complexity,
and can lead to security issues.
SANDY GHAI: And that means fewer customers actually integrate
machine learning into their apps.
I think we can both agree that there's a better way.
ABHINAV KHUSHRAJ: Absolutely.
Let's show them.
SANDY GHAI: Chances are, you're already
using a database to back your application and a data
warehouse for analytics.
Today, Abhinav and I are going to talk you
through a demo on how you can use your existing stack
to build models and integrate them with your apps
without having to move data around.
ABHINAV KHUSHRAJ: You can even use your existing SQL skills
to both train models and deploy them in your applications.
SANDY GHAI: There are a few products
that make this super easy.
Today, we're going to talk about AlloyDB and BigQuery.
AlloyDB is a fully Postgres-compatible database
engine with superior performance, availability,
and scale.
It's great for demanding workloads.
It's four times faster than standard Postgres
for transactional workloads.
But it also allows you to unify analytical workloads
and generate more real-time insights on your data.
But one of my favorite things about AlloyDB
is its native integration with Vertex AI.
With AlloyDB, you can call a model in Vertex AI
directly from the database, enabling
high throughput, low latency augmented transactions.
One of the examples we're going to talk about today
is a fraud detection use case.
AlloyDB can call a fraud detection model
within a transaction.
So if John Doe tries to make a purchase for $20,
we can call Vertex AI using this ML
predict function and determine if it's fraudulent
as we save it down.
This feature is actually also available in Cloud Spanner,
which is GCP's relational database
offering, with unlimited scale, strong consistency, and up to 5
nines of availability.
ABHINAV KHUSHRAJ: BigQuery is an industry-leading cloud data
warehouse, processing 110 terabytes of data
every single second.
It has a serverless design.
Users don't have to worry about managing scale.
Whether you have just a few hundred rows or billions
of rows, BigQuery automatically scales up or down to manage it.
And it is unique in the industry to have built-in capability
for doing ML, with BigQuery ML.
By being right inside the database,
it removes the need to manage data,
and BigQuery manages all the training and serving
infrastructure.
Enterprises large and small have trained
millions of models using BigQuery
ML for a variety of use cases, ranging
from forecasting, customer segmentation,
or anomaly detection.
In fact, its usage grew by over 200% in 2022.
BigQuery ML brings BigQuery and Vertex together
to create a simplified data to AI journey.
It integrates with Vertex in many different ways.
Users can train models for BigQuery,
leveraging Vertex's training infrastructure,
or users can write a few lines of SQL code
to run inference against Google's state of the art
pre-trained models for images, natural language,
or translation.
And finally, for enterprise users,
it provides a seamless MLOps experience for managing models,
building a feature store, and more.
SANDY GHAI: What's so cool about these products
is that they make it easy to integrate ML
into your existing apps.
Today we're going to walk through a fraud detection use
case.
First, let's start with the existing application.
You are already using AlloyDB as your database
and storing your retail transactions there.
You might also have a database pipeline using Datastream
that is replicating data to BigQuery for offline analytics,
and maybe even a Looker dashboard for reporting.
Now, we'd like to start incorporating ML to predict
whether our retail transactions are fraudulent.
We can use Vertex AI, GCP's AI and ML platform,
to build, deploy, and scale machine learning models faster,
with fully managed ML tools for any use case.
This integrates nicely with both BigQuery and AlloyDB.
So first, we need to train our model.
For data scientists who want full control,
Vertex AI offers custom training.
But for the many users who don't have data science expertise,
or who want to speed up time to market,
BigQuery ML can help you train a model super quickly.
And all you need is SQL.
ABHINAV KHUSHRAJ: Users write a simple SQL query
with a create model statement specifying the model type
and the training data set that is inside BigQuery.
And then in addition to that, during model creation,
users specify an option to register the model with Vertex
AI's Model Registry.
Now this makes it super easy for enterprises
to have a single place to manage all their models.
And once the model is registered,
users can go to Vertex AI to create a prediction endpoint
and deploy the model for online serving using Vertex AI
Prediction while specifying the serving infrastructure
that they like.
It's that easy, Sandy.
SANDY GHAI: So now we've trained our model
and registered an endpoint.
Let's talk about inference.
How do we actually integrate those predictions
into our apps?
Again, AlloyDB makes that easy because you
can call your Vertex AI endpoint from the database itself.
I'm turning it over to Gabe Weiss
to show you how this works.
[MUSIC PLAYING]
GABE WEISS: We're going to walk through how
you can invoke predictions on Vertex AI endpoints
from AlloyDB using SQL.
This is functionality AlloyDB provides so that users don't
have to manually build a custom integration for machine
learning.
Here's our project where an AlloyDB cluster has
been created, and we will be using the primary instance
in this cluster to invoke predictions.
First, we need to grant this AlloyDB cluster permissions
to access Vertex AI.
To grant those permissions, we need the project number.
To find it, navigate to the project dashboard,
and project number should be listed under project info.
We're using a separate project for our Vertex AI endpoints.
As you can see, we have a fraud detection endpoint
deployed here already.
Instructions to train and deploy this model
will be linked in the description below.
To grant permissions, go to the IAM page of the same project
and click on Add.
Paste the principal in the format mentioned
in the documentation and change the project number to the value
we got from our AlloyDB project.
Choose the Vertex AI user role so
that AlloyDB clusters in that project
can invoke predictions on Vertex AI.
And finally, click Save.
As we can see, the principal has been
granted the necessary permissions,
and now AlloyDB instances should be
able to access Vertex AI endpoints.
Note in some scenarios, users might
have to check the Include Google-provided role grants
box to view the principal we just added in this list.
Now, to finally invoke predictions on the endpoints,
we need some details.
Navigate back to the Vertex AI endpoints page.
In this case, we want to invoke predictions on the fraud
detection endpoint.
Click on the sample request link.
Take note of the endpoint ID and project ID.
Also note the region in which this endpoint is deployed.
In this case, it's us-central1.
Now the final step is to invoke predictions
on this endpoint using SQL.
We will connect to the AlloyDB's primary instance
from a client VM in the AlloyDB project.
We are using the pgcli client, which
is a wrapper over psql to connect to the fraud database.
Let's create a table, fraud detection data,
which will contain transactions over which prediction will
be invoked to determine whether a particular transaction is
fraudulent or not.
For demonstration purposes, we'll load data from a CSV file
into this table.
Let's look at some rows in this table.
Some of our data has been transformed
to protect user identities and sensitive features.
That's why we see the data in this form and columns named
as V1, V2, et cetera.
And some columns have been preserved as is,
like Time and Amount.
To invoke predictions, we'll be using a function
named ml_predict_row.
This function is provided by the Google Vertex AI
extension for AlloyDB.
To use it, we need to install the extension first using
the create extension command.
Now that the extension is installed,
let's start with a query which runs predictions
on some rows of our fraud detection data table.
For each row, ml_predict_row will be
called with relevant arguments.
The first argument of the ml_predict_row function
is the endpoint, and we plugged in project ID, region,
and endpoint ID into this format, which we got earlier
from the sample request.
The second argument is a JSON object
with an instances key containing the actual data
that we want to get the prediction scores for.
We want to get individual prediction scores
for each row, which is why row_to_json here is converting
a row in the table to JSON.
The response JSON object returned by Vertex
will contain prediction scores in the predictions key.
So here you see we're extracting data from the predictions
key using this operator.
Finally, we're limiting the number of rows to 500 just
for demonstration purposes.
Now let's execute our query.
The response will take a little time,
so we're speeding up a bit here to get to the good stuff.
This is the response returned by the query.
We have a Time column that is directly
projected from the table and Fraud Score
column, which contains the prediction results.
Class 0 here means the transaction is non-fraudulent
and class 1 means that the transaction is fraudulent.
So for the first row, the prediction
score corresponding to class 0 is
0.99, which is significantly higher than the score
corresponding to class 1, meaning
the model has predicted that this transaction is
non-fraudulent.
However, looking at our second row,
the score corresponding to class 0 is only 0.1,
which is significantly lower than the score corresponding
to class 1, meaning the model predicted that this transaction
is fraudulent.
This query took around 14 and 1/2 seconds
to execute because we're sending only one
row to each request for Vertex AI,
as in 500 requests just got sent to Vertex AI
to get predictions for the 500 rows.
To improve throughput, we can send
multiple rows in a single request to Vertex AI.
Our next query will do exactly that.
We're going to use GROUP BY clause to batch
multiple rows together.
Just to demonstrate, we're going to group
on a random integer ranging from 0 to 4,
meaning there can be at most five groups or batches.
And since we're invoking predictions on 500 rows,
the expected batch size would be about 100.
Now the data under the instances key
will be a list of JSON objects instead of a single JSON
object like the previous query.
We generate this list using the json_agg function.
Finally, we also then need to unpack the aggregated rows
into individual rows.
Unnest and json_array_elements functions
are responsible for doing that work.
Now let's run the query.
We roughly see around a 21 times improvement,
even though both the queries are invoking predictions
on the same sets of rows.
So just by batching, we can get significant performance
improvements.
The amount of performance improvement
will obviously vary from case to case
and depends on factors like network latency, batch size,
number of parallel workers used by the queries, et cetera.
Thanks for watching.
[MUSIC PLAYING]
ABHINAV KHUSHRAJ: Wow, that was pretty cool and super easy,
Sandy.
SANDY GHAI: Thanks.
So now we've trained our model and deployed it
into our application.
We didn't need to move data around or host or manage
any additional resources.
We also didn't need any specific data science skills.
We were able to run and train our predictions using just SQL.
ABHINAV KHUSHRAJ: Thanks so much for tuning in.
To learn more about using AlloyDB and BigQuery
to integrate machine learning into your applications,
check out the description below.
[MUSIC PLAYING]