Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 37 ยท BigQuery ML

Introduction to BigQuery ML

Meet BigQuery ML: train, evaluate and use machine learning models with plain SQL using CREATE MODEL, ML.EVALUATE and ML.PREDICT, without moving data out.

Traditional machine learning means exporting data from the warehouse, loading it into a Python environment, training, and then shipping predictions back. BigQuery ML (BQML) removes that trip. You create, train, evaluate and run models using SQL statements, right where the data lives. A data analyst who knows SELECT can build a churn predictor or a demand forecast without leaving the console.

This lesson shows the shape of every BQML workflow, the model types available, and the statements you will use. BigQuery itself cannot run in this page, so the BQML statements are read-only. To make the ideas concrete, the runnable examples compute by hand what the simplest model type, linear regression, does inside the service.

The workflow in four statements

Every BQML project follows the same loop:

flowchart LR
  D["Training data<br/>(a table or query)"] --> C["CREATE MODEL"]
  C --> M["Model object<br/>in a dataset"]
  M --> E["ML.EVALUATE"]
  M --> P["ML.PREDICT"]
  E -->|tune| C

A model is a first-class object stored in a dataset, next to tables and views.

  1. CREATE MODEL trains a model from a SELECT.
  2. ML.EVALUATE measures how good it is on held-out data.
  3. ML.PREDICT applies it to new rows.
  4. Optional inspection: ML.WEIGHTS, ML.TRAINING_INFO, ML.FEATURE_INFO, ML.EXPLAIN_PREDICT.

Your first model, read-only

Suppose shop.customers_features has one row per customer with a numeric target lifetime_spend. This is real BigQuery syntax:

SQL
CREATE OR REPLACE MODEL `shop.spend_model`
OPTIONS (
  model_type = 'linear_reg',
  input_label_cols = ['lifetime_spend']
) AS
SELECT
  visits_last_90d,
  avg_basket,
  days_since_signup,
  lifetime_spend
FROM `shop.customers_features`
WHERE lifetime_spend IS NOT NULL;

Three things to notice. The model type is just an option. The label (what you predict) is named by input_label_cols. Every other selected column becomes a feature. BigQuery handles missing values, scales numbers and one-hot encodes strings automatically, which is why this short statement is enough.

After training you can evaluate and predict:

SQL
SELECT * FROM ML.EVALUATE(MODEL `shop.spend_model`);

SELECT *
FROM ML.PREDICT(
  MODEL `shop.spend_model`,
  (SELECT visits_last_90d, avg_basket, days_since_signup
   FROM `shop.new_customers`)
);

ML.PREDICT returns the input columns plus predicted_lifetime_spend.

What linear regression computes

A linear model predicts y = intercept + slope * x. For one feature, the best-fit slope is the covariance of x and y divided by the variance of x. You can compute that with ordinary SQL aggregates, which is exactly the arithmetic BigQuery does at scale (for several features it solves a larger system, or iterates).

SQL Linear regression with SQL aggregates

Press Run (or Ctrl + Enter) to see the result here.

Each extra visit adds roughly ten to the predicted spend. A trained BQML model stores these numbers as weights, which you can read with ML.WEIGHTS.

Predicting with the fitted line

Once you have slope and intercept, "predict" is just arithmetic on new rows. This is the job of ML.PREDICT.

Python Apply the fitted line to new customers

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that the prediction for cy (9 visits) lies outside the training range of 1 to 7. Models extrapolate poorly, a point we return to in evaluating and predicting.

Model types at a glance

Models you create with SQL

linear_reg Predict a number
logistic_reg Predict a class
Boosted trees Accurate on tables
kmeans Group similar rows

Pick by what you want to predict.

1 / 2

You can also import a trained TensorFlow or ONNX model and call it with ML.PREDICT, or register a remote model that points to a Vertex AI endpoint. The next lessons walk through regression and classification, evaluation and clustering and forecasting.

Costs and limits

Training a model is a query job and is billed by the bytes processed (on-demand) or uses slots (editions). Some model types, such as deep neural networks and boosted trees, use more compute and are billed on a different rate; check the current pricing page before big runs. Predictions with ML.PREDICT are billed like normal queries. A free monthly allowance of query data applies, which is enough for small experiments.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which statement trains a model in BigQuery ML?

    CREATE MODEL with OPTIONS(model_type=...) trains a model from a SELECT.
  2. 2. How do you tell BQML which column to predict?

    input_label_cols names the label; all other selected columns become features.
  3. 3. What does ML.PREDICT return for a linear_reg model?

    It returns the input rows with the prediction column appended, for example predicted_lifetime_spend.
  4. 4. Where does a BigQuery ML model live?

    Models are dataset objects with their own IAM and metadata.

Summary

  • BigQuery ML trains and uses models with SQL, so data never leaves the warehouse.
  • The loop is CREATE MODEL, ML.EVALUATE, ML.PREDICT.
  • model_type selects the algorithm; input_label_cols names the label; other columns are features.
  • Linear regression is slope and intercept computed from aggregates, which BigQuery does at scale.
  • Models can also be imported (TensorFlow, ONNX) or remote (Vertex AI).

Next: Regression and Classification Models