notebooks/regression/bq_dataframes_ml_linear_regression.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ur8xi4C7S06n"
      },
      "outputs": [],
      "source": [
        "# Copyright 2023 Google LLC\n",
        "#\n",
        "# Licensed under the Apache License, Version 2.0 (the \"License\");\n",
        "# you may not use this file except in compliance with the License.\n",
        "# You may obtain a copy of the License at\n",
        "#\n",
        "#     https://www.apache.org/licenses/LICENSE-2.0\n",
        "#\n",
        "# Unless required by applicable law or agreed to in writing, software\n",
        "# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
        "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
        "# See the License for the specific language governing permissions and\n",
        "# limitations under the License."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JAPoU8Sm5E6e"
      },
      "source": [
        "## Train a linear regression model with BigQuery DataFrames ML\n",
        "\n",
        "\n",
        "<table align=\"left\">\n",
        "\n",
        "  <td>\n",
        "    <a href=\"https://colab.research.google.com/github/googleapis/python-bigquery-dataframes/blob/main/notebooks/regression/bq_dataframes_ml_linear_regression.ipynb\">\n",
        "      <img src=\"https://cloud.google.com/ml-engine/images/colab-logo-32px.png\" alt=\"Colab logo\"> Run in Colab\n",
        "    </a>\n",
        "  </td>\n",
        "  <td>\n",
        "    <a href=\"https://github.com/googleapis/python-bigquery-dataframes/blob/main/notebooks/regression/bq_dataframes_ml_linear_regression.ipynb\">\n",
        "      <img src=\"https://cloud.google.com/ml-engine/images/github-logo-32px.png\" alt=\"GitHub logo\">\n",
        "      View on GitHub\n",
        "    </a>\n",
        "  </td>\n",
        "  <td>\n",
        "    <a href=\"https://console.cloud.google.com/vertex-ai/workbench/deploy-notebook?download_url=https://raw.githubusercontent.com/googleapis/python-bigquery-dataframes/blob/main/notebooks/regression/bq_dataframes_ml_linear_regression.ipynb\">\n",
        "      <img src=\"https://lh3.googleusercontent.com/UiNooY4LUgW_oTvpsNhPpQzsstV5W8F7rYgxgGBD85cWJoLmrOzhVs_ksK_vgx40SHs7jCqkTkCk=e14-rj-sc0xffffff-h130-w32\" alt=\"Vertex AI logo\">\n",
        "      Open in Vertex AI Workbench\n",
        "    </a>\n",
        "  </td>                                                                                               \n",
        "</table>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "24743cf4a1e1"
      },
      "source": [
        "**_NOTE_**: This notebook has been tested in the following environment:\n",
        "\n",
        "* Python version = 3.10"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tvgnzT1CKxrO"
      },
      "source": [
        "## Overview\n",
        "\n",
        "Use this notebook to learn how to train a linear regression model by using BigQuery DataFrames ML. BigQuery DataFrames ML provides a provides a scikit-learn-like API for ML powered by the BigQuery engine.\n",
        "\n",
        "This example is adapted from the [BQML linear regression tutorial](https://cloud.google.com/bigquery-ml/docs/linear-regression-tutorial).\n",
        "\n",
        "Learn more about [BigQuery DataFrames](https://cloud.google.com/python/docs/reference/bigframes/latest)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "d975e698c9a4"
      },
      "source": [
        "### Objective\n",
        "\n",
        "In this tutorial, you use BigQuery DataFrames to create a linear regression model that predicts the weight of an Adelie penguin based on the penguin's island of residence, culmen length and depth, flipper length, and sex.\n",
        "\n",
        "The steps include:\n",
        "\n",
        "- Creating a DataFrame from a BigQuery table.\n",
        "- Cleaning and preparing data using pandas.\n",
        "- Creating a linear regression model using `bigframes.ml`.\n",
        "- Saving the ML model to BigQuery for future use."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "08d289fa873f"
      },
      "source": [
        "### Dataset\n",
        "\n",
        "This tutorial uses the [```penguins``` table](https://console.cloud.google.com/bigquery?p=bigquery-public-data&d=ml_datasets&t=penguins) (a BigQuery Public Dataset) which includes data on a set of penguins including species, island of residence, weight, culmen length and depth, flipper length, and sex."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aed92deeb4a0"
      },
      "source": [
        "### Costs\n",
        "\n",
        "This tutorial uses billable components of Google Cloud:\n",
        "\n",
        "* BigQuery (compute)\n",
        "* BigQuery ML\n",
        "\n",
        "Learn about [BigQuery compute pricing](https://cloud.google.com/bigquery/pricing#analysis_pricing_models)\n",
        "and [BigQuery ML pricing](https://cloud.google.com/bigquery/pricing#bqml),\n",
        "and use the [Pricing Calculator](https://cloud.google.com/products/calculator/)\n",
        "to generate a cost estimate based on your projected usage."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "i7EUnXsZhAGF"
      },
      "source": [
        "## Installation\n",
        "\n",
        "Install the following packages, which are required to run this notebook:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9O0Ka4W2MNF3"
      },
      "outputs": [],
      "source": [
        "!pip install bigframes"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "58707a750154"
      },
      "source": [
        "### Colab only\n",
        "\n",
        "Uncomment and run the following cell to restart the kernel:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "f200f10a1da3"
      },
      "outputs": [],
      "source": [
        "# Automatically restart kernel after installs so that your environment can access the new packages\n",
        "# import IPython\n",
        "\n",
        "# app = IPython.Application.instance()\n",
        "# app.kernel.do_shutdown(True)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BF1j6f9HApxa"
      },
      "source": [
        "## Before you begin\n",
        "\n",
        "Complete the tasks in this section to set up your environment."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oDfTjfACBvJk"
      },
      "source": [
        "### Set up your Google Cloud project\n",
        "\n",
        "**The following steps are required, regardless of your notebook environment.**\n",
        "\n",
        "1. [Select or create a Google Cloud project](https://console.cloud.google.com/cloud-resource-manager). When you first create an account, you get a $300 credit towards your compute/storage costs.\n",
        "\n",
        "2. [Make sure that billing is enabled for your project](https://cloud.google.com/billing/docs/how-to/modify-project).\n",
        "\n",
        "3. [Enable the BigQuery API](https://console.cloud.google.com/flows/enableapi?apiid=bigquery.googleapis.com).\n",
        "\n",
        "4. If you are running this notebook locally, install the [Cloud SDK](https://cloud.google.com/sdk)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WReHDGG5g0XY"
      },
      "source": [
        "#### Set your project ID\n",
        "\n",
        "If you don't know your project ID, try the following:\n",
        "* Run `gcloud config list`.\n",
        "* Run `gcloud projects list`.\n",
        "* See the support page: [Locate the project ID](https://support.google.com/googleapi/answer/7014113)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "oM1iC_MfAts1"
      },
      "outputs": [],
      "source": [
        "PROJECT_ID = \"\"  # @param {type:\"string\"}\n",
        "\n",
        "# Set the project id\n",
        "! gcloud config set project {PROJECT_ID}"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "region"
      },
      "source": [
        "#### Set the region\n",
        "\n",
        "You can also change the `REGION` variable used by BigQuery. Learn more about [BigQuery regions](https://cloud.google.com/bigquery/docs/locations#supported_locations)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "eF-Twtc4XGem"
      },
      "outputs": [],
      "source": [
        "REGION = \"US\"  # @param {type: \"string\"}"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sBCra4QMA2wR"
      },
      "source": [
        "### Authenticate your Google Cloud account\n",
        "\n",
        "Depending on your Jupyter environment, you might have to manually authenticate. Follow the relevant instructions below."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "74ccc9e52986"
      },
      "source": [
        "**Vertex AI Workbench**\n",
        "\n",
        "Do nothing, you are already authenticated."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "de775a3773ba"
      },
      "source": [
        "**Local JupyterLab instance**\n",
        "\n",
        "Uncomment and run the following cell:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "254614fa0c46"
      },
      "outputs": [],
      "source": [
        "# ! gcloud auth login"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ef21552ccea8"
      },
      "source": [
        "**Colab**\n",
        "\n",
        "Uncomment and run the following cell:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "603adbbf0532"
      },
      "outputs": [],
      "source": [
        "# from google.colab import auth\n",
        "# auth.authenticate_user()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "960505627ddf"
      },
      "source": [
        "### Import libraries"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "PyQmSRbKA8r-"
      },
      "outputs": [],
      "source": [
        "import bigframes.pandas as bpd"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "init_aip:mbsdk,all"
      },
      "source": [
        "### Set BigQuery DataFrames options"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "NPPMuw2PXGeo"
      },
      "outputs": [],
      "source": [
        "# Note: The project option is not required in all environments.\n",
        "# On BigQuery Studio, the project ID is automatically detected.\n",
        "bpd.options.bigquery.project = PROJECT_ID\n",
        "\n",
        "# Note: The location option is not required.\n",
        "# It defaults to the location of the first table or query\n",
        "# passed to read_gbq(). For APIs where a location can't be\n",
        "# auto-detected, the location defaults to the \"US\" location.\n",
        "bpd.options.bigquery.location = REGION"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "D21CoOlfFTYI"
      },
      "source": [
        "If you want to reset the location of the created DataFrame or Series objects, reset the session by executing `bpd.close_session()`. After that, you can reuse `bpd.options.bigquery.location` to specify another location."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9EMAqR37AfLS"
      },
      "source": [
        "## Read a BigQuery table into a BigQuery DataFrames DataFrame\n",
        "\n",
        "Read the [```penguins``` table](https://console.cloud.google.com/bigquery?p=bigquery-public-data&d=ml_datasets&t=penguins) into a BigQuery DataFrames DataFrame:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "EDAaIwHpQCDZ"
      },
      "outputs": [],
      "source": [
        "df = bpd.read_gbq(\"bigquery-public-data.ml_datasets.penguins\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DJu837YEXD7B"
      },
      "source": [
        "Take a look at the DataFrame:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "_gPD0Zn1Stdb"
      },
      "outputs": [],
      "source": [
        "df.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rwPLjqW2Ajzh"
      },
      "source": [
        "## Clean and prepare data\n",
        "\n",
        "You can use pandas as you normally would on the BigQuery DataFrames DataFrame, but calculations happen in the BigQuery query engine instead of your local environment.\n",
        "\n",
        "Because this model will focus on the Adelie Penguin species, you need to filter the data for only those rows representing Adelie penguins. Then you drop the `species` column because it is no longer needed.\n",
        "\n",
        "As these functions are applied, only the new DataFrame object `adelie_data` is modified. The source table and the original DataFrame object `df` don't change."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6i6HkFJZa8na"
      },
      "outputs": [],
      "source": [
        "# Filter down to the data to the Adelie Penguin species\n",
        "adelie_data = df[df.species == \"Adelie Penguin (Pygoscelis adeliae)\"]\n",
        "\n",
        "# Drop the species column\n",
        "adelie_data = adelie_data.drop(columns=[\"species\"])\n",
        "\n",
        "# Take a look at the filtered DataFrame\n",
        "adelie_data"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jhK2OlyMbY4L"
      },
      "source": [
        "Drop rows with `NULL` values in order to create a BigQuery DataFrames DataFrame for the training data:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0am3hdlXZfxZ"
      },
      "outputs": [],
      "source": [
        "# Drop rows with nulls to get training data\n",
        "training_data = adelie_data.dropna()\n",
        "\n",
        "# Take a peek at the training data\n",
        "training_data"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "M_-0X7NxYK5f"
      },
      "source": [
        "Specify your feature (or input) columns and the label (or output) column:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "YKwCW7Nsavap"
      },
      "outputs": [],
      "source": [
        "feature_columns = training_data[['island', 'culmen_length_mm', 'culmen_depth_mm', 'flipper_length_mm', 'sex']]\n",
        "label_columns = training_data[['body_mass_g']]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CjyM7vZJZ0sQ"
      },
      "source": [
        "There is a row within the `adelie_data` BigQuery DataFrames DataFrame that has a `NULL` value for the `body mass` column. `body mass` is the label column, which is the value that the model you are creating is trying to predict.\n",
        "\n",
        "Create a new BigQuery DataFrames DataFrame, `test_data`, for this row so that you can use it as test data on which to make a prediction later:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "wej78IDUaRW9"
      },
      "outputs": [],
      "source": [
        "test_data = adelie_data[adelie_data.body_mass_g.isnull()]\n",
        "\n",
        "test_data"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Fx4lsNqMorJ-"
      },
      "source": [
        "## Create the linear regression model\n",
        "\n",
        "BigQuery DataFrames ML lets you move from exploring data to creating machine learning models through its scikit-learn-like API, `bigframes.ml`. BigQuery DataFrames ML supports several types of [ML models](https://cloud.google.com/python/docs/reference/bigframes/latest#ml-capabilities).\n",
        "\n",
        "In this notebook, you create a linear regression model, a type of regression model that generates a continuous value from a linear combination of input features.\n",
        "\n",
        "When you create a model with BigQuery DataFrames ML, it is saved locally and limited to the BigQuery session. However, as you'll see in the next section, you can use `to_gbq` to save the model permanently to your BigQuery project."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EloGtMnverFF"
      },
      "source": [
        "### Create the model using `bigframes.ml`\n",
        "\n",
        "When you pass the feature columns without transforms, BigQuery ML uses\n",
        "[automatic preprocessing](https://cloud.google.com/bigquery/docs/auto-preprocessing) to encode string values and scale numeric values.\n",
        "\n",
        "BigQuery ML also [automatically splits the data for training and evaluation](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-create-glm#data_split_method), although for datasets with less than 500 rows (such as this one), all rows are used for training."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "GskyyUQPowBT"
      },
      "outputs": [],
      "source": [
        "from bigframes.ml.linear_model import LinearRegression\n",
        "\n",
        "model = LinearRegression()\n",
        "\n",
        "model.fit(feature_columns, label_columns)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UGjeMPC2caKK"
      },
      "source": [
        "### Score the model\n",
        "\n",
        "Check how the model performed by using the `score` method. More information on model scoring can be found [here](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-evaluate#mlevaluate_output)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "kGBJKafpo0dl"
      },
      "outputs": [],
      "source": [
        "model.score(feature_columns, label_columns)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "P2lUiZZ_cjri"
      },
      "source": [
        "### Predict using the model\n",
        "\n",
        "Use the model to predict the body mass of the data row you saved earlier to the `test_data` DataFrame:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bsQ9cmoWo0Ps"
      },
      "outputs": [],
      "source": [
        "model.predict(test_data)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GTRdUw-Ro5R1"
      },
      "source": [
        "## Save the model in BigQuery\n",
        "\n",
        "The model is saved locally within this session. You can save the model permanently to BigQuery for use in future sessions, and to make the model sharable with others."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "K0mPaoGpcwwy"
      },
      "source": [
        "Create a BigQuery dataset to house the model, adding a name for your dataset as the `DATASET_ID` variable:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ZSP7gt13QrQt"
      },
      "outputs": [],
      "source": [
        "DATASET_ID = \"\"  # @param {type:\"string\"}\n",
        "\n",
        "from google.cloud import bigquery\n",
        "client = bigquery.Client(project=PROJECT_ID)\n",
        "dataset = bigquery.Dataset(PROJECT_ID + \".\" + DATASET_ID)\n",
        "dataset.location = REGION\n",
        "dataset = client.create_dataset(dataset, exists_ok=True)\n",
        "print(f\"Dataset {dataset.dataset_id} created.\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zqAIWWgJczp-"
      },
      "source": [
        "Save the model using the `to_gbq` method:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "QE_GD4Byo_jb"
      },
      "outputs": [],
      "source": [
        "model.to_gbq(DATASET_ID + \".penguin_weight\" , replace=True)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f7uHacAy49rT"
      },
      "source": [
        "You can view the saved model in the BigQuery console under the dataset you created in the first step. Run the following cell and follow the link to view your BigQuery console:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qDBoiA_0488Z"
      },
      "outputs": [],
      "source": [
        "print(f'https://console.developers.google.com/bigquery?p={PROJECT_ID}')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "G_wjSfXpWTuy"
      },
      "source": [
        "# Summary and next steps\n",
        "\n",
        "You've created a linear regression model using `bigframes.ml`.\n",
        "\n",
        "Learn more about BigQuery DataFrames in the [documentation](https://cloud.google.com/python/docs/reference/bigframes/latest) and find more sample notebooks in the [GitHub repo](https://github.com/googleapis/python-bigquery-dataframes/tree/main/notebooks)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TpV-iwP9qw9c"
      },
      "source": [
        "## Cleaning up\n",
        "\n",
        "To clean up all Google Cloud resources used in this project, you can [delete the Google Cloud\n",
        "project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#shutting_down_projects) you used for the tutorial.\n",
        "\n",
        "Otherwise, you can uncomment the remaining cells and run them to delete the individual resources you created in this tutorial:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "sx_vKniMq9ZX"
      },
      "outputs": [],
      "source": [
        "# # Delete the BigQuery dataset and associated ML model\n",
        "# from google.cloud import bigquery\n",
        "# client = bigquery.Client(project=PROJECT_ID)\n",
        "# client.delete_dataset(\n",
        "#  DATASET_ID, delete_contents=True, not_found_ok=True\n",
        "# )\n",
        "# print(\"Deleted dataset '{}'.\".format(DATASET_ID))"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}