%%bq query --name texi_query_eval
SELECT
  unique_key,
  fare,
  CAST(EXTRACT(DAYOFWEEK FROM trip_start_timestamp) AS STRING) as weekday,
  CAST(EXTRACT(DAYOFYEAR FROM trip_start_timestamp) AS STRING) as day,
  CAST(EXTRACT(HOUR FROM trip_start_timestamp) AS STRING) as hour,
  pickup_latitude,
  pickup_longitude,
  dropoff_latitude,
  dropoff_longitude,
  company
FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
WHERE 
  fare > 2.0 AND fare < 200.0 AND
  pickup_latitude IS NOT NULL AND
  pickup_longitude IS NOT NULL AND
  dropoff_latitude IS NOT NULL AND
  dropoff_longitude IS NOT NULL AND
  MOD(ABS(FARM_FINGERPRINT(unique_key)), 100) < 5
  

%%bq query --name texi_query_train
SELECT
  unique_key,
  fare,
  CAST(EXTRACT(DAYOFWEEK FROM trip_start_timestamp) AS STRING) as weekday,
  CAST(EXTRACT(DAYOFYEAR FROM trip_start_timestamp) AS STRING) as day,
  CAST(EXTRACT(HOUR FROM trip_start_timestamp) AS STRING) as hour,
  pickup_latitude,
  pickup_longitude,
  dropoff_latitude,
  dropoff_longitude,
  company
FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
WHERE 
  fare > 2.0 AND fare < 200.0 AND
  pickup_latitude IS NOT NULL AND
  pickup_longitude IS NOT NULL AND
  dropoff_latitude IS NOT NULL AND
  dropoff_longitude IS NOT NULL AND
  MOD(ABS(FARM_FINGERPRINT(unique_key)), 100) >= 5

"""
Explanation: About This Notebook
This notebook demonstrate how to use ML Workbench to create a regression model that accepts numeric and categorical data. This one shows "cloud run" mode, which does each step in Google Cloud Platform with various services. Cloud run can be distributed so it can handle large data without being restricted on memory, computation, or disk limits. The notebook is similar to last one (Taxi Fare Model (small data)), but it uses full data (about 77M instances).
There are only a few things that need to change between "local run" and "cloud run":

all data sources or file paths must be on GCS.
the --cloud flag must be set for each step.
"cloud_config" can be set for cloud specific settings, such as project_id, machine_type. In some cases it is required.

Other than this, nothing else changes from local to cloud!
Note: "Run all cells" does not work for this notebook because the steps are asynchonous. In many steps it submits a cloud job, and you should track the status by following the job link.
Execution of this notebook requires Google Datalab (see setup instructions).
The Data
We will use Chicago Taxi Trip Data. Using pickup location, drop off location, taxi company, the model we will build predicts the trip fare.
Split Data Into Train/Eval Sets
Use bigquery to select the features we need and also randomly choose 5% for eval, 95% for training.
End of explanation
"""


%%bq datasets create --name chicago_taxi

%%bq execute
query: texi_query_eval
table: chicago_taxi.eval
mode: overwrite

%%bq execute
query: texi_query_train
table: chicago_taxi.train
mode: overwrite

"""
Explanation: Create "chicago_taxi.train" and "chicago_taxi.eval" BQ tables to store results.
End of explanation
"""


%%bq query
SELECT count(*) FROM chicago_taxi.train

%%bq query
SELECT count(*) FROM chicago_taxi.eval

"""
Explanation: Sanity check on the data.
End of explanation
"""


import google.datalab.contrib.mlworkbench.commands # this loads the %%ml commands

%%ml dataset create
name: taxi_data_full
format: bigquery
train: chicago_taxi.train
eval: chicago_taxi.eval

!gsutil mb gs://datalab-chicago-taxi-demo # Create a Storage Bucket to store results.

