Complete BigQuery ML Tutorial: Machine Learning with SQL

BigQuery ML enables you to create and execute machine learning models using SQL queries. No data movement required - train and predict directly on your data warehouse.

Why BigQuery ML?

• SQL-based: Use familiar SQL syntax
• No data movement: Train on data in place
• Scalable: Leverage BigQuery infrastructure
• Integrated: Works with BigQuery ecosystem
• Multiple models: Classification, regression, clustering

• Linear/Logistic Regression
• XGBoost, Random Forest
• Deep Neural Networks
• K-means Clustering
• Time Series (ARIMA)
• Matrix Factorization

Prerequisites

pip install google-cloud-bigquery
gcloud auth login

Quick Start

1. Create Classification Model

-- Create logistic regression model
CREATE OR REPLACE MODEL project.dataset.churnmodel

OPTIONS(
  modeltype='LOGISTICREG',

  inputlabelcols=['churn'],

  autoclassweights=TRUE

) AS
SELECT
  age,
  tenuremonths,
  monthlycharges,

  totalcharges,
  contracttype,

  churn
FROM project.dataset.customers
WHERE PARTITIONTIME BETWEEN '2024-01-01' AND '2024-06-01';

2. Evaluate Model

-- Evaluate model performance
SELECT
  

FROM
  ML.EVALUATE(MODEL project.dataset.churnmodel,

    (SELECT  FROM project.dataset.customers
     WHERE PARTITIONTIME > '2024-06-01'));

3. Make Predictions

-- Predict churn probability
SELECT
  customerid,

  predictedchurn,
  predictedchurnprobs
FROM
  ML.PREDICT(MODEL project.dataset.churnmodel,

    (SELECT  FROM project.dataset.newcustomers));

CREATE OR REPLACE MODEL project.dataset.xgbclassifier
OPTIONS(
  modeltype='BOOSTEDTREECLASSIFIER',
  numparalleltree=5,
  maxiterations=50,

  learnrate=0.1,
  inputlabelcols=['target']
) AS
SELECT  FROM project.dataset.trainingdata;

CREATE OR REPLACE MODEL project.dataset.rfmodel
OPTIONS(
  modeltype='RANDOMFORESTCLASSIFIER',

  numparalleltree=100,

  maxtreedepth=10,

  inputlabelcols=['label']

) AS
SELECT  FROM project.dataset.trainingdata;

CREATE OR REPLACE MODEL project.dataset.dnnmodel
OPTIONS(
  modeltype='DNNCLASSIFIER',

  hiddenunits=[128, 64, 32],
  dropout=0.2,
  batchsize=256,

  inputlabelcols=['label']

) AS
SELECT  FROM project.dataset.trainingdata;

4. Linear Regression

CREATE OR REPLACE MODEL project.dataset.pricepredictor
OPTIONS(
  modeltype='LINEARREG',

  inputlabelcols=['price'],

  l2reg=0.1
) AS
SELECT
  bedrooms,
  bathrooms,
  sqft,
  location,
  price
FROM project.dataset.houses;

5. K-Means Clustering

CREATE OR REPLACE MODEL project.dataset.customersegments
OPTIONS(
  modeltype='KMEANS',
  numclusters=5,

  standardizefeatures=TRUE
) AS
SELECT
  age,
  income,
  spendingscore,

  purchasefrequency
FROM project.dataset.customers;

-- Get cluster assignments
SELECT
  customerid,

  CENTROIDID as segment
FROM
  ML.PREDICT(MODEL project.dataset.customersegments,

    (SELECT  FROM project.dataset.customers));

CREATE OR REPLACE MODEL project.dataset.salesforecast
OPTIONS(
  modeltype='ARIMAPLUS',
  timeseriestimestampcol='date',

  timeseriesdatacol='sales',
  timeseriesidcol='productid',
  autoarima=TRUE,

  datafrequency='DAILY'
) AS
SELECT
  date,
  productid,

  sales
FROM project.dataset.dailysales;

-- Forecast future sales
SELECT
  
FROM
  ML.FORECAST(MODEL project.dataset.salesforecast,

    STRUCT(30 AS horizon, 0.95 AS confidencelevel));

Feature Engineering

1. Transform Features

CREATE OR REPLACE MODEL project.dataset.transformedmodel
TRANSFORM(
  age,
  ML.BUCKETIZE(income, [30000, 60000, 100000]) AS incomebucket,
  ML.FEATURECROSS(STRUCT(gender, contracttype)) AS gendercontract,

  ML.POLYNOMIALEXPAND(STRUCT(tenure, monthlycharges), 2) AS polynomialfeatures,
  target
)
OPTIONS(
  modeltype='LOGISTICREG',
  inputlabelcols=['target']
) AS
SELECT  FROM project.dataset.customers;

