Using BigQuery ML to Predict Birth Weight

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:

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.

      Create dataset page

  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:

    ML.TRAINING_INFO output

    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.

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:

    ML.EVALUATE output

    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:

  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:

    ML.PREDICT output

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 down arrow image 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:

  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. After selecting the checkbox next to the project name, click
      Delete project
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

What's next

Was this page helpful? Let us know how we did:

Send feedback about...

Need help? Visit our support page.