#!pip install google-cloud-bigquery
%load_ext google.cloud.bigquery

"""
Explanation: How to forecast time series in BigQuery ML
This notebook accompanies the article
How to do time series forecasting in BigQuery
Install library and extensions if needed
You don't need to do this if you use AI Platform Notebooks
End of explanation
"""


import matplotlib.pyplot as plt
import pandas as pd

def plot_historical_and_forecast(input_timeseries, timestamp_col_name, data_col_name, forecast_output=None, actual=None):
  input_timeseries = input_timeseries.sort_values(timestamp_col_name)    

  plt.figure(figsize=(20,6))
  plt.plot(input_timeseries[timestamp_col_name], input_timeseries[data_col_name], label = 'Historical')
  plt.xlabel(timestamp_col_name)
  plt.ylabel(data_col_name)

  if forecast_output is not None:
    forecast_output = forecast_output.sort_values('forecast_timestamp')
    forecast_output['forecast_timestamp'] = pd.to_datetime(forecast_output['forecast_timestamp'])
    x_data = forecast_output['forecast_timestamp']
    y_data = forecast_output['forecast_value']
    confidence_level = forecast_output['confidence_level'].iloc[0] * 100
    low_CI = forecast_output['confidence_interval_lower_bound']
    upper_CI = forecast_output['confidence_interval_upper_bound']
    # Plot the data, set the linewidth, color and transparency of the
    # line, provide a label for the legend
    plt.plot(x_data, y_data, alpha = 1, label = 'Forecast', linestyle='--')
    # Shade the confidence interval
    plt.fill_between(x_data, low_CI, upper_CI, color = '#539caf', alpha = 0.4, label = str(confidence_level) + '% confidence interval')

  # actual
  if actual is not None:
    actual = actual.sort_values(timestamp_col_name)
    plt.plot(actual[timestamp_col_name], actual[data_col_name], label = 'Actual', linestyle='--')   

  # Display legend
  plt.legend(loc = 'upper center', prop={'size': 16})

"""
Explanation: Helper plot functions
End of explanation
"""


%%bigquery df
SELECT
   CAST(EXTRACT(date from start_date) AS TIMESTAMP) AS date
   , COUNT(*) AS numrentals
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE start_station_name LIKE '%Hyde%'  -- all stations in Hyde Park
GROUP BY date
HAVING date BETWEEN '2015-05-01' AND '2015-06-15'
ORDER BY date

plot_historical_and_forecast(df, 'date', 'numrentals');

"""
Explanation: Plot the time series
The first step, as with any machine learning problem is to gather the training data and explore it. Assume that we have the data on rentals until mid-June of 2015 and we'd like to predict for the rest of the month. We can gather the past 6 weeks of data using
End of explanation
"""


!bq ls ch09eu || bq mk --location EU ch09eu

%%bigquery
CREATE OR REPLACE MODEL ch09eu.numrentals_forecast
OPTIONS(model_type='ARIMA',
        time_series_data_col='numrentals',
        time_series_timestamp_col='date') AS
SELECT
   CAST(EXTRACT(date from start_date) AS TIMESTAMP) AS date
   , COUNT(*) AS numrentals
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE start_station_name LIKE '%Hyde%'  -- all stations in Hyde Park
GROUP BY date
HAVING date BETWEEN '2015-05-01' AND '2015-06-15'

"""
Explanation: Train ARIMA model
We can use this data to train an ARIMA model, telling BigQuery which column is the data column and which one the timestamp column:
End of explanation
"""


%%bigquery fcst
SELECT * FROM ML.FORECAST(MODEL ch09eu.numrentals_forecast, 
                  STRUCT(14 AS horizon, 0.9 AS confidence_level))

plot_historical_and_forecast(df, 'date', 'numrentals', fcst);

%%bigquery actual
SELECT
   CAST(EXTRACT(date from start_date) AS TIMESTAMP) AS date
   , COUNT(*) AS numrentals
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE start_station_name LIKE '%Hyde%'  -- all stations in Hyde Park
GROUP BY date
HAVING date BETWEEN '2015-06-16' AND '2015-07-01'
ORDER BY date

