Using BigQuery ML to predict birth weight

Beta

This is a beta release of BigQuery ML. This product might be changed in backward-incompatible ways and is not subject to any SLA or deprecation policy.

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

You incur charges for:

• Storing your ML model and training data in BigQuery
  • The first 10 GB of storage is free each month.
• Querying data in BigQuery
  • The first 1 TB is free each month.
  • If you are using flat-rate pricing, query costs are included in the monthly flat-rate price.
• Running BigQuery ML SQL statements

Before you begin

1. Sign in to your Google Account.

   If you don't already have one, sign up for a new account.

2. Select or create a GCP project.

   Go to the Manage resources page

3. Make sure that billing is enabled for your project.

   Learn how to enable billing

4. BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, go to Enable the BigQuery API.

   Enable the 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
• 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:

1. Go to the BigQuery web UI.

   Go to the BigQuery web UI

2. In the navigation panel, in the Resources section, click your project name.

3. On the right side, in the details panel, click Create dataset.

4. 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.

     

5. 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 as child_race in the table schema). To force BigQuery ML to treat mother_race as a non-numeric feature, with each distinct value representing a different category, the query casts mother_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:

1. In the BigQuery web UI, click the Compose New Query button.

2. 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
   

3. Click Run query.

   The query takes a few minutes to complete. After the first iteration is complete, your model (natality_model) appears in the navigation panel of the BigQuery web UI. Because the query uses a CREATE 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:

1. In the BigQuery web UI, in the Resources section, expand [PROJECT_ID] > bqml_tutorial and then click natality_model.

2. Click the Model stats tab. The results should look like the following:

   

   The Training Data Loss column represents the loss metric calculated after the given iteration 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).

   For more details on the ML.TRAINING_INFO function, 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.

Note: A proper evaluation would be on a subset of the natality table that is separate from the data used to train the model. For simplicity, this step is omitted from this tutorial.

Run the ML.EVALUATE query

To run the ML.EVALUATE query that evaluates the model:

1. In the BigQuery web UI, click the Compose New Query button.

2. 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))
   

3. (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.

4. Click Run query.

5. 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 R² score. The R² 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:

1. In the BigQuery web UI, click the Compose New Query button.

2. 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"))
   

3. (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.

4. Click Run query.

5. 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:

1. If necessary, open the web UI.

   Go to the web UI

2. In the navigation, hover on the bqml_tutorial dataset you created.

3. 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.

4. 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:

Warning: Deleting a project has the following consequences:

• If you used an existing project, you'll also delete any other work you've done in the project.
• You can't reuse the project ID of a deleted project. If you created a custom project ID that you plan to use in the future, delete the resources inside the project instead. This step ensures that URLs that use the project ID, such as an appspot.com URL, remain available.

If you are exploring multiple tutorials and quickstarts, reusing projects instead of deleting them prevents you from exceeding project quota limits.

1. In the GCP Console, go to the Projects page.

   Go to the Projects page

2. In the project list, select the project you want to delete and click Delete project.
3. 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.