notebooks/official/bigquery_ml/get_started_with_bqml_training.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "copyright"
      },
      "outputs": [],
      "source": [
        "# Copyright 2022 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": "title:generic,gcp"
      },
      "source": [
        "# Get started with BigQuery ML Training\n",
        "\n",
        "<table align=\"left\">\n",
        "  <td>\n",
        "    <a href=\"https://github.com/GoogleCloudPlatform/vertex-ai-samples/blob/main/notebooks/official/bigquery_ml/get_started_with_bqml_training.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://colab.research.google.com/github/GoogleCloudPlatform/vertex-ai-samples/blob/main/notebooks/official/bigquery_ml/get_started_with_bqml_training.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://console.cloud.google.com/vertex-ai/workbench/deploy-notebook?download_url=https://raw.githubusercontent.com/GoogleCloudPlatform/vertex-ai-samples/main/notebooks/official/bigquery_ml/get_started_with_bqml_training.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>\n",
        "<br/><br/><br/>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "overview:mlops"
      },
      "source": [
        "## Overview\n",
        "\n",
        "This tutorial demonstrates how to use Vertex AI in production. This tutorial covers get started with BigQuery ML training.\n",
        "\n",
        "Learn more about [BigQuery ML](https://cloud.google.com/vertex-ai/docs/beginner/bqml)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "objective:mlops,stage2,get_started_bqml_training"
      },
      "source": [
        "### Objective\n",
        "\n",
        "In this tutorial, you learn how to use BigQuery ML for training with Vertex AI.\n",
        "\n",
        "This tutorial uses the following Google Cloud ML services:\n",
        "\n",
        "- BigQuery ML training\n",
        "- Vertex AI model resource\n",
        "- Vertex AI Vizier\n",
        "\n",
        "The steps performed include:\n",
        "\n",
        "- Create a local BigQuery table in your project\n",
        "- Train a BigQuery ML model\n",
        "- Evaluate the BigQuery ML model\n",
        "- Export the BigQuery ML model as a cloud model\n",
        "- Upload the exported model as a Vertex AI model resource\n",
        "- Hyperparameter tune a BigQuery ML model with Vertex AI Vizier\n",
        "- Automatically register a BigQuery ML model to Vertex AI Model Registry"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dataset:penguins,lcn,bq"
      },
      "source": [
        "### Dataset\n",
        "\n",
        "The dataset used for this tutorial is the Penguins dataset from [BigQuery public datasets](https://cloud.google.com/bigquery/public-data). This version of the dataset is used to predict the species of penguins from the available features like culmen-length, flipper-depth etc."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "81c777b8ad32"
      },
      "source": [
        "### Costs\n",
        "This tutorial uses billable components of Google Cloud:\n",
        "\n",
        "- Vertex AI\n",
        "- Cloud Storage\n",
        "- BigQuery\n",
        "\n",
        "Learn about [Vertex AI pricing](https://cloud.google.com/vertex-ai/pricing), [Cloud Storage pricing](https://cloud.google.com/storage/pricing) and [BigQuery pricing](https://cloud.google.com/bigquery/pricing) and use the [Pricing Calculator](https://cloud.google.com/products/calculator/) to generate a cost estimate based on your projected usage."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "install_mlops"
      },
      "source": [
        "## Installations\n",
        "\n",
        "Install the following packages for executing this notebook."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "install_mlops"
      },
      "outputs": [],
      "source": [
        "# Install the packages\n",
        "! pip3 install --upgrade --quiet pyarrow \\\n",
        "                                 google-cloud-aiplatform \\\n",
        "                                 google-cloud-bigquery \\\n",
        "                                 google-cloud-bigquery-storage \\\n",
        "                                 db-dtypes "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "restart"
      },
      "source": [
        "### Colab only: Uncomment the following cell to restart the kernel"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "D-ZBOjErv5mM"
      },
      "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": "yfEglUHQk9S3"
      },
      "source": [
        "## Before you begin\n",
        "\n",
        "### 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": "set_project_id"
      },
      "outputs": [],
      "source": [
        "PROJECT_ID = \"[your-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": [
        "#### Region\n",
        "\n",
        "You can also change the `REGION` variable used by Vertex AI. Learn more about [Vertex AI regions](https://cloud.google.com/vertex-ai/docs/general/locations)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "region"
      },
      "outputs": [],
      "source": [
        "REGION = \"us-central1\"  # @param {type: \"string\"}"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gcp_authenticate"
      },
      "source": [
        "### Authenticate your Google Cloud account\n",
        "\n",
        "Depending on your Jupyter environment, you may have to manually authenticate. Follow the relevant instructions below.\n",
        "\n",
        "**1. Vertex AI Workbench**\n",
        "* Do nothing as you are already authenticated.\n",
        "\n",
        "**2. Local JupyterLab instance, uncomment and run:**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ce6043da7b33"
      },
      "outputs": [],
      "source": [
        "# ! gcloud auth login"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0367eac06a10"
      },
      "source": [
        "**3. Colab, uncomment and run:**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "21ad4dbb4a61"
      },
      "outputs": [],
      "source": [
        "# from google.colab import auth\n",
        "# auth.authenticate_user()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "c13224697bfb"
      },
      "source": [
        "**4. Service account or other**\n",
        "* See how to grant Cloud Storage permissions to your service account at https://cloud.google.com/storage/docs/gsutil/commands/iam#ch-examples."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bucket:mbsdk"
      },
      "source": [
        "### Create a Cloud Storage bucket\n",
        "\n",
        "Create a storage bucket to store intermediate artifacts such as datasets."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bucket"
      },
      "outputs": [],
      "source": [
        "BUCKET_URI = f\"gs://your-bucket-name-{PROJECT_ID}-unique\"  # @param {type:\"string\"}"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "create_bucket"
      },
      "source": [
        "**If your bucket doesn't already exist**: Run the following cell to create your Cloud Storage bucket."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "create_bucket"
      },
      "outputs": [],
      "source": [
        "! gsutil mb -l {REGION} {BUCKET_URI}"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "set_service_account"
      },
      "source": [
        "#### Service Account\n",
        "\n",
        "You use a service account to create Vertex AI Pipeline jobs. If you don't want to use your project's Compute Engine service account, set `SERVICE_ACCOUNT` to another service account ID."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "set_service_account"
      },
      "outputs": [],
      "source": [
        "SERVICE_ACCOUNT = \"[your-service-account]\"  # @param {type:\"string\"}"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "autoset_service_account"
      },
      "outputs": [],
      "source": [
        "import sys\n",
        "\n",
        "IS_COLAB = \"google.colab\" in sys.modules\n",
        "\n",
        "if (\n",
        "    SERVICE_ACCOUNT == \"\"\n",
        "    or SERVICE_ACCOUNT is None\n",
        "    or SERVICE_ACCOUNT == \"[your-service-account]\"\n",
        "):\n",
        "    # Get your service account from gcloud\n",
        "    if not IS_COLAB:\n",
        "        shell_output = !gcloud auth list 2>/dev/null\n",
        "        SERVICE_ACCOUNT = shell_output[2].replace(\"*\", \"\").strip()\n",
        "\n",
        "    if IS_COLAB:\n",
        "        shell_output = ! gcloud projects describe  $PROJECT_ID\n",
        "        project_number = shell_output[-1].split(\":\")[1].strip().replace(\"'\", \"\")\n",
        "        SERVICE_ACCOUNT = f\"{project_number}-compute@developer.gserviceaccount.com\"\n",
        "\n",
        "    print(\"Service Account:\", SERVICE_ACCOUNT)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "setup_vars"
      },
      "source": [
        "### Import libraries and define constants"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "import_aip:mbsdk"
      },
      "outputs": [],
      "source": [
        "import google.cloud.aiplatform as aiplatform\n",
        "from google.cloud import bigquery"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "init_aip:mbsdk"
      },
      "source": [
        "### Initialize Vertex AI and BigQuery SDKs for Python\n",
        "\n",
        "Initialize the Vertex AI SDK for Python for your project and corresponding bucket."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "init_aip:mbsdk"
      },
      "outputs": [],
      "source": [
        "aiplatform.init(project=PROJECT_ID, staging_bucket=BUCKET_URI)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "init_bq"
      },
      "source": [
        "Create the BigQuery client."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "init_bq"
      },
      "outputs": [],
      "source": [
        "bqclient = bigquery.Client(project=PROJECT_ID)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "accelerators:prediction,mbsdk"
      },
      "source": [
        "### Set hardware accelerators\n",
        "\n",
        "You can set hardware accelerators for prediction.\n",
        "\n",
        "Set the variable `DEPLOY_GPU/DEPLOY_NGPU` to use a container image supporting a GPU and the number of GPUs allocated to the virtual machine (VM) instance. For example, to use a GPU container image with 4 Nvidia Telsa K80 GPUs allocated to each VM, you would specify:\n",
        "\n",
        "    (aiplatform.AcceleratorType.NVIDIA_TESLA_K80, 4)\n",
        "\n",
        "Otherwise specify `(None, None)` to use a container image to run on a CPU.\n",
        "\n",
        "Learn more [here](https://cloud.google.com/vertex-ai/docs/general/locations#accelerators) hardware accelerator support for your region."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "accelerators:prediction,mbsdk"
      },
      "outputs": [],
      "source": [
        "import os\n",
        "\n",
        "if os.getenv(\"IS_TESTING_DEPLOY_GPU\"):\n",
        "    DEPLOY_GPU, DEPLOY_NGPU = (\n",
        "        aiplatform.gapic.AcceleratorType.NVIDIA_TESLA_K80,\n",
        "        int(os.getenv(\"IS_TESTING_DEPLOY_GPU\")),\n",
        "    )\n",
        "else:\n",
        "    DEPLOY_GPU, DEPLOY_NGPU = (aiplatform.gapic.AcceleratorType.NVIDIA_TESLA_K80, 1)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "container:prediction"
      },
      "source": [
        "### Set pre-built containers\n",
        "\n",
        "Set the pre-built Docker container image for prediction.\n",
        "\n",
        "- Set the variable `TF` to the TensorFlow version of the container image. For example, `2-1` would be version 2.1, and `1-15` would be version 1.15. The following list shows some of the pre-built images available:\n",
        "\n",
        "\n",
        "For the latest list, see [Pre-built containers for prediction](https://cloud.google.com/ai-platform-unified/docs/predictions/pre-built-containers)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "container:prediction"
      },
      "outputs": [],
      "source": [
        "if os.getenv(\"IS_TESTING_TF\"):\n",
        "    TF = os.getenv(\"IS_TESTING_TF\")\n",
        "else:\n",
        "    TF = \"2.5\".replace(\".\", \"-\")\n",
        "\n",
        "if TF[0] == \"2\":\n",
        "    if DEPLOY_GPU:\n",
        "        DEPLOY_VERSION = \"tf2-gpu.{}\".format(TF)\n",
        "    else:\n",
        "        DEPLOY_VERSION = \"tf2-cpu.{}\".format(TF)\n",
        "else:\n",
        "    if DEPLOY_GPU:\n",
        "        DEPLOY_VERSION = \"tf-gpu.{}\".format(TF)\n",
        "    else:\n",
        "        DEPLOY_VERSION = \"tf-cpu.{}\".format(TF)\n",
        "\n",
        "DEPLOY_IMAGE = \"{}-docker.pkg.dev/vertex-ai/prediction/{}:latest\".format(\n",
        "    REGION.split(\"-\")[0], DEPLOY_VERSION\n",
        ")\n",
        "\n",
        "print(\"Deployment:\", DEPLOY_IMAGE, DEPLOY_GPU)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "machine:prediction"
      },
      "source": [
        "### Set machine type\n",
        "\n",
        "Next, set the machine type to use for prediction.\n",
        "\n",
        "- Set the variable `DEPLOY_COMPUTE` to configure the compute resources for the VM which is used for prediction.\n",
        " - `machine type`\n",
        "     - `n1-standard`: 3.75GB of memory per vCPU.\n",
        "     - `n1-highmem`: 6.5GB of memory per vCPU\n",
        "     - `n1-highcpu`: 0.9 GB of memory per vCPU\n",
        " - `vCPUs`: number of \\[2, 4, 8, 16, 32, 64, 96 \\]\n",
        "\n",
        "*Note: You may also use n2 and e2 machine types for training and deployment, but they do not support GPUs*"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "machine:prediction"
      },
      "outputs": [],
      "source": [
        "if os.getenv(\"IS_TESTING_DEPLOY_MACHINE\"):\n",
        "    MACHINE_TYPE = os.getenv(\"IS_TESTING_DEPLOY_MACHINE\")\n",
        "else:\n",
        "    MACHINE_TYPE = \"n1-standard\"\n",
        "\n",
        "VCPU = \"4\"\n",
        "DEPLOY_COMPUTE = MACHINE_TYPE + \"-\" + VCPU\n",
        "print(\"Deploy machine type\", DEPLOY_COMPUTE)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bqml_intro"
      },
      "source": [
        "## BigQuery ML introduction\n",
        "\n",
        "BigQuery ML (BQML) provides the capability to train ML tabular models, such as classification and regression, in BigQuery using SQL syntax.\n",
        "\n",
        "Learn more about [BigQuery ML documentation](https://cloud.google.com/bigquery-ml/docs)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "import_file:penguins,bq,lcn"
      },
      "outputs": [],
      "source": [
        "IMPORT_FILE = \"bq://bigquery-public-data.ml_datasets.penguins\"\n",
        "BQ_TABLE = \"bigquery-public-data.ml_datasets.penguins\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bqml_create_dataset"
      },
      "source": [
        "### Create BQ dataset resource\n",
        "\n",
        "First, you create an empty dataset resource in your project."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bqml_create_dataset"
      },
      "outputs": [],
      "source": [
        "BQ_DATASET_NAME = \"penguins\"\n",
        "DATASET_QUERY = f\"\"\"CREATE SCHEMA {BQ_DATASET_NAME}\n",
        "\"\"\"\n",
        "\n",
        "job = bqclient.query(DATASET_QUERY)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bqml_create_model"
      },
      "source": [
        "### Train BigQuery ML model\n",
        "\n",
        "Next, you create and train a BigQuery ML tabular classification model from the public dataset penguins and store the model in your project using the `CREATE MODEL` statement. The model configuration is specified in the `OPTIONS` statement as follows:\n",
        "\n",
        "- `model_type`: The type and archictecture of tabular model to train, e.g., DNN classification.\n",
        "- `labels`: The column which are the labels.\n",
        "\n",
        "Learn more about [The CREATE MODEL statement](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bqml_create_model"
      },
      "outputs": [],
      "source": [
        "MODEL_NAME = \"penguins\"\n",
        "MODEL_QUERY = f\"\"\"\n",
        "CREATE OR REPLACE MODEL `{BQ_DATASET_NAME}.{MODEL_NAME}`\n",
        "OPTIONS(\n",
        "    model_type='DNN_CLASSIFIER',\n",
        "    labels = ['species']\n",
        "    )\n",
        "AS\n",
        "SELECT *\n",
        "FROM `{BQ_TABLE}`\n",
        "\"\"\"\n",
        "\n",
        "job = bqclient.query(MODEL_QUERY)\n",
        "print(job.errors, job.state)\n",
        "\n",
        "while job.running():\n",
        "    from time import sleep\n",
        "\n",
        "    sleep(30)\n",
        "    print(\"Running ...\")\n",
        "print(job.errors, job.state)\n",
        "\n",
        "try:\n",
        "    tblname = job.ddl_target_table\n",
        "    tblname = \"{}.{}\".format(tblname.dataset_id, tblname.table_id)\n",
        "    print(\"{} created in {}\".format(tblname, job.ended - job.started))\n",
        "except Exception as e:\n",
        "    print(e)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bqml_eval_model"
      },
      "source": [
        "### Evaluate the trained BigQuery ML model\n",
        "\n",
        "Next, retrieve the model evaluation for the trained BigQuery ML model.\n",
        "\n",
        "Learn more about [The ML.EVALUATE function](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-evaluate)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bqml_eval_model"
      },
      "outputs": [],
      "source": [
        "EVAL_QUERY = f\"\"\"\n",
        "SELECT *\n",
        "FROM\n",
        "  ML.EVALUATE(MODEL {BQ_DATASET_NAME}.{MODEL_NAME})\n",
        "ORDER BY  roc_auc desc\n",
        "LIMIT 1\"\"\"\n",
        "\n",
        "try:\n",
        "    job = bqclient.query(EVAL_QUERY)\n",
        "    results = job.result().to_dataframe()\n",
        "    print(results)\n",
        "except Exception as e:\n",
        "    print(e)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bqml_export_model"
      },
      "source": [
        "### Export the model from BigQuery ML\n",
        "\n",
        "The model you trained in BigQuery ML is a TensorFlow model. Next, you export the TensorFlow model artifacts in TF.SavedModel format."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bqml_export_model"
      },
      "outputs": [],
      "source": [
        "param = f\"{PROJECT_ID}:{BQ_DATASET_NAME}.{MODEL_NAME} {BUCKET_URI}/{MODEL_NAME}\"\n",
        "! bq extract -m $param\n",
        "\n",
        "MODEL_DIR = f\"{BUCKET_URI}/{BQ_DATASET_NAME}\"\n",
        "! gsutil ls $MODEL_DIR"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "upload_bqml_model"
      },
      "source": [
        "## Upload the BigQuery ML model to a Vertex AI Model resource\n",
        "\n",
        "Finally, now that you have the BigQuery ML model exported, you upload the model artifacts to Vertex AI Model resource, in the same way as if you were uploading a custom trained model.\n",
        "\n",
        "Below is a partial list of mapping BigQuery ML model types to their corresponding exported model format:\n",
        "\n",
        "'LINEAR_REG'<br/>\n",
        "'LOGISTIC_REG' --> TensorFlow SavedFormat\n",
        "\n",
        "'AUTOML_CLASSIFIER'<br/>\n",
        "'AUTOML_REGRESSOR' --> TensorFlow SavedFormat\n",
        "\n",
        "'BOOSTED_TREE_CLASSIFIER'<br/>\n",
        "'BOOSTED_TREE_REGRESSOR' --> XGBoost format\n",
        "\n",
        "'DNN_CLASSIFIER'<br/>\n",
        "'DNN_REGRESSOR'<br/>\n",
        "'DNN_LINEAR_COMBINED_CLASSIFIER'<br/>\n",
        "'DNN_LINEAR_COMBINED_REGRESSOR' --> TensorFlow Estimator"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "upload_bqml_model"
      },
      "outputs": [],
      "source": [
        "try:\n",
        "    model = aiplatform.Model.upload(\n",
        "        display_name=\"penguins\",\n",
        "        artifact_uri=MODEL_DIR,\n",
        "        serving_container_image_uri=DEPLOY_IMAGE,\n",
        "        sync=True,\n",
        "    )\n",
        "except Exception as e:\n",
        "    print(e)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "deploy_model:mbsdk,all"
      },
      "source": [
        "## Deploy the model\n",
        "\n",
        "Next, deploy your model for online prediction. To deploy the model, you invoke the `deploy` method, with the following parameters:\n",
        "\n",
        "- `deployed_model_display_name`: A human readable name for the deployed model.\n",
        "- `traffic_split`: Percent of traffic at the endpoint that goes to this model, which is specified as a dictionary of one or more key/value pairs.\n",
        "If only one model, then specify as { \"0\": 100 }, where \"0\" refers to this model being uploaded and 100 means 100% of the traffic.\n",
        "If there are existing models on the endpoint, for which the traffic needs to be split, then use model_id to specify as { \"0\": percent, model_id: percent, ... }, where model_id is the model id of an existing model to the deployed endpoint. The percents must add up to 100.\n",
        "- `machine_type`: The type of machine to use for training.\n",
        "- `accelerator_type`: The hardware accelerator type.\n",
        "- `accelerator_count`: The number of accelerators to attach to a worker replica.\n",
        "- `starting_replica_count`: The number of compute instances to initially provision.\n",
        "- `max_replica_count`: The maximum number of compute instances to scale to. In this tutorial, only one instance is provisioned."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "deploy_model:mbsdk,all"
      },
      "outputs": [],
      "source": [
        "DEPLOYED_NAME = \"penguins\"\n",
        "\n",
        "TRAFFIC_SPLIT = {\"0\": 100}\n",
        "\n",
        "MIN_NODES = 1\n",
        "MAX_NODES = 1\n",
        "\n",
        "try:\n",
        "    if DEPLOY_GPU:\n",
        "        endpoint = model.deploy(\n",
        "            deployed_model_display_name=DEPLOYED_NAME,\n",
        "            traffic_split=TRAFFIC_SPLIT,\n",
        "            machine_type=DEPLOY_COMPUTE,\n",
        "            accelerator_type=DEPLOY_GPU.name,\n",
        "            accelerator_count=DEPLOY_NGPU,\n",
        "            min_replica_count=MIN_NODES,\n",
        "            max_replica_count=MAX_NODES,\n",
        "        )\n",
        "    else:\n",
        "        endpoint = model.deploy(\n",
        "            deployed_model_display_name=DEPLOYED_NAME,\n",
        "            traffic_split=TRAFFIC_SPLIT,\n",
        "            machine_type=DEPLOY_COMPUTE,\n",
        "            min_replica_count=MIN_NODES,\n",
        "            max_replica_count=MAX_NODES,\n",
        "        )\n",
        "except Exception as e:\n",
        "    print(e)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "undeploy_model:mbsdk"
      },
      "source": [
        "#### Undeploy the model\n",
        "\n",
        "When you are done doing predictions, you undeploy the model from the `Endpoint` resouce. This deprovisions all compute resources and ends billing for the deployed model."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "undeploy_model:mbsdk"
      },
      "outputs": [],
      "source": [
        "try:\n",
        "    endpoint.undeploy_all()\n",
        "except Exception as e:\n",
        "    print(e)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "model_delete:mbsdk"
      },
      "source": [
        "#### Delete the `Vertex AI Model` resource\n",
        "\n",
        "The method 'delete()' deletes the model."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "model_delete:mbsdk"
      },
      "outputs": [],
      "source": [
        "try:\n",
        "    model.delete()\n",
        "except Exception as e:\n",
        "    print(e)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7890ae6f6410"
      },
      "source": [
        "### Delete the `BigQuery ML` model\n",
        "\n",
        "Next, delete the `BigQuery ML` instance of the model."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "f0b6163e70c0"
      },
      "outputs": [],
      "source": [
        "MODEL_QUERY = f\"\"\"\n",
        "DROP MODEL `{BQ_DATASET_NAME}.{MODEL_NAME}`\n",
        "\"\"\"\n",
        "\n",
        "try:\n",
        "    job = bqclient.query(MODEL_QUERY)\n",
        "except Exception as e:\n",
        "    print(e)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bqml_create_model:vizier"
      },
      "source": [
        "### Hyperparameter Tune and train a BigQuery ML model\n",
        "\n",
        "Next, you train a BigQuery ML tabular classification model with hyperparameter tuning using the Vertex AI Vizier service. The hyperparameter settings are specified in the `OPTIONS` statement as follows:\n",
        "\n",
        "- `HPARAM_TUNING_ALGORITHM`: The algorithm for selecting the next trial parameters.\n",
        "- `num_trials`: The number of trials.\n",
        "- `max_parallel_trials`: The number of trials to do in parallel.\n",
        "\n",
        "Learn more about [Hyperparameter tuning for CREATE MODEL statements](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-hyperparameter-tuning)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bqml_create_model:vizier"
      },
      "outputs": [],
      "source": [
        "MODEL_NAME = \"penguins\"\n",
        "MODEL_QUERY = f\"\"\"\n",
        "CREATE OR REPLACE MODEL `{BQ_DATASET_NAME}.{MODEL_NAME}`\n",
        "OPTIONS(\n",
        "    model_type='DNN_CLASSIFIER',\n",
        "    labels = ['species'],\n",
        "    num_trials=10,\n",
        "    max_parallel_trials=2,\n",
        "    HPARAM_TUNING_ALGORITHM = 'VIZIER_DEFAULT'\n",
        "    )\n",
        "AS\n",
        "SELECT *\n",
        "FROM `{BQ_TABLE}`\n",
        "\"\"\"\n",
        "\n",
        "job = bqclient.query(MODEL_QUERY)\n",
        "print(job.errors, job.state)\n",
        "\n",
        "while job.running():\n",
        "    from time import sleep\n",
        "\n",
        "    sleep(30)\n",
        "    print(\"Running ...\")\n",
        "print(job.errors, job.state)\n",
        "\n",
        "try:\n",
        "    tblname = job.ddl_target_table\n",
        "    tblname = \"{}.{}\".format(tblname.dataset_id, tblname.table_id)\n",
        "    print(\"{} created in {}\".format(tblname, job.ended - job.started))\n",
        "except Exception as e:\n",
        "    print(e)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bqml_eval_model"
      },
      "source": [
        "### Evaluate the BigQuery ML trained model\n",
        "\n",
        "Next, retrieve the model evaluation results for the trained BigQuery ML model.\n",
        "\n",
        "Learn more about [The ML.EVALUATE function](https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-evaluate)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bqml_eval_model"
      },
      "outputs": [],
      "source": [
        "try:\n",
        "    EVAL_QUERY = f\"\"\"\n",
        "    SELECT *\n",
        "    FROM\n",
        "      ML.EVALUATE(MODEL {BQ_DATASET_NAME}.{MODEL_NAME})\n",
        "    ORDER BY  roc_auc desc\n",
        "    LIMIT 1\"\"\"\n",
        "\n",
        "    job = bqclient.query(EVAL_QUERY)\n",
        "    results = job.result().to_dataframe()\n",
        "    print(results)\n",
        "except Exception as e:\n",
        "    print(e)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3f3cee1236b1"
      },
      "source": [
        "### Delete the BigQuery ML model\n",
        "\n",
        "Next, delete the BigQuery ML instance of the model."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "957b7d841502"
      },
      "outputs": [],
      "source": [
        "MODEL_QUERY = f\"\"\"\n",
        "DROP MODEL `{BQ_DATASET_NAME}.{MODEL_NAME}`\n",
        "\"\"\"\n",
        "\n",
        "job = bqclient.query(MODEL_QUERY)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bqml_create_model:xai"
      },
      "source": [
        "### Train a BigQuery ML model with Explainability\n",
        "\n",
        "Next, you train the same BigQuery ML model, but this time you enable Vertex AI Explainability on the model predictions by adding the option:\n",
        "\n",
        "- `ENABLE_GLOBAL_EXPLAIN`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "bqml_create_model:xai"
      },
      "outputs": [],
      "source": [
        "MODEL_NAME = \"penguins\"\n",
        "MODEL_QUERY = f\"\"\"\n",
        "CREATE OR REPLACE MODEL `{BQ_DATASET_NAME}.{MODEL_NAME}`\n",
        "OPTIONS(\n",
        "    model_type='DNN_CLASSIFIER',\n",
        "    labels = ['species'],\n",
        "    ENABLE_GLOBAL_EXPLAIN = True\n",
        "    )\n",
        "AS\n",
        "SELECT *\n",
        "FROM `{BQ_TABLE}`\n",
        "\"\"\"\n",
        "\n",
        "job = bqclient.query(MODEL_QUERY)\n",
        "print(job.errors, job.state)\n",
        "\n",
        "while job.running():\n",
        "    from time import sleep\n",
        "\n",
        "    sleep(30)\n",
        "    print(\"Running ...\")\n",
        "print(job.errors, job.state)\n",
        "\n",
        "try:\n",
        "    tblname = job.ddl_target_table\n",
        "    tblname = \"{}.{}\".format(tblname.dataset_id, tblname.table_id)\n",
        "    print(\"{} created in {}\".format(tblname, job.ended - job.started))\n",
        "except Exception as e:\n",
        "    print(e)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4def8aaf3398"
      },
      "source": [
        "### Delete the BigQuery ML model\n",
        "\n",
        "Next, delete the BigQuery ML instance of the model."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ff5b32618018"
      },
      "outputs": [],
      "source": [
        "MODEL_QUERY = f\"\"\"\n",
        "DROP MODEL `{BQ_DATASET_NAME}.{MODEL_NAME}`\n",
        "\"\"\"\n",
        "\n",
        "job = bqclient.query(MODEL_QUERY)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2b4498ca6fea"
      },
      "source": [
        "## Model Registry\n",
        "\n",
        "Alternatively, you can implicitly upload your BigQuery ML model as a Vertex AI model resource with exporting and importing the model artifacts. In this method, you add additional options when training the model that tells BigQuery ML to automatically upload and register the trained model as a model resource.\n",
        "\n",
        "### Setting permissions to automatically register the model\n",
        "\n",
        "You need to set some additional IAM permissions for BigQuery ML to automatically upload and register the model after training. Depending on your service account, the setting of the permissions below may fail. In this case, we recommend executing the permissions in a Cloud Shell.\n",
        "\n",
        "Learn more about [Setting permissions for Model Registry](https://cloud.google.com/bigquery-ml/docs/managing-models-vertex)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "29229f72d13d"
      },
      "outputs": [],
      "source": [
        "! gcloud projects add-iam-policy-binding $PROJECT_ID \\\n",
        "    --member=serviceAccount:$SERVICE_ACCOUNT --role=roles/aiplatform.admin --condition=None"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6c390ee7c11a"
      },
      "source": [
        "### Training and registering the model\n",
        "\n",
        "Next, you train the model and automatically register the model to the Vertex AI Model Registry, by adding the following parameters as options:\n",
        "\n",
        "- `model_registry`: Set to \"vertex_ai\" to indicate automatic registration to Vertex AI Model Registry.\n",
        "- `vertex_ai_model_id`: The human readable display name for the registered model.\n",
        "- `vertex_ai_model_version_aliases`: Alternate names for the model."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "57db464f4c42"
      },
      "outputs": [],
      "source": [
        "MODEL_NAME = \"penguins\"\n",
        "MODEL_QUERY = f\"\"\"\n",
        "CREATE OR REPLACE MODEL `{BQ_DATASET_NAME}.{MODEL_NAME}`\n",
        "OPTIONS(\n",
        "    model_type='DNN_CLASSIFIER',\n",
        "    labels = ['species'],\n",
        "    model_registry=\"vertex_ai\",\n",
        "    vertex_ai_model_id=\"bqml_model\", \n",
        "    vertex_ai_model_version_aliases=[\"1\"]\n",
        "    )\n",
        "AS\n",
        "SELECT *\n",
        "FROM `{BQ_TABLE}`\n",
        "\"\"\"\n",
        "\n",
        "job = bqclient.query(MODEL_QUERY)\n",
        "print(job.errors, job.state)\n",
        "\n",
        "while job.running():\n",
        "    from time import sleep\n",
        "\n",
        "    sleep(30)\n",
        "    print(\"Running ...\")\n",
        "print(job.errors, job.state)\n",
        "\n",
        "try:\n",
        "    tblname = job.ddl_target_table\n",
        "    tblname = \"{}.{}\".format(tblname.dataset_id, tblname.table_id)\n",
        "    print(\"{} created in {}\".format(tblname, job.ended - job.started))\n",
        "except Exception as e:\n",
        "    print(e)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5b4970272040"
      },
      "source": [
        "### Find the model in the Vertex Model Registry\n",
        "\n",
        "Finally, you can use the Vertex AI Model `list()` method with a filter query to find the automatically registered model."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "76c22674ba99"
      },
      "outputs": [],
      "source": [
        "models = aiplatform.Model.list(filter=\"display_name=bqml_model\")\n",
        "model = models[0]\n",
        "\n",
        "print(model.gca_resource)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "48e6ef5d5ffa"
      },
      "outputs": [],
      "source": [
        "models = aiplatform.Model.list()\n",
        "for model in models:\n",
        "    if model.gca_resource.display_name.startswith(\"bqml\"):\n",
        "        print(model.gca_resource.display_name)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ef61354b1a5f"
      },
      "source": [
        "### Delete the BigQuery ML model\n",
        "\n",
        "Next, delete the BigQuery ML instance of the model."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "f6004d1ce59d"
      },
      "outputs": [],
      "source": [
        "MODEL_QUERY = f\"\"\"\n",
        "DROP MODEL `{BQ_DATASET_NAME}.{MODEL_NAME}`\n",
        "\"\"\"\n",
        "\n",
        "job = bqclient.query(MODEL_QUERY)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cleanup:mbsdk"
      },
      "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 delete the individual resources you created in this tutorial.\n",
        "\n",
        "Set `delete_storage` to `True` to delete the Cloud Storage bucket used in this notebook."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "cleanup:mbsdk"
      },
      "outputs": [],
      "source": [
        "# Delete the endpoint using the Vertex endpoint object\n",
        "try:\n",
        "    endpoint.undeploy_all()\n",
        "    endpoint.delete()\n",
        "except Exception as e:\n",
        "    print(e)\n",
        "\n",
        "# Delete the model using the Vertex model object\n",
        "try:\n",
        "    model.delete()\n",
        "except Exception as e:\n",
        "    print(e)\n",
        "\n",
        "# Delete the created BigQuery dataset\n",
        "! bq rm -r -f $PROJECT_ID:$BQ_DATASET_NAME\n",
        "\n",
        "delete_storage = False\n",
        "if delete_storage or os.getenv(\"IS_TESTING\"):\n",
        "    # Delete the created GCS bucket\n",
        "    ! gsutil rm -r $BUCKET_URI"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "get_started_with_bqml_training.ipynb",
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}