%%bigquery
SELECT * FROM flights.tzcorr
LIMIT 5

%%bigquery
SELECT * FROM flights.trainday
LIMIT 5

"""
Explanation: <h1> Training flight delay model in BigQuery ML </h1>

Run this notebook in Cloud AI Platform Notebooks. In this notebook, we will use BigQuery ML to train the same model that we did in Spark ML.
Note how much easier this is ... (and also much more scaleable)
Verify dataset
Let's make sure that we have the traindays and flights data in BigQuery. If you don't, please follow steps in the README.md in this directory.
End of explanation
"""


%%bigquery
CREATE OR REPLACE MODEL flights.arr_delay_lm
OPTIONS(input_label_cols=['ontime'], 
        model_type='logistic_reg', 
        data_split_method='custom',
        data_split_col='is_eval_day')
AS

SELECT
  IF(arr_delay < 15, 'ontime', 'late') AS ontime
  , dep_delay
  , taxi_out
  , distance
  , IF(is_train_day = 'True', False, True) AS is_eval_day
FROM flights.tzcorr f
JOIN flights.trainday t
ON f.FL_DATE = t.FL_DATE
WHERE
  f.CANCELLED = '0.00' AND 
  f.DIVERTED = '0.00'

%%bigquery
SELECT * 
FROM ML.EVALUATE(MODEL flights.arr_delay_lm,
                 (
SELECT
  IF(arr_delay < 15, 'ontime', 'late') AS ontime
  , dep_delay
  , taxi_out
  , distance
FROM flights.tzcorr f
JOIN flights.trainday t
ON f.FL_DATE = t.FL_DATE
WHERE
  f.CANCELLED = '0.00' AND 
  f.DIVERTED = '0.00' AND
  t.is_train_day = 'False'
                 ),
                 STRUCT(0.7 AS threshold))

"""
Explanation: <h2> Logistic regression </h2>

This query will take about 10 minutes.
End of explanation
"""


%%bigquery

WITH predictions AS (
SELECT 
  *
FROM ML.PREDICT(MODEL flights.arr_delay_lm,
                 (
SELECT
  IF(arr_delay < 15, 'ontime', 'late') AS ontime
  , dep_delay
  , taxi_out
  , distance
FROM flights.tzcorr f
JOIN flights.trainday t
ON f.FL_DATE = t.FL_DATE
WHERE
  f.CANCELLED = '0.00' AND 
  f.DIVERTED = '0.00' AND
  t.is_train_day = 'False'
                 ),
                 STRUCT(0.7 AS threshold))),

stats AS (
SELECT 
  COUNTIF(ontime != 'ontime' AND ontime = predicted_ontime) AS correct_cancel
  , COUNTIF(predicted_ontime = 'ontime') AS total_noncancel
  , COUNTIF(ontime = 'ontime' AND ontime = predicted_ontime) AS correct_noncancel
  , COUNTIF(ontime != 'ontime') AS total_cancel
  , SQRT(SUM((IF(ontime = 'ontime', 1, 0) - p.prob) * (IF(ontime = 'ontime', 1, 0) - p.prob))/COUNT(*)) AS rmse
FROM predictions, UNNEST(predicted_ontime_probs) p
WHERE p.label = 'ontime'
)

SELECT
   correct_cancel / total_cancel AS correct_cancel
   , total_noncancel
   , correct_noncancel / total_noncancel AS correct_noncancel
   , total_cancel
   , rmse
FROM stats

"""
Explanation: Do same metrics as in Spark code
We are using ML.PREDICT and computing the necessary stats
End of explanation
"""


%%bigquery
CREATE OR REPLACE MODEL flights.arr_delay_airports_lm
OPTIONS(input_label_cols=['ontime'], 
        model_type='logistic_reg', 
        data_split_method='custom',
        data_split_col='is_eval_day')
AS

SELECT
  IF(arr_delay < 15, 'ontime', 'late') AS ontime
  , dep_delay
  , taxi_out
  , distance
  , origin
  , dest
  , IF(is_train_day = 'True', False, True) AS is_eval_day
FROM flights.tzcorr f
JOIN flights.trainday t
ON f.FL_DATE = t.FL_DATE
WHERE
  f.CANCELLED = '0.00' AND 
  f.DIVERTED = '0.00'

%%bigquery
SELECT * 
FROM ML.EVALUATE(MODEL flights.arr_delay_airports_lm,
                 (
SELECT
  IF(arr_delay < 15, 'ontime', 'late') AS ontime
  , dep_delay
  , taxi_out
  , distance
  , origin
  , dest
FROM flights.tzcorr f
JOIN flights.trainday t
ON f.FL_DATE = t.FL_DATE
WHERE
  f.CANCELLED = '0.00' AND 
  f.DIVERTED = '0.00' AND
  t.is_train_day = 'False'
                 ),
                 STRUCT(0.7 AS threshold))

