Google Cloud

Create ML Models with BigQuery ML Challenge Lab Solution

by Niyander

In this article i am gone to share Create ML Models with BigQuery ML Challenge Lab Solution with you..

Also visit:ย  Engineer Data in Google Cloud Challenge Lab Solution

 

Create ML Models with BigQuery ML Challenge Lab Solution

Main Task

Task 1) Create a dataset to store your machine learning models
Task 2) Create a forecasting BigQuery machine learning model
Task 3) Create the second machine learning model
Task 4) Evaluate the two machine learning models
Task 5) Use the subscriber type machine learning model to predict average trip durations

Follow My Steps to get 100 out of 100 score in this Qwiklabs.

Task 1) Create a dataset to store your machine learning models
  • First Activate Cloud Shell
Create ML Models with BigQuery ML Challenge Lab Solution

Copy this Query & Run in Cloud Shell

bq mk austin
Note: It can be called any name to pass the test but for the remaining instructions to work use `austin` as the dataset name.
Once Query Execution is complete go back to qwiklabs page & Click onย 
Check My Progress Button try 2 or more times…
Task 2) Create a forecasting BigQuery machine learning model.
First using this command to export your project id
1st) Copy this Query & Run in Cloud Shell
export PROJECT_ID=DEVSHELL_PROJECT_ID
2nd) Copy this Query & Run in Cloud Shell
bq query --use_legacy_sql=false "CREATE OR REPLACE MODEL austin.austin_1 OPTIONS(input_label_cols=['duration_minutes'],
model_type='linear_reg') AS SELECT duration_minutes, location, start_station_name, CAST(EXTRACT(dayofweek FROM start_time) AS STRING)
as dayofweek, CAST(EXTRACT(hour FROM start_time) AS STRING) AS hourofday, FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
AS trips JOIN `bigquery-public-data.austin_bikeshare.bikeshare_stations` AS stations ON trips.start_station_id = stations.station_id
WHERE EXTRACT(year from start_time) = 2018"
Once Query Execution is complete go back to qwiklabs page & Click onย 
Check My Progress Button try 2 or more times…

Task 3) Create the second machine learning model

1st) Copy this Query & Run in Cloud Shell
export PROJECT_ID=DEVSHELL_PROJECT_ID
2nd) Copy this Query & Run in Cloud Shell
bq query –use_legacy_sql=false “CREATE OR REPLACE MODEL austin.austin_2 OPTIONS(input_label_cols=[‘duration_minutes’],
model_type=’linear_reg’) AS SELECT duration_minutes, subscriber_type, start_station_name, CAST(EXTRACT(hour FROM start_time) AS STRING)

AS hourofday FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` AS trips WHERE EXTRACT(year from start_time) = 2018″

Once Query Execution is complete go back to qwiklabs page & Click onย 
Check My Progress Button try 2 or more times…
Task 4) Evaluate the two machine learning models.
1st) Copy this Query & Run in Cloud Shell
bq query --use_legacy_sql=false "SELECT SQRT(mean_squared_error)AS rmse, mean_absolute_error FROM ML.EVALUATE(MODEL austin.austin_1, 
(SELECT duration_minutes, location, start_station_name, CAST(EXTRACT(dayofweek FROM start_time) AS STRING) as dayofweek, CAST(EXTRACT(hour FROM start_time) 
AS STRING) AS hourofday, FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` AS trips JOIN `bigquery-public-data.austin_bikeshare.bikeshare_stations` 
AS stations ON trips.start_station_id = stations.station_id WHERE EXTRACT(year from start_time) = 2019))"
2nd) Copy this Query & Run in Cloud Shell
bq query --use_legacy_sql=false "SELECT SQRT(mean_squared_error)AS rmse, mean_absolute_error FROM ML.EVALUATE(MODEL austin.austin_2, 
(SELECT duration_minutes, subscriber_type, start_station_name, CAST(EXTRACT(hour FROM start_time) AS STRING) 
AS hourofday FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` AS trips WHERE EXTRACT(year from start_time) = 2019))"
Once Query Execution is complete go back to qwiklabs page & Click onย 
Check My Progress Button try 2 or more times…
Task 5) Use the subscriber type machine learning model to predict average trip durations
1st) Copy this Query & Run in Cloud Shell
bq query --use_legacy_sql=false "SELECT start_station_name, avg(duration_minutes) as avg_duration, count(*) 
as total_trips FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` </span > 
WHERE EXTRACT(year FROM start_time) = 2019 GROUP BY start_station_name ORDER BY total_trips DESC"
2st) Copy this Query & Run in Cloud Shell
bq query --use_legacy_sql=false "SELECTย  ย avg(predicted_duration_minutes),count(duration_minutes)
FROM Ml.PREDICT(MODEL austin.austin_2, (SELECT duration_minutes, subscriber_type, start_station_name,
CAST(EXTRACT(hour FROM start_time) AS STRING) AS hourofday FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` 
AS trips WHERE EXTRACT(year from start_time)=2019 and start_station_name = '21st & Speedway @PCL' and subscriber_type='Single Trip' ))"
Once Query Execution is complete go back to qwiklabs page & Click onย 
Check My Progress Button try 2 or more times…

Once you get 100 points on this lab, now you can Click Button to End this lab.
Now You Successfully Clear this Qwiklabs.