BigQuery ML in SQL

BigQuery ML trains and runs models with CREATE MODEL and ML.PREDICT. Built-in models train inside BigQuery and are billed per byte read, at 50 times the normal query rate. Here is what each step returns, what it costs, and the defaults that quietly change your results.

BigQuery ML lets you train a model with CREATE MODEL ... AS SELECT, then call it from a query with ML.PREDICT, ML.EVALUATE or ML.EXPLAIN_PREDICT. The model is an object in a dataset. Training reads your data once or many times depending on the model type, and you pay for the bytes it reads at $312.50 per TiB for the simple built-in models, 50 times the $6.25 per TiB of an on-demand query. Prediction and evaluation are billed at the normal query rate. BigQuery ML isn’t available in the Standard edition.

The steps, run on a tiny model

Everything below we ran on real BigQuery against a linear regression of house price on size and bedrooms. These are plain blocks, not runnable on this page, because a model has to be created in a dataset: CREATE TEMP MODEL failed in our test.

CREATE OR REPLACE MODEL mydataset.price_model
OPTIONS (model_type = 'LINEAR_REG', input_label_cols = ['price'], data_split_method = 'NO_SPLIT')
AS
SELECT sqft, bedrooms, price FROM UNNEST([
  STRUCT(600 AS sqft, 1 AS bedrooms, 150.0 AS price),
  STRUCT(800, 2, 200.0), STRUCT(1000, 2, 240.0), STRUCT(1200, 3, 310.0),
  STRUCT(1500, 3, 365.0), STRUCT(1800, 4, 450.0), STRUCT(2000, 4, 480.0), STRUCT(2400, 5, 600.0)])

The training data came from an inline UNNEST, so the job processed 0 bytes and was billed 0. Eight rows still took 100 seconds, and the job statistics show 20 iterations of 2.5 to 6.5 seconds each (the default max_iterations is 20). Creating a model takes minutes even when the data is tiny.

ML.PREDICT returns your input columns plus predicted_<label>:

SELECT * FROM ML.PREDICT(MODEL mydataset.price_model, (SELECT 1000 AS sqft, 2 AS bedrooms));
-- predicted_price 240.337, sqft 1000, bedrooms 2

ML.EVALUATE takes held-out rows with the label and returns error metrics. With two rows we got mean_absolute_error 3.57 and r2_score 0.997. Called with no data argument it evaluates on the data the model was trained on or held out, which with NO_SPLIT or under 500 rows is the training data. ML.WEIGHTS returns the coefficients (0.150 per square foot, 47.1 per bedroom, an intercept of -4.0 here), and ML.EXPLAIN_PREDICT adds the contribution of each feature to a prediction, with a baseline of 349.4 and attributions of -61.9 for size and -47.1 for bedrooms.

What ML.PREDICT does with bad input

The input to ML.PREDICT is matched to the model by column name. We tested what happens when it isn’t what the model was trained on:

The last row is the dangerous one. A NULL feature isn’t an error. The 287.5 equals the prediction for 3 bedrooms, the average of the training data, so BigQuery substituted the mean, and nothing in the output says so. Filter or flag NULL features yourself before you trust a prediction. The type error also shows that the INT64 columns of the training data became FLOAT64 features.

Splitting and evaluating

data_split_method defaults to AUTO_SPLIT. With fewer than 500 rows all rows train. From 500 to 50,000 rows, 20% is held out for evaluation. Above 50,000 rows, 10,000 rows are held out. Use NO_SPLIT for a tiny table, RANDOM for control over the fractions, SEQ to split in the order of a column and CUSTOM to mark the evaluation rows yourself.

Preprocessing with TRANSFORM

Put the feature preprocessing in a TRANSFORM clause and BigQuery stores it with the model, so ML.PREDICT gets raw columns and applies the same scaling and bucketing:

CREATE OR REPLACE MODEL mydataset.price_model_t
TRANSFORM (
  ML.STANDARD_SCALER(sqft) OVER () AS sqft_z,
  ML.BUCKETIZE(bedrooms, [2, 4]) AS bed_bucket,
  price
)
OPTIONS (model_type = 'LINEAR_REG', input_label_cols = ['price'])
AS SELECT sqft, bedrooms, price FROM mydataset.listings;

SELECT * FROM ML.PREDICT(MODEL mydataset.price_model_t, (SELECT 1000 AS sqft, 2 AS bedrooms));

On the same data this gave a prediction of 250.7 from the raw sqft and bedrooms. ML.WEIGHTS then lists the processed inputs: sqft_z, bed_bucket (with a weight for each of its categories) and the intercept. The scaling functions also work outside a model, in a plain query:

SELECT
  sqft,
  ML.BUCKETIZE(sqft, [800, 1500]) AS size_bucket,
  ROUND(ML.MIN_MAX_SCALER(sqft) OVER (), 3) AS min_max,
  ROUND(ML.STANDARD_SCALER(sqft) OVER (), 3) AS standardized,
  ROUND(ML.MAX_ABS_SCALER(sqft) OVER (), 3) AS max_abs
FROM UNNEST([600, 1000, 1500, 2400]) AS sqft
ORDER BY sqft

ML.BUCKETIZE puts a value equal to a split point in the higher bucket, and names buckets bin_1, bin_2 and so on:

SELECT x, ML.BUCKETIZE(x, [10, 20]) AS bucket
FROM UNNEST([5, 10, 15, 20, 25]) AS x
ORDER BY x

Rare categories become 0

ML.ONE_HOT_ENCODER and ML.LABEL_ENCODER only keep categories that appear at least frequency_threshold times, and the default is 5. Every other category is encoded as 0. On a small table that is every category:

-- each city appears fewer than 5 times, so the default frequency_threshold of 5 encodes all of them as 0
SELECT city, ML.LABEL_ENCODER(city) OVER () AS default_code
FROM UNNEST(['leeds', 'york', 'leeds', 'bath']) AS city
ORDER BY city

Pass the arguments explicitly, ML.LABEL_ENCODER(city, 10, 1) for the top 10 values with a threshold of 1:

-- label_encoder(value, top_k, frequency_threshold)
SELECT city, ML.LABEL_ENCODER(city, 10, 1) OVER () AS code
FROM UNNEST(['leeds', 'york', 'leeds', 'bath']) AS city
ORDER BY city

On a real table the threshold of 5 is fine. It matters on test data, in small segments, and for any column where most values are rare.

Which model types exist

The dry-run column is from Google’s introduction: you can dry-run CREATE MODEL for the built-in models to get the training bytes, and you can’t for models trained on Vertex AI. Contribution analysis and matrix factorization are listed with the built-in models; the pricing page puts matrix factorization under editions or reservations only.

What it costs

Google’s pricing note says CREATE MODEL stops at 50 iterations for iterative models. Time series models with auto_arima are billed for each candidate model they fit, so the bytes are multiplied by the number of candidates, 3 to 42 depending on auto_arima_max_order and the differencing order. A model’s storage is billed at the normal storage rates. A remote model’s queries are billed by BigQuery for the bytes and by Vertex AI for the model calls.

Because built-in training is 50 times the query rate, the cost is the table you train on. Training on 1 TiB costs $312.50 once, however small the model. Dry-run the CREATE MODEL first, and train on a sample table or a narrow query that selects only the label and the features. See how queries are billed for how bytes are counted.

Forecasting models are covered in forecasting, and the models that call a Vertex AI endpoint in AI functions.