This tutorial introduces data analysts to BigQuery ML. BigQuery ML enables users to create and execute machine learning models in BigQuery using SQL queries. The goal is to democratize machine learning by enabling SQL practitioners to build models using their existing tools and to increase development speed by eliminating the need for data movement.
In this tutorial, you use the natality
sample table
to create a model that predicts the birth weight of a child based on the baby’s
gender, the length of the pregnancy, and demographic information about the
mother. The natality
sample table contains information about every birth in the
United States over a 40 year period.
Objectives
In this tutorial, you use:
- BigQuery ML to create a binary linear regression model using the
CREATE MODEL
statement - The
ML.EVALUATE
function to evaluate the ML model - The
ML.PREDICT
function to make predictions using the ML model
Costs
This tutorial uses billable components of Cloud Platform, including:
- Google BigQuery
- BigQuery ML
For more information on BigQuery costs, see the Pricing page.
For more information on BigQuery ML costs, see the BigQuery ML pricing section of the pricing page.
Before you begin
-
Sign in to your Google Account.
If you don't already have one, sign up for a new account.
-
Select or create a GCP project.
-
Make sure that billing is enabled for your project.
- BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, go to Enable the BigQuery API.
Audience
This is an introductory tutorial that is intended for data analysts.
A data analyst uses BigQuery standard SQL to analyze data trends that inform business strategy and operations. This includes using BigQuery ML to train ML models, to evaluate ML models, and to do predictive analytics.
Data analysts use a variety of primarily UI-based tools including:
- The BigQuery web UI in the GCP Console
- Spreadsheets
- Statistical software such as RStudio
- Visualization tools such as Cloud Datalab and Data Studio
Step one: Create your dataset
The first step is to create a BigQuery dataset to store your ML model. To create your dataset:
Go to the BigQuery web UI in the GCP Console.
In the navigation panel, in the Resources section, click your project name.
On the right side, in the details panel, click Create dataset.
On the Create dataset page:
- For Dataset ID, enter
bqml_tutorial
. For Data location, choose United States (US). Currently, the public datasets are stored in the
US
multi-region location. For simplicity, you should place your dataset in the same location.
- For Dataset ID, enter
Leave all of the other default settings in place and click Create dataset.
Step two: Create your model
Next, you create a linear regression model using the natality sample table for BigQuery. The following standard SQL query is used to create the model you use to predict the birth weight of a child.
#standardSQL CREATE MODEL `bqml_tutorial.natality_model` OPTIONS (model_type='linear_reg', input_label_cols=['weight_pounds']) AS SELECT weight_pounds, is_male, gestation_weeks, mother_age, CAST(mother_race AS string) AS mother_race FROM `bigquery-public-data.samples.natality` WHERE weight_pounds IS NOT NULL AND RAND() < 0.001
In addition to creating the model, running the CREATE MODEL
command trains the
model you create.
Query details
The CREATE MODEL
clause is used to create and train the model named bqml_tutorial.natality_model
.
The OPTIONS(model_type='linear_reg', input_label_cols=['weight_pounds'])
clause
indicates that you are creating a linear regression
model. A linear regression is a type of regression model that generates a
continuous value from a linear combination of input features. The weight_pounds
column is is the input label column. For linear regression models, the label
column must be real-valued (the column values must be real numbers).
This query's SELECT
statement uses the following columns to predict a child's
birth weight:
weight_pounds
— Weight of the child, in pounds (FLOAT64).is_male
— TRUE if the child is male, FALSE if female (BOOL).gestation_weeks
— The number of weeks of the pregnancy (INT64).mother_age
— Reported age of the mother when giving birth (INT64).mother_race
— An integer value that corresponds to the race of the mother (INT64 - same aschild_race
in the table schema). To force BigQuery ML to treatmother_race
as a non-numeric feature, with each distinct value representing a different category, the query castsmother_race
to a STRING. This is important because race is more likely to have more meaning as a category rather than an integer (which has ordering and scale).
The FROM
clause — bigquery-public-data.samples.natality
—
indicates that you are querying the natality sample table in the samples dataset.
This dataset is in the bigquery-public-data
project.
The WHERE
clause — WHERE weight_pounds IS NOT NULL AND RAND() < 0.001
— excludes rows where weight is NULL and uses the RAND
function to draw
a random sample of the data.
Run the CREATE MODEL
query
To run the CREATE MODEL
query to create and train your model:
In the BigQuery web UI, click the Compose New Query button.
Enter the following standard SQL query in the Query editor text area.
#standardSQL CREATE MODEL `bqml_tutorial.natality_model` OPTIONS (model_type='linear_reg', input_label_cols=['weight_pounds']) AS SELECT weight_pounds, is_male, gestation_weeks, mother_age, CAST(mother_race AS string) AS mother_race FROM `bigquery-public-data.samples.natality` WHERE weight_pounds IS NOT NULL AND RAND() < 0.001
Click Run query.
The query takes about 30 seconds to complete, after which your model (
natality_model
) appears in the navigation panel of the BigQuery web UI. Because the query uses aCREATE MODEL
statement to create a table, you do not see query results.
(Optional) Step three: Get training statistics
To see the results of the model training, you can use the
ML.TRAINING_INFO
function, or you can view the statistics in the BigQuery web UI. In this tutorial, you
use the BigQuery web UI.
A machine learning algorithm builds a model by examining many examples and attempting to find a model that minimizes loss. This process is called empirical risk minimization.
Loss is the penalty for a bad prediction — a number indicating how bad the model's prediction was on a single example. If the model's prediction is perfect, the loss is zero; otherwise, the loss is greater. The goal of training a model is to find a set of weights and biases that have low loss, on average, across all examples.
To see the model training statistics that were generated when you ran the
CREATE MODEL
query:
In the BigQuery web UI, in the Resources section, expand [PROJECT_ID] > bqml_tutorial and then click natality_model.
Click the Model stats tab. The results should look like the following:
The Training Data Loss column represents the loss metric calculated after the model is trained on the training dataset. Since you performed a linear regression, this column is the mean squared error. The Evaluation Data Loss column is the same loss metric calculated on the holdout dataset (data that is held back from training to validate the model). "normal_equation" optimize strategy is automatically used for the training here, thus only one iteration is required to converge to the final model. For more details on the
optimize_strategy
option, see the The CREATE MODEL Statement.For more details on the
ML.TRAINING_INFO
function and "optimize_strategy" training option, see the BigQuery ML syntax reference.
Step four: Evaluate your model
After creating your model, you evaluate the performance of the classifier using
the ML.EVALUATE
function. The ML.EVALUATE
function evaluates the predicted
values against the actual data. A classifier is one of a set of enumerated target
values for a label.
The query used to evaluate the model is as follows:
#standardSQL SELECT * FROM ML.EVALUATE(MODEL `bqml_tutorial.natality_model`, ( SELECT weight_pounds, is_male, gestation_weeks, mother_age, CAST(mother_race AS STRING) AS mother_race FROM `bigquery-public-data.samples.natality` WHERE weight_pounds IS NOT NULL))
Query details
The top-most SELECT
statement retrieves the columns from your model.
The FROM
clause uses the ML.EVALUATE
function against your model: bqml_tutorial.natality_model
.
This query's nested SELECT
statement and FROM
clause are the same as those
in the CREATE MODEL
query.
The WHERE
clause — WHERE weight_pounds IS NOT NULL
— excludes
rows where weight is NULL.
Run the ML.EVALUATE
query
To run the ML.EVALUATE
query that evaluates the model:
In the BigQuery web UI, click the Compose New Query button.
Enter the following standard SQL query in the Query editor text area.
#standardSQL SELECT * FROM ML.EVALUATE(MODEL `bqml_tutorial.natality_model`, ( SELECT weight_pounds, is_male, gestation_weeks, mother_age, CAST(mother_race AS STRING) AS mother_race FROM `bigquery-public-data.samples.natality` WHERE weight_pounds IS NOT NULL))
(Optional) To set the processing location, click Options > Query Settings. For Processing Location, choose
US
. This step is optional because the processing location is automatically detected based on the dataset's location.Click Run query.
When the query is complete, click the Results tab below the query text area. The results should look like the following:
Because you performed a linear regression, the results include the following columns:
mean_absolute_error
mean_squared_error
mean_squared_log_error
median_absolute_error
r2_score
explained_variance
An important metric in the evaluation results is the R2 score. The R2 score is a statistical measure that determines if the linear regression predictions approximate the actual data. 0 indicates that the model explains none of the variability of the response data around the mean. 1 indicates that the model explains all the variability of the response data around the mean.
Step five: Use your model to predict outcomes
Now that you have evaluated your model, the next step is to use it to predict an outcome. You use your model to predict the birth weight of all babies born in Wyoming.
The query used to predict the outcome is as follows:
#standardSQL SELECT predicted_weight_pounds FROM ML.PREDICT(MODEL `bqml_tutorial.natality_model`, ( SELECT is_male, gestation_weeks, mother_age, CAST(mother_race AS STRING) AS mother_race FROM `bigquery-public-data.samples.natality` WHERE state = "WY"))
Query details
The top-most SELECT
statement retrieves the predicted_weight_pounds
column.
This column is generated by the ML.PREDICT
function. When you use the
ML.PREDICT
function the output column name for the model is
predicted_<label_column_name>
. For linear regression models, predicted_label
is the estimated value of label
. For logistic regression models,
predicted_label
is one of the two input labels depending on which label has
the higher predicted probability.
The ML.PREDICT
function is used to predict results using your model: bqml_tutorial.natality_model
.
This query's nested SELECT
statement and FROM
clause are the same as those
in the CREATE MODEL
query.
The WHERE
clause — WHERE state = “WY”
— indicates that you are
limiting the prediction to the state of Wyoming.
Run the ML.PREDICT
query
To run the query that uses the model to predict an outcome:
In the BigQuery web UI, click the Compose New Query button.
Enter the following standard SQL query in the Query editor text area.
#standardSQL SELECT predicted_weight_pounds FROM ML.PREDICT(MODEL `bqml_tutorial.natality_model`, ( SELECT is_male, gestation_weeks, mother_age, CAST(mother_race AS STRING) AS mother_race FROM `bigquery-public-data.samples.natality` WHERE state = "WY"))
(Optional) To set the processing location, click Options > Query Settings. For Processing Location, choose
US
. This step is optional because the processing location is automatically detected based on the dataset's location.Click Run query.
When the query is complete, click the Results tab below the query text area. The results should look like the following:
Cleaning up
To avoid incurring charges to your Google Cloud Platform account for the resources used in this tutorial:
- You can delete the project you created.
- Or you can keep the project and delete the dataset.
Deleting your dataset
Deleting your project removes all datasets and all tables in the project. If you prefer to reuse the project, you can delete the dataset you created in this tutorial:
If necessary, open the web UI.
In the navigation, hover on the bqml_tutorial dataset you created.
Click the down arrow icon
next to your dataset name in the navigation pane and click Delete dataset. This action deletes the dataset, the table, and all the data.
In the Delete dataset dialog box, confirm the delete command by typing the name of your dataset (
bqml_tutorial
) and then click OK.
Deleting your project
To delete the project:
- In the GCP Console, go to the Projects page.
- In the project list, select the project you want to delete and click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.
What's next
- To learn more about machine learning, see the Machine learning crash course.
- For an overview of BigQuery ML, see Introduction to BigQuery ML.
- To learn more about the web UI, see Using the BigQuery web UI.