PROJECT = "your-gcp-project-here" # REPLACE WITH YOUR PROJECT NAME
REGION = "us-central1" # REPLACE WITH YOUR BUCKET REGION e.g. us-central1

%env
PROJECT = PROJECT
REGION = REGION

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

"""
Explanation: Time Series Prediction with BQML and AutoML
Objectives
 1. Learn how to use BQML to create a classification time-series model using CREATE MODEL.
 2. Learn how to use BQML to create a linear regression time-series model.
 3. Learn how to use AutoML Tables to build a time series model from data in BigQuery.
Set up environment variables and load necessary libraries
End of explanation
"""


from google.cloud import bigquery
from IPython import get_ipython

bq = bigquery.Client(project=PROJECT)


def create_dataset():
    dataset = bigquery.Dataset(bq.dataset("stock_market"))
    try:
        bq.create_dataset(dataset)  # Will fail if dataset already exists.
        print("Dataset created")
    except:
        print("Dataset already exists")


def create_features_table():
    error = None
    try:
        bq.query('''
        CREATE TABLE stock_market.eps_percent_change_sp500
        AS
        SELECT *
        FROM `asl-ml-immersion.stock_market.eps_percent_change_sp500`
        ''').to_dataframe()
    except Exception as e:
        error = str(e)
    if error is None:
        print('Table created')
    elif 'Already Exists' in error:
        print('Table already exists.')
    else:
        raise Exception('Table was not created.')

create_dataset()
create_features_table()

"""
Explanation: Create the dataset
End of explanation
"""


%%bigquery --project $PROJECT
#standardSQL
SELECT
  *
FROM
  stock_market.eps_percent_change_sp500
LIMIT
  10

"""
Explanation: Review the dataset
In the previous lab we created the data, if you haven’t run the previous notebook, go back to 2_feature_engineering.ipynb to create them. We will use modeling and saved them as tables in BigQuery.
Let's examine that table again to see that everything is as we expect. Then, we will build a model using BigQuery ML using this table.
End of explanation
"""


%%bigquery --project $PROJECT
#standardSQL
CREATE OR REPLACE MODEL
  # TODO: Your code goes here
  -- query to fetch training data
SELECT
  # TODO: Your code goes here
FROM
  `stock_market.eps_percent_change_sp500`
WHERE
  # TODO: Your code goes here

"""
Explanation: Using BQML
Create classification model for direction
To create a model
1. Use CREATE MODEL and provide a destination table for resulting model. Alternatively we can use CREATE OR REPLACE MODEL which allows overwriting an existing model.
2. Use OPTIONS to specify the model type (linear_reg or logistic_reg). There are many more options we could specify, such as regularization and learning rate, but we'll accept the defaults.
3. Provide the query which fetches the training data 
Have a look at Step Two of this tutorial to see another example.
The query will take about two minutes to complete
We'll start with creating a classification model to predict the direction of each stock. 
We'll take a random split using the symbol value. With about 500 different values, using ABS(MOD(FARM_FINGERPRINT(symbol), 15)) = 1 will give 30 distinct symbol values which corresponds to about 171,000 training examples. After taking 70% for training, we will be building a model on about 110,000 training examples.
Lab Task #1a: Create model using BQML
Use BQML's CREATE OR REPLACE MODEL to train a classification model which predicts the direction of a stock using the features in the percent_change_sp500 table. Look at the documentation for creating a BQML model to get the right syntax. Use ABS(MOD(FARM_FINGERPRINT(symbol), 15)) = 1 to train on a subsample.
End of explanation
"""


%%bigquery --project $PROJECT
#standardSQL
SELECT
  *
FROM
  # TODO: Your code goes here.
  

"""
Explanation: Get training statistics and examine training info
After creating our model, we can evaluate the performance using the ML.EVALUATE function. With this command, we can find the precision, recall, accuracy F1-score and AUC of our classification model.
Lab Task #1b: Evaluate your BQML model.
Use BQML's EVALUATE to evaluate the performance of your model on the validation set. Your query should be similar to this example.
End of explanation
"""


%%bigquery --project $PROJECT
#standardSQL
SELECT
  *
FROM
  # TODO: Your code goes here

