1_bqml_basic_feat_eng_bqml-lab.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "3o8Qof7Cy165"
   },
   "source": [
    "# LAB 01:  Basic Feature Engineering in BQML \n",
    "\n",
    "**Learning Objectives**\n",
    "\n",
    "1. Create SQL statements to evaluate the model\n",
    "2. Extract temporal features\n",
    "3. Perform a feature cross on temporal features\n",
    "\n",
    "\n",
    "## Introduction \n",
    "In this lab, we utilize feature engineering to improve the prediction of the fare amount for a taxi ride in New York City.  We will use BigQuery ML to build a taxifare prediction model, using feature engineering to improve and create a final model.\n",
    "\n",
    "In this Notebook we set up the environment, create the project dataset, create a feature engineering table, create and evaluate a baseline model, extract temporal features, perform a feature cross on temporal features, and evaluate model performance throughout the process. \n",
    "\n",
    "Each learning objective will correspond to a __#TODO__ in the notebook where you will complete the notebook cell's code before running. Refer to the [solution](https://github.com/GoogleCloudPlatform/training-data-analyst/blob/master/courses/machine_learning/deepdive2/feature_engineering/solutions/1_bqml_basic_feat_eng.ipynb) for reference. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "hJ7ByvoXzpVI"
   },
   "source": [
    "### Set up environment variables and load necessary libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Nny3m465gKkY"
   },
   "outputs": [],
   "source": [
    "!pip install --user google-cloud-bigquery==3.4.1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Note**: Restart your kernel to use updated packages."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Kindly ignore the deprecation warnings and incompatibility errors related to google-cloud-storage."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Nny3m465gKkY"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TensorFlow version:  2.6.5\n"
     ]
    }
   ],
   "source": [
    "# Installing the latest version of the package\n",
    "import tensorflow as tf\n",
    "print(\"TensorFlow version: \",tf.version.VERSION)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Your current GCP Project Name is: qwiklabs-gcp-02-ce4b002c67ef\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "export PROJECT=$(gcloud config list project --format \"value(core.project)\")\n",
    "echo \"Your current GCP Project Name is: \"$PROJECT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "L0-vOB4y2BJM"
   },
   "source": [
    "## The source dataset\n",
    "\n",
    "Our dataset is hosted in [BigQuery](https://cloud.google.com/bigquery/). The taxi fare data is a publically available dataset, meaning anyone with a GCP account has access. Click [here](https://console.cloud.google.com/bigquery?project=bigquery-public-data&p=nyc-tlc&d=yellow&t=trips&page=table) to access the dataset.\n",
    "\n",
    "The Taxi Fare dataset is relatively large at 55 million training rows, but simple to understand, with only six features. The fare_amount is the target, the continuous value we’ll train a model to predict.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a BigQuery Dataset\n",
    "\n",
    "A BigQuery dataset is a container for tables, views, and models built with BigQuery ML. Let's create one called __feat_eng__ if we have not already done so in an earlier lab. We'll do the same for a GCS bucket for our project too."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/lib/google-cloud-sdk/platform/bq/bq.py:42: DeprecationWarning: the imp module is deprecated in favour of importlib; see the module's documentation for alternative uses\n",
      "  import imp\n",
      "W0613 14:45:14.687843 139914546779968 bigquery_client.py:731] There is no apilog flag so non-critical logging is disabled.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Creating BigQuery dataset titled: feat_eng\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/lib/google-cloud-sdk/platform/bq/bq.py:42: DeprecationWarning: the imp module is deprecated in favour of importlib; see the module's documentation for alternative uses\n",
      "  import imp\n",
      "W0613 14:45:16.148099 140511082989376 bigquery_client.py:731] There is no apilog flag so non-critical logging is disabled.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset 'qwiklabs-gcp-02-ce4b002c67ef:feat_eng' successfully created.\n",
      "\\nHere are your current datasets:\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/lib/google-cloud-sdk/platform/bq/bq.py:42: DeprecationWarning: the imp module is deprecated in favour of importlib; see the module's documentation for alternative uses\n",
      "  import imp\n",
      "W0613 14:45:18.225475 140365519697728 bigquery_client.py:731] There is no apilog flag so non-critical logging is disabled.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  datasetId  \n",
      " ----------- \n",
      "  feat_eng   \n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "# Create a BigQuery dataset for feat_eng if it doesn't exist\n",
    "datasetexists=$(bq ls -d | grep -w feat_eng)\n",
    "\n",
    "if [ -n \"$datasetexists\" ]; then\n",
    "    echo -e \"BigQuery dataset already exists, let's not recreate it.\"\n",
    "\n",
    "else\n",
    "    echo \"Creating BigQuery dataset titled: feat_eng\"\n",
    "    \n",
    "    bq --location=US mk --dataset \\\n",
    "        --description 'Taxi Fare' \\\n",
    "        $PROJECT:feat_eng\n",
    "   echo \"\\nHere are your current datasets:\"\n",
    "   bq ls\n",
    "fi    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "b2TuS1s9vREL"
   },
   "source": [
    "## Create the training data table\n",
    "\n",
    "Since there is already a publicly available dataset, we can simply create the training data table using this raw input data.  Note the WHERE clause in the below query:  This clause allows us to TRAIN a portion of the data (e.g. one hundred thousand rows versus one million rows), which keeps your query costs down.  If you need a refresher on using MOD() for repeatable splits see this [post](https://www.oreilly.com/learning/repeatable-sampling-of-data-sets-in-bigquery-for-machine-learning).  \n",
    "\n",
    "* Note:  The dataset in the create table code below is the one created previously, e.g. \"feat_eng\".  The table name is \"feateng_training_data\".  Run the query to create the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "CMNRractvREL"
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "49ef78c7025b4f119a7b719122e7338b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    " \n",
    "CREATE OR REPLACE TABLE\n",
    "  feat_eng.feateng_training_data AS\n",
    "SELECT\n",
    "  (tolls_amount + fare_amount) AS fare_amount,\n",
    "  passenger_count*1.0 AS passengers,\n",
    "  pickup_datetime,\n",
    "  pickup_longitude AS pickuplon,\n",
    "  pickup_latitude AS pickuplat,\n",
    "  dropoff_longitude AS dropofflon,\n",
    "  dropoff_latitude AS dropofflat\n",
    "FROM\n",
    "  `nyc-tlc.yellow.trips`\n",
    "WHERE\n",
    "  MOD(ABS(FARM_FINGERPRINT(CAST(pickup_datetime AS STRING))), 10000) = 1\n",
    "  AND fare_amount >= 2.5\n",
    "  AND passenger_count > 0\n",
    "  AND pickup_longitude > -78\n",
    "  AND pickup_longitude < -70\n",
    "  AND dropoff_longitude > -78\n",
    "  AND dropoff_longitude < -70\n",
    "  AND pickup_latitude > 37\n",
    "  AND pickup_latitude < 45\n",
    "  AND dropoff_latitude > 37\n",
    "  AND dropoff_latitude < 45"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "clnaaqQsXkwC"
   },
   "source": [
    "## Verify table creation\n",
    "\n",
    "Verify that you created the dataset.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6fa325df8586434bb8805d692852238b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d44f0064c27e45a4aa7f4bbc443ebdd8",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Downloading: |          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>passengers</th>\n",
       "      <th>pickup_datetime</th>\n",
       "      <th>pickuplon</th>\n",
       "      <th>pickuplat</th>\n",
       "      <th>dropofflon</th>\n",
       "      <th>dropofflat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [fare_amount, passengers, pickup_datetime, pickuplon, pickuplat, dropofflon, dropofflat]\n",
       "Index: []"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "# LIMIT 0 is a free query; this allows us to check that the table exists.\n",
    "\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  feat_eng.feateng_training_data\n",
    "LIMIT\n",
    "  0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "RhgXan8wvREN"
   },
   "source": [
    "### Baseline Model:  Create the baseline model\n",
    "\n",
    "Next, you create a linear regression baseline model with no feature engineering.  Recall that a model in BigQuery ML represents what an ML system has learned from the training data.  A baseline model is a solution to a problem without applying any machine learning techniques.  \n",
    "\n",
    "When creating a BQML model, you must specify the model type (in our case linear regression) and the input label (fare_amount).  Note also that we are using the training data table as the data source."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "kb_5NlfU7oyT"
   },
   "source": [
    "Now we create the SQL statement to create the baseline model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "2a9e52f012494430aab7185afc08a157",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "CREATE OR REPLACE MODEL\n",
    "  feat_eng.baseline_model OPTIONS (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount']) AS\n",
    "SELECT\n",
    "  fare_amount,\n",
    "  passengers,\n",
    "  pickup_datetime,\n",
    "  pickuplon,\n",
    "  pickuplat,\n",
    "  dropofflon,\n",
    "  dropofflat\n",
    "FROM\n",
    "  feat_eng.feateng_training_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Tq2KYJOM9ULC"
   },
   "source": [
    "\n",
    "REMINDER:  The query takes several minutes to complete. After the first iteration is complete, your model (baseline_model) appears in the navigation panel of the BigQuery web UI. Because the query uses a CREATE MODEL statement to create a model, you do not see query results.\n",
    "\n",
    "You can observe the model as it's being trained by viewing the Model stats tab in the BigQuery web UI. As soon as the first iteration completes, the tab is updated. The stats continue to update as each iteration completes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "HO5d50Eic-X1"
   },
   "source": [
    "Once the training is done, visit the [BigQuery Cloud Console](https://console.cloud.google.com/bigquery) and look at the model that has been trained. Then, come back to this notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "RSgIJqN6vREV"
   },
   "source": [
    "### Evaluate the baseline model\n",
    "Note that BigQuery automatically split the data we gave it, and trained on only a part of the data and used the rest for evaluation.  After creating your model, you evaluate the performance of the regressor using the ML.EVALUATE function. The ML.EVALUATE function evaluates the predicted values against the actual data.\n",
    "\n",
    "NOTE: The results are also displayed in the [BigQuery Cloud Console](https://console.cloud.google.com/bigquery) under the **Evaluation** tab."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Review the learning and eval statistics for the baseline_model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7cb3654c90134e97975be525582ee1b1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "fec7dab3cd51402abe7fc23824489baf",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Downloading:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>training_run</th>\n",
       "      <th>iteration</th>\n",
       "      <th>loss</th>\n",
       "      <th>eval_loss</th>\n",
       "      <th>learning_rate</th>\n",
       "      <th>duration_ms</th>\n",
       "      <th>rmse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>74.434634</td>\n",
       "      <td>68.880166</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8538</td>\n",
       "      <td>8.627551</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   training_run  iteration       loss  eval_loss  learning_rate  duration_ms  \\\n",
       "0             0          0  74.434634  68.880166            NaN         8538   \n",
       "\n",
       "       rmse  \n",
       "0  8.627551  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "# Eval statistics on the held out data.\n",
    "\n",
    "SELECT\n",
    "  *,\n",
    "  SQRT(loss) AS rmse\n",
    "FROM\n",
    "  ML.TRAINING_INFO(MODEL feat_eng.baseline_model)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "fd56826fbd0d493096fde8ec6f8a19fa",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b097051a7ec040579b61276ea6b7c831",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Downloading:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.213554</td>\n",
       "      <td>68.880166</td>\n",
       "      <td>0.258109</td>\n",
       "      <td>3.791838</td>\n",
       "      <td>0.226068</td>\n",
       "      <td>0.226133</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             5.213554           68.880166                0.258109   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               3.791838  0.226068            0.226133  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.EVALUATE(MODEL feat_eng.baseline_model)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "xJGbfYuD8a9d"
   },
   "source": [
    "**NOTE:** Because you performed a linear regression, the results include the following columns:\n",
    "\n",
    "*   mean_absolute_error\n",
    "*   mean_squared_error\n",
    "*   mean_squared_log_error\n",
    "*   median_absolute_error\n",
    "*   r2_score\n",
    "*   explained_variance\n",
    "\n",
    "**Resource** for an explanation of the [Regression Metrics](https://towardsdatascience.com/metrics-to-evaluate-your-machine-learning-algorithm-f10ba6e38234).\n",
    "\n",
    "**Mean squared error** (MSE) - Measures the difference between the values our model predicted using the test set and the actual values. You can also think of it as the distance between your regression (best fit) line and the predicted values. \n",
    "\n",
    "**Root mean squared error** (RMSE) - The primary evaluation metric for this ML problem is the root mean-squared error. RMSE measures the difference between the predictions of a model, and the observed values. A large RMSE is equivalent to a large average error, so smaller values of RMSE are better. One nice property of RMSE is that the error is given in the units being measured, so you can tell very directly how incorrect the model might be on unseen data.\n",
    "\n",
    "**R2**:  An important metric in the evaluation results is the R2 score. The R2 score is a statistical measure that determines if the linear regression predictions approximate the actual data. Zero (0) indicates that the model explains none of the variability of the response data around the mean.  One (1) indicates that the model explains all the variability of the response data around the mean."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "p_21sAIR7LZw"
   },
   "source": [
    "**Lab Task 1:** Next, we write a SQL query to take the SQRT() of the mean squared error as your loss metric for evaluation for the benchmark_model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "cellView": "form",
    "colab": {},
    "colab_type": "code",
    "id": "8mAXRTvbvRES"
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ad733afd00474d4a85ae8d52cc04dc39",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a06b1fb3f8414486a458bb6a7ab5d75f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Downloading:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rmse_model_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8.299408</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rmse_model_1\n",
       "0      8.299408"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "#TODO 1 - your code here\n",
    "SELECT\n",
    "  sqrt(mean_squared_error) as rmse_model_1\n",
    "FROM\n",
    "  ML.EVALUATE(MODEL feat_eng.baseline_model)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "nW6fnqAW8vyI"
   },
   "source": [
    "#### Model 1:  EXTRACT dayofweek from the pickup_datetime feature.\n",
    "\n",
    "* As you recall, dayofweek is an enum representing the 7 days of the week. This factory allows the enum to be obtained from the int value. The int value follows the ISO-8601 standard, from 1 (Monday) to 7 (Sunday).\n",
    "\n",
    "* If you were to extract the dayofweek from pickup_datetime using BigQuery SQL, the datatype returned would be integer."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lab Task 2:** Next, we create a model titled \"model_1\" from the benchmark model and extract out the DayofWeek."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "ZQ0kT2jN-vpm"
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "534f2069042c4b26aefc8d44cf87cca1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "CREATE OR REPLACE MODEL\n",
    "  feat_eng.model_1 OPTIONS (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount']) AS\n",
    "SELECT\n",
    "  fare_amount,\n",
    "  passengers,\n",
    "  pickup_datetime,\n",
    "    \n",
    "  #TODO 2 - Your code here\n",
    "    EXTRACT(DAYOFWEEK FROM pickup_datetime) AS dayofweek,\n",
    "  pickuplon,\n",
    "  pickuplat,\n",
    "  dropofflon,\n",
    "  dropofflat\n",
    "FROM\n",
    "  feat_eng.feateng_training_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "T24XjIJgdLCH"
   },
   "source": [
    "Once the training is done, visit the [BigQuery Cloud Console](https://console.cloud.google.com/bigquery) and look at the model that has been trained. Then, come back to this notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "SRLxpccX_Tin"
   },
   "source": [
    "Next, two distinct SQL statements show the TRAINING and EVALUATION metrics of model_1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "cced006204e742a7b8f9cb87cd6ac97f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b6ba7196c016417cb2b21572e56194f5",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Downloading:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>training_run</th>\n",
       "      <th>iteration</th>\n",
       "      <th>loss</th>\n",
       "      <th>eval_loss</th>\n",
       "      <th>learning_rate</th>\n",
       "      <th>duration_ms</th>\n",
       "      <th>sqrt_loss_model_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>72.438762</td>\n",
       "      <td>88.95804</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9236</td>\n",
       "      <td>8.511096</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   training_run  iteration       loss  eval_loss  learning_rate  duration_ms  \\\n",
       "0             0          0  72.438762   88.95804            NaN         9236   \n",
       "\n",
       "   sqrt_loss_model_1  \n",
       "0           8.511096  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT\n",
    "  *,\n",
    "  SQRT(loss) AS sqrt_loss_model_1\n",
    "FROM\n",
    "  ML.TRAINING_INFO(MODEL feat_eng.model_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "8f7763ddde3a463aa252708d2b9d90e3",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "8b19db1db64b4d5da7a3e16e205760db",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Downloading:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.287443</td>\n",
       "      <td>88.95804</td>\n",
       "      <td>0.260945</td>\n",
       "      <td>3.711332</td>\n",
       "      <td>0.08476</td>\n",
       "      <td>0.084761</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             5.287443            88.95804                0.260945   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               3.711332   0.08476            0.084761  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.EVALUATE(MODEL feat_eng.model_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we run a SQL query to take the SQRT() of the mean squared error as your loss metric for evaluation for the benchmark_model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "CsVBzNef_TjC"
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "01a2974e1a6b42cf8852b2fb29265e83",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7c8119ad089942908c02713990f8c414",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Downloading:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rmse_model_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9.431757</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rmse_model_1\n",
       "0      9.431757"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT\n",
    "  SQRT(mean_squared_error) AS rmse_model_1\n",
    "FROM\n",
    "  ML.EVALUATE(MODEL feat_eng.model_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Lw30UexH8v9P"
   },
   "source": [
    "### Model 2:  EXTRACT hourofday from the pickup_datetime feature\n",
    "\n",
    "As you recall, **pickup_datetime** is stored as a TIMESTAMP, where the Timestamp format is retrieved in the standard output format – year-month-day hour:minute:second (e.g. 2016-01-01 23:59:59).  Hourofday returns the integer number representing the hour number of the given date.\n",
    "\n",
    "Hourofday is best thought of as a discrete ordinal variable (and not a categorical feature), as the hours can be ranked (e.g. there is a natural ordering of the values).  Hourofday has an added characteristic of being cyclic, since 12am follows 11pm and precedes 1am."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lab Task 3a:** Next, we create a model titled \"model_2\" and EXTRACT the hourofday from the pickup_datetime feature to improve our model's rmse."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "FHeqcYz-B9F1"
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "8bb598be64cf412c8db58c79b9f2654b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "CREATE OR REPLACE MODEL\n",
    "  feat_eng.model_2 OPTIONS (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount']) AS\n",
    "SELECT\n",
    "  fare_amount,\n",
    "  passengers,\n",
    "\n",
    "   #TODO 3a -- Your code here \n",
    "    EXTRACT(DAYOFWEEK FROM pickup_datetime) AS dayofweek,\n",
    "    EXTRACT(HOUR FROM pickup_datetime) AS hourofday,\n",
    "  pickuplon,\n",
    "  pickuplat,\n",
    "  dropofflon,\n",
    "  dropofflat\n",
    "FROM\n",
    "  `feat_eng.feateng_training_data`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "u2TU5nG6CiZe"
   },
   "source": [
    "Evaluate the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "h2yjF6uGCiZh"
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d177a6d6821e4dbd85e47092b8633301",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "169bd3ffe48e44838db0056b6b088f50",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Downloading:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.256421</td>\n",
       "      <td>70.709518</td>\n",
       "      <td>0.262399</td>\n",
       "      <td>3.894141</td>\n",
       "      <td>0.236668</td>\n",
       "      <td>0.236768</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             5.256421           70.709518                0.262399   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               3.894141  0.236668            0.236768  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.EVALUATE(MODEL feat_eng.model_2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "bhfabG8XCiZm"
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "075e675dc40c4fa0bea17ac732823a87",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "cdb2c22e6b7743b9b729094180ce7bf6",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Downloading:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rmse_model_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8.408895</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rmse_model_2\n",
       "0      8.408895"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT\n",
    "  SQRT(mean_squared_error) AS rmse_model_2\n",
    "FROM\n",
    "  ML.EVALUATE(MODEL feat_eng.model_2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "vbOSxv6BDqB-"
   },
   "source": [
    "### Model 3:  Feature cross dayofweek and hourofday using CONCAT\n",
    "\n",
    "First, let’s allow the model to learn traffic patterns by creating a new feature that combines the time of day and day of week (this is called a [feature cross](https://developers.google.com/machine-learning/crash-course/feature-crosses/video-lecture). \n",
    "\n",
    "Note:  BQML by default assumes that numbers are numeric features, and strings are categorical features.  We need to convert both the dayofweek and hourofday features to strings because the model (Neural Network) will automatically treat any integer as a numerical value rather than a categorical value.  Thus, if not cast as a string, the dayofweek feature will be interpreted as numeric values (e.g. 1,2,3,4,5,6,7) and hourofday will also be interpreted as numeric values (e.g. the day begins at midnight, 00:00, and the last minute of the day begins at 23:59 and ends at 24:00).  As such, there is no way to distinguish the \"feature cross\" of hourofday and dayofweek \"numerically\".  Casting the dayofweek and hourofday as strings ensures that each element will be treated like a label and will get its own coefficient associated with it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lab Task 3b:** Create the SQL statement to feature cross the dayofweek and hourofday using the CONCAT function.  Name the model \"model_3\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "cellView": "both",
    "colab": {},
    "colab_type": "code",
    "id": "F7l02C9KFMy7"
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "18277e83b02f4a4b95777fc933ba7b7a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "CREATE OR REPLACE MODEL\n",
    "  feat_eng.model_3 OPTIONS (model_type='linear_reg',\n",
    "    input_label_cols=['fare_amount']) AS\n",
    "SELECT\n",
    "  fare_amount,\n",
    "  passengers,\n",
    "\n",
    "  #TODO 3b -- Your code here  \n",
    "    CONCAT(CAST(EXTRACT(DAYOFWEEK\n",
    "      FROM\n",
    "        pickup_datetime) AS STRING), CAST(EXTRACT(HOUR\n",
    "      FROM\n",
    "        pickup_datetime) AS STRING)) AS hourofday,\n",
    "\n",
    "  pickuplon,\n",
    "  pickuplat,\n",
    "  dropofflon,\n",
    "  dropofflat\n",
    "FROM\n",
    "  `feat_eng.feateng_training_data`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we evaluate the model. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "8d5c4010900647bd97d9a25c9e8f43e8",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "dc51219c328f498ca9333c521845625f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Downloading:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.265321</td>\n",
       "      <td>69.356068</td>\n",
       "      <td>0.267477</td>\n",
       "      <td>3.887588</td>\n",
       "      <td>0.220371</td>\n",
       "      <td>0.220574</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             5.265321           69.356068                0.267477   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               3.887588  0.220371            0.220574  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.EVALUATE(MODEL feat_eng.model_3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d85aa1b466244eb6a266a7686e7a3c91",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Query is running:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4363324f914f48b2a7786938a8ac729a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Downloading:   0%|          |"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rmse_model_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8.328029</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   rmse_model_3\n",
       "0      8.328029"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT\n",
    "  SQRT(mean_squared_error) AS rmse_model_3\n",
    "FROM\n",
    "  ML.EVALUATE(MODEL feat_eng.model_3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "####  Markdown table generator:  http://www.tablesgenerator.com/markdown_tables\n",
    "Optional:  Create a RMSE summary table to evaluate model performance."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| Model          | Taxi Fare | Description                                  |\n",
    "|----------------|-----------|----------------------------------------------|\n",
    "| baseline_model | 8.627551      | --Baseline model - no feature engineering    |\n",
    "| model_1        | 9.431757      | --EXTRACT dayofweek from the pickup_datetime |\n",
    "| model_2        | 8.408895      | --EXTRACT hourofday from the pickup_datetime |\n",
    "| model_3        | 8.328029      | --FEATURE CROSS hourofday and dayofweek      |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copyright 2021 Google Inc.\n",
    "Licensed under the Apache License, Version 2.0 (the \"License\"); you may not use this file except in compliance with the License. You may obtain a copy of the License at\n",
    "http://www.apache.org/licenses/LICENSE-2.0\n",
    "Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an \"AS IS\" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License."
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "10.3.2019-DRAFT-_1 - FeatEnG - LABS.ipynb",
   "provenance": [],
   "toc_visible": true
  },
  "environment": {
   "kernel": "python3",
   "name": "tf2-gpu.2-6.m108",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/tf2-gpu.2-6:m108"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}