"""
Explanation: Explore Data
See previous notebook (Taxi Fare Model (small data)) for data exploration.
Create Model with ML Workbench
The MLWorkbench Magics are a set of Datalab commands that allow an easy code-free experience to training, deploying, and predicting ML models. This notebook will take the data in BigQuery tables and build a regression model. The MLWorkbench Magics are a collection of magic commands for each step in ML workflows: analyzing input data to build transforms, transforming data, training a model, evaluating a model, and deploying a model.
For details of each command, run with --help. For example, "%%ml train --help".
This notebook will run the analyze, transform, and training steps in cloud with services. Notice the "--cloud" flag is set for each step.
End of explanation
"""


!gsutil rm -r -f gs://datalab-chicago-taxi-demo/analysis # Remove previous analysis results if any

%%ml analyze --cloud
output: gs://datalab-chicago-taxi-demo/analysis
data: taxi_data_full
features:
  unique_key:
    transform: key
  fare:
    transform: target         
  company:
    transform: embedding
    embedding_dim: 10
  weekday:
    transform: one_hot
  day:
    transform: one_hot
  hour:
    transform: one_hot
  pickup_latitude:
    transform: scale    
  pickup_longitude:
    transform: scale
  dropoff_latitude:
    transform: scale
  dropoff_longitude:
    transform: scale

"""
Explanation: Step 1: Analyze
The first step in the MLWorkbench workflow is to analyze the data for the requested transformations. Analysis in this case builds vocabulary for categorical features, and compute numeric stats for numeric features.
End of explanation
"""


!gsutil -m rm -r -f gs://datalab-chicago-taxi-demo/transform # Remove previous transform results if any.

"""
Explanation: Step 2: Transform
The transform step performs some transformations on the input data and saves the results to a special TensorFlow file called a TFRecord file containing TF.Example protocol buffers. This allows training to start from preprocessed data. If this step is not used, training would have to perform the same preprocessing on every row of csv data every time it is used. As TensorFlow reads the same data row multiple times during training, this means the same row would be preprocessed multiple times. By writing the preprocessed data to disk, we can speed up training.
The transform is required if your source data is in BigQuery table.
We run the transform step for the training and eval data.
End of explanation
"""


%%ml transform --cloud
output: gs://datalab-chicago-taxi-demo/transform
analysis: gs://datalab-chicago-taxi-demo/analysis
data: taxi_data_full

!gsutil list gs://datalab-chicago-taxi-demo/transform/eval-*

%%ml dataset create
name: taxi_data_transformed
format: transformed
train: gs://datalab-chicago-taxi-demo/transform/train-*
eval: gs://datalab-chicago-taxi-demo/transform/eval-*

"""
Explanation: Transform takes about 6 hours in cloud. Data is fairely big (33GB) and processing locally on a single VM would be much longer.
End of explanation
"""


!gsutil -m rm -r -f gs://datalab-chicago-taxi-demo/train # Remove previous training results.

"""
Explanation: Step 3: Training
MLWorkbench help build standard TensorFlow models without you having to write any TensorFlow code. We already know from last notebook that DNN regression model works better.
End of explanation
"""


%%ml train --cloud
output: gs://datalab-chicago-taxi-demo/train
analysis: gs://datalab-chicago-taxi-demo/analysis
data: taxi_data_transformed
model_args:
    model: dnn_regression
    hidden-layer-size1: 400
    hidden-layer-size2: 200
    train-batch-size: 1000
    max-steps: 1000000
cloud_config:
    region: us-east1
    scale_tier: STANDARD_1

"""
Explanation: Training takes about 30 min with "STANRDARD_1" scale_tier. Note that we will perform 1M steps. This will take much longer if we run it locally on Datalab's VM. With CloudML Engine, it runs training in a distributed way with multiple VMs, so it runs much faster.
End of explanation
"""


# Delete previous results
!gsutil -m rm -r gs://datalab-chicago-taxi-demo/batch_prediction