"""
Explanation: We can also examine the training statistics collected by Big Query. To view training results we use the ML.TRAINING_INFO function.
Lab Task #1c: Examine the training information in BQML.
Use BQML's TRAINING_INFO to see statistics of the training job executed above.
End of explanation
"""


%%bigquery --project $PROJECT
#standardSQL
WITH
  eval_data AS (
  SELECT
    symbol,
    Date,
    Open,
    close_MIN_prior_5_days,
    close_MIN_prior_20_days,
    close_MIN_prior_260_days,
    close_MAX_prior_5_days,
    close_MAX_prior_20_days,
    close_MAX_prior_260_days,
    close_AVG_prior_5_days,
    close_AVG_prior_20_days,
    close_AVG_prior_260_days,
    close_STDDEV_prior_5_days,
    close_STDDEV_prior_20_days,
    close_STDDEV_prior_260_days,
    direction
  FROM
    `stock_market.eps_percent_change_sp500`
  WHERE
    tomorrow_close IS NOT NULL
    AND ABS(MOD(FARM_FINGERPRINT(symbol), 15)) = 1
    AND ABS(MOD(FARM_FINGERPRINT(symbol), 15 * 100)) > 15 * 70
    AND ABS(MOD(FARM_FINGERPRINT(symbol), 15 * 100)) <= 15 * 85)
SELECT
  direction,
  (COUNT(direction)* 100 / (
    SELECT
      COUNT(*)
    FROM
      eval_data)) AS percentage
FROM
  eval_data
GROUP BY
  direction

"""
Explanation: Compare to simple benchmark
Another way to asses the performance of our model is to compare with a simple benchmark. We can do this by seeing what kind of accuracy we would get using the naive strategy of just predicted the majority class. For the training dataset, the majority class is 'STAY'. The following query we can see how this naive strategy would perform on the eval set.
End of explanation
"""


%%bigquery --project $PROJECT
#standardSQL
CREATE OR REPLACE MODEL
  # TODO: Your code goes here
  -- query to fetch training data
SELECT
  # TODO: Your code goes here
FROM
  `stock_market.eps_percent_change_sp500`
WHERE
  # TODO: Your code goes here

"""
Explanation: So, the naive strategy of just guessing the majority class would have accuracy of 0.5509 on the eval dataset, just below our BQML model. 
Create regression model for normalized change
We can also use BigQuery to train a regression model to predict the normalized change for each stock. To do this in BigQuery we need only change the OPTIONS when calling CREATE OR REPLACE MODEL. This will give us a more precise prediction rather than just predicting if the stock will go up, down, or stay the same. Thus, we can treat this problem as either a regression problem or a classification problem, depending on the business needs.
Lab Task #2a: Create a regression model in BQML.
Use BQML's CREATE OR REPLACE MODEL to train another model, this time a regression model, which predicts the normalized_change of a given stock based on the same features we used above.
End of explanation
"""


%%bigquery --project $PROJECT
#standardSQL
SELECT
  *
FROM
  ML.EVALUATE(MODEL `stock_market.price_model`,
    (
    SELECT
      symbol,
      Date,
      Open,
      close_MIN_prior_5_days,
      close_MIN_prior_20_days,
      close_MIN_prior_260_days,
      close_MAX_prior_5_days,
      close_MAX_prior_20_days,
      close_MAX_prior_260_days,
      close_AVG_prior_5_days,
      close_AVG_prior_20_days,
      close_AVG_prior_260_days,
      close_STDDEV_prior_5_days,
      close_STDDEV_prior_20_days,
      close_STDDEV_prior_260_days,
      normalized_change
    FROM
      `stock_market.eps_percent_change_sp500`
    WHERE
      normalized_change IS NOT NULL
      AND ABS(MOD(FARM_FINGERPRINT(symbol), 15)) = 1
      AND ABS(MOD(FARM_FINGERPRINT(symbol), 15 * 100)) > 15 * 70
      AND ABS(MOD(FARM_FINGERPRINT(symbol), 15 * 100)) <= 15 * 85))

%%bigquery --project $PROJECT
#standardSQL
SELECT
  *
FROM
  ML.TRAINING_INFO(MODEL `stock_market.price_model`)
ORDER BY iteration

"""
Explanation: Just as before we can examine the evaluation metrics for our regression model and examine the training statistics in Big Query
End of explanation
"""


