Hyperparameter tuning BigQuery ML

BigQuery ML can use Vertex AI to tune common model parameters

CREATE OR REPLACE MODEL ch09eu.bicycle_model_linear
OPTIONS(
model_type='linear_reg', input_label_cols=['duration']
)
AS
SELECT
start_station_name,
CAST(EXTRACT(DAYOFWEEK FROM start_date) AS STRING) AS dayofweek,
CAST(EXTRACT HOUR FROM start_date) AS STRING) AS hourofday,
duration
FROM `bigquery-public-data.london_bicycles.cycle_hire`
CREATE OR REPLACE MODEL ch09eu.bicycle_model_dnn
TRANSFORM(
start_station_name,
CAST(EXTRACT(DAYOFWEEK FROM start_date) AS STRING) AS dayofweek,
ML.BUCKETIZE(EXTRACT(HOUR FROM start_date), [0, 6, 12, 18, 24]) AS hourofday,
duration
)

OPTIONS(
model_type='dnn_regressor', input_label_cols=['duration'],
hidden_units=[32, 8],
learn_rate=0.1,
dropout=0.25,
)
AS
SELECT
start_station_name, start_date, duration
FROM `bigquery-public-data.london_bicycles.cycle_hire`
CREATE OR REPLACE MODEL ch09eu.bicycle_model_dnn_hparam
TRANSFORM(
start_station_name,
CAST(EXTRACT(DAYOFWEEK FROM start_date) AS STRING) AS dayofweek,
ML.BUCKETIZE(EXTRACT(HOUR FROM start_date), [0, 6, 12, 18, 24]) AS hourofday,
duration
)
OPTIONS(
model_type='dnn_regressor', input_label_cols=['duration'],
num_trials=10,
hidden_units=hparam_candidates([struct([32,8]), struct([32]), struct([64,32,4])]),
learn_rate=hparam_range(0, 0.5),
dropout=hparam_candidates([0, 0.1, 0.25, 0.4])

)
AS
SELECT
start_station_name, start_date, duration
FROM `bigquery-public-data.london_bicycles.cycle_hire`
SELECT * FROM ML.EVALUATE(MODEL ch09eu.bicycle_model_dnn_hparam) 
ORDER BY mean_absolute_error ASC
SELECT * FROM ML.PREDICT(MODEL ch09eu.bicycle_model_dnn_hparam,
(
SELECT
CURRENT_TIMESTAMP() AS start_date,
'Waterloo Station 1, Waterloo' AS start_station_name
)
)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Lak Lakshmanan

Operating Executive at a technology investment firm; articles are personal observations and not investment advice.