import matplotlib.pyplot as plt

"""
Explanation: Introduction to BigQuery ML - Predict Birth Weight
Learning Objectives

Use BigQuery to explore the natality dataset
Create a regression (linear regression) model in BQML
Evaluate the performance of your machine learning model
Make predictions with a trained BQML model

Introduction
In this lab, you will be using the US Centers for Disease Control and Prevention's (CDC) natality data to build a model to predict baby birth weights based on a handful of features known at pregnancy. Because we're predicting a continuous value, this is a regression problem, and for that, we'll use the linear regression model built into BQML.
End of explanation
"""


PROJECT = '<YOUR PROJECT>' #TODO Replace with your GCP PROJECT

"""
Explanation: Set up the notebook environment
VERY IMPORTANT:  In the cell below you must replace the text &lt;YOUR PROJECT&gt; with your GCP project id as provided during the setup of your environment. Please leave any surrounding single quotes in place.
End of explanation
"""


%%bigquery
SELECT
    *
FROM
  publicdata.samples.natality
WHERE
  year > 2000
    AND gestation_weeks > 0
    AND mother_age > 0
    AND plurality > 0
    AND weight_pounds > 0
LIMIT 10

"""
Explanation: Exploring the Data
This lab will use natality data and training on features to predict the birth weight.
The CDC's Natality data has details on US births from 1969 to 2008 and is available in BigQuery as a public data set. More details: https://bigquery.cloud.google.com/table/publicdata:samples.natality?tab=details
Start by looking at the data since 2000 with useful values, those greater than 0.
Note:  "%%bigquery" is a magic which allows quick access to BigQuery from within a notebook.
TODO 1
End of explanation
"""


%%bigquery
SELECT
    weight_pounds, -- this is the label; because it is continuous, we need to use regression
    CAST(is_male AS STRING) AS is_male,
    mother_age,
    CAST(plurality AS STRING) AS plurality,
    gestation_weeks,
    FARM_FINGERPRINT(CONCAT(CAST(YEAR AS STRING), CAST(month AS STRING))) AS hashmonth
FROM
    publicdata.samples.natality
WHERE
    year > 2000
    AND gestation_weeks > 0
    AND mother_age > 0
    AND plurality > 0
    AND weight_pounds > 0
LIMIT 10

"""
Explanation: Define Features
Looking over the data set, there are a few columns of interest that could be leveraged into features for a reasonable prediction of approximate birth weight.
Further, some feature engineering may be accomplished with the BigQuery CAST function -- in BQML, all strings are considered categorical features and all numeric types are considered continuous ones.
The hashmonth is added so that we can repeatably split the data without leakage -- the goal is to have all babies that share a birthday to be either in training set or in test set and not spread between them (otherwise, there would be information leakage when it comes to triplets, etc.)
End of explanation
"""


%%bash
bq --location=US mk -d demo

"""
Explanation: Train Model
With the relevant columns chosen to accomplish predictions, it is then possible to create and train the model in BigQuery. First, a dataset will be needed store the model.
End of explanation
"""


%%bigquery
CREATE or REPLACE MODEL demo.babyweight_model_asis
OPTIONS
  (model_type='linear_reg', labels=['weight_pounds'], optimize_strategy='batch_gradient_descent') AS
WITH natality_data AS (
  SELECT
    weight_pounds,-- this is the label; because it is continuous, we need to use regression
    CAST(is_male AS STRING) AS is_male,
    mother_age,
    CAST(plurality AS STRING) AS plurality,
    gestation_weeks,
    FARM_FINGERPRINT(CONCAT(CAST(YEAR AS STRING), CAST(month AS STRING))) AS hashmonth
  FROM
    publicdata.samples.natality
  WHERE
    year > 2000
    AND gestation_weeks > 0
    AND mother_age > 0
    AND plurality > 0
    AND weight_pounds > 0
)

SELECT
    weight_pounds,
    is_male,
    mother_age,
    plurality,
    gestation_weeks
FROM
    natality_data
WHERE
  ABS(MOD(hashmonth, 4)) < 3  -- select 75% of the data as training

"""
Explanation: With the demo dataset ready, it is possible to create a linear regression model to train the model.
This will take approximately 5 to 7 minutes to run.  Feedback from BigQuery will cease in output cell and the notebook will leave the "busy" state when complete.
End of explanation
"""


%%bigquery
SELECT * FROM ML.TRAINING_INFO(MODEL demo.babyweight_model_asis);

"""
Explanation: Training Statistics
For all training runs, statistics are captured in the "TRAINING_INFO" table. This table has basic performance statistics for each iteration.
The query below returns the training details.
End of explanation
"""


%%bigquery history 

SELECT * FROM ML.TRAINING_INFO(MODEL demo.babyweight_model_asis)

history

plt.plot('iteration', 'loss', data=history,
         marker='o', color='orange', linewidth=2)

plt.plot('iteration', 'eval_loss', data=history,
         marker='', color='green', linewidth=2, linestyle='dashed')

plt.xlabel('iteration')
plt.ylabel('loss')
plt.legend();

"""
Explanation: Some of these columns are obvious although what do the non-specific ML columns mean (specific to BQML)?
training_run - Will be zero for a newly created model. If the model is re-trained using warm_start, this will increment for each re-training.
iteration - Number of the associated training_run, starting with zero for the first iteration.
duration_ms - Indicates how long the iteration took (in ms).
Next plot the training and evaluation loss to see if the model has an overfit.
End of explanation
"""


%%bigquery
SELECT
  *
FROM
  ml.PREDICT(MODEL demo.babyweight_model_asis,
      (SELECT
        weight_pounds,
        CAST(is_male AS STRING) AS is_male,
        mother_age,
        CAST(plurality AS STRING) AS plurality,
        gestation_weeks
      FROM
        publicdata.samples.natality
      WHERE
        year > 2000
        AND gestation_weeks > 0
        AND mother_age > 0
        AND plurality > 0
        AND weight_pounds > 0
    ))
LIMIT 100

"""
Explanation: As you can see, the training loss and evaluation loss are essentially identical. There does not appear to be any overfitting.
Make a Prediction with BQML using the Model
With a trained model, it is now possible to make a prediction on the values. The only difference from the second query above is the reference to the model. The data has been limited (LIMIT 100) to reduce amount of data returned.
When the ml.predict function is leveraged, output prediction column name for the model is predicted_&lt;label_column_name&gt;.
End of explanation
"""