plot_historical_and_forecast(df, 'date', 'numrentals', fcst, actual);

"""
Explanation: We can get the forecast data using:
End of explanation
"""


%%bigquery
CREATE OR REPLACE MODEL ch09eu.numrentals_forecast
OPTIONS(model_type='ARIMA',
        time_series_data_col='numrentals',
        time_series_timestamp_col='date',
        time_series_id_col='start_station_name') AS
SELECT
   start_station_name
   , CAST(EXTRACT(date from start_date) AS TIMESTAMP) AS date
   , COUNT(*) AS numrentals
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE start_station_name LIKE '%Hyde%'  -- all stations in Hyde Park
GROUP BY start_station_name, date
HAVING date BETWEEN '2015-01-01' AND '2015-06-15'

"""
Explanation: Forecasting a bunch of series
So far, I have been forecasting the overall rental volume for all the bicycle stations in Hyde Park. How do we predict the rental volume for each individual station? Use the time_series_id_col:
End of explanation
"""


%%bigquery
SELECT * 
FROM ML.ARIMA_COEFFICIENTS(MODEL ch09eu.numrentals_forecast)
ORDER BY start_station_name

%%bigquery fcst
SELECT 
  * 
FROM ML.FORECAST(MODEL ch09eu.numrentals_forecast, 
                  STRUCT(14 AS horizon, 0.9 AS confidence_level))
ORDER By start_station_name, forecast_timestamp

%%bigquery df
SELECT
   start_station_name
   , CAST(EXTRACT(date from start_date) AS TIMESTAMP) AS date
   , COUNT(*) AS numrentals
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE start_station_name LIKE '%Hyde%'  -- all stations in Hyde Park
GROUP BY start_station_name, date
HAVING date BETWEEN '2015-05-01' AND '2015-06-15'  -- this is just for plotting, hence we'll keep this 45 days.

%%bigquery actual
SELECT
   start_station_name
   , CAST(EXTRACT(date from start_date) AS TIMESTAMP) AS date
   , COUNT(*) AS numrentals
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE start_station_name LIKE '%Hyde%'  -- all stations in Hyde Park
GROUP BY start_station_name, date
HAVING date BETWEEN '2015-06-16' AND '2015-07-01'

"""
Explanation: Note that instead of training the series on 45 days (May 1 to June 15), I'm now training on a longer time period.
That's because the aggregate time series will tend to be smoother and much easier to predict than the time series
for individual stations. So, we have to show the model a longer trendline.
End of explanation
"""


%%bigquery stations
SELECT DISTINCT start_station_name
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE start_station_name LIKE '%Hyde%'  -- all stations in Hyde Park
ORDER by start_station_name ASC

stations

station = stations['start_station_name'].iloc[3] # Hyde Park Corner
print(station)
plot_historical_and_forecast(df[df['start_station_name']==station],
                             'date', 'numrentals',
                             fcst[fcst['start_station_name']==station], 
                             actual[actual['start_station_name']==station]);

station = stations['start_station_name'].iloc[6] # Serpentine Car Park,
print(station)
plot_historical_and_forecast(df[df['start_station_name']==station],
                             'date', 'numrentals',
                             fcst[fcst['start_station_name']==station], 
                             actual[actual['start_station_name']==station]);

station = stations['start_station_name'].iloc[4] # Knightsbridge
print(station)
plot_historical_and_forecast(df[df['start_station_name']==station],
                             'date', 'numrentals',
                             fcst[fcst['start_station_name']==station], 
                             actual[actual['start_station_name']==station]);

"""
Explanation: As you would expect, the aggregated time series over all the stations is much smoother and more predictable than the time series of just one station (the one station data will be more noisy). So, some forecasts will be better than others.
End of explanation
"""


%%bigquery
SELECT * FROM ML.EVALUATE(MODEL ch09eu.numrentals_forecast)
ORDER BY variance DESC

"""
Explanation: Evaluation
As you can see from the graphs above, the predictions accuracy varies by station. Can we gauge how good the prediction for a station is going to be?
End of explanation
"""


