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!