Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 37 ยท BigQuery ML

Clustering, Forecasting and Remote Models

Go beyond prediction in BigQuery ML: segment customers with k-means, forecast time series with ARIMA_PLUS, and call Vertex AI or imported models from SQL.

Not every machine learning task has a label. Sometimes you want to find natural groups in your customers, or predict the next twelve weeks of sales from the history alone. BigQuery ML supports both with unsupervised and time-series models, and lets you reach beyond its built-in algorithms through imported and remote models.

All statements in this lesson are BigQuery syntax, shown read-only. The runnable examples build small stand-ins: a hand-written k-means and a seasonal forecast in Python, so you see what the service does behind CREATE MODEL.

K-means clustering

kmeans groups rows so that rows in one cluster are close to each other in feature space. There is no input_label_cols:

SQL
CREATE OR REPLACE MODEL `shop.segments`
OPTIONS (
  model_type = 'kmeans',
  num_clusters = 4,
  standardize_features = TRUE
) AS
SELECT recency_days, order_count, total_spend
FROM `shop.rfm_features`;

Assign every customer to a segment with ML.PREDICT, which adds CENTROID_ID, and inspect the centers with ML.CENTROIDS:

SQL
SELECT customer_id, CENTROID_ID
FROM ML.PREDICT(MODEL `shop.segments`, TABLE `shop.rfm_features`);

SELECT * FROM ML.CENTROIDS(MODEL `shop.segments`);

If you omit num_clusters, BigQuery chooses a number automatically. Label each segment from its centroid ("high spend, recent" is your best customers) and join the results back to marketing tables.

Python K-means with two features

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.

The loop is the whole algorithm: assign each point to the nearest centroid, move each centroid to the mean of its group, repeat. BigQuery does it on millions of rows in parallel.

Time-series forecasting with ARIMA_PLUS

arima_plus trains a model that captures trend, seasonality (weekly, yearly), holidays and outliers. You name the time and value columns, and optionally an ID column to train one model per series:

SQL
CREATE OR REPLACE MODEL `shop.sales_forecast`
OPTIONS (
  model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'sale_date',
  time_series_data_col = 'revenue',
  time_series_id_col = 'store_id',
  holiday_region = 'US',
  auto_arima = TRUE
) AS
SELECT sale_date, store_id, revenue
FROM `shop.daily_sales`;

Forecast the future with ML.FORECAST, which returns the prediction and a confidence interval:

SQL
SELECT *
FROM ML.FORECAST(MODEL `shop.sales_forecast`,
                 STRUCT(28 AS horizon, 0.9 AS confidence_level));

ML.DETECT_ANOMALIES flags points that fall outside the expected range, and ML.EXPLAIN_FORECAST splits a forecast into trend and seasonal parts. A newer TimesFM option in the AI.FORECAST family forecasts without training a model of your own; check the current documentation for its availability in your region.

Python A seasonal forecast by hand

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.

The forecast combines a seasonal pattern with a trend, the two components arima_plus models, plus noise terms and confidence intervals that this toy version leaves out.

Recommendations with matrix factorization

matrix_factorization learns hidden "taste" factors for users and items from ratings or implicit feedback such as purchases. ML.RECOMMEND then returns the highest-scored items per user:

SQL
CREATE OR REPLACE MODEL `shop.recs`
OPTIONS (
  model_type = 'matrix_factorization',
  user_col = 'customer_id',
  item_col = 'book_id',
  rating_col = 'rating',
  feedback_type = 'EXPLICIT'
) AS
SELECT customer_id, book_id, rating FROM `shop.book_ratings`;

Implicit feedback needs flat-rate slot capacity (an edition reservation) rather than on-demand billing for this model type, so check the documentation before planning it.

Imported and remote models

You are not limited to the built-in algorithms.

Bring your own model

Imported TensorFlow, ONNX, XGBoost files
Remote Vertex AI endpoint
Generative Gemini from SQL

All called with ML.PREDICT.

1 / 2

An imported model is a file in Cloud Storage that BigQuery loads and runs inside the warehouse. A remote model is a pointer to a Vertex AI endpoint or a foundation model, reached through a connection resource:

SQL
CREATE OR REPLACE MODEL `shop.sentiment`
REMOTE WITH CONNECTION `us.vertex_conn`
OPTIONS (endpoint = 'gemini-2.0-flash');

SELECT review_id, ml_generate_text_llm_result
FROM ML.GENERATE_TEXT(
  MODEL `shop.sentiment`,
  (SELECT review_id,
          CONCAT('Classify the sentiment of this review: ', review_text) AS prompt
   FROM `shop.reviews`),
  STRUCT(0.2 AS temperature, 64 AS max_output_tokens, TRUE AS flatten_json_output)
);

Generative calls are billed by the model provider per token in addition to the query, so test with a small sample first, as described in controlling query cost.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which model type groups rows without a label?

    kmeans is unsupervised; it finds clusters from the features alone.
  2. 2. Which function returns future values from an ARIMA_PLUS model?

    ML.FORECAST returns forecasted values with prediction intervals for a horizon you choose.
  3. 3. What does time_series_id_col do?

    With an ID column, BigQuery fits one time series per ID value in a single statement.
  4. 4. How is a remote model different from an imported one?

    Imported models are loaded into BigQuery from Cloud Storage; remote models call Vertex AI or a foundation model.

Summary

  • kmeans finds segments without labels; ML.PREDICT returns a CENTROID_ID.
  • ARIMA_PLUS models trend, seasonality and holidays; ML.FORECAST projects the future with intervals.
  • matrix_factorization powers recommendations through ML.RECOMMEND.
  • Imported and remote models extend BQML to TensorFlow, ONNX, Vertex AI and generative models.
  • Generative calls have extra per-token cost; test on samples first.

Next: IAM Roles and Dataset Access