Building Demand Forecasting with BigQuery ML

[saumya_prasad]

Can someone help me with the code for this task Explore the NYC Citi Bike Trips dataset which is the 2nd task for the lab . I am copying and pasting the given code but the green tick is not showing. I tried3-4 times. 

SELECT
EXTRACT (DATE FROM TIMESTAMP(starttime)) AS start_date,
start_station_id,
COUNT(*) as total_trips
FROM
`bigquery-public-data.new_york_citibike.citibike_trips`
WHERE
starttime BETWEEN DATE('2016-01-01') AND DATE('2017-01-01')
GROUP BY
start_station_id, start_date
LIMIT 5

Building Demand Forecasting with BigQuery ML

[harshit1921 · Accepted solution]

Task 1 - Explore the NYC Citi Bike Trips dataset

SELECT
   bikeid,
   starttime,
   start_station_name,
   end_station_name,
FROM
  `bigquery-public-data.new_york_citibike.citibike_trips`
LIMIT 5

SELECT
  EXTRACT (DATE FROM TIMESTAMP(starttime)) AS start_date,
  start_station_id,
  COUNT(*) as total_trips
FROM
 `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE
   starttime BETWEEN DATE('2016-01-01') AND DATE('2017-01-01')
GROUP BY
    start_station_id, start_date
LIMIT 5

 Task 2 - Cleaned training data

Select Create Dataset.

Enter the dataset name as bqmlforecast, and Default table expration as 1 day.

bqmlforecast

Select the Create dataset button.

SELECT
 DATE(starttime) AS trip_date,
 start_station_id,
 COUNT(*) AS num_trips
FROM
 `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE
 starttime BETWEEN DATE('2014-01-01') AND ('2016-01-01')
 AND start_station_id IN (521,435,497,293,519)
GROUP BY
 start_station_id,
 trip_date

• Select SAVE RESULTS .
• In the dropdown menu, select BigQuery Table.
• Add a table name of training_data . 

training_data​

• Select SAVE .

Task 3- Training a Model

CREATE OR REPLACE MODEL bqmlforecast.bike_model
  OPTIONS(
    MODEL_TYPE='ARIMA',
    TIME_SERIES_TIMESTAMP_COL='trip_date',
    TIME_SERIES_DATA_COL='num_trips',
    TIME_SERIES_ID_COL='start_station_id',
    HOLIDAY_REGION='US'
  ) AS
  SELECT
    trip_date,
    start_station_id,
    num_trips
  FROM
    bqmlforecast.training_data

Task -4 Evaluate the time series model

SELECT
  *
FROM
  ML.EVALUATE(MODEL bqmlforecast.bike_model)

Task-5  Make Predictions using the model

 DECLARE HORIZON STRING DEFAULT "30"; #number of values to forecast
 DECLARE CONFIDENCE_LEVEL STRING DEFAULT "0.90";
 EXECUTE IMMEDIATE format("""
     SELECT
         *
     FROM
       ML.FORECAST(MODEL bqmlforecast.bike_model,
                   STRUCT(%s AS horizon,
                          %s AS confidence_level)
                  )
     """, HORIZON, CONFIDENCE_LEVEL)

  Hope this helps!

[harshit1921]

Hi, Give me a minute I'll send codes for every task.

[saumya_prasad]

Thanks a lot.

[harshit1921 · Accepted solution]

Task 1 - Explore the NYC Citi Bike Trips dataset

SELECT
   bikeid,
   starttime,
   start_station_name,
   end_station_name,
FROM
  `bigquery-public-data.new_york_citibike.citibike_trips`
LIMIT 5

SELECT
  EXTRACT (DATE FROM TIMESTAMP(starttime)) AS start_date,
  start_station_id,
  COUNT(*) as total_trips
FROM
 `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE
   starttime BETWEEN DATE('2016-01-01') AND DATE('2017-01-01')
GROUP BY
    start_station_id, start_date
LIMIT 5

 Task 2 - Cleaned training data

Select Create Dataset.

Enter the dataset name as bqmlforecast, and Default table expration as 1 day.

bqmlforecast

Select the Create dataset button.

SELECT
 DATE(starttime) AS trip_date,
 start_station_id,
 COUNT(*) AS num_trips
FROM
 `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE
 starttime BETWEEN DATE('2014-01-01') AND ('2016-01-01')
 AND start_station_id IN (521,435,497,293,519)
GROUP BY
 start_station_id,
 trip_date

• Select SAVE RESULTS .
• In the dropdown menu, select BigQuery Table.
• Add a table name of training_data . 

training_data​

• Select SAVE .

Task 3- Training a Model

CREATE OR REPLACE MODEL bqmlforecast.bike_model
  OPTIONS(
    MODEL_TYPE='ARIMA',
    TIME_SERIES_TIMESTAMP_COL='trip_date',
    TIME_SERIES_DATA_COL='num_trips',
    TIME_SERIES_ID_COL='start_station_id',
    HOLIDAY_REGION='US'
  ) AS
  SELECT
    trip_date,
    start_station_id,
    num_trips
  FROM
    bqmlforecast.training_data

Task -4 Evaluate the time series model

SELECT
  *
FROM
  ML.EVALUATE(MODEL bqmlforecast.bike_model)

Task-5  Make Predictions using the model

 DECLARE HORIZON STRING DEFAULT "30"; #number of values to forecast
 DECLARE CONFIDENCE_LEVEL STRING DEFAULT "0.90";
 EXECUTE IMMEDIATE format("""
     SELECT
         *
     FROM
       ML.FORECAST(MODEL bqmlforecast.bike_model,
                   STRUCT(%s AS horizon,
                          %s AS confidence_level)
                  )
     """, HORIZON, CONFIDENCE_LEVEL)

  Hope this helps!

[saumya_prasad]

1st Code itself is not working for me... :")

[harshit1921]

Hi, try now I made some changes.

[saumya_prasad]

Oh okay I tried it still to no avail Maybe I'll take the lab again.

[harshit1921]

Yeah, restart the lab. I just redid the lab it works fine for me.

[harshit1921]

In first task there are two sql commands not one.

[saumya_prasad]

Yes....I am running the 2nd one but im not getting the tick mark.

[harshit1921]

Did you do everything else like making dataset and then saving output in a table?

[saumya_prasad]

I am getting stuck in the task 2 itself...so i have not moved further.

[harshit1921]

These are part of task 2. check the code provided by me above

[saumya_prasad]

hey, Even though task 2 is not showing green tick, it is successful.

[saumya_prasad]

Thanks dude for providing the codes. 

[saumya_prasad]

I moved further in the lab (task 2 is not successful yet) all the other tasks are happening except this one, i.e. Task 2.

[Himanshu_rokde]

Yes, it happens sometimes that the full code is not loaded. Just refresh the page and the code should load again completely.

[saumya_prasad]

I did..but not happening.

[Himanshu_rokde]

If that happens, just go to the question mark on the top, and select chat support. I told of the same issue once to them and they sent me the code.

[saumya_prasad]

Even though task 2 is not showing green tick, it is successful. Thanks a lot.

[saumya_prasad · Accepted solution]

Task 2 is not showing Green Tick but do all the further steps , the lab will get completed. 

Even though task 2 is not showing green tick, it is successful. I hope this helps!!!!