Google Cloud

Engineer Data in Google Cloud Challenge Lab Solution

Hello Friends in this article i am gone to share Engineer Data in Google Cloud Challenge Lab Solution with you..


Also visit:ย  Build a Website on Google Cloud Challenge Lab Solution


 

Engineer Data in Google Cloud Challenge Lab Solution

Main Topics

  1. Create a new BigQuery table from existing data
  2. Clean data for ML Model using BigQuery, Dataprep or Dataflow
  3. Build and tune a model in BQML
  4. Perform a batch prediction into a new table with BQML

 

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

Task 1) Clean your training data

  • Copy & Run This Query to Clean your training data.
  • Once Query Execution is complete go back to qwiklabs page & Click on
  • Check My Progress Button try 2 or more times…
CREATE OR REPLACE TABLE
taxirides.taxi_training_data AS
SELECT
(tolls_amount + fare_amount) AS fare_amount,
pickup_datetime,
pickup_longitude AS pickuplon,
pickup_latitude AS pickuplat,
dropoff_longitude AS dropofflon,
dropoff_latitude AS dropofflat,
passenger_count AS passengers,
FROM
taxirides.historical_taxi_rides_raw
WHERE
RAND() < 0.001
AND trip_distance > 0
AND fare_amount >= 2.5
AND pickup_longitude > -78
AND pickup_longitude < -70
AND dropoff_longitude > -78
AND dropoff_longitude < -70
AND pickup_latitude > 37
AND pickup_latitude < 45
AND dropoff_latitude > 37
AND dropoff_latitude < 45
AND passenger_count > 0

Task 2) Create a BQML model called taxirides.fare_model

  • By Running this Query it will Create a BQML model that’s called taxirides.fare_model
  • Once Query Execution is complete go back to qwiklabs page & Click on
  • Check My Progress Button try 2 or more times…
CREATE OR REPLACE MODEL taxirides.fare_model
TRANSFORM(
* EXCEPT(pickup_datetime)
, ST_Distance(ST_GeogPoint(pickuplon, pickuplat), ST_GeogPoint(dropofflon, dropofflat)) AS euclidean
, CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING) AS dayofweek
, CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING) AS hourofday
)
OPTIONS(input_label_cols=['fare_amount'], model_type='linear_reg')
AS
SELECT * FROM taxirides.taxi_training_data

Task 3) Perform a batch prediction on new data.

  • Run this Query to get a fresh data.
  • Once Query Execution is complete go back to qwiklabs page & Click on
  • Check My Progress Button try 2 or more times…
CREATE OR REPLACE TABLE taxirides.2015_fare_amount_predictions
AS
SELECT * FROM ML.PREDICT(MODEL taxirides.fare_model,(
SELECT * FROM taxirides.report_prediction_data)
)

Once you get 100 points on this lab, now you can Click Button to End this lab.

Now You Successfully Clear this Qwiklabs.