Tutorial Lengkap BigQuery ML: Machine Learning dengan SQL

BigQuery ML memungkinkan Anda membuat dan mengeksekusi model machine learning menggunakan query SQL. Tidak perlu memindahkan data - training dan prediksi langsung di data warehouse Anda.

Mengapa BigQuery ML?

• Berbasis SQL: Gunakan sintaks SQL yang familiar
• Tanpa pemindahan data: Training pada data di tempat
• Scalable: Manfaatkan infrastruktur BigQuery
• Terintegrasi: Bekerja dengan ekosistem BigQuery
• 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. Buat Classification Model

-- Buat 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. Evaluasi Model

-- Evaluasi performa model
SELECT
  

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

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

3. Buat Prediksi

-- Prediksi probabilitas churn
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;

-- Dapatkan 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 penjualan masa depan
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;

-- Dapatkan hasil hyperparameter tuning
SELECT
  trialid,
  hyperparameters,
  evalmetrics.

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

ORDER BY evalmetrics.rocauc DESC;

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

-- Dapatkan penjelasan per-prediksi
SELECT
  customerid,
  predictedchurn,

  topfeatureattributions

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

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

-- Export model ke 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 dengan Vertex AI

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

Export dan register
bqclient = bigquery.Client()

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

Register di 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 berhasil ditraining")

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

-- Gunakan splitting deterministik
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 dengan timestamps
CREATE OR REPLACE MODEL project.dataset.modelv20240201

OPTIONS(...) AS ...;

-- Buat view ke model terbaru
CREATE OR REPLACE VIEW project.dataset.latestmodel AS
SELECT  FROM ML.PREDICT(MODEL project.dataset.model_v20240201, ...)

Kesimpulan

BigQuery ML menyediakan:

Key takeaways:

• Gunakan tipe model yang sesuai untuk tugas
• Terapkan transforms feature engineering
• Tune hyperparameters untuk hasil terbaik
• Aktifkan explainability untuk insights
• Export models untuk deployment