<jupyter_start><jupyter_text>How to Efficiently Read BigQuery Data from TensorFlow 2.3  Learning Objectives* Build a benchmark model.* Find the breakoff point for Keras.* Training a TensorFlow/Keras model that reads from BigQuery.* Load TensorFlow model into BigQuery. IntroductionIn this notebook, you learn ["How to efficiently read BigQuery data from TensorFlow 2.x"](https://medium.com/@lakshmanok/how-to-read-bigquery-data-from-tensorflow-2-0-efficiently-9234b69165c8)The example problem is to find credit card fraud from the dataset published in:<NAME>, <NAME>, <NAME> and <NAME>. Calibrating Probability with Undersampling for Unbalanced Classification. In Symposium on Computational Intelligence and Data Mining (CIDM), IEEE, 2015and available in BigQuery at bigquery-public-data.ml_datasets.ulb_fraud_detectionEach learning objective will correspond to a __TODO__ in this student lab notebook -- try to complete this notebook first and then review the [solution notebook](../solutions/bigquery_tensorflow.ipynb).  Build a benchmark modelIn order to compare things, we will do a simple logistic regression in BigQuery ML.Note that we are using all the columns in the dataset as predictors (except for the Time and Class columns).The Time column is used to split the dataset 80:20 with the first 80% used for training and the last 20% used for evaluation.We will also have BigQuery ML automatically balance the weights.Because the Amount column has a huge range, we take the log of it in preprocessing.<jupyter_code>%%bash
# create output dataset
bq mk advdata
%%bigquery
CREATE OR REPLACE MODEL advdata.ulb_fraud_detection 
TRANSFORM(
    * EXCEPT(Amount),
    SAFE.LOG(Amount) AS log_amount
)
OPTIONS(
    INPUT_LABEL_COLS=['class'],
    AUTO_CLASS_WEIGHTS = TRUE,
    DATA_SPLIT_METHOD='seq',
    DATA_SPLIT_COL='Time',
    MODEL_TYPE='logistic_reg'
) AS

SELECT 
 *
FROM `bigquery-public-data.ml_datasets.ulb_fraud_detection`
%%bigquery
# Use the ML.EVALUATE function to evaluate model metrics
SELECT * FROM TODO: ___________(MODEL advdata.ulb_fraud_detection)
%%bigquery
SELECT predicted_class_probs, Class
# The ML.PREDICT function is used to predict outcomes using the model
FROM TODO: ___________( MODEL advdata.ulb_fraud_detection,
  (SELECT * FROM `bigquery-public-data.ml_datasets.ulb_fraud_detection` WHERE Time = 85285.0)
)<jupyter_output>Query complete after 0.00s: 100%|██████████| 2/2 [00:00<00:00, 1095.83query/s]                        
Downloading: 100%|██████████| 5/5 [00:01<00:00,  3.29rows/s]<jupyter_text>Find the breakoff point for KerasWhen we do the training in Keras & TensorFlow, we need to find the place to split the dataset and how to weight the imbalanced data.(BigQuery ML did that for us because we specified 'seq' as the split method and auto_class_weights to be True).<jupyter_code>%%bigquery
WITH counts AS (
SELECT
    APPROX_QUANTILES(Time, 5)[OFFSET(4)] AS train_cutoff
    , COUNTIF(CLASS > 0) AS pos
    , COUNTIF(CLASS = 0) AS neg
FROM `bigquery-public-data`.ml_datasets.ulb_fraud_detection
)

SELECT
   train_cutoff
    , SAFE.LOG(SAFE_DIVIDE(pos,neg)) AS output_bias
    , 0.5*SAFE_DIVIDE(pos + neg, pos) AS weight_pos
    , 0.5*SAFE_DIVIDE(pos + neg, neg) AS weight_neg
FROM TODO: ___________ # Table Name<jupyter_output>Query complete after 0.00s: 100%|██████████| 1/1 [00:00<00:00, 708.62query/s] 
Downloading: 100%|██████████| 1/1 [00:01<00:00,  1.47s/rows]<jupyter_text>The time cutoff is 144803 and the Keras model's output bias needs to be set at -6.36The class weights need to be 289.4 and 0.5  Training a TensorFlow/Keras model that reads from BigQueryCreate the dataset from BigQuery<jupyter_code># import necessary libraries
import tensorflow as tf
from tensorflow.python.framework import dtypes
from tensorflow_io.bigquery import BigQueryClient
from tensorflow_io.bigquery import BigQueryReadSession

def features_and_labels(features):
  label = features.pop('Class') # this is what we will train for
  return features, label

def read_dataset(client, row_restriction, batch_size=2048):
    GCP_PROJECT_ID='qwiklabs-gcp-03-5b2f0816822f'  # CHANGE
    COL_NAMES = ['Time', 'Amount', 'Class'] + ['V{}'.format(i) for i in range(1,29)]
    COL_TYPES = [dtypes.float64, dtypes.float64, dtypes.int64] + [dtypes.float64 for i in range(1,29)]
    DATASET_GCP_PROJECT_ID, DATASET_ID, TABLE_ID,  = 'bigquery-public-data.ml_datasets.ulb_fraud_detection'.split('.')
    bqsession = client.read_session(
        "projects/" + GCP_PROJECT_ID,
        DATASET_GCP_PROJECT_ID, TABLE_ID, DATASET_ID,
        COL_NAMES, COL_TYPES,
        requested_streams=2,
        row_restriction=row_restriction)
    dataset = bqsession.parallel_read_rows()
    return dataset.prefetch(1).map(features_and_labels).shuffle(batch_size*10).batch(batch_size)

client = BigQueryClient()
temp_df = TODO: ___________(client, 'Time <= 144803', 2) # Function Name
for row in temp_df:
    print(row)
    break
train_df = read_dataset(client, 'Time <= 144803', 2048)
eval_df = read_dataset(client, 'Time > 144803', 2048)<jupyter_output><empty_output><jupyter_text>Create Keras model<jupyter_code>metrics = [
      tf.keras.metrics.BinaryAccuracy(name='accuracy'),
      tf.keras.metrics.Precision(name='precision'),
      tf.keras.metrics.Recall(name='recall'),
      tf.keras.metrics.AUC(name='roc_auc'),
]

# create inputs, and pass them into appropriate types of feature columns (here, everything is numeric)
inputs = {
    'V{}'.format(i) : tf.keras.layers.Input(name='V{}'.format(i), shape=(), dtype='float64') for i in range(1, 29)
}
inputs['Amount'] = tf.keras.layers.Input(name='Amount', shape=(), dtype='float64')
input_fc = [tf.feature_column.numeric_column(colname) for colname in inputs.keys()]

# transformations. only the Amount is transformed
transformed = inputs.copy()
transformed['Amount'] = tf.keras.layers.Lambda(
    lambda x: tf.math.log(tf.math.maximum(x, 0.01)), name='log_amount')(inputs['Amount'])
input_layer = tf.keras.layers.DenseFeatures(input_fc, name='inputs')(transformed)

# Deep learning model
d1 = tf.keras.layers.Dense(16, activation='relu', name='d1')(input_layer)
d2 = tf.keras.layers.Dropout(0.25, name='d2')(d1)
d3 = tf.keras.layers.Dense(16, activation='relu', name='d3')(d2)
output = tf.keras.layers.Dense(1, activation='sigmoid', name='d4', bias_initializer=tf.keras.initializers.Constant())(d3)

model = tf.keras.Model(inputs, output)
model.compile(optimizer='adam',
              loss='binary_crossentropy',
              metrics=metrics)
tf.keras.utils.plot_model(model, rankdir='LR')
class_weight = {0: 0.5, 1: 289.4}
# Trains the model for a fixed number of epochs
history = TODO: _________(train_df, validation_data=eval_df, epochs=20, class_weight=class_weight)
import matplotlib.pyplot as plt
plt.plot(history.history['val_roc_auc']);
plt.xlabel('Epoch');
plt.ylabel('AUC');<jupyter_output><empty_output><jupyter_text>Load TensorFlow model into BigQueryNow that we have trained a TensorFlow model off BigQuery data ...let's load the model into BigQuery and use it for batch prediction!<jupyter_code>BUCKET='<your-bucket>'  # CHANGE TO SOMETHING THAT YOU OWN
model.save('gs://{}/bqexample/export'.format(BUCKET))
%%bigquery
CREATE OR REPLACE MODEL advdata.keras_fraud_detection 
OPTIONS(model_type='tensorflow', model_path='gs://qwiklabs-gcp-03-5b2f0816822f/bqexample/export/*')<jupyter_output>Query complete after 0.00s: 100%|██████████| 2/2 [00:00<00:00, 1045.31query/s]<jupyter_text>Now predict with this model (the reason it's called 'd4' is because the output node of my Keras model was called 'd4').To get probabilities, etc. we'd have to add the corresponding outputs to the Keras model.<jupyter_code>%%bigquery
SELECT d4, Class
FROM ML.PREDICT( MODEL advdata.keras_fraud_detection,
  (SELECT * FROM `bigquery-public-data.ml_datasets.ulb_fraud_detection` WHERE Time = 85285.0)
)<jupyter_output>Query complete after 0.00s: 100%|██████████| 1/1 [00:00<00:00, 384.30query/s]                          
Downloading: 100%|██████████| 5/5 [00:01<00:00,  2.69rows/s]
{'fraction_non_alphanumeric': 0.1181592039800995, 'fraction_numerical': 0.038308457711442784, 'mean_word_length': 5.2285050348567, 'pattern_counts': {'":': 0, '<': 35, '<?xml version=': 0, '>': 28, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '44709', 'license': 'Apache-2.0', 'n_tokens_mistral': 2968, 'n_tokens_neox': 2681, 'n_words': 732, 'path': 'courses/machine_learning/deepdive2/introduction_to_tensorflow/labs/bigquery_tensorflow.ipynb', 'repo_name': 'Glairly/introduction_to_tensorflow', 'size': '8080'}