Engineer Data in Google Cloud: Challenge Lab

Photo by Luke Chesser on Unsplash

Task 1: Clean your training data

taxirides.taxi_training_data AS
(tolls_amount + fare_amount) AS fare_amount,
pickup_longitude AS pickuplon,
pickup_latitude AS pickuplat,
dropoff_longitude AS dropofflon,
dropoff_latitude AS dropofflat,
passenger_count AS passengers,
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

CREATE OR REPLACE MODEL taxirides.fare_modelTRANSFORM(  * 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

CREATE OR REPLACE TABLE taxirides.2015_fare_amount_predictions  ASSELECT * FROM ML.PREDICT(MODEL taxirides.fare_model,(  SELECT * FROM taxirides.report_prediction_data))​




Reach out to me at

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Finding the right SaaS application development tools.

Programming principles — Android app architecture by example Part 2/5

HMAC and MAC Explained Simply — Building Secure Auth With JWTs

Alces Flight to provide cloud HPC clusters for the first annual CIUK Student Cluster Competition

[Hack] Unpack, Crack tool C# .NET

5 New Key Features that Drupal 8.6 brings along!

Unconventional intro to python — Numerical Programs (III)

5 Lessons Learned (So Far) as a Programmer

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
Ajil T U

Ajil T U

Reach out to me at

More from Medium

Software Testing: Why Data needs Testing?

Understanding the Dataverse Project

Leveraging Flow Service API in Adobe Experience Platform

Tree Data Structure & Application