YUFENG GUO: Not everyone uses SQL, but if you do,
wouldn't it be nice to be able to use machine learning
within your SQL environment?
BigQuery ML does just that.
Stay tuned to find out how.
Welcome to AI Adventures, where we explore the art, science,
and tools of machine learning.
My name is Yufeng Guo, and on this episode,
we're going to try out BigQuery ML, which
enables you to create and execute
machine learning models in BigQuery
using standard SQL queries.
Currently, BigQuery ML, or BQML for short,
supports linear regression, binary logistic regression,
and multiclass logistic regression.
Linear regressions are models which
predict a numerical value, like an insurance premium or a home
value.
Binary logistic regressions are models
which predict one of two classes,
like identifying whether a machine needs
to be checked for maintenance work, a yes or a no, or perhaps
more famously, whether an image is a cat or a dog.
Multiclass logistic regressions are models which
predict more than two classes--
for example, whether or not you should buy, hold, or sell
a stock, or if, say, traffic on the highway
is light, medium, heavy, or a parking lot.
Let's see what BQML looks like in practice.
You can access it via the BigQuery UI
or through the command line and API.
Today, we'll look at it on the BigQuery UI,
but the queries we'll use are identical to if you use
the command line or the API.
I've got some sample data here from Google Analytics, taken
from a BigQuery public data set.
We did an episode on the BigQuery public data sets
program a while back, so be sure to check that out
to learn more about all the public data sets that
are free for you to use.
Now, let's take a look at our data set today.
Notice that we're looking specifically at whether or not
a transaction took place.
That's what we'll be trying to predict later on.
Now, what will we base these predictions on?
We have the operating system of the user's device,
whether it was a mobile device or not, the country of origin,
and the number of page views that the user performed.
I have a feeling that last column,
that's probably going to be pretty useful.
Notice also that we're pulling data from this sample data
set from between August 2016 through June of 2017.
OK, let's try building a model to see
if we can make some predictions based on these criteria,
on these columns.
We'll add a create model statement
at the top with the table name, and give our sample a name--
say, just sample model.
And we'll pass in some options, including the fact that we're
doing a logistic regression.
So we're just going to say yes or no, one
of two options, whether or not a transaction took place.
I'll just put that at the top of our SELECT statement.
And that's really the only edit you'll
need to turn your query into a model.
Now, while that's running, I want
to point out that the aliasing on that first column
of transactions, to call it a label, that
wasn't a coincidence.
BQML looks specifically for a column named label
so that it can figure out which column you want to predict on
and which are the columns you want as inputs.
So if you don't want to create a column called label,
for some reason, or if you have a column actually called label
that you don't want to use this label,
you can set the input label calls option
in the create model statement.
Say we call that column transactions instead, right,
and there was no column called labels.
Then we could do something like this,
where we pass in the name transactions
as the column to look for as our label.
What's really handy about BQML is
that you don't need to move your data anywhere to take advantage
of its features, since it's built right into BigQuery.
This means that it can serve as a really easy way
to get a first look at any notable patterns in your data.
Once your model has started training,
you can check out the training statistics if you want--
click on the model in the left-hand panel
and go over to the Model Stats tab down below.
There, you can see the train progress
as it works through the data set and improves the model
performance with each pass.
Now, when the create model statement finishes running,
it's time to run evaluation using ML.EVALUATE.
We'll take the original SQL query that we had for the data
set and wrap it in ML.EVALUATE, passing
in the newly created model and then selecting
from that result.
You will get an output that includes
a number of different metrics of your model.
Notice that we've actually queried
dates that are outside of the range of the training data.
And specifically, it's the month immediately following the dates
that we use to train on.
We don't want to pass in the training data
for evaluation, because that would
be like cheating on a test.
Finally, it's time to run Predict.
Here, we'll wrap that same query we used during evaluation
with the ML.PREDICT function, and pass the model in as well.
This particular prediction groups
up the countries so we can see what their predictions are
for each country ranked.
We can also see that for this particular data set,
most of the transactions came from the United States,
though it might be worth diving in deeper
to see how these match up across mobile devices
and different operating systems.
So that's BigQuery ML in a nutshell-- a simple way
to do some easy machine learning right
within your SQL environment.
I'm looking forward to seeing what models you build.
Thanks for watching this episode of Cloud AI Adventures.
And if you enjoyed it, be sure to hit that Like button
and subscribe to get all the latest updates right
when they come out.
For now, check out BigQuery ML and start building
your first model today.