language/use-cases/applying-llms-to-data/bigquery_ml_llm.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "RWwfxTOXHI56"
   },
   "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": "iZXx5I1XHPkv"
   },
   "source": [
    "# Using Vertex AI LLMs with data in BigQuery\n",
    "\n",
    "<table align=\"left\">\n",
    "  <td style=\"text-align: center\">\n",
    "    <a href=\"https://colab.research.google.com/github/GoogleCloudPlatform/generative-ai/blob/main/language/use-cases/applying-llms-to-data/bigquery_ml_llm.ipynb\">\n",
    "      <img src=\"https://cloud.google.com/ml-engine/images/colab-logo-32px.png\" alt=\"Google Colaboratory logo\"><br> Run in Colab\n",
    "    </a>\n",
    "  </td>\n",
    "  <td style=\"text-align: center\">\n",
    "    <a href=\"https://github.com/GoogleCloudPlatform/generative-ai/blob/main/language/use-cases/applying-llms-to-data/bigquery_ml_llm.ipynb\">\n",
    "      <img src=\"https://cloud.google.com/ml-engine/images/github-logo-32px.png\" alt=\"GitHub logo\"><br> View on GitHub\n",
    "    </a>\n",
    "  </td>\n",
    "  <td style=\"text-align: center\">\n",
    "    <a href=\"https://console.cloud.google.com/vertex-ai/workbench/deploy-notebook?download_url=https://raw.githubusercontent.com/GoogleCloudPlatform/generative-ai/main/language/use-cases/applying-llms-to-data/bigquery_ml_llm.ipynb\">\n",
    "      <img src=\"https://lh3.googleusercontent.com/UiNooY4LUgW_oTvpsNhPpQzsstV5W8F7rYgxgGBD85cWJoLmrOzhVs_ksK_vgx40SHs7jCqkTkCk=e14-rj-sc0xffffff-h130-w32\" alt=\"Vertex AI logo\"><br> Open in Vertex AI Workbench\n",
    "    </a>\n",
    "  </td>\n",
    "</table>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| | |\n",
    "|-|-|\n",
    "|Author(s) | [Rachael Deacon-smith](https://github.com/rachael-ds) |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uvc6DhQ_WXN9"
   },
   "source": [
    "## Overview\n",
    "\n",
    "You might wonder: how do you use LLMs with your data sitting in a data warehouse?\n",
    "\n",
    "The latest integrations between [BigQuery ML](https://cloud.google.com/bigquery/docs/bqml-introduction) (BQML) and [Vertex AI LLMs](https://cloud.google.com/vertex-ai) (PaLM 2 for Text) mean that organizations can now use Vertex AI LLMs on their BigQuery data. Organizations can continue to use BigQuery for data analytics, while also taking advantage of the power of generative AI without the need to move their data.\n",
    "\n",
    "In this tutorial, you will go through examples of how to use Vertex AI LLMs with data stored in BigQuery."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "n_RiJpQX1BuX"
   },
   "source": [
    "### Objectives\n",
    "The objective is to demonstrate some of the many ways LLMs can be applied to your BigQuery data using BigQuery ML.\n",
    "\n",
    "\n",
    "You will execute simple SQL statements that call the Vertex AI API with the `ML.GENERATE_TEXT` function to:\n",
    "\n",
    "- Summarize and classify text\n",
    "- Perform entity recognition\n",
    "- Enrich data\n",
    "- Run sentiment analysis\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "hbGaRkPdW8h2"
   },
   "source": [
    "### Services and Costs\n",
    "This tutorial uses the following Google Cloud data analytics and ML services, they are billable components of Google Cloud:\n",
    "\n",
    "* BigQuery & BigQuery ML <a href=\"https://cloud.google.com/bigquery/pricing\" target=\"_blank\">(pricing)</a>\n",
    "* Vertex AI API <a href=\"https://cloud.google.com/vertex-ai/pricing\" target=\"_blank\">(pricing)</a>\n",
    "\n",
    "Check out the [BQML Pricing page](https://cloud.google.com/bigquery/pricing#bqml) for a breakdown of costs are applied across these services.\n",
    "\n",
    "Use the [Pricing\n",
    "Calculator](https://cloud.google.com/products/calculator)\n",
    "to generate a cost estimate based on your projected usage."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uo6BplVWBKEG"
   },
   "source": [
    "### Installation\n",
    "\n",
    "Install the following packages required to execute this notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "RkSpG3NGBHi6"
   },
   "outputs": [],
   "source": [
    "%pip install --upgrade --user google-cloud-bigquery-connection google-cloud-aiplatform"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "OCYieookBsZ7"
   },
   "source": [
    "#### Restart current runtime\n",
    "\n",
    "To use the newly installed packages in this Jupyter runtime, you must restart the runtime. You can do this by running the cell below, which will restart the current kernel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "gTUc2uR3BsKi"
   },
   "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": {},
   "source": [
    "<div class=\"alert alert-block alert-warning\">\n",
    "<b>⚠️ Before proceeding, please wait for the kernel to finish restarting ⚠️</b>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vyX2PzO2Cttl"
   },
   "source": [
    "#### Set the project and BigQuery region\n",
    "\n",
    "You will need to set the `PROJECT_ID` and the `REGION` variable when creating the BigQuery dataset and Cloud resource connection.\n",
    "\n",
    "For now, only the `us` multi-region and `us-central1` single region are supported for remote model services in BigQuery.\n",
    "\n",
    "**For this notebook, set the region to `US` to ensure access to all public datasets used below.**\n",
    "\n",
    "Learn more about [BigQuery public dataset regions](https://cloud.google.com/bigquery/public-data?gad=1&gclid=CjwKCAjw_aemBhBLEiwAT98FMhtM2q0Il2M4xU_eLwO_mAJpaZuuzBlQCNEkHKDDI-snZyGguxqnaRoCBdYQAvD_BwE&gclsrc=aw.ds#public_dataset_locations)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ZuVcOtXaC0fU"
   },
   "outputs": [],
   "source": [
    "PROJECT_ID = \"[your-project-id]\"  # @param {type:\"string\"}\n",
    "REGION = \"US\"  # @param {type: \"string\"}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "PYfiKTh1E1Sv"
   },
   "source": [
    "#### Setup project variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "wcEmPOYZB8za"
   },
   "source": [
    "These variables will be used throughout this notebook\n",
    "\n",
    "\n",
    "*   **DATASET_ID:** ID of BigQuery dataset\n",
    "*   **CONN_NAME**: Name of a BigQuery connector that will be used to connect to Vertex AI services\n",
    "*   **LLM_MODEL_NAME**: Name given to the LLM created in BigQuery\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "_-q5vGIWdBiC"
   },
   "outputs": [],
   "source": [
    "DATASET_ID = \"bqml_llm\"\n",
    "CONN_NAME = \"bqml_llm_conn\"\n",
    "LLM_MODEL_NAME = \"bqml-vertex-llm\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Ga6HKImjhI8l"
   },
   "source": [
    "### Authenticating your notebook environment\n",
    "If you are using **Colab**, you will need to authenticate yourself first. The next cell will check if you are currently using Colab, and will start the authentication process.\n",
    "\n",
    "If you are using **Vertex AI Workbench**, you will not require additional authentication. For more information, you can check out the setup instructions [here](https://github.com/GoogleCloudPlatform/generative-ai/tree/main/setup-env)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "KiB5cLgc4g2j"
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "\n",
    "# Additional authentication is required for Google Colab\n",
    "if \"google.colab\" in sys.modules:\n",
    "    # Authenticate user to Google Cloud\n",
    "    from google.colab import auth\n",
    "\n",
    "    auth.authenticate_user()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "EA2CDuZDXEs0"
   },
   "source": [
    "### Import libraries\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "wRHOEQ-tYnet"
   },
   "outputs": [],
   "source": [
    "from google.cloud import bigquery\n",
    "from google.cloud import bigquery_connection_v1 as bq_connection\n",
    "import pandas as pd\n",
    "\n",
    "pd.set_option(\"display.max_colwidth\", 1000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "yo2m3VCXnXWV"
   },
   "source": [
    "### Create BigQuery Cloud resource connection\n",
    "\n",
    "You will need to create a [Cloud resource connection](https://cloud.google.com/bigquery/docs/create-cloud-resource-connection) to enable BigQuery to interact with Vertex AI services.\n",
    "\n",
    "You may need to first [enable the BigQuery Connection API](https://console.developers.google.com/apis/api/bigqueryconnection.googleapis.com/overview)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "379c93f2-2ce3-4963-b355-d18ffc77dbb1"
   },
   "outputs": [],
   "source": [
    "client = bq_connection.ConnectionServiceClient()\n",
    "new_conn_parent = f\"projects/{PROJECT_ID}/locations/{REGION}\"\n",
    "exists_conn_parent = f\"projects/{PROJECT_ID}/locations/{REGION}/connections/{CONN_NAME}\"\n",
    "cloud_resource_properties = bq_connection.CloudResourceProperties({})\n",
    "\n",
    "# Try to use an existing connection if one already exists. If not, create a new one.\n",
    "try:\n",
    "    request = client.get_connection(\n",
    "        request=bq_connection.GetConnectionRequest(name=exists_conn_parent)\n",
    "    )\n",
    "    CONN_SERVICE_ACCOUNT = f\"serviceAccount:{request.cloud_resource.service_account_id}\"\n",
    "except Exception:\n",
    "    connection = bq_connection.types.Connection(\n",
    "        {\"friendly_name\": CONN_NAME, \"cloud_resource\": cloud_resource_properties}\n",
    "    )\n",
    "    request = bq_connection.CreateConnectionRequest(\n",
    "        {\n",
    "            \"parent\": new_conn_parent,\n",
    "            \"connection_id\": CONN_NAME,\n",
    "            \"connection\": connection,\n",
    "        }\n",
    "    )\n",
    "    response = client.create_connection(request)\n",
    "    CONN_SERVICE_ACCOUNT = (\n",
    "        f\"serviceAccount:{response.cloud_resource.service_account_id}\"\n",
    "    )\n",
    "print(CONN_SERVICE_ACCOUNT)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "OqrFKwdUnqlW"
   },
   "source": [
    "### Set permissions for Service Account\n",
    "The resource connection service account requires certain project-level permissions which are outlined in the <a href=\"https://cloud.google.com/bigquery/docs/bigquery-ml-remote-model-tutorial#set_up_access\" target=\"_blank\">Vertex AI function documentation</a>.\n",
    "\n",
    "<br>\n",
    "\n",
    "**Note:** If you are using **Vertex AI Workbench**, the service account used by Vertex AI may not have sufficient permissions to add IAM policy bindings. You may see the error:\n",
    "> `ERROR: (gcloud.projects.add-iam-policy-binding) User [12345-compute@developer.gserviceaccount.com] does not have permission to access projects instance [my-project-id:setIamPolicy] (or it may not exist): Policy update access denied.`\n",
    "\n",
    "If you see the above error with Vertex AI Workbench, open a Terminal (File -> New -> Terminal), and then authenticate your Google Cloud user account with: `gcloud auth login` and follow the steps therein. Once authenticated, return to this notebook and run the cell below. Alternatively, you can set the IAM roles "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "pyOp6r_SeZ6I"
   },
   "outputs": [],
   "source": [
    "gcloud_serviceusage = f\"\"\"\n",
    "gcloud projects add-iam-policy-binding {PROJECT_ID} --condition=None --no-user-output-enabled --member=\"{CONN_SERVICE_ACCOUNT}\" --role=\"roles/serviceusage.serviceUsageConsumer\"\n",
    "\"\"\"\n",
    "\n",
    "gcloud_bigquery = f\"\"\"\n",
    "gcloud projects add-iam-policy-binding {PROJECT_ID} --condition=None --no-user-output-enabled --member=\"{CONN_SERVICE_ACCOUNT}\" --role=\"roles/bigquery.connectionUser\"\n",
    "\"\"\"\n",
    "\n",
    "gcloud_aiplatform = f\"\"\"\n",
    "gcloud projects add-iam-policy-binding {PROJECT_ID} --condition=None --no-user-output-enabled --member=\"{CONN_SERVICE_ACCOUNT}\" --role=\"roles/aiplatform.user\"\n",
    "\"\"\"\n",
    "\n",
    "print(gcloud_serviceusage)\n",
    "!$gcloud_serviceusage #execute gcloud script\n",
    "\n",
    "print(gcloud_bigquery)\n",
    "!$gcloud_bigquery #execute gcloud script\n",
    "\n",
    "print(gcloud_aiplatform)\n",
    "!$gcloud_aiplatform #execute gcloud script"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can confirm that the three IAM roles have been set by running the cell below:\n",
    "- roles/aiplatform.user\n",
    "- roles/bigquery.connectionUser\n",
    "- roles/serviceusage.serviceUsageConsumer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!gcloud projects get-iam-policy $PROJECT_ID  \\\n",
    "--flatten=\"bindings[].members\" \\\n",
    "--format=\"table(bindings.role)\" \\\n",
    "--filter=\"bindings.members:$CONN_SERVICE_ACCOUNT\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "7aJ_rzE8-nsT"
   },
   "source": [
    "## Prepare BigQuery Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "G6NYBJlSciEN"
   },
   "source": [
    "### Create a BigQuery Dataset\n",
    "You will need a BigQuery dataset to store your ML model and tables. This dataset must be created in the same region used by the BigQuery Cloud resource connection.\n",
    "\n",
    "Run the following to create a dataset within your project:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "NYSwQkixctO9"
   },
   "outputs": [],
   "source": [
    "client = bigquery.Client(project=PROJECT_ID)\n",
    "\n",
    "dataset_id = f\"\"\"{PROJECT_ID}.{DATASET_ID}\"\"\"\n",
    "dataset = bigquery.Dataset(dataset_id)\n",
    "dataset.location = REGION\n",
    "\n",
    "dataset = client.create_dataset(dataset, exists_ok=True)\n",
    "\n",
    "print(f\"Dataset {dataset.dataset_id} created.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "J8lKov-Y-wkz"
   },
   "source": [
    "Create a wrapper to use the BigQuery client to run queries and return the result:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Y8F-vcik4JVK"
   },
   "outputs": [],
   "source": [
    "# Wrapper to use BigQuery client to run query and return result\n",
    "\n",
    "\n",
    "def run_bq_query(sql: str):\n",
    "    \"\"\"\n",
    "    Input: SQL query, as a string, to execute in BigQuery\n",
    "    Returns the query results or error, if any\n",
    "    \"\"\"\n",
    "    try:\n",
    "        query_job = client.query(sql)\n",
    "        result = query_job.result()\n",
    "        print(f\"JOB ID: {query_job.job_id} STATUS: {query_job.state}\")\n",
    "        return result\n",
    "\n",
    "    except Exception as e:\n",
    "        raise Exception(str(e))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "gJE6PHD_6vq9"
   },
   "source": [
    "## Using LLMs with BigQuery ML"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "xf-f6XKC618_"
   },
   "source": [
    "To use LLMs with BigQuery ML you will first need to configure the LLM and then execute the `ML.GENERATE_TEXT` function with a prompt. This can all be done in SQL."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "dTuIqYeEHF8Z"
   },
   "source": [
    "### Configure Vertex AI Model\n",
    "\n",
    "You can configure a Vertex AI remote model in BigQuery using the CREATE MODEL statement:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "f9OUDRjzWsWg"
   },
   "outputs": [],
   "source": [
    "sql = f\"\"\"\n",
    "      CREATE OR REPLACE MODEL\n",
    "        `{PROJECT_ID}.{DATASET_ID}.{LLM_MODEL_NAME}`\n",
    "        REMOTE WITH CONNECTION\n",
    "          `{PROJECT_ID}.{REGION}.{CONN_NAME}`\n",
    "          OPTIONS ( remote_service_type = 'CLOUD_AI_LARGE_LANGUAGE_MODEL_V1');\n",
    "      \"\"\"\n",
    "result = run_bq_query(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "LyK-KKouqM7W"
   },
   "source": [
    "### Using the LLM\n",
    "You can use the LLMs in BQML by executing the `ML.GENERATE_TEXT` function against free text or data stored in BigQuery.\n",
    "\n",
    "[The BQML documentation](https://cloud.google.com/bigquery/docs/generate-text#generate_text) gives further details on the parameters used: `temperature, max_output_tokens, top_p and top_k.`\n",
    "\n",
    "*Note: The table column with the input text must have the alias 'prompt'*\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "BWijPOQ3wOKD"
   },
   "outputs": [],
   "source": [
    "PROMPT = \"Describe a cat in one paragraph\"\n",
    "\n",
    "sql = f\"\"\"\n",
    "          SELECT\n",
    "            *\n",
    "          FROM\n",
    "            ML.GENERATE_TEXT(\n",
    "              MODEL `{PROJECT_ID}.{DATASET_ID}.{LLM_MODEL_NAME}`,\n",
    "              (\n",
    "              SELECT\n",
    "                '{PROMPT}' AS prompt\n",
    "              ),\n",
    "              STRUCT\n",
    "              (\n",
    "                1 AS temperature,\n",
    "                1024 AS max_output_tokens,\n",
    "                0.8 AS top_p,\n",
    "                40 AS top_k,\n",
    "                TRUE AS flatten_json_output\n",
    "              ));\n",
    "        \"\"\"\n",
    "result = run_bq_query(sql)\n",
    "result.to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "oBDGjTLQHeza"
   },
   "source": [
    "In this case, the LLM responded to the simple prompt to describe a cat in one paragraph.\n",
    "\n",
    "The LLM response is returned as a table of results in BigQuery. The table includes JSON that can be parsed to extract the content result.\n",
    "\n",
    "Setting the `flatten_json_output` parameter to TRUE will return a flattened JSON as a string: `ml_generate_text_llm_result`.\n",
    "\n",
    "For the rest of the examples, you can just display the prompt and `ml_generate_text_llm_result` for simplicity."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "l0dU6XNTUwQQ"
   },
   "source": [
    "## Example Use Cases\n",
    "\n",
    "The following examples explore using the BQML LLM for content creation, text summarization, classification, entity recognition, data enrichment and sentiment analysis.\n",
    "\n",
    "When writing your own prompts, we recommend you first review these [Prompt Design best practices](https://github.com/GoogleCloudPlatform/generative-ai/blob/main/language/prompts/intro_prompt_design.ipynb)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "jf00IgH0jER_"
   },
   "source": [
    "#### Text Classification\n",
    "\n",
    "This example categorizes news articles into one of the following categories: tech, sport, business, politics, or entertainment. The articles are stored in the BigQuery BBC News public dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "m61p1jp8Fesm"
   },
   "outputs": [],
   "source": [
    "PROMPT = \"Please categorize this BBC news article into either tech, sport, business, politics, or entertainment and return the category. Here is an example. News article: Intel has unveiled research that could mean data is soon being moved around chips at the speed of light., Category: Tech \"\n",
    "\n",
    "sql = f\"\"\"\n",
    "          SELECT\n",
    "            body AS article_body,\n",
    "            CONCAT('{PROMPT}','News article: ', 'article_body', ', Category:') as prompt_template,\n",
    "            ml_generate_text_llm_result as llm_result\n",
    "          FROM\n",
    "            ML.GENERATE_TEXT(\n",
    "              MODEL `{PROJECT_ID}.{DATASET_ID}.{LLM_MODEL_NAME}`,\n",
    "              (\n",
    "              SELECT\n",
    "                CONCAT('{PROMPT}','News article: ', body, ', Category:') AS prompt,\n",
    "                body\n",
    "              FROM\n",
    "                `bigquery-public-data.bbc_news.fulltext`\n",
    "              LIMIT\n",
    "                5),\n",
    "              STRUCT(1 AS temperature, 1024 AS max_output_tokens, 0.8 AS top_p, 40 AS top_k, TRUE AS flatten_json_output));\n",
    "        \"\"\"\n",
    "result = run_bq_query(sql)\n",
    "result.to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "SCreOP1si33Y"
   },
   "source": [
    "#### Text Summarization\n",
    "\n",
    "This example rewrites news articles stored in the BigQuery BBC News public dataset into text that can be understood by a 12 year old."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "tzg7El87fzWe"
   },
   "outputs": [],
   "source": [
    "PROMPT = \"Please rewrite this article to enable easier understanding for a 12 year old. Article: \"\n",
    "\n",
    "sql = f\"\"\"\n",
    "          SELECT\n",
    "            body as article_before,\n",
    "            CONCAT('{PROMPT}', 'article_before') as prompt_template,\n",
    "            ml_generate_text_llm_result as llm_result\n",
    "          FROM\n",
    "            ML.GENERATE_TEXT(\n",
    "              MODEL `{PROJECT_ID}.{DATASET_ID}.{LLM_MODEL_NAME}`,\n",
    "              (\n",
    "              SELECT\n",
    "                CONCAT('{PROMPT}', body) AS prompt,\n",
    "                body\n",
    "              FROM  `bigquery-public-data.bbc_news.fulltext`\n",
    "              LIMIT 5\n",
    "              ),\n",
    "              STRUCT(1 AS temperature, 1024 AS max_output_tokens, 0.8 AS top_p, 40 AS top_k, TRUE AS flatten_json_output));\n",
    "        \"\"\"\n",
    "result = run_bq_query(sql)\n",
    "result.to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "y3awToblHGzH"
   },
   "source": [
    "This example summarizes lengthy news articles stored in the BigQuery BBC News public dataset into 25 words or less"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "lNjtW8j3moUX"
   },
   "outputs": [],
   "source": [
    "PROMPT = \"Please summarize this BBC news article into 25 words or less: \"\n",
    "\n",
    "sql = f\"\"\"\n",
    "          SELECT\n",
    "            body as article_before,\n",
    "            ARRAY_LENGTH(SPLIT(body, ' ')) AS word_count_before,\n",
    "            CONCAT('{PROMPT}') as prompt_template,\n",
    "            ml_generate_text_llm_result as article_after,\n",
    "            ARRAY_LENGTH(SPLIT(ml_generate_text_llm_result, ' ')) AS word_count_after,\n",
    "            1-ARRAY_LENGTH(SPLIT(ml_generate_text_llm_result, ' '))/ARRAY_LENGTH(SPLIT(body, ' ')) as percent_reduction_words\n",
    "          FROM\n",
    "            ML.GENERATE_TEXT(\n",
    "              MODEL `{PROJECT_ID}.{DATASET_ID}.{LLM_MODEL_NAME}`,\n",
    "              (\n",
    "              SELECT\n",
    "                CONCAT('{PROMPT}', body) AS prompt,\n",
    "                body\n",
    "              FROM\n",
    "                `bigquery-public-data.bbc_news.fulltext`\n",
    "              LIMIT\n",
    "                5),\n",
    "              STRUCT(1 AS temperature, 1024 AS max_output_tokens, 0.8 AS top_p, 40 AS top_k, TRUE AS flatten_json_output));\n",
    "        \"\"\"\n",
    "result = run_bq_query(sql)\n",
    "result.to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "jVDeKe3YHk1J"
   },
   "source": [
    "The word count of the results may not always be within the 25 words requested and so further [prompt engineering](https://cloud.google.com/vertex-ai/docs/generative-ai/learn/introduction-prompt-design) may be required."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "BfMmRrv4jMjp"
   },
   "source": [
    "#### Entity Recognition\n",
    "\n",
    "This example extracts the sentences from news articles that contain a statistic. The articles are stored in the BigQuery BBC News public dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "k-8BuOYvERHh"
   },
   "outputs": [],
   "source": [
    "PROMPT = \"Please return a bullet-point list of all sentences in this article that cite a statistic: \"\n",
    "\n",
    "sql = f\"\"\"\n",
    "          SELECT\n",
    "            body AS article_body,\n",
    "            CONCAT('{PROMPT}', 'article_body') AS prompt,\n",
    "            ml_generate_text_llm_result AS llm_result\n",
    "          FROM\n",
    "            ML.GENERATE_TEXT(\n",
    "              MODEL `{PROJECT_ID}.{DATASET_ID}.{LLM_MODEL_NAME}`,\n",
    "              (\n",
    "              SELECT\n",
    "                CONCAT('{PROMPT}', body) AS prompt,\n",
    "                body\n",
    "              FROM\n",
    "                `bigquery-public-data.bbc_news.fulltext`\n",
    "              LIMIT\n",
    "                5),\n",
    "              STRUCT(1 AS temperature, 1024 AS max_output_tokens, 0.8 AS top_p, 40 AS top_k, TRUE AS flatten_json_output));\n",
    "        \"\"\"\n",
    "result = run_bq_query(sql)\n",
    "result.to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rSP17UbCYDR_"
   },
   "source": [
    "This example extracts the brand names from product descriptions stored in the BigQuery thelook_ecommerce public dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "i_Go_5XMSei7"
   },
   "outputs": [],
   "source": [
    "PROMPT = \"Please return the brand name listed in this product description. Here is an example. Product: TYR Sport Mens Solid Durafast Jammer Swim Suit, Brand: TYR ; Product: \"\n",
    "\n",
    "sql = f\"\"\"\n",
    "          SELECT\n",
    "            name AS product_description,\n",
    "            CONCAT('{PROMPT}', 'product_description,',' Brand: ') as prompt_template,\n",
    "            ml_generate_text_llm_result as llm_result\n",
    "          FROM\n",
    "            ML.GENERATE_TEXT(\n",
    "              MODEL `{PROJECT_ID}.{DATASET_ID}.{LLM_MODEL_NAME}`,\n",
    "              (\n",
    "              SELECT\n",
    "                CONCAT('{PROMPT}', name,' Brand: ') AS prompt,\n",
    "                name\n",
    "              FROM\n",
    "                `bigquery-public-data.thelook_ecommerce.products`\n",
    "              LIMIT\n",
    "                5),\n",
    "              STRUCT(1 AS temperature, 1024 AS max_output_tokens, 0.8 AS top_p, 40 AS top_k, TRUE AS flatten_json_output));\n",
    "        \"\"\"\n",
    "result = run_bq_query(sql)\n",
    "result.to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5JzOqTnIizNw"
   },
   "source": [
    "#### Sentiment Analysis\n",
    "\n",
    "This example runs sentiment analysis on movie reviews stored in the BigQuery IMDB public dataset to determine whether the movie review is positive, negative or neutral."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "4ALWl_i4oF-X"
   },
   "outputs": [],
   "source": [
    "PROMPT = \"Please categorize this movie review as either Positive, Negative or Neutral. Here is an example. Review: I dislike this movie, Sentiment: Negative\"\n",
    "\n",
    "sql = f\"\"\"\n",
    "          SELECT\n",
    "            review,\n",
    "            CONCAT('{PROMPT}',' Review: review Sentiment:') as prompt_template,\n",
    "            ml_generate_text_llm_result as llm_result\n",
    "\n",
    "          FROM\n",
    "            ML.GENERATE_TEXT(\n",
    "              MODEL `{PROJECT_ID}.{DATASET_ID}.{LLM_MODEL_NAME}`,\n",
    "              (\n",
    "              SELECT\n",
    "                CONCAT('{PROMPT}',' Review: ', review, ', Sentiment:') AS prompt,\n",
    "                review\n",
    "              FROM\n",
    "                `bigquery-public-data.imdb.reviews`\n",
    "              WHERE\n",
    "                UPPER(title) = 'TROY'\n",
    "              LIMIT\n",
    "                10 ),\n",
    "              STRUCT(0.2 AS temperature,\n",
    "                50 AS max_output_tokens,\n",
    "                0.8 AS top_p,\n",
    "                40 AS top_k, TRUE AS flatten_json_output))\n",
    "          \"\"\"\n",
    "result = run_bq_query(sql)\n",
    "result.to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "YmeeDBBrOJ3r"
   },
   "source": [
    "#### Content Creation\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "1ofIZ7ozZnSu"
   },
   "source": [
    "This examples creates a marketing campaign email based on recipient demographic and spending data. Commerce data is taken from BigQuery's [thelook eCommerce public dataset](https://console.cloud.google.com/marketplace/product/bigquery-public-data/thelook-ecommerce)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "iS6MFUvZ09cN"
   },
   "source": [
    "First, you will need to join the order_items, products, and users tables of the dataset in order to get a table that includes the information for the email, including the item purchased, description of that item, and demographic data about the purchaser."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "eMjm_YCrsKmC"
   },
   "outputs": [],
   "source": [
    "sql = f\"\"\"\n",
    "      CREATE OR REPLACE TABLE\n",
    "        `{PROJECT_ID}.{DATASET_ID}.purchases` AS\n",
    "      SELECT\n",
    "        u.id,\n",
    "        u.first_name,\n",
    "        u.email,\n",
    "        u.postal_code,\n",
    "        u.country,\n",
    "        o.order_id,\n",
    "        o.created_at,\n",
    "        p.category,\n",
    "        p.name\n",
    "      FROM\n",
    "        `bigquery-public-data.thelook_ecommerce.users` u\n",
    "      JOIN (\n",
    "        SELECT\n",
    "          user_id,\n",
    "          order_id,\n",
    "          created_at,\n",
    "          product_id,\n",
    "          ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn\n",
    "        FROM\n",
    "          `bigquery-public-data.thelook_ecommerce.order_items`\n",
    "      ) o\n",
    "      ON\n",
    "        u.id = o.user_id\n",
    "      JOIN\n",
    "        `bigquery-public-data.thelook_ecommerce.products` p\n",
    "      ON\n",
    "        o.product_id = p.id\n",
    "      WHERE\n",
    "        o.rn = 1 AND p.category = \"Active\" AND u.country = \"United States\";\n",
    "       \"\"\"\n",
    "\n",
    "result = run_bq_query(sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ypwarbiXtWaM"
   },
   "source": [
    "Querying the new table, you will see a comprehensive set of data for each purchase."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "B49ngc6dsYkR"
   },
   "outputs": [],
   "source": [
    "sql = f\"\"\"\n",
    "        SELECT\n",
    "            *\n",
    "        FROM\n",
    "            `{PROJECT_ID}.{DATASET_ID}.purchases`\n",
    "        LIMIT\n",
    "            10;\n",
    "      \"\"\"\n",
    "result = run_bq_query(sql)\n",
    "result.to_dataframe().head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "N8CX3Hwntd2D"
   },
   "source": [
    "Now you will prepare the prompt for the LLM, incorporating the item's name and the purchaser's name and postal code.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Y2LT134zQEGy"
   },
   "outputs": [],
   "source": [
    "PROMPT_PART1 = \"A user bought a product with this description: \"\n",
    "PROMPT_PART2 = ' Write a follow up marketing email mentioning the high-level product category of their purchase in one word, for example \"We hope you are enjoying your new t-shirt\". '\n",
    "PROMPT_PART3 = \"Encourage the individual to shop with the store again using the coupon code RETURN10 for 10% off their next purchase. \"\n",
    "PROMPT_PART4 = \"Provide two local outdoor activities they could pursue with their new purchase. They live in the zip code \"\n",
    "PROMPT_PART5 = '. Do not mention the brand of the product, just sign off the email with \"-TheLook.\" Address the email to: '\n",
    "\n",
    "sql = f\"\"\"\n",
    "          SELECT\n",
    "            prompt,\n",
    "            ml_generate_text_llm_result\n",
    "          FROM\n",
    "            ML.GENERATE_TEXT(\n",
    "              MODEL `{PROJECT_ID}.{DATASET_ID}.{LLM_MODEL_NAME}`,\n",
    "              (\n",
    "              SELECT\n",
    "                CONCAT('{PROMPT_PART1}',name,'{PROMPT_PART2}','{PROMPT_PART3}','{PROMPT_PART4}',postal_code,'{PROMPT_PART5}',first_name) AS prompt\n",
    "              FROM\n",
    "                `{PROJECT_ID}.{DATASET_ID}.purchases`\n",
    "              LIMIT\n",
    "                5),\n",
    "              STRUCT(1 AS temperature,\n",
    "                1024 AS max_output_tokens,\n",
    "                0.8 AS top_p,\n",
    "                40 AS top_k,\n",
    "                TRUE AS flatten_json_output));\n",
    "        \"\"\"\n",
    "result = run_bq_query(sql)\n",
    "result.to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "DBgDJ4lZW3UE"
   },
   "source": [
    "## Cleaning Up\n",
    "To clean up all Google Cloud resources used in this project, you can <a href=\"https://cloud.google.com/resource-manager/docs/creating-managing-projects#shutting_down_projects\" target=\"_blank\">delete the Google Cloud\n",
    "project</a> you used for the tutorial.\n",
    "\n",
    "Otherwise, you can delete the individual resources you created in this tutorial by uncommenting the below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "9Oa51GiesTqA"
   },
   "outputs": [],
   "source": [
    "# # Delete BigQuery dataset, including the BigQuery ML models you just created, and the BigQuery Connection\n",
    "# ! bq rm -r -f $PROJECT_ID:$DATASET_ID\n",
    "# ! bq rm --connection --project_id=$PROJECT_ID --location=$REGION $CONN_NAME"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "prf0sOh3ER4A"
   },
   "source": [
    "## Wrap Up\n",
    "\n",
    "In this tutorial we have shown how to integrate BQML with Vertex AI LLMs, and given examples of how the new `ML.GENERATE_TEXT` function can be applied directly to data stored in BigQuery.\n",
    "\n",
    "Check out our [BigQuery ML LLM page](https://cloud.google.com/bigquery/docs/inference-overview#generative_ai) to learn more about remote models and generative AI in BigQuery."
   ]
  }
 ],
 "metadata": {
  "colab": {
   "name": "bqml_llm_examples.ipynb",
   "toc_visible": true
  },
  "environment": {
   "kernel": "python3",
   "name": "tf2-gpu.2-11.m108",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/tf2-gpu.2-11: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.10.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}