Engineer Data in Google Cloud: Challenge Lab

 

GSP327 : Engineer Data in Google Cloud: Challenge Lab :-


----------------------------------------------------------------------------------------------------------------------------------------------


Task - 1 : Clean your training data :-


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 :-


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 :-


CREATE OR REPLACE TABLE taxirides.2015_fare_amount_predictions

  AS

SELECT * FROM ML.PREDICT(MODEL taxirides.fare_model,(

  SELECT * FROM taxirides.report_prediction_data)

)

Location: Mumbai, Maharashtra, India

Comments

Post a Comment

Popular posts from this blog

Perform Foundational Data, ML, and AI Tasks in Google Cloud

Image
  Task 1: Run a simple Dataflow job In this task, you have to transfer the data in a CSV file to BigQuery using Dataflow via Pub/Sub. First of all, you need to create a BigQuery dataset called  lab  and a Cloud Storage bucket called with your project ID. 1.1 Created a BigQuery dataset called  lab In the Cloud Console, click on  Navigation Menu  >  BigQuery . Select your project in the left pane. Click  CREATE DATASET . Enter  lab  in the Dataset ID, then click  Create dataset . Run  gsutil cp gs://cloud-training/gsp323/lab.schema .  in the Cloud Shell to download the schema file. View the schema by running  cat lab.schema . Go back to the Cloud Console, select the new dataset  lab  and click  Create Table . In the Create table dialog, select  Google Cloud Storage  from the dropdown in the Source section. Copy  gs://cloud-training/gsp323/lab.csv  to  Select file from GCS bucket . Enter  customers  to “Table name” in the Destination section. Enable  Edit as text  and copy the JSON dat
Read more

Create ML Models with BigQuery ML:Challenge Lab

Image
  GSP341 : Create ML Models with BigQuery ML: Challenge Lab :- ---------------------------------------------------------------------------------------------------------------------------------------------- Task 1: Create a dataset to store your machine learning models :- In Cloud Shell :- bq mk austin // Navigation Menu -> BigQuery. ---------------------------------------------------------------------------------------------------------------------------------------------- Task 2: Create a forecasting BigQuery machine learning model :- // In BigQuery Console Query Editor :- CREATE OR REPLACE MODEL austin.location_model OPTIONS   (model_type='linear_reg', labels=['duration_minutes']) AS SELECT     start_station_name,     EXTRACT(HOUR FROM start_time) AS start_hour,     EXTRACT(DAYOFWEEK FROM start_time) AS day_of_week,     duration_minutes, FROM     `bigquery-public-data.austin_bikeshare.bikeshare_trips` AS trips JOIN     `bigquery-public-data.austin_bikeshare.bikesha
Read more

Ensure Access & Identity in Google Cloud: Challenge Lab

Image
  FOLLOW THE STEPS CORRECTLY: Task 1 : Create a custom security role :- Activate Cloud Shell: nano role-definition.yaml // Copy the below content to file :- title: "orca_storage_update" description: "Permissions" stage: "ALPHA" includedPermissions: - storage.buckets.get - storage.objects.get - storage.objects.list - storage.objects.update - storage.objects.create // Save by : ctrl + o -> enter -> ctrl + x gcloud iam roles create orca_storage_update --project $DEVSHELL_PROJECT_ID \ --file role-definition.yaml ---------------------------------------------------------------------------------------------------------------------------------------------- Task 2 & 3 : Create a service account and Bind a custom security role to an account :- // Navigate to IAM & Admin -> Service Accounts -> CREATE SERVICE ACCOUNT.   Service account name : orca-private-cluster-sa , then click create and add roles.   Roles : orca_storage_update           Monitori
Read more