!sudo chown -R jupyter:jupyter /home/jupyter/training-data-analyst

%%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.
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
    weight_pounds,
    is_male,
    mother_age,
    plurality,
    gestation_weeks,
    ML.FEATURE_CROSS(
        STRUCT(
            is_male,
            plurality)
    ) AS gender_plurality_cross
FROM
    babyweight.babyweight_data_train

"""
Explanation: 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
        weight_pounds,
        is_male,
        mother_age,
        plurality,
        gestation_weeks,
        ML.FEATURE_CROSS(
            STRUCT(
                is_male,
                plurality)
        ) AS gender_plurality_cross
    FROM
        babyweight.babyweight_data_eval
    ))

%%bigquery
SELECT
    SQRT(mean_squared_error) AS rmse
FROM
    ML.EVALUATE(MODEL babyweight.model_1,
    (
    SELECT
        weight_pounds,
        is_male,
        mother_age,
        plurality,
        gestation_weeks,
        ML.FEATURE_CROSS(
            STRUCT(
                is_male,
                plurality)
        ) AS gender_plurality_cross
    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(
                mother_age,
                GENERATE_ARRAY(15, 45, 1)
            ) AS bucketed_mothers_age,
            plurality,
            ML.BUCKETIZE(
                gestation_weeks,
                GENERATE_ARRAY(17, 47, 1)
            ) AS bucketed_gestation_weeks
        )
    ) AS crossed
FROM
    babyweight.babyweight_data_train

"""
Explanation: 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.
End of explanation
"""


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

"""
Explanation: Let's now retrieve the training statistics and evaluate the model.
End of explanation
"""


%%bigquery
SELECT
    *
FROM
    ML.EVALUATE(MODEL babyweight.model_2,
    (
    SELECT
        weight_pounds,
        is_male,
        mother_age,
        plurality,
        gestation_weeks,
        ML.FEATURE_CROSS(
            STRUCT(
                is_male,
                ML.BUCKETIZE(
                    mother_age,
                    GENERATE_ARRAY(15, 45, 1)
                ) AS bucketed_mothers_age,
                plurality,
                ML.BUCKETIZE(
                    gestation_weeks,
                    GENERATE_ARRAY(17, 47, 1)
                ) AS bucketed_gestation_weeks
            )
        ) AS crossed
    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
        weight_pounds,
        is_male,
        mother_age,
        plurality,
        gestation_weeks,
        ML.FEATURE_CROSS(
            STRUCT(
                is_male,
                ML.BUCKETIZE(
                    mother_age,
                    GENERATE_ARRAY(15, 45, 1)
                ) AS bucketed_mothers_age,
                plurality,
                ML.BUCKETIZE(
                    gestation_weeks,
                    GENERATE_ARRAY(17, 47, 1)
                ) AS bucketed_gestation_weeks
            )
        ) AS crossed
    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(
    weight_pounds,
    is_male,
    mother_age,
    plurality,
    gestation_weeks,
    ML.FEATURE_CROSS(
        STRUCT(
            is_male,
            ML.BUCKETIZE(
                mother_age,
                GENERATE_ARRAY(15, 45, 1)
            ) AS bucketed_mothers_age,
            plurality,
            ML.BUCKETIZE(
                gestation_weeks,
                GENERATE_ARRAY(17, 47, 1)
            ) AS bucketed_gestation_weeks
        )
    ) AS crossed
)

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: 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
"""


