%%bash
pip freeze | grep google-cloud-bigquery==1.6.1 || \
pip install google-cloud-bigquery==1.6.1

"""
Explanation: LAB 3b:  BigQuery ML Model Linear Feature Engineering/Transform.
Learning Objectives

Create and evaluate linear model with BigQuery's ML.FEATURE_CROSS
Create and evaluate linear model with BigQuery's ML.FEATURE_CROSS and ML.BUCKETIZE
Create and evaluate linear model with ML.TRANSFORM

Introduction
In this notebook, we will create multiple linear models to predict the weight of a baby before it is born, using increasing levels of feature engineering using BigQuery ML. If you need a refresher, you can go back and look how we made a baseline model in the previous notebook BQML Baseline Model.
We will create and evaluate a linear model using BigQuery's ML.FEATURE_CROSS, create and evaluate a linear model using BigQuery's ML.FEATURE_CROSS and ML.BUCKETIZE, and create and evaluate a linear model using BigQuery's ML.TRANSFORM.
Each learning objective will correspond to a #TODO in this student lab notebook -- try to complete this notebook first and then review the solution notebook.
Load necessary libraries
Check that the Google BigQuery library is installed and if not, install it.
End of explanation
"""


%%bigquery
-- LIMIT 0 is a free query; this allows us to check that the table exists.
SELECT * FROM babyweight.babyweight_data_train
LIMIT 0

%%bigquery
-- LIMIT 0 is a free query; this allows us to check that the table exists.
SELECT * FROM babyweight.babyweight_data_eval
LIMIT 0

"""
Explanation: Verify tables exist
Run the following cells to verify that we previously created the dataset and data tables. If not, go back to lab 1b_prepare_data_babyweight to create them.
End of explanation
"""


%%bigquery
CREATE OR REPLACE MODEL
    babyweight.model_1

OPTIONS (
    MODEL_TYPE="LINEAR_REG",
    INPUT_LABEL_COLS=["weight_pounds"],
    L2_REG=0.1,
    DATA_SPLIT_METHOD="NO_SPLIT") AS

SELECT
    # TODO: Add base features and label
    ML.FEATURE_CROSS(
        # TODO: Cross categorical features
    ) AS gender_plurality_cross
FROM
    babyweight.babyweight_data_train

"""
Explanation: Lab Task #1: Model 1:  Apply the ML.FEATURE_CROSS clause to categorical features
BigQuery ML now has ML.FEATURE_CROSS, a pre-processing clause that performs a feature cross with syntax ML.FEATURE_CROSS(STRUCT(features), degree) where features are comma-separated categorical columns and degree is highest degree of all combinations.
Create model with feature cross.
End of explanation
"""


%%bigquery
SELECT
    *
FROM
    ML.EVALUATE(MODEL babyweight.model_1,
    (
    SELECT
        # TODO: Add same features and label as training
    FROM
        babyweight.babyweight_data_eval
    ))

%%bigquery
SELECT
    # TODO: Select just the calculated RMSE
FROM
    ML.EVALUATE(MODEL babyweight.model_1,
    (
    SELECT
        # TODO: Add same features and label as training
    FROM
        babyweight.babyweight_data_eval
    ))

"""
Explanation: Create two SQL statements to evaluate the model.
End of explanation
"""


%%bigquery
CREATE OR REPLACE MODEL
    babyweight.model_2

OPTIONS (
    MODEL_TYPE="LINEAR_REG",
    INPUT_LABEL_COLS=["weight_pounds"],
    L2_REG=0.1,
    DATA_SPLIT_METHOD="NO_SPLIT") AS

SELECT
    weight_pounds,
    is_male,
    mother_age,
    plurality,
    gestation_weeks,
    ML.FEATURE_CROSS(
        STRUCT(
            is_male,
            ML.BUCKETIZE(
                # TODO: Bucketize mother_age
            ) AS bucketed_mothers_age,
            plurality,
            ML.BUCKETIZE(
                # TODO: Bucketize gestation_weeks
            ) AS bucketed_gestation_weeks
        )
    ) AS crossed
FROM
    babyweight.babyweight_data_train

"""
Explanation: Lab Task #2: Model 2:  Apply the BUCKETIZE Function
Bucketize is a pre-processing function that creates "buckets" (e.g bins) - e.g. it bucketizes a continuous numerical feature into a string feature with bucket names as the value with syntax ML.BUCKETIZE(feature, split_points) with split_points being an array of numerical points to determine bucket bounds.
Apply the BUCKETIZE function within FEATURE_CROSS.

Hint:  Create a model_2.
End of explanation
"""


%%bigquery
SELECT * FROM ML.TRAINING_INFO(MODEL babyweight.model_2)

"""
Explanation: Create three SQL statements to EVALUATE the model.
Let's now retrieve the training statistics and evaluate the model.
End of explanation
"""


%%bigquery
SELECT
    *
FROM
    ML.EVALUATE(MODEL babyweight.model_2,
    (
    SELECT
        # TODO: Add same features and label as training
    FROM
        babyweight.babyweight_data_eval))

"""
Explanation: We now evaluate our model on our eval dataset:
End of explanation
"""


%%bigquery
SELECT
    SQRT(mean_squared_error) AS rmse
FROM
    ML.EVALUATE(MODEL babyweight.model_2,
    (
    SELECT
        # TODO: Add same features and label as training
    FROM
        babyweight.babyweight_data_eval))

"""
Explanation: Let's select the mean_squared_error from the evaluation table we just computed and square it to obtain the rmse.
End of explanation
"""


%%bigquery
CREATE OR REPLACE MODEL
    babyweight.model_3

TRANSFORM(
    # TODO: Add base features and label as you would in select
    # TODO: Add transformed features as you would in select
)

OPTIONS (
    MODEL_TYPE="LINEAR_REG",
    INPUT_LABEL_COLS=["weight_pounds"],
    L2_REG=0.1,
    DATA_SPLIT_METHOD="NO_SPLIT") AS

SELECT
    *
FROM
    babyweight.babyweight_data_train

"""
Explanation: Lab Task #3: Model 3:  Apply the TRANSFORM clause
Before we perform our prediction, we should encapsulate the entire feature set in a TRANSFORM clause. This way we can have the same transformations applied for training and prediction without modifying the queries.
Let's apply the TRANSFORM clause to the model_3 and run the query.
End of explanation
"""


%%bigquery
SELECT * FROM ML.TRAINING_INFO(MODEL babyweight.model_3)

"""
Explanation: Let's retrieve the training statistics:
End of explanation
"""


%%bigquery
SELECT
    *
FROM
    ML.EVALUATE(MODEL babyweight.model_3,
    (
    SELECT
        *
    FROM
        babyweight.babyweight_data_eval
    ))

"""
Explanation: We now evaluate our model on our eval dataset:
End of explanation
"""


%%bigquery
SELECT
    SQRT(mean_squared_error) AS rmse
FROM
    ML.EVALUATE(MODEL babyweight.model_3,
    (
    SELECT
        *
    FROM
        babyweight.babyweight_data_eval
    ))

"""
Explanation: Let's select the mean_squared_error from the evaluation table we just computed and square it to obtain the rmse.
End of explanation
"""


