Building, fitting, and scoring a model with SQL alone

BigQuery ML lets you create, train, and evaluate machine learning models directly in BigQuery using standard SQL, eliminating the need to export data to a separate ML platform. Models are created with CREATE MODEL statements, trained on data via SELECT queries, and evaluated using built-in functions like ML.EVALUATE. This keeps data, training, and prediction all within BigQuery's serverless environment.

1 · Learn the must-know

• 1The CREATE MODEL statement defines the model type via OPTIONS (e.g., model_type='linear_reg', 'logistic_reg', 'kmeans', 'boosted_tree_classifier') and specifies the label column for supervised models.
• 2Training data is supplied via the AS SELECT clause of CREATE MODEL, so any query result (joins, filters, transformations) can become training input.
• 3ML.EVALUATE returns model-specific metrics (e.g., precision, recall, ROC AUC for classification; RMSE, R² for regression) and can be run against a held-out evaluation set or the training data if none is specified.
• 4ML.PREDICT is used to generate predictions on new data using a trained model, returning predicted labels/values alongside input features.
• 5BigQuery ML automatically splits data into training and evaluation sets when no explicit split is provided, but you can control this via options like DATA_SPLIT_METHOD.
• 6Model training and prediction incur BigQuery processing costs based on bytes processed (or flat-rate slots), and some model types (e.g., DNN, boosted trees, AutoML-backed models) may have different pricing or resource behavior than simple linear/logistic models.

2 · Check your understanding