%%bigquery

WITH predictions AS (
SELECT 
  *
FROM ML.PREDICT(MODEL flights.arr_delay_airports_lm,
                 (
SELECT
  IF(arr_delay < 15, 'ontime', 'late') AS ontime
  , dep_delay
  , taxi_out
  , origin
  , dest
  , distance
FROM flights.tzcorr f
JOIN flights.trainday t
ON f.FL_DATE = t.FL_DATE
WHERE
  f.CANCELLED = '0.00' AND 
  f.DIVERTED = '0.00' AND
  t.is_train_day = 'False'
                 ),
                 STRUCT(0.7 AS threshold))),

stats AS (
SELECT 
  COUNTIF(ontime != 'ontime' AND ontime = predicted_ontime) AS correct_cancel
  , COUNTIF(predicted_ontime = 'ontime') AS total_noncancel
  , COUNTIF(ontime = 'ontime' AND ontime = predicted_ontime) AS correct_noncancel
  , COUNTIF(ontime != 'ontime') AS total_cancel
  , SQRT(SUM((IF(ontime = 'ontime', 1, 0) - p.prob) * (IF(ontime = 'ontime', 1, 0) - p.prob))/COUNT(*)) AS rmse
FROM predictions, UNNEST(predicted_ontime_probs) p
WHERE p.label = 'ontime'
)

SELECT
   correct_cancel / total_cancel AS correct_cancel
   , total_noncancel
   , correct_noncancel / total_noncancel AS correct_noncancel
   , total_cancel
   , rmse
FROM stats

"""
Explanation: Add airport info
Add airport information to model (note two additional columns: origin and dest). This seemingly simple change adds two categorical variables that, when one-hot-encoded, adds 600+ new columns to the model. BigQuery ML doesn't skip a beat ...
End of explanation
"""


%%bigquery
CREATE OR REPLACE MODEL flights.arr_delay_airports_dnn
OPTIONS(input_label_cols=['ontime'], 
        model_type='dnn_classifier',
        hidden_units=[64, 8],
        data_split_method='custom',
        data_split_col='is_eval_day')
AS

SELECT
  IF(arr_delay < 15, 'ontime', 'late') AS ontime
  , dep_delay
  , taxi_out
  , distance
  , origin
  , dest
  , IF(is_train_day = 'True', False, True) AS is_eval_day
FROM flights.tzcorr f
JOIN flights.trainday t
ON f.FL_DATE = t.FL_DATE
WHERE
  f.CANCELLED = '0.00' AND 
  f.DIVERTED = '0.00'

"""
Explanation: Note that the addition of the airports information has improved both the AUC and the RMSE
Deep Neural Network
This is from Chapter 9, but in order to keep all the BigQuery ML stuff in one place, I'm updating the Chapter 7 notebook.
Let's train a DNN model instead of a linear model
End of explanation
"""


%%bigquery

WITH predictions AS (
SELECT 
  *
FROM ML.PREDICT(MODEL flights.arr_delay_airports_lm,
                 (
SELECT
  IF(arr_delay < 15, 'ontime', 'late') AS ontime
  , dep_delay
  , taxi_out
  , origin
  , dest
  , distance
FROM flights.tzcorr f
JOIN flights.trainday t
ON f.FL_DATE = t.FL_DATE
WHERE
  f.CANCELLED = '0.00' AND 
  f.DIVERTED = '0.00' AND
  t.is_train_day = 'False'
                 ),
                 STRUCT(0.7 AS threshold))),

stats AS (
SELECT 
  COUNTIF(ontime != 'ontime' AND ontime = predicted_ontime) AS correct_cancel
  , COUNTIF(predicted_ontime = 'ontime') AS total_noncancel
  , COUNTIF(ontime = 'ontime' AND ontime = predicted_ontime) AS correct_noncancel
  , COUNTIF(ontime != 'ontime') AS total_cancel
  , SQRT(SUM((IF(ontime = 'ontime', 1, 0) - p.prob) * (IF(ontime = 'ontime', 1, 0) - p.prob))/COUNT(*)) AS rmse
FROM predictions, UNNEST(predicted_ontime_probs) p
WHERE p.label = 'ontime'
)

SELECT
   correct_cancel / total_cancel AS correct_cancel
   , total_noncancel
   , correct_noncancel / total_noncancel AS correct_noncancel
   , total_cancel
   , rmse
FROM stats

"""
Explanation: And evaluate this model as before
End of explanation
"""


