Training and Evaluating BigQuery ML Models with SQL

BigQuery ML (opens in a new tab) is a capability in BigQuery that lets you create and train machine learning models with SQL. Models are stored in BigQuery datasets like tables and views, so training data does not have to move to Python or Java code. The CREATE MODEL (opens in a new tab) statement both creates the model object and starts training on the results of a query.

1.1The CREATE MODEL Workflow From Training to Explanation

The CREATE MODEL workflow is the ordered lifecycle that moves data from a training query to a stored model and then to predictions. The workflow starts when CREATE MODEL reads training rows and ends when you inspect and explain the trained model. Default settings handle much of the work, while options let you tune the result.

1. Create the model: Run a CREATE MODEL statement that names the model, sets OPTIONS such as MODEL_TYPE, and supplies training data with a query.
2. Preprocess features: BigQuery ML applies automatic preprocessing, and you can add manual preprocessing inside the TRANSFORM clause.
3. Refine the model: Use hyperparameter options such as NUM_TRIALS to better fit the model to the training data.
4. Evaluate the model: Check performance on data outside the training set and compare it with other models.
5. Perform inference: Use prediction functions such as ML.PREDICT to score new data with the trained model.
6. Explain the model: Review explainability output to see how features influenced a prediction and the model overall.
7. Inspect model weights: Learn about model components such as learned coefficients or cluster centers.

After training, later prediction queries read new input rows and pass them through the stored model object.

1.2Core Parts of a CREATE MODEL Statement

A CREATE MODEL statement names a destination model and defines how training should happen. The model name follows the form project.dataset.model, so the trained model lives with the data it learned from. An OPTIONS clause sets MODEL_TYPE and other settings, and a final query after AS SELECT supplies the rows and columns used for training.

The MODEL_TYPE option controls which algorithm BigQuery ML trains, so the choice must match the dataset and the desired outcome. Classification and regression models use INPUT_LABEL_COLS to identify the label column that holds the answer to predict. The TRANSFORM clause sits between the options and the training query and applies manual feature engineering every time the model trains or predicts. Data-split options such as DATA_SPLIT_METHOD, DATA_SPLIT_EVAL_FRACTION, and DATA_SPLIT_COL control which rows are reserved for evaluation during training.

Different model families require different required columns in the statement:

• Classification and regression models require INPUT_LABEL_COLS for the label column, and logistic regression labels can have up to 50 unique values.
• Matrix factorization models for recommendations require USER_COL for user data, ITEM_COL for item data, and RATING_COL for rating data.
• Time series forecasting models require TIME_SERIES_TIMESTAMP_COL for time points and TIME_SERIES_DATA_COL for the values to forecast.

1.3Internally Trained Models Inside BigQuery

Internally trained models are built-in BigQuery ML models that train entirely inside BigQuery. These models support a dry run on the CREATE MODEL statement to estimate how much data the statement will process. Choose an internally trained model when you want fast SQL-based training without an external service.

• Contribution analysis determines the effect of dimensions on a metric, such as store location and sales date on revenue.
• Linear regression predicts a numeric value, where labels must be real numbers and cannot be infinity or NaN.
• Logistic regression classifies two or more values, such as low-value, medium-value, or high-value.
• K-means clustering segments data, such as identifying customer segments, without requiring labels or split data.
• Matrix factorization builds product recommendation systems from behavior, transactions, and ratings.
• Principal component analysis (PCA) reduces dimensionality by projecting data onto the first few principal components.
• Time series models using ARIMA_PLUS and ARIMA_PLUS_XREG forecast values and detect anomalies while handling seasonality, holidays, and outliers.

1.4Externally Trained, Remote, and Imported Models

Externally trained, remote, and imported models extend BigQuery ML beyond built-in algorithms. The three categories differ by where training happens and how the CREATE MODEL statement connects to the model.