CREATE OR REPLACE MODEL project.dataset.modelwithimputation
OPTIONS(
  modeltype='LINEARREG',
  inputlabelcols=['target'],
  enableglobalexplain=TRUE
) AS
SELECT
  IFNULL(age, (SELECT AVG(age) FROM project.dataset.data)) as age,
  IFNULL(income, 0) as income,
  COALESCE(city, 'Unknown') as city,
  target
FROM project.dataset.data;

CREATE OR REPLACE MODEL project.dataset.tunedmodel
OPTIONS(
  modeltype='BOOSTEDTREECLASSIFIER',
  numtrials=20,

  maxparalleltrials=5,

  hparamtuningobjectives=['ROCAUC'],
  learnrate=HPARAMRANGE(0.01, 0.3),
  maxtreedepth=HPARAMCANDIDATES([4, 6, 8, 10]),

  numparalleltree=HPARAMCANDIDATES([1, 5, 10]),
  inputlabelcols=['label']
) AS
SELECT  FROM project.dataset.trainingdata;

-- Get hyperparameter tuning results
SELECT
  trialid,
  hyperparameters,
  evalmetrics.

FROM
  ML.TRIALINFO(MODEL project.dataset.tunedmodel)

ORDER BY evalmetrics.rocauc DESC;

-- Get global feature importance
SELECT
  
FROM
  ML.GLOBALEXPLAIN(MODEL project.dataset.churnmodel);

-- Get per-prediction explanations
SELECT
  customerid,
  predictedchurn,

  topfeatureattributions

FROM
  ML.EXPLAINPREDICT(MODEL project.dataset.churnmodel,

    (SELECT  FROM project.dataset.testcustomers),
    STRUCT(5 as topkfeatures));

-- Export model to GCS
EXPORT MODEL project.dataset.churnmodel

OPTIONS(
  URI='gs://bucket/models/churnmodel',
  FORMAT='TFSAVEDMODEL'
);

-- Import TensorFlow model
CREATE OR REPLACE MODEL project.dataset.importedmodel

OPTIONS(
  modeltype='TENSORFLOW',
  modelpath='gs://bucket/models/tfmodel/'
);

3. Register with Vertex AI

from google.cloud import bigquery
from google.cloud import aiplatform

Export and register
bqclient = bigquery.Client()

bqclient.query("""
  EXPORT MODEL project.dataset.model
  OPTIONS(URI='gs://bucket/model')
""").result()

Register in Vertex AI
model = aiplatform.Model.upload(
    displayname="bqml-model",

    artifacturi="gs://bucket/model",
    servingcontainerimageuri="us-docker.pkg.dev/vertex-ai/prediction/tf2-cpu.2-8:latest"

)

from google.cloud import bigquery

client = bigquery.Client()

query = """
CREATE OR REPLACE MODEL project.dataset.model
OPTIONS(modeltype='LOGISTICREG', inputlabelcols=['target'])

AS SELECT  FROM project.dataset.trainingdata
"""

job = client.query(query)
job.result()
print("Model trained successfully")

predictionsquery = """
SELECT
  customerid,
  predictedtarget,

  predictedtargetprobs

FROM
  ML.PREDICT(MODEL project.dataset.model,
    (SELECT  FROM project.dataset.newdata))
"""

predictionsdf = client.query(predictionsquery).todataframe()

print(predictionsdf.head())

Best Practices

1. Data Splitting

-- Use deterministic splitting
CREATE OR REPLACE MODEL project.dataset.model
OPTIONS(
  modeltype='LOGISTICREG',
  datasplitmethod='CUSTOM',
  datasplitcol='splitcol',

  inputlabelcols=['target']

) AS
SELECT
  ,

  CASE
    WHEN MOD(ABS(FARMFINGERPRINT(customerid)), 10) < 8 THEN 'TRAIN'

    ELSE 'TEST'
  END as splitcol
FROM project.dataset.data;

-- Version models with timestamps
CREATE OR REPLACE MODEL project.dataset.modelv20240201

OPTIONS(...) AS ...;

-- Create view to latest model
CREATE OR REPLACE VIEW project.dataset.latestmodel AS
SELECT  FROM ML.PREDICT(MODEL project.dataset.model_v20240201, ...)

Conclusion

BigQuery ML provides:

Key takeaways:

• Use appropriate model type for task
• Apply feature engineering transforms
• Tune hyperparameters for best results
• Enable explainability for insights
• Export models for deployment