{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "cMQCs0oQf5Jo"
      },
      "outputs": [],
      "source": [
        "# Copyright 2025 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": "iR0jdheRGG89"
      },
      "source": [
        "# Semantic Analysis in BigQuery with AI Functions"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Lt2rldOmcJg9"
      },
      "source": [
        "<table align=\"left\">\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://colab.research.google.com/github/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\">\n",
        "      <img width=\"32px\" src=\"https://www.gstatic.com/pantheon/images/bigquery/welcome_page/colab-logo.svg\" alt=\"Google Colaboratory logo\"><br> Open in Colab\n",
        "    </a>\n",
        "  </td>\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://console.cloud.google.com/vertex-ai/colab/import/https:%2F%2Fraw.githubusercontent.com%2FGoogleCloudPlatform%2Fgenerative-ai%2Fmain%2Fgemini%2Fuse-cases%2Fapplying-llms-to-data%2Fbigquery_ai_operators.ipynb\">\n",
        "      <img width=\"32px\" src=\"https://lh3.googleusercontent.com/JmcxdQi-qOpctIvWKgPtrzZdJJK-J3sWE1RsfjZNwshCFgE_9fULcNpuXYTilIR2hjwN\" alt=\"Google Cloud Colab Enterprise logo\"><br> Open in Colab Enterprise\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/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\">\n",
        "      <img src=\"https://www.gstatic.com/images/branding/gcpiconscolors/vertexai/v1/32px.svg\" alt=\"Vertex AI logo\"><br> Open in Vertex AI Workbench\n",
        "    </a>\n",
        "  </td>\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://console.cloud.google.com/bigquery/import?url=https://github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\">\n",
        "      <img src=\"https://www.gstatic.com/images/branding/gcpiconscolors/bigquery/v1/32px.svg\" alt=\"BigQuery Studio logo\"><br> Open in BigQuery Studio\n",
        "    </a>\n",
        "  </td>\n",
        "  <td style=\"text-align: center\">\n",
        "    <a href=\"https://github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\">\n",
        "      <img width=\"32px\" src=\"https://raw.githubusercontent.com/primer/octicons/refs/heads/main/icons/mark-github-24.svg\" alt=\"GitHub logo\"><br> View on GitHub\n",
        "    </a>\n",
        "  </td>\n",
        "</table>\n",
        "\n",
        "<div style=\"clear: both;\"></div>\n",
        "\n",
        "<b>Share to:</b>\n",
        "\n",
        "<a href=\"https://www.linkedin.com/sharing/share-offsite/?url=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/8/81/LinkedIn_icon.svg\" alt=\"LinkedIn logo\">\n",
        "</a>\n",
        "\n",
        "<a href=\"https://bsky.app/intent/compose?text=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/7/7a/Bluesky_Logo.svg\" alt=\"Bluesky logo\">\n",
        "</a>\n",
        "\n",
        "<a href=\"https://twitter.com/intent/tweet?url=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/5/5a/X_icon_2.svg\" alt=\"X logo\">\n",
        "</a>\n",
        "\n",
        "<a href=\"https://reddit.com/submit?url=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://redditinc.com/hubfs/Reddit%20Inc/Brand/Reddit_Logo.png\" alt=\"Reddit logo\">\n",
        "</a>\n",
        "\n",
        "<a href=\"https://www.facebook.com/sharer/sharer.php?u=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\" target=\"_blank\">\n",
        "  <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/5/51/Facebook_f_logo_%282019%29.svg\" alt=\"Facebook logo\">\n",
        "</a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5h3O5b6P8WEx"
      },
      "source": [
        "| Author(s) |\n",
        "| --- |\n",
        "| [Alicia Williams](https://github.com/aliciawilliams) |"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "intro_md_new"
      },
      "source": [
        "## Overview\n",
        "\n",
        "This tutorial will guide you through the powerful AI functions available in BigQuery. You'll get hands-on experience using two collections of functions that integrate directly with powerful Gemini models, allowing you to perform sophisticated AI-driven analysis on your data right within your familiar SQL environment.\n",
        "\n",
        "1.  **managed AI functions ([`AI.SCORE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-score), [`AI.CLASSIFY`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-classify), [`AI.IF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if))**: These are high-level, easy-to-use functions for data analysts who are not necessarily prompt engineers or ML practitioners. BigQuery manages the model and parameter choices, provides prompt engineering, and handles scalability to provide high-quality results for common tasks like semantic filtering, joining, ranking, and classification.\n",
        "\n",
        "2.  **general-purpose AI functions ([`AI.GENERATE_BOOL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool), [`AI.GENERATE_DOUBLE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-double), [`AI.GENERATE_INT`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-int))**: These are inference functions for power-users who want full control over the prompt. They are perfect for row-level AI tasks, especially enriching data in a `SELECT` clause, and returning a specific data type (`BOOL`, `DOUBLE`, or `INT`)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SwQLISjqGTHd"
      },
      "source": [
        "### Objectives\n",
        "\n",
        "We'll cover how to:\n",
        "\n",
        "* **Perform semantic ranking** with the managed `AI.SCORE()` function.\n",
        "* **Perform classification** with the managed `AI.CLASSIFY()` function.\n",
        "* **Perform semantic filtering** with the managed `AI.IF()` function.\n",
        "* **Perform semantic joins** with the managed `AI.IF()` function.\n",
        "* **Perform powerful, row-level analysis** using general-purpose inference functions like `AI.GENERATE_BOOL`, `AI.GENERATE_DOUBLE`, and `AI.GENERATE_INT`."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "costs_md"
      },
      "source": [
        "### Services and Costs\n",
        "\n",
        "This tutorial uses the following billable components of Google Cloud:\n",
        "\n",
        "* **BigQuery**: [Pricing](https://cloud.google.com/bigquery/pricing)\n",
        "\n",
        "* **BigQuery ML**: [Pricing](https://cloud.google.com/bigquery/pricing#bqml)\n",
        "\n",
        "* **Vertex AI**: [Pricing](https://cloud.google.com/vertex-ai/generative-ai/pricing)\n",
        "\n",
        "You can 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": "nkoCFoFVSPii"
      },
      "source": [
        "---\n",
        "\n",
        "## Before you begin"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "setup_md_1"
      },
      "source": [
        "### Setting up your Google Cloud project\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 free 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, BigQuery Connection, and Vertex AI APIs](https://console.cloud.google.com/flows/enableapi?apiid=bigquery.googleapis.com,bigqueryconnection.googleapis.com,aiplatform.googleapis.com).\n",
        "\n",
        "4. If you are running this notebook locally, you need to install the [Cloud SDK](https://cloud.google.com/sdk)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YQ3g-h7uTaSf"
      },
      "source": [
        "### Setting your project ID"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "set_project_id"
      },
      "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": "auth_md"
      },
      "source": [
        "### Authenticating to your Google Cloud account\n",
        "\n",
        "Depending on your Jupyter environment, you may have to manually authenticate. Follow the relevant instructions below."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "V6NjZRCXU5Ro"
      },
      "source": [
        "**1. Colab Enterprise or BigQuery Studio Notebooks**\n",
        "* Do nothing as you are already authenticated."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "l0dV1hvAU1ed"
      },
      "source": [
        "**2. Colab, uncomment and run:**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "auth_code"
      },
      "outputs": [],
      "source": [
        "# from google.colab import auth\n",
        "#\n",
        "# auth.authenticate_user()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "conn_md"
      },
      "source": [
        "### Creating a 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."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "conn_code"
      },
      "outputs": [],
      "source": [
        "!bq mk --connection --location=us \\\n",
        "    --connection_type=CLOUD_RESOURCE test_connection"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "perms_md"
      },
      "source": [
        "### Setting permissions for the service account\n",
        "\n",
        "The resource connection service account requires certain project-level permissions to interact with Vertex AI."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6OqpZNY953xR"
      },
      "outputs": [],
      "source": [
        "SERVICE_ACCT = !bq show --format=prettyjson --connection us.test_connection | grep \"serviceAccountId\" | cut -d '\"' -f 4\n",
        "SERVICE_ACCT_EMAIL = SERVICE_ACCT[-1]\n",
        "print(SERVICE_ACCT_EMAIL)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "perms_code"
      },
      "outputs": [],
      "source": [
        "import time\n",
        "\n",
        "!gcloud projects add-iam-policy-binding --format=none $PROJECT_ID --member=serviceAccount:$SERVICE_ACCT_EMAIL --role='roles/bigquery.connectionUser'\n",
        "!gcloud projects add-iam-policy-binding --format=none $PROJECT_ID --member=serviceAccount:$SERVICE_ACCT_EMAIL --role='roles/aiplatform.user'\n",
        "\n",
        "## wait 60 seconds, give IAM updates time to propagate, otherwise, following cells will fail\n",
        "time.sleep(60)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3JiX7KA5uVl3"
      },
      "source": [
        "###Creating a helper function to view images\n",
        "This is a helpful utility function that you'll use later in the tutorial. It takes the results of your search query (stored in a pandas DataFrame) and displays the corresponding product images in a nice grid format, making it easy to see the results of a query that contain images."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DVncmYt5ufC9"
      },
      "outputs": [],
      "source": [
        "## Code created with Gemini\n",
        "\n",
        "import html\n",
        "\n",
        "import pandas as pd\n",
        "from IPython.display import HTML, display\n",
        "\n",
        "\n",
        "def display_image_grid(\n",
        "    df: pd.DataFrame, url_column: str = \"signed_url\", image_width: int = 220\n",
        "):\n",
        "    \"\"\"Renders a grid of cards, each with an image and its corresponding\n",
        "    DataFrame row details.\n",
        "\n",
        "    Args:\n",
        "        df (pd.DataFrame): DataFrame containing image URLs and other metadata.\n",
        "        url_column (str): The name of the column that contains the image URLs.\n",
        "        image_width (int): The width of each card in pixels.\n",
        "    \"\"\"\n",
        "    # --- Validate Input ---\n",
        "    if not isinstance(df, pd.DataFrame) or df.empty:\n",
        "        print(\"Input is not a valid or non-empty DataFrame. Nothing to display.\")\n",
        "        return\n",
        "    if url_column not in df.columns:\n",
        "        print(f\"Error: Column '{url_column}' not found in the DataFrame.\")\n",
        "        return\n",
        "\n",
        "    # Get a list of all columns that are NOT the url_column\n",
        "    detail_columns = [col for col in df.columns if col != url_column]\n",
        "\n",
        "    # --- Build HTML for each card ---\n",
        "    card_html_list = []\n",
        "    for index, row in df.iterrows():\n",
        "        # --- MODIFICATION IS HERE ---\n",
        "        # Strip any leading/trailing single or double quotes from the URL\n",
        "        url = str(row[url_column]).strip(\"'\\\"\")\n",
        "\n",
        "        # Create an HTML block for the other details\n",
        "        details_html = \"\"\n",
        "        for col in detail_columns:\n",
        "            # Escape data to prevent HTML rendering issues\n",
        "            value = html.escape(str(row[col]))\n",
        "            col_name = html.escape(col.replace(\"_\", \" \").title())\n",
        "            details_html += f'<p style=\"margin: 4px 0; font-size: 14px;\"><strong>{col_name}:</strong> {value}</p>'\n",
        "\n",
        "        # Assemble the full card\n",
        "        card_html_list.append(f\"\"\"\n",
        "            <div style=\"width: {image_width}px; margin: 10px; border-radius: 8px;\n",
        "                        box-shadow: 0 4px 8px 0 rgba(0,0,0,0.2); font-family: sans-serif;\n",
        "                        text-align: left; background-color: white;\">\n",
        "                <img src=\"{url}\"\n",
        "                     alt=\"Product Image\"\n",
        "                     style=\"width: 100%; height: {image_width - 40}px; object-fit: cover;\n",
        "                            border-top-left-radius: 8px; border-top-right-radius: 8px;\"\n",
        "                     onerror=\"this.onerror=null;this.src='https://placehold.co/{image_width}x{image_width - 40}/eee/ccc?text=Image+Expired';\"\n",
        "                >\n",
        "                <div style=\"padding: 10px 15px;\">\n",
        "                    {details_html}\n",
        "                </div>\n",
        "            </div>\n",
        "        \"\"\")\n",
        "\n",
        "    # --- Display the final grid ---\n",
        "    all_cards_string = \"\".join(card_html_list)\n",
        "    final_html = f\"\"\"\n",
        "        <p><strong>Displaying {len(card_html_list)} result(s):</strong></p>\n",
        "        <div style=\"display: flex; flex-wrap: wrap; justify-content: flex-start;\">\n",
        "            {all_cards_string}\n",
        "        </div>\n",
        "    \"\"\"\n",
        "    display(HTML(final_html))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SMU-CoJcr8ls"
      },
      "source": [
        "### Loading and viewing the sample data\n",
        "\n",
        "This tutorial will use data from a fictional e-commerce pet supply company called **Cymbal Pets**."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0nzBL109OdVw"
      },
      "source": [
        "The following query performs two main actions:\n",
        "* creates a BigQuery dataset called `cymbal_pets`, and\n",
        "* creates two tables using data stored in Google Cloud Storage (GCS); one standard table containing product information (`products`) and one object table containing product images (`product_images`)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "1Ozb_b5XsHh_"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "CREATE SCHEMA IF NOT EXISTS cymbal_pets;\n",
        "\n",
        "-- Load a non-object table\n",
        "LOAD DATA OVERWRITE cymbal_pets.products\n",
        "FROM\n",
        "  FILES(\n",
        "    format = 'avro',\n",
        "    uris = [\n",
        "      'gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/tables/products/products_*.avro']);\n",
        "\n",
        "\n",
        "-- Create an object table\n",
        "CREATE OR REPLACE EXTERNAL TABLE cymbal_pets.product_images\n",
        "  WITH CONNECTION `us.test_connection`\n",
        "  OPTIONS (\n",
        "    object_metadata = 'SIMPLE',\n",
        "    uris = ['gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*.png']);"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BNVKfg6BN7yg"
      },
      "source": [
        "Let's take a peek at a few rows of each table to get familiar with the data."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9IXviWAgPe-j"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "SELECT *\n",
        "FROM cymbal_pets.products\n",
        "LIMIT 2"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "5usXj1u2Phuy"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "SELECT *\n",
        "FROM cymbal_pets.product_images\n",
        "LIMIT 2"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aiqe_md_intro"
      },
      "source": [
        "---\n",
        "\n",
        "##BigQuery managed AI functions\n",
        "\n",
        "The [**BigQuery managed AI functions**](https://docs.cloud.google.com/bigquery/docs/generative-ai-overview#managed_ai_functions) ([`AI.SCORE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-score), [`AI.CLASSIFY`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-classify), and [`AI.IF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if)) extend traditional SQL with natural language conditions.\n",
        "\n",
        "These functions are designed to be accessible to all users. They enhance quality by applying **prompt rewrite strategies** automatically, allowing you to write simple prompts while achieving accurate results.\n",
        "\n",
        "We'll run through a few examples of using these functions for analysis with the **`cymbal_pets`** dataset."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aiqe_score_md"
      },
      "source": [
        "###Using `AI.SCORE`: Ranking by \"giftability\"\n",
        "\n",
        "[`AI.SCORE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-score) is a function that accepts text input and uses a Vertex AI Gemini model to rate those inputs based on a scoring system that you describe as part of the prompt. If you do not provide a scoring system, the function automatically rewrites your prompt to generate a scoring rubric.\n",
        "\n",
        "It is perfect for ranking items based on semantic criteria that are not explicitly in the data. Let's apply it to a potential marketing use case: determining how suitable a product is as a gift for a pet owner."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "aiqe_score_code"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "SELECT\n",
        "  product_name,\n",
        "  description,\n",
        "  AI.SCORE((\n",
        "    'How \"giftable\" is this product for a pet owner? ', description,\n",
        "    'Use a scale from 1-10.'),\n",
        "  connection_id => 'us.test_connection') AS giftability_score\n",
        "FROM\n",
        "  `cymbal_pets.products`\n",
        "ORDER BY\n",
        "  giftability_score DESC"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aiqe_classify_md"
      },
      "source": [
        "### Using `AI.CLASSIFY`: Classifying by intended animal\n",
        "\n",
        "[`AI.CLASSIFY`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-classify) uses a Vertex AI Gemini model to classify inputs into categories that you provide. `AI.CLASSIFY` accepts multimodal input (text, image, video, etc), and can be used for tasks such as classifying reviews by sentiment, classifying support tickets or emails by topic, or\n",
        "classifying an image by its style or contents.\n",
        "\n",
        "Let's use it to assign each toy product to an animal type using the product name and description. We'll define a set of target categories and ask the model to assign each product to the most appropriate one. Notice we include a fallback \"All Pets\" category."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "aiqe_classify_code"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "SELECT\n",
        "  product_name,\n",
        "  AI.CLASSIFY(\n",
        "    ('What animal is this product for?',product_name,' ',description),\n",
        "    categories => [\"Dog\", \"Cat\", \"Bird\", \"Fish\", \"Small Animal\", \"All Pets\"],\n",
        "    connection_id => 'us.test_connection') AS animal_type\n",
        "FROM\n",
        "  `cymbal_pets.products`\n",
        "WHERE category = \"Toys\"\n",
        "LIMIT 20"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aiqe_if_md"
      },
      "source": [
        "### Using `AI.IF`: Filtering product images\n",
        "\n",
        "[`AI.IF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if) uses a Vertex AI Gemini model to evaluate a condition described in natural language and returns a `BOOL`. Similar to `AI.CLASSIFY`, it can also accept multimodal input. If you haven't yet worked with multimodal data in BigQuery, you can learn more in the [Analyzing Multimodal Data in BigQuery](https://github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/multimodal-analysis-bigquery/analyze_multimodal_data_bigquery.ipynb) notebook.\n",
        "\n",
        "Let's use `AI.IF` to perform a visual filtering task on our `product_images` table. We'll use it to find all product images that contain a ball by using `AI.IF` within the `WHERE` clause."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "F3EQDlngy3Fb"
      },
      "outputs": [],
      "source": [
        "%%bigquery images_df --project {PROJECT_ID}\n",
        "\n",
        "SELECT\n",
        "  STRING(OBJ.GET_ACCESS_URL(ref, 'r').access_urls.read_url) AS signed_url,\n",
        "  uri,\n",
        "  metadata\n",
        "FROM\n",
        "  `cymbal_pets.product_images`\n",
        "WHERE\n",
        "  AI.IF((\n",
        "    'Does this product image contain a ball? ',ref),\n",
        "    connection_id => 'us.test_connection');"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sesnAbr-vQMP"
      },
      "source": [
        "We used [`OBJ.GET_ACCESS_URL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/objectref_functions#objget_access_url) in the `SELECT` to produce a read-only URL containing the product image.\n",
        "\n",
        "The results of the previous query are now stored in a pandas DataFrame called `images_df` (a parameter added to the [`%%bigquery magic` utility](https://googleapis.dev/python/bigquery-magics/latest/) in the prior cell).\n",
        "\n",
        "Let's take a look at the results."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DJyw9P67vTDg"
      },
      "outputs": [],
      "source": [
        "images_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Jmt9B-Y3vUMa"
      },
      "source": [
        "Now, let's view these images using the helper function created in the **Before you begin** section of this notebook."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "JGe1xdhcvaWx"
      },
      "outputs": [],
      "source": [
        "display_image_grid(images_df)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FQyhHKaMdTHD"
      },
      "source": [
        "### Using `AI.IF`: Performing a semantic join of product images with product table\n",
        "\n",
        "We can also use [`AI.IF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if) to perform semantic joins. In this next query, we will use it within the `JOIN` clause to join the `products` table (text) with the `product_images` table (image). The join will only succeed if the image *semantically matches* the product description."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "yZMPtc-HG8D4"
      },
      "outputs": [],
      "source": [
        "%%bigquery join_df --project {PROJECT_ID}\n",
        "\n",
        "SELECT\n",
        "  product_name,\n",
        "  description,\n",
        "  brand,\n",
        "  STRING(OBJ.GET_ACCESS_URL(ref, 'r').access_urls.read_url) AS signed_url\n",
        "FROM\n",
        "  `cymbal_pets.products` as products\n",
        "INNER JOIN\n",
        "  `cymbal_pets.product_images` as images\n",
        "ON\n",
        "  AI.IF((\n",
        "      'You will be provided an image of a pet product. ',\n",
        "      'Determine if the image is of the following pet toy: ',\n",
        "      products.product_name,\n",
        "      products.description,\n",
        "      images.ref\n",
        "      ),\n",
        "    connection_id => 'us.test_connection')\n",
        "WHERE\n",
        "  products.category = \"Toys\" AND\n",
        "  products.brand = \"Fluffy Buns\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kLA2ol2N4OBp"
      },
      "source": [
        "Let's take a look at the resulting DataFrame."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ZEBuaLcowCzH"
      },
      "outputs": [],
      "source": [
        "join_df"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AJAHROEJ4m2B"
      },
      "source": [
        "Now, let's view the results including images using the helper function."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "slbCltSr4vF0"
      },
      "outputs": [],
      "source": [
        "display_image_grid(join_df)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "scalar_md_new"
      },
      "source": [
        "---\n",
        "\n",
        "## General-purpose AI functions\n",
        "\n",
        "BigQuery's general-purpose AI functions ([`AI.GENERATE_BOOL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool), [`AI.GENERATE_DOUBLE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-double), and [`AI.GENERATE_INT`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-int)) are another set of capabilities that bring the power of LLMs for AI-driven data extraction and inference tasks directly within your SQL queries.\n",
        "\n",
        "These functions are considered \"general-purpose\" because they provide full control over the prompt and are designed for power-users. Similar to the managed functions, they can be used alongside your standard SQL in `SELECT` and `WHERE` clauses, giving you the power to do complex analysis with natural language."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "generate_bool_md"
      },
      "source": [
        "### Using `AI.GENERATE_BOOL`: Enriching product details\n",
        "\n",
        "The [`AI.GENERATE_BOOL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool) function allows you to analyze any combination of text and unstructured data and returns a `BOOL` value for each row in the table.\n",
        "\n",
        "Let's use `AI.GENERATE_BOOL` for a data enrichment task. We'll find the products in our catalog that require a power source (like electricity or batteries) to operate and add a clear attribute that isn't already available in our `products` table."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "generate_bool_code"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "SELECT\n",
        "  product_name,\n",
        "  category,\n",
        "  description,\n",
        "  AI.GENERATE_BOOL(\n",
        "    ('Does this product require electricity, batteries, ',\n",
        "     'or a power source to operate?',product_name,' ',description),\n",
        "    connection_id => 'us.test_connection',\n",
        "    endpoint => 'gemini-2.5-flash').* EXCEPT(full_response)\n",
        "FROM\n",
        "  `cymbal_pets.products`"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I7QyOnztBpCs"
      },
      "source": [
        "A few items to notice from the query text:\n",
        "* `AI.GENERATE_BOOL` accepts an [`endpoint` argument](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool#arguments). This allows you to specify any [generally available](https://cloud.google.com/vertex-ai/generative-ai/docs/models#generally_available_models) or [preview](https://cloud.google.com/vertex-ai/generative-ai/docs/models#preview_models) Gemini model. If you don't specify an `endpoint` value, BigQuery selects a recent stable version of Gemini to use.\n",
        "* `AI.GENERATE_BOOL` allows you to [specify output options](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool#output):\n",
        "  * `result` - the `BOOL` value containing the model's response to the prompt\n",
        "  * `full_response` - a JSON value containing all fields from the [response](https://cloud.google.com/vertex-ai/docs/reference/rest/v1/GenerateContentResponse)\n",
        "  * `status` - a `STRING` value that contains the API response status for the corresponding row (this value is empty if the operation was successful)\n",
        "\n",
        "In this query, we chose Gemini 2.5 Flash as the `endpoint` and specified the `result` and `status` fields be returned (by using the `*` wildcard after the function and adding an `EXCEPT` to skip returning the `full_response`)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Xm_nv6k5tlYw"
      },
      "source": [
        "---\n",
        "### Comparison: `AI.GENERATE_BOOL` vs. `AI.IF`\n",
        "\n",
        "At first glance, [`AI.GENERATE_BOOL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool) and [`AI.IF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if) seem to do similar jobs. However, they are designed for different users and different tasks.\n",
        "\n",
        "| Feature | `AI.GENERATE_BOOL` | `AI.IF` |\n",
        "| :--- | :--- | :--- |\n",
        "| **Primary Use Case** | **Data enrichment** in the `SELECT` clause. | **Filtering** in the `WHERE` clause and **joining** with the `JOIN ON` clause. |\n",
        "| **Prompting** | **Direct Passthrough**<br>The model sees your exact prompt. You are responsible for all prompt engineering. | **Automatic Prompt Optimization**<br>BigQuery enhances your prompt to be more effective. |\n",
        "| **Model Option** | Adjustable via the `endpoint` parameter. | A model is chosen for you. |\n",
        "| **Error handling** | Records errors in its output. | Returns `NULL` for any rows with errors. |\n",
        "\n",
        "`AI.IF` is generally recommended for semantic filtering because its built-in prompt enhancements mean you don't have to be a prompt engineer to get good results.\n",
        "\n",
        "**Key Takeaway:**\n",
        "* Use `AI.GENERATE_BOOL` for use cases where you want full control, or when using it to augment data in the `SELECT` clause.\n",
        "* Use `AI.IF` for smart, semantic filtering in the `WHERE` clause or for joining with the `JOIN ON` clause.\n",
        "---"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "generate_double_md_corrected"
      },
      "source": [
        "### Using `AI.GENERATE_INT`: Counting mentioned ingredients\n",
        "\n",
        "[`AI.GENERATE_INT`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-int) is similar to `AI.GENERATE_BOOL`, but differs in that it will return an integer value.\n",
        "\n",
        "Let's use `AI.GENERATE_INT` to perform an extraction task, specifically to count how many distinct ingredients or food items it can identify in the food product descriptions. While the descriptions are high-level marketing text and not detailed ingredient lists, this will test the model's ability to infer ingredients from a product's name and general description."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "generate_int_code"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "SELECT\n",
        "  product_name,\n",
        "  description,\n",
        "  AI.GENERATE_INT(\n",
        "    ('Based on the text, how many distinct food ingredients ',\n",
        "     'can you identify? If none are listed, return 0.',\n",
        "     product_name,' ',description),\n",
        "    connection_id => 'us.test_connection',\n",
        "    endpoint => 'gemini-2.5-flash').result AS ingredient_count\n",
        "FROM\n",
        "  `cymbal_pets.products`\n",
        "WHERE\n",
        "  category = 'Food'\n",
        "ORDER BY\n",
        "  ingredient_count DESC"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "generate_int_md_corrected"
      },
      "source": [
        "### Using `AI.GENERATE_DOUBLE`: Estimating shipping weight\n",
        "\n",
        "[`AI.GENERATE_DOUBLE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-double) can be used for similar tasks as `AI.GENERATE_INT`, but differs in that it will return a decimal value. Both functions can be used for *extraction* tasks, as we saw in the last example. In this example, we will see an *inference* task.\n",
        "\n",
        "Since our product data doesn't include shipping weights, let's ask the model to estimate the weight in pounds with decimal precision. It will have to infer this based on the product's name and description (e.g., a \"50 Gallon Aquarium\" is much heavier than a \"Dog Bone\"). This is a great way to generate missing data."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xT9vakyw1PZ2"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "SELECT\n",
        "  product_name,\n",
        "  description,\n",
        "  AI.GENERATE_DOUBLE(\n",
        "    ('Based on this product description, what is a rough ',\n",
        "     'estimated weight of the product for shipping in pounds (lbs)?',\n",
        "     product_name,' ',description),\n",
        "    connection_id => 'us.test_connection',\n",
        "    endpoint => 'gemini-2.5-flash').result AS estimated_shipping_weight\n",
        "FROM\n",
        "  `cymbal_pets.products`"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hyl5LncS0UBh"
      },
      "source": [
        "##Recap\n",
        "\n",
        "In this notebook, you explored the powerful suite of AI functions in BigQuery to perform advanced data analysis using natural language.\n",
        "\n",
        "You learned how to:\n",
        "* **Perform semantic analysis with managed AI functions**, which are ideal for all users:\n",
        "    * Used [`AI.SCORE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-score) to **rank** products based on a subjective quality like \"giftability.\"\n",
        "    * Used [`AI.CLASSIFY`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-classify) to **categorize** products into predefined animal types.\n",
        "    * Used [`AI.IF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if) for powerful **multimodal filtering** (finding balls in product images) and **semantic joins** (matching products to their images).\n",
        "* **Execute row-level tasks with general-purpose AI functions**, which give power-users full control:\n",
        "    * Used [`AI.GENERATE_BOOL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool) to **enrich data** to determine which products require power based on a yes/no condition.\n",
        "    * Used [`AI.GENERATE_INT`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-int) to **extract** structured data (a count of ingredients) from unstructured text.\n",
        "    * Used [`AI.GENERATE_DOUBLE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-double) to **infer** and create new data points (estimating shipping weights) that were not present in the original dataset.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "U4Zv6BqE4ko6"
      },
      "source": [
        "##Next Steps\n",
        "Continue your learning with the following notebooks:\n",
        "* [Introduction to Generative AI functions in BigQuery](https://github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_generative_ai_intro.ipynb)\n",
        "* [Analyze Multimodal Data in BigQuery](https://github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/multimodal-analysis-bigquery/analyze_multimodal_data_bigquery.ipynb)\n",
        "* [Text + multimodal embedding generation and vector search in BigQuery](https://github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_embeddings_vector_search.ipynb)\n",
        "\n",
        "Take a look at the product documentation:\n",
        "* the [Generative AI Overview](https://cloud.google.com/bigquery/docs/generative-ai-overview) landing page\n",
        "* the [`AI.SCORE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-score) function documentation\n",
        "* the [`AI.CLASSIFY`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-classify) function documentation\n",
        "* the [`AI.IF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if) function documentation\n",
        "* the [`AI.GENERATE_BOOL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool) function documentation\n",
        "* the [`AI.GENERATE_INT`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-int) function documentation\n",
        "* the [`AI.GENERATE_DOUBLE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-double) function documentation\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cleanup_md"
      },
      "source": [
        "# Cleaning Up\n",
        "\n",
        "To clean up all Google Cloud resources used in this project, you can [delete the Google Cloud 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:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "cleanup_code_modified"
      },
      "outputs": [],
      "source": [
        "# Delete the BigQuery Connection\n",
        "! bq rm --connection --project_id=$PROJECT_ID --location=us test_connection\n",
        "\n",
        "# Delete the BigQuery dataset\n",
        "! bq rm -r -f $PROJECT_ID:cymbal_pets"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "bigquery_ai_operators.ipynb",
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}