Evaluation in BigQuery ML (opens in a new tab) is the step that checks how well a trained model predicts new data. The ML.EVALUATE (opens in a new tab) function performs this check in SQL by comparing predictions against known true values and returning performance metrics. Those metrics help decide if the model is ready to use and which model performs better when models are compared.

2.1How ML.EVALUATE Measures a Stored Model

ML.EVALUATE is a SQL table function that takes a stored model and an evaluation dataset as inputs and returns a single row of statistics. Data flows from the model object and from the evaluation table or query into the function, and the function outputs metrics by matching each prediction to its actual label value. For example, predictions flow from a model such as census.census_model and true labels flow from rows filtered as dataframe = 'evaluation', so the function measures performance on data the model did not train on. The result is one summary row, not one row per prediction, which makes overall effectiveness easy to read at a glance.

2.2Automatic Evaluation During CREATE MODEL

Automatic evaluation means BigQuery ML calculates evaluation metrics during the CREATE MODEL statement and stores them with the model. The dataset used for those stored metrics depends on the model type, the data-split options, and whether hyperparameter tuning is used. To retrieve the stored metrics later, call ML.EVALUATE on the model with no input data specified.

The evaluation source varies by model group:

• K-means, principal component analysis (PCA), autoencoder, and ARIMA_PLUS models: BigQuery ML uses all of the input data as training data, and evaluation metrics are calculated against the entire input dataset.
• Linear and logistic regression, boosted tree, random forest, deep neural network (DNN), Wide-and-deep, and matrix factorization models: evaluation metrics are calculated against the dataset defined by DATA_SPLIT_METHOD, DATA_SPLIT_EVAL_FRACTION, and DATA_SPLIT_COL, plus DATA_SPLIT_TEST_FRACTION when hyperparameter tuning is used.
• AutoML Tables models: data splits follow the separate AutoML data-split rules.

Because the split controls which rows are reserved for evaluation, the same model code can produce different stored metrics when the split options change.

2.3Evaluation With a New Dataset After Training

Evaluation with a new dataset means calling ML.EVALUATE with input data specified after the model already exists. In this pattern, fresh rows flow from a table or query into the function along with the stored model, and the function returns new metrics for that specific dataset. This approach tests how the model might perform outside the training set and supports fair comparison when two models are scored on the same new data. Choose this path when validating before deployment or when checking performance over time as new labeled data arrives.

2.4Interpreting Classification and Regression Metrics

Classification and regression models use different metrics because they predict different kinds of outputs. Classification models predict categories, so their metrics describe how often predicted categories match true categories. Regression models predict numbers, so their metrics describe how far predicted numbers are from true numbers.

• Accuracy: share of all predictions that match the true category; higher means more overall correct predictions.
• Precision: share of positive predictions that are truly positive; higher means fewer false alarms for the predicted class.
• Recall: share of actual positives that the model correctly finds; higher means fewer missed cases for that class.
• Mean squared error (MSE): average of squared differences between predicted and actual numbers; lower means predictions sit closer to the true values.

A useful model shows strong classification scores or low regression error on evaluation data, not only on training data, since evaluation data better reflects unseen use.

2.5Evaluate Any Predictions With ML.METRICS

The ML.METRICS function computes evaluation metrics for classification or regression from any table or query that contains actual and predicted values. Unlike ML.EVALUATE, the ML.METRICS function does not depend on a stored model object, so predictions can flow directly from a query result into the function. This is useful when predictions come from another system, from manual logic, or from a model that was not saved in BigQuery. Choose ML.EVALUATE when validating a stored BigQuery ML model, and choose ML.METRICS when only actual and predicted columns are available.

BigQuery ML (opens in a new tab) is a capability in BigQuery that lets you create, train, and use machine learning models with SQL instead of Python or Java. Training data stays inside BigQuery tables or views, and the trained model is stored in a BigQuery dataset like a table or view. This approach brings machine learning to the data, so you do not have to move or reformat large datasets for an outside framework.