"""
Explanation: Step 4: Evaluation using batch prediction
Below, we use the evaluation model and run batch prediction in cloud. For demo purpose, we will use the evaluation data again.
End of explanation
"""


%%bq extract
table: chicago_taxi.eval
format: csv
path: gs://datalab-chicago-taxi-demo/eval.csv

"""
Explanation: Currently, batch_prediction service does not work with BigQuery data. So we export eval data to csv file.
End of explanation
"""


%%ml batch_predict --cloud
model: gs://datalab-chicago-taxi-demo/train/evaluation_model
output: gs://datalab-chicago-taxi-demo/batch_prediction
format: csv
data:
  csv: gs://datalab-chicago-taxi-demo/eval.csv
cloud_config:
  region: us-east1

"""
Explanation: Run batch prediction. Note that we use evaluation_model because it takes input data with target (truth) column.
End of explanation
"""


!gsutil list -l -h gs://datalab-chicago-taxi-demo/batch_prediction

"""
Explanation: Once batch prediction is done, check results files. Batch prediction service outputs to JSON files.
End of explanation
"""


%%bq load
format: json
mode: overwrite  
table: chicago_taxi.eval_results
path: gs://datalab-chicago-taxi-demo/batch_prediction/prediction.results*
schema:
  - name: unique_key
    type: STRING
  - name: predicted
    type: FLOAT
  - name: target
    type: FLOAT

"""
Explanation: We can load the results back to BigQuery.
End of explanation
"""


%%ml evaluate regression
bigquery: chicago_taxi.eval_results

"""
Explanation: With data in BigQuery can do some query analysis. For example, RMSE.
End of explanation
"""


%%bq query
SELECT
  predicted, 
  target,
  ABS(predicted-target) as error,
  s.* 
FROM `chicago_taxi.eval_results` as r 
JOIN `chicago_taxi.eval` as s 
ON r.unique_key = s.unique_key 
ORDER BY error DESC
LIMIT 10

"""
Explanation: From above, the results are better than local run with sampled data. RMSE reduced by 2.5%, MAE reduced by around 20%. Average absolute error reduced by around 30%.
Select top results sorted by error.
End of explanation
"""


%%bq query --name error_by_hour
SELECT
  COUNT(*) as count,
  hour as feature,
  AVG(ABS(predicted - target)) as avg_error,
  STDDEV(ABS(predicted - target)) as stddev_error
FROM `chicago_taxi.eval_results` as r
JOIN `chicago_taxi.eval` as s 
ON r.unique_key = s.unique_key 
GROUP BY hour

# Note: the interactive output is replaced with a static image so it displays well in github.
# Please execute this cell to see the interactive component.

from google.datalab.ml import FeatureSliceView

FeatureSliceView().plot(error_by_hour)

%%bq query --name error_by_weekday
SELECT
  COUNT(*) as count,
  weekday as feature,
  AVG(ABS(predicted - target)) as avg_error,
  STDDEV(ABS(predicted - target)) as stddev_error
FROM `chicago_taxi.eval_results` as r
JOIN `chicago_taxi.eval` as s 
ON r.unique_key = s.unique_key 
GROUP BY weekday

# Note: the interactive output is replaced with a static image so it displays well in github.
# Please execute this cell to see the interactive component.

from google.datalab.ml import FeatureSliceView

FeatureSliceView().plot(error_by_weekday)

"""
Explanation: There is also a feature slice visualization component designed for viewing evaluation results. It shows correlation between features and prediction results.
End of explanation
"""


!gsutil -m rm -rf gs://datalab-chicago-taxi-demo

"""
Explanation: What we can see from above charts is that model performs worst in hour 5 and 6 (why?), and best on Sundays (less traffic?).
Model Deployment and Online Prediction
Model deployment works the same between locally trained models and cloud trained models. Please see previous notebook (Taxi Fare Model (small data)).
Cleanup
End of explanation
"""