3.1Model Development Workflow in BigQuery ML

The BigQuery ML development workflow is an ordered lifecycle that moves data from training tables to a stored model and then to predictions. The workflow starts with a CREATE MODEL (opens in a new tab) statement that reads training data from BigQuery, and it ends with inference and explanation on new data. Default settings let you complete this workflow without deep machine learning knowledge, while tuning helps improve results.

1. Create the model: Run a CREATE MODEL statement that names the model, sets MODEL_TYPE, and selects training data from a table, view, or query.
2. Preprocess features: BigQuery ML applies automatic preprocessing, plus you can add manual preprocessing functions inside the TRANSFORM clause.
3. Refine the model: Perform hyperparameter tuning to better fit the model to the training data.
4. Evaluate the model: Assess how the model may perform on data outside the training set and compare it with other models.
5. Perform inference: Use the trained model to analyze new data and generate predictions.
6. Explain the model: Clarify how particular features influenced a given prediction and the model overall.
7. Inspect model weights: Learn more about the components that comprise the model.

After creation, the stored model depends on the dataset that contains it, and later prediction queries read input data and pass it through that stored model.

3.2Linear Regression for Numeric Predictions

Linear regression in BigQuery ML predicts the value of a numerical metric for new data from similar historical data. A common example is estimating a house sale price from location and house characteristics compared with past sales. You create a linear regression model by setting the MODEL_TYPE option to LINEAR_REG in the CREATE MODEL statement.

Labels for linear regression are real-valued numbers, which means they cannot be positive infinity, negative infinity, or NaN (Not a Number). During training, feature columns flow from the input table or query into the model, while the label column provides the true numeric value the model learns to predict. Choose linear regression when the answer you need is a number rather than a category.

3.3Logistic Regression for Category Predictions

Logistic regression in BigQuery ML classifies new data into two or more possible values using a model trained on similar labeled data. Examples include predicting whether an email is spam or whether a product review is positive, negative, or neutral. You create a logistic regression model by setting the MODEL_TYPE option to LOGISTIC_REG in the CREATE MODEL statement.

Labels for logistic regression are categories, and they can have up to 50 unique values such as low-value, medium-value, or high-value. During training, feature columns flow from the input table or query into the model, while the label column provides the correct category for each training row. Choose logistic regression when the answer you need is a category rather than a continuous number.

3.4K-Means Clustering for Group Discovery

K-means clustering in BigQuery ML segments data into groups without using predefined labels. A common example is identifying customer segments from purchase behavior so each customer belongs to the most similar group. K-means is an unsupervised learning technique, so model training does not require labels or split data for training or evaluation.

During training, all of the input data is used as training data, and the model learns groupings based on similarity across the selected feature columns. After training, new rows flow into the prediction function and the model assigns each row to a cluster. Choose k-means clustering when you need to discover natural groups rather than predict a known label.

3.5Choosing Between Linear Regression, Logistic Regression, and K-Means

The choice between linear regression, logistic regression, and k-means depends on the kind of question the model must answer and whether a label column exists. All three train on data from a BigQuery table, view, or query, and all three save the resulting model in the dataset for later evaluation and prediction. The key difference is whether the output is a number, a category, or a group assignment.

Key Facts

• The CREATE MODEL statement names a model as project.dataset.model, sets MODEL_TYPE in OPTIONS, and supplies training data with an AS SELECT query.
• Linear regression predicts a numeric label, logistic regression predicts a categorical label with up to 50 unique values, and k-means clustering assigns groups without a label.
• ML.EVALUATE with no input data returns metrics stored during CREATE MODEL, while ML.EVALUATE with input data scores the model on a new table or query.
• Classification effectiveness uses accuracy, precision, and recall where higher is better, while regression error uses mean squared error where lower is better.
• ML.METRICS evaluates actual and predicted columns without a stored model, while ML.EVALUATE validates a stored BigQuery ML model.