{
  "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": [
        "# Text + multimodal embedding generation and vector search in BigQuery\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "td9kx9LVgSve"
      },
      "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_embeddings_vector_search.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_embeddings_vector_search.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_embeddings_vector_search.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_embeddings_vector_search.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_embeddings_vector_search.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_embeddings_vector_search.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_embeddings_vector_search.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_embeddings_vector_search.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_embeddings_vector_search.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_embeddings_vector_search.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": "QoWWwsHwSrCc"
      },
      "source": [
        "| | |\n",
        "|-|-|\n",
        "|Authors | [Alicia Williams](https://github.com/aliciawilliams), [Jeff Nelson](https://github.com/jeffonelson) |"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "intro_md"
      },
      "source": [
        "## Overview\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SwQLISjqGTHd"
      },
      "source": [
        "### Objectives\n",
        "\n",
        "In this tutorial, you'll build a [**semantic search**](https://cloud.google.com/discover/what-is-semantic-search) utility for a fictional e-commerce pet store. You will use [BigQuery](https://cloud.google.com/bigquery/docs) and [Vertex AI](https://cloud.google.com/vertex-ai/docs), to go beyond simple keyword matching by generating [**vector embeddings**](https://cloud.google.com/vertex-ai/generative-ai/docs/embeddings) for product text descriptions _and_ for images. This allows customers to find products based on conceptual meaning, not just keywords, creating a more intuitive shopping experience.\n",
        "\n",
        "By the end, you will know how to:\n",
        "\n",
        "* **Generate embeddings (numerical representations)** for text and image data.\n",
        "\n",
        "* **Perform text-to-text vector search** to find products with similar text descriptions\n",
        "\n",
        "* **Perform text-to-image vector search** to find product images that visually match a text query\n",
        "\n",
        "\n",
        "All within BigQuery!\n"
      ]
    },
    {
      "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": [
        "### Set 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": [
        "### Set 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": [
        "### Authenticate 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": "4Pp4LAJ3UyRP"
      },
      "source": [
        "**3. Local JupyterLab instance, uncomment and run:**\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "AzU7S3fMVDkW"
      },
      "outputs": [],
      "source": [
        "# ! gcloud auth login"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "conn_md"
      },
      "source": [
        "### Create BigQuery Cloud resource connection\n",
        "\n",
        "To allow BigQuery to securely interact with other Google Cloud services like Vertex AI (where embedding models are hosted), you need to create a [Cloud resource connection](https://cloud.google.com/bigquery/docs/create-cloud-resource-connection). This code uses the `bq` command-line tool to create a `CLOUD_RESOURCE` connection named `cymbal_conn` in the `US` location."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "conn_code"
      },
      "outputs": [],
      "source": [
        "!bq mk --connection --location=us \\\n",
        "    --connection_type=CLOUD_RESOURCE cymbal_conn"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "perms_md"
      },
      "source": [
        "### Set permissions for Service Account\n",
        "\n",
        "The resource connection service account requires certain project-level permissions to interact with Vertex AI and Google Cloud Storage."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-3iizFFY8503"
      },
      "source": [
        "First, you need to find the email address associated with your new connection's service account."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6OqpZNY953xR"
      },
      "outputs": [],
      "source": [
        "SERVICE_ACCT = !bq show --format=prettyjson --connection us.cymbal_conn | grep \"serviceAccountId\" | cut -d '\"' -f 4\n",
        "SERVICE_ACCT_EMAIL = SERVICE_ACCT[-1]\n",
        "print(SERVICE_ACCT_EMAIL)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mt9Tb-li9BSu"
      },
      "source": [
        "Next you'll assign roles that give their service account permission to be a \"BigQuery Connection User,\" an \"AI Platform User\" (so it can access Gemini models in Vertex AI), and a \"Storage Object Viewer\" (so it can access images in Cloud Storage).\n",
        "\n",
        "*A 60 second pause is added to allow the new IAM permissions to propagate throughout Google Cloud.*"
      ]
    },
    {
      "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",
        "!gcloud projects add-iam-policy-binding --format=none $PROJECT_ID --member=serviceAccount:$SERVICE_ACCT_EMAIL --role='roles/storage.objectViewer'\n",
        "\n",
        "time.sleep(60)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BfaINa3YxYQ3"
      },
      "source": [
        "### Create helper function to render images from Google Cloud Storage in this notebook\n",
        "\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 how well your search worked."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4g4dsEJKusGN"
      },
      "source": [
        "#### Import necessary libraries for the helper function"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "jiepvKmLuyob"
      },
      "outputs": [],
      "source": [
        "# Imports for GCS, plotting, and image handling\n",
        "import io\n",
        "import math\n",
        "\n",
        "import matplotlib.pyplot as plt\n",
        "from PIL import Image\n",
        "from google.cloud import storage"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rfm005SWxhxg"
      },
      "source": [
        "#### Create a Google Cloud Storage client\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xUcaWbhWxkVv"
      },
      "outputs": [],
      "source": [
        "# Set variables\n",
        "REGION = \"US\"\n",
        "bucket = \"sample-data-and-media\"\n",
        "# Create Cloud Storage client\n",
        "client = storage.Client(project=PROJECT_ID)\n",
        "bucket = client.bucket(bucket)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "s6YFp-2Axer1"
      },
      "outputs": [],
      "source": [
        "def display_product_images(df):\n",
        "    \"\"\"Displays product images from a DataFrame in a 3-column grid.\n",
        "    Simplified for clarity in a tutorial context.\n",
        "    \"\"\"\n",
        "    # Calculate grid size\n",
        "    num_images = len(df)\n",
        "    cols = 3\n",
        "    rows = math.ceil(num_images / cols)\n",
        "\n",
        "    fig, axs = plt.subplots(rows, cols, figsize=(10, rows * 3.5))\n",
        "\n",
        "    # Flatten the axes array for easy iteration, regardless of shape\n",
        "    axs = axs.flatten()\n",
        "\n",
        "    for i, row in df.iterrows():\n",
        "        # Extract the image path from the full GCS URI\n",
        "        image_path = row[\"uri\"].split(\"/\", 3)[-1]\n",
        "\n",
        "        # Get the image from GCS\n",
        "        blob = bucket.blob(image_path)\n",
        "        image = Image.open(io.BytesIO(blob.download_as_bytes()))\n",
        "\n",
        "        # Plot the image\n",
        "        axs[i].imshow(image)\n",
        "        axs[i].set_title(image_path.split(\"/\")[-1], fontsize=10)\n",
        "        axs[i].axis(\"off\")\n",
        "\n",
        "    # Hide any unused subplots\n",
        "    for j in range(num_images, len(axs)):\n",
        "        axs[j].axis(\"off\")\n",
        "\n",
        "    plt.tight_layout()\n",
        "    plt.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HozZFfxrg9d_"
      },
      "source": [
        "---\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yla8q_2EFQl9"
      },
      "source": [
        "## Load and prepare the sample data"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "prepare_md"
      },
      "source": [
        "### Create a BigQuery dataset\n",
        "\n",
        "\n",
        "Before you load data into new tables, you need a place to store them. Running the following query will create a BigQuery dataset called **`cymbal_pets`**."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "create_dataset_code"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "CREATE SCHEMA `cymbal_pets` OPTIONS (location = 'us');"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CFncMrqEGMAO"
      },
      "source": [
        "### Create the `products` table\n",
        "\n",
        "This query uses the `LOAD DATA` command to create a new table called `products` inside your `cymbal_pets` dataset. It simultaneously loads the table with product data from a Parquet file in Google Cloud Storage (GCS)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "3N94VjIMeX-F"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "LOAD DATA OVERWRITE cymbal_pets.products\n",
        "FROM FILES (\n",
        "  uris = ['gs://sample-data-and-media/cymbal-pets/tables/products'],\n",
        "  format = 'PARQUET'\n",
        ");"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cgSJkD3Q93l7"
      },
      "source": [
        "Run a quick `SELECT` query to view a couple of rows and confirm that your `products` table was created and loaded correctly."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tIKDAa-a0K13"
      },
      "outputs": [
        {
          "data": {
            "application/vnd.jupyter.widget-view+json": {
              "model_id": "3cd8d1266a12498881b5d5559e130101",
              "version_major": 2,
              "version_minor": 0
            },
            "text/plain": [
              "Query is running:   0%|          |"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "application/vnd.jupyter.widget-view+json": {
              "model_id": "f5d93345481541379624cafd4cee6891",
              "version_major": 2,
              "version_minor": 0
            },
            "text/plain": [
              "Downloading:   0%|          |"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "repr_error": "Out of range float values are not JSON compliant: nan",
              "type": "dataframe"
            },
            "text/html": [
              "\n",
              "  <div id=\"df-bb32eb4a-e853-4d8e-99f0-d2390d9d44a2\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>product_id</th>\n",
              "      <th>product_name</th>\n",
              "      <th>brand</th>\n",
              "      <th>category</th>\n",
              "      <th>subcategory</th>\n",
              "      <th>animal_type</th>\n",
              "      <th>search_keywords</th>\n",
              "      <th>price</th>\n",
              "      <th>description</th>\n",
              "      <th>inventory_level</th>\n",
              "      <th>supplier_id</th>\n",
              "      <th>average_rating</th>\n",
              "      <th>uri</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>3</td>\n",
              "      <td>AquaClear 50 Gallon Aquarium</td>\n",
              "      <td>AquaClear</td>\n",
              "      <td>Accessories</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'list': []}</td>\n",
              "      <td>199.99</td>\n",
              "      <td>This stunning aquarium provides a spacious and...</td>\n",
              "      <td>15</td>\n",
              "      <td>103</td>\n",
              "      <td>4.0</td>\n",
              "      <td>gs://sample-data-and-media/cymbal-pets/images/...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>9</td>\n",
              "      <td>AquaClear 20 Gallon Aquarium</td>\n",
              "      <td>AquaClear</td>\n",
              "      <td>Accessories</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>{'list': []}</td>\n",
              "      <td>129.99</td>\n",
              "      <td>This spacious glass enclosure provides a comfo...</td>\n",
              "      <td>25</td>\n",
              "      <td>103</td>\n",
              "      <td>2.5</td>\n",
              "      <td>gs://sample-data-and-media/cymbal-pets/images/...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-bb32eb4a-e853-4d8e-99f0-d2390d9d44a2')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-bb32eb4a-e853-4d8e-99f0-d2390d9d44a2 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-bb32eb4a-e853-4d8e-99f0-d2390d9d44a2');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "    <div id=\"df-cb42a4c3-f8b1-4030-a3f7-434261d4917c\">\n",
              "      <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-cb42a4c3-f8b1-4030-a3f7-434261d4917c')\"\n",
              "                title=\"Suggest charts\"\n",
              "                style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "      </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "      <script>\n",
              "        async function quickchart(key) {\n",
              "          const quickchartButtonEl =\n",
              "            document.querySelector('#' + key + ' button');\n",
              "          quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "          quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "          try {\n",
              "            const charts = await google.colab.kernel.invokeFunction(\n",
              "                'suggestCharts', [key], {});\n",
              "          } catch (error) {\n",
              "            console.error('Error during call to suggestCharts:', error);\n",
              "          }\n",
              "          quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "          quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "        }\n",
              "        (() => {\n",
              "          let quickchartButtonEl =\n",
              "            document.querySelector('#df-cb42a4c3-f8b1-4030-a3f7-434261d4917c button');\n",
              "          quickchartButtonEl.style.display =\n",
              "            google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "        })();\n",
              "      </script>\n",
              "    </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "text/plain": [
              "   product_id                  product_name      brand     category  \\\n",
              "0           3  AquaClear 50 Gallon Aquarium  AquaClear  Accessories   \n",
              "1           9  AquaClear 20 Gallon Aquarium  AquaClear  Accessories   \n",
              "\n",
              "  subcategory animal_type search_keywords   price  \\\n",
              "0        None        None    {'list': []}  199.99   \n",
              "1        None        None    {'list': []}  129.99   \n",
              "\n",
              "                                         description  inventory_level  \\\n",
              "0  This stunning aquarium provides a spacious and...               15   \n",
              "1  This spacious glass enclosure provides a comfo...               25   \n",
              "\n",
              "   supplier_id  average_rating  \\\n",
              "0          103             4.0   \n",
              "1          103             2.5   \n",
              "\n",
              "                                                 uri  \n",
              "0  gs://sample-data-and-media/cymbal-pets/images/...  \n",
              "1  gs://sample-data-and-media/cymbal-pets/images/...  "
            ]
          },
          "execution_count": 11,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "SELECT *\n",
        "FROM cymbal_pets.products\n",
        "LIMIT 2"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "039VFiVBwzsF"
      },
      "source": [
        "---\n",
        "\n",
        "\n",
        "## Perform text-to-text semantic search\n",
        "\n",
        "With the product data prepared, it's time to perform the first [semantic search](https://cloud.google.com/discover/what-is-semantic-search). You want to input a text query, like \"kitten toy\" and find the most similar products. To do this, you first need to convert the product names and descriptions into [vector embeddings](https://cloud.google.com/vertex-ai/generative-ai/docs/embeddings) -- numerical representations that capture their semantic meaning. Once the embeddings are generated, you can run a vector search.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Y8yup-saGjtc"
      },
      "source": [
        "### Create remote model for text embedding\n",
        "\n",
        "Begin by creating a remote model in BigQuery named `text_embedding_model`. This command doesn't create a new model; instead, it creates a pointer to an existing, pre-trained text embedding model hosted on Vertex AI ([`gemini-embedding-001`](https://ai.google.dev/gemini-api/docs/embeddings#model-versions)). This allows you to leverage Vertex AI models directly from your SQL queries."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qHc6MHazGqR9"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "CREATE OR REPLACE MODEL `cymbal_pets.text_embedding_model`\n",
        "  REMOTE WITH CONNECTION `us.cymbal_conn`\n",
        "  OPTIONS (ENDPOINT = 'gemini-embedding-001');"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AgzCgXcUwcm4"
      },
      "source": [
        "### Generate text embeddings for product descriptions"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dtgmxoax-pUT"
      },
      "source": [
        " To store embeddings, alter the `products` table to add a new column. Since embeddings are numerical representations of text (an array of floating point numbers), the column type will be `ARRAY<FLOAT>`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "614L4qqk4ePX"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "ALTER TABLE cymbal_pets.products\n",
        "ADD COLUMN text_embedding ARRAY<FLOAT64>;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kOo1l6rdOYk8"
      },
      "source": [
        "Now, generate embeddings with the [`ML.GENERATE_EMBEDDING`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-generate-embedding) function. The following query passes the `product_name` and `description` to the `text_embedding_model` for each item. An `UPDATE` statement saves the resulting embeddings in a new column."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "pLKxq9UrwreS"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "UPDATE `cymbal_pets.products` AS t\n",
        "SET t.text_embedding = s.ml_generate_embedding_result\n",
        "FROM (\n",
        "  SELECT\n",
        "    product_id,\n",
        "    ml_generate_embedding_result\n",
        "  FROM\n",
        "    ML.GENERATE_EMBEDDING(\n",
        "      MODEL `cymbal_pets.text_embedding_model`,\n",
        "      (\n",
        "        SELECT\n",
        "          product_id,\n",
        "          CONCAT(product_name, ' ', description) AS content\n",
        "        FROM `cymbal_pets.products`\n",
        "      ),\n",
        "      STRUCT(TRUE AS flatten_json_output)\n",
        "    )\n",
        ") AS s\n",
        "WHERE t.product_id = s.product_id;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "FylUH24h_kXN"
      },
      "source": [
        "You should now see the `text_embedding` column populated with a long array of numbers. Those are vector embeddings that numerically represent each products' name and description!"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "wdwWBdL-5yiP"
      },
      "outputs": [
        {
          "data": {
            "application/vnd.jupyter.widget-view+json": {
              "model_id": "748969aad71e47e7aecf35ef0b6fb475",
              "version_major": 2,
              "version_minor": 0
            },
            "text/plain": [
              "Query is running:   0%|          |"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "application/vnd.jupyter.widget-view+json": {
              "model_id": "407d8a55b98a49218fa7ffc88c4e746e",
              "version_major": 2,
              "version_minor": 0
            },
            "text/plain": [
              "Downloading:   0%|          |"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "summary": "{\n  \"name\": \"get_ipython()\",\n  \"rows\": 2,\n  \"fields\": [\n    {\n      \"column\": \"product_id\",\n      \"properties\": {\n        \"dtype\": \"Int64\",\n        \"num_unique_values\": 2,\n        \"samples\": [\n          171,\n          57\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"product_name\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 2,\n        \"samples\": [\n          \"AquaClear Aquarium Fish Net\",\n          \"AquaClear Aquarium Filter Cartridge\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"description\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 2,\n        \"samples\": [\n          \"This convenient net makes it easy to safely transfer fish or other aquatic pets.  The soft mesh is gentle on delicate scales and fins, while the sturdy handle provides a comfortable grip.  Ideal for aquarium maintenance and cleaning.\",\n          \"This multi-stage filter provides superior water quality for your aquarium, ensuring a healthy and thriving environment for your fish.  The layered design effectively removes debris, impurities, and unwanted particles, promoting clear and clean water.  Enjoy a pristine aquarium with this high-performance filtration system.\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"text_embedding\",\n      \"properties\": {\n        \"dtype\": \"object\",\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}",
              "type": "dataframe"
            },
            "text/html": [
              "\n",
              "  <div id=\"df-07310e8b-d261-4a6b-a327-53148c037187\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>product_id</th>\n",
              "      <th>product_name</th>\n",
              "      <th>description</th>\n",
              "      <th>text_embedding</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>57</td>\n",
              "      <td>AquaClear Aquarium Filter Cartridge</td>\n",
              "      <td>This multi-stage filter provides superior wate...</td>\n",
              "      <td>[0.011718875728547573, 0.008779315277934074, 0...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>171</td>\n",
              "      <td>AquaClear Aquarium Fish Net</td>\n",
              "      <td>This convenient net makes it easy to safely tr...</td>\n",
              "      <td>[-0.013994182460010052, -0.009928294457495213,...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-07310e8b-d261-4a6b-a327-53148c037187')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-07310e8b-d261-4a6b-a327-53148c037187 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-07310e8b-d261-4a6b-a327-53148c037187');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "    <div id=\"df-7007fbe0-a8ce-40d7-a2a5-e461a346e43c\">\n",
              "      <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-7007fbe0-a8ce-40d7-a2a5-e461a346e43c')\"\n",
              "                title=\"Suggest charts\"\n",
              "                style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "      </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "      <script>\n",
              "        async function quickchart(key) {\n",
              "          const quickchartButtonEl =\n",
              "            document.querySelector('#' + key + ' button');\n",
              "          quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "          quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "          try {\n",
              "            const charts = await google.colab.kernel.invokeFunction(\n",
              "                'suggestCharts', [key], {});\n",
              "          } catch (error) {\n",
              "            console.error('Error during call to suggestCharts:', error);\n",
              "          }\n",
              "          quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "          quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "        }\n",
              "        (() => {\n",
              "          let quickchartButtonEl =\n",
              "            document.querySelector('#df-7007fbe0-a8ce-40d7-a2a5-e461a346e43c button');\n",
              "          quickchartButtonEl.style.display =\n",
              "            google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "        })();\n",
              "      </script>\n",
              "    </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "text/plain": [
              "   product_id                         product_name  \\\n",
              "0          57  AquaClear Aquarium Filter Cartridge   \n",
              "1         171          AquaClear Aquarium Fish Net   \n",
              "\n",
              "                                         description  \\\n",
              "0  This multi-stage filter provides superior wate...   \n",
              "1  This convenient net makes it easy to safely tr...   \n",
              "\n",
              "                                      text_embedding  \n",
              "0  [0.011718875728547573, 0.008779315277934074, 0...  \n",
              "1  [-0.013994182460010052, -0.009928294457495213,...  "
            ]
          },
          "execution_count": 15,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "SELECT product_id, product_name, description, text_embedding\n",
        "FROM cymbal_pets.products\n",
        "LIMIT 2"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EBBg2qw0_7Q-"
      },
      "source": [
        "### Use `VECTOR SEARCH` to perform a text-to-text search\n",
        "\n",
        "Now, use [`VECTOR_SEARCH`](https://cloud.google.com/bigquery/docs/reference/standard-sql/search_functions#vector_search) to perform a text-to-text search. This function works in two main steps:\n",
        "* A subquery generates an embedding for a search query (e.g. \"kitten toy\")\n",
        "* `VECTOR_SEARCH` then compares this new embedding against all existing product embeddings and returns the `top_k` (3 in this case) most similar results"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7bPZj0FM5heu"
      },
      "outputs": [],
      "source": [
        "%%bigquery text_matches --project {PROJECT_ID}\n",
        "\n",
        "SELECT base.product_id, base.product_name, base.brand, base.category, base.description, base.average_rating, base.uri, distance\n",
        "    FROM\n",
        "      VECTOR_SEARCH(\n",
        "        TABLE `cymbal_pets.products`,\n",
        "        'text_embedding',\n",
        "        (\n",
        "        -- GENERATE AN EMBEDDING AS A SUBQUERY\n",
        "        SELECT\n",
        "          ml_generate_embedding_result,\n",
        "          content AS query\n",
        "        FROM\n",
        "          ML.GENERATE_EMBEDDING(\n",
        "            MODEL `cymbal_pets.text_embedding_model`,\n",
        "            ( SELECT \"kitten toy\" AS content)\n",
        "          )\n",
        "        ),\n",
        "        top_k => 3)\n",
        "ORDER BY distance ASC;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xzaZ3Gs2ASLu"
      },
      "source": [
        "The results of the vector search are now stored in a pandas DataFrame called `text_matches` (a parameter added to the [`%%bigquery` magic](https://cloud.google.com/python/docs/reference/bigquery/latest/magics) utility in the prior cell).\n",
        "\n",
        "Let's display the top rows."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "a1SlsPNz79nk"
      },
      "outputs": [
        {
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "summary": "{\n  \"name\": \"text_matches\",\n  \"rows\": 3,\n  \"fields\": [\n    {\n      \"column\": \"product_id\",\n      \"properties\": {\n        \"dtype\": \"Int64\",\n        \"num_unique_values\": 3,\n        \"samples\": [\n          27,\n          33,\n          157\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"product_name\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 3,\n        \"samples\": [\n          \"Purrfect Perch Cat Toy\",\n          \"Cozy Naps Catnip Toy\",\n          \"Cozy Naps Catnip Toy Ball\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"brand\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 2,\n        \"samples\": [\n          \"Cozy Naps\",\n          \"Purrfect Perch\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"category\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 1,\n        \"samples\": [\n          \"Toys\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"description\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 3,\n        \"samples\": [\n          \"This cat tree provides multiple levels for climbing, scratching, and resting, allowing your cat to enjoy playtime and relaxation.  The soft, plush surfaces offer comfortable spots to nap, while the sisal-covered post satisfies natural scratching instincts.\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"average_rating\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 0.43588989435406733,\n        \"min\": 4.0,\n        \"max\": 4.8,\n        \"num_unique_values\": 3,\n        \"samples\": [\n          4.0\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"uri\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 3,\n        \"samples\": [\n          \"gs://sample-data-and-media/cymbal-pets/images/purrfect-perch-cat-toy.png\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"distance\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 0.006529728333769095,\n        \"min\": 0.7847798709976486,\n        \"max\": 0.797839228798168,\n        \"num_unique_values\": 3,\n        \"samples\": [\n          0.7847798709976486\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}",
              "type": "dataframe",
              "variable_name": "text_matches"
            },
            "text/html": [
              "\n",
              "  <div id=\"df-8b621b3e-1819-4cdd-b879-39d19dd8c853\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>product_id</th>\n",
              "      <th>product_name</th>\n",
              "      <th>brand</th>\n",
              "      <th>category</th>\n",
              "      <th>description</th>\n",
              "      <th>average_rating</th>\n",
              "      <th>uri</th>\n",
              "      <th>distance</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>27</td>\n",
              "      <td>Purrfect Perch Cat Toy</td>\n",
              "      <td>Purrfect Perch</td>\n",
              "      <td>Toys</td>\n",
              "      <td>This cat tree provides multiple levels for cli...</td>\n",
              "      <td>4.0</td>\n",
              "      <td>gs://sample-data-and-media/cymbal-pets/images/...</td>\n",
              "      <td>0.784780</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>33</td>\n",
              "      <td>Cozy Naps Catnip Toy</td>\n",
              "      <td>Cozy Naps</td>\n",
              "      <td>Toys</td>\n",
              "      <td>This soft, plush cushion provides a cozy and c...</td>\n",
              "      <td>4.7</td>\n",
              "      <td>gs://sample-data-and-media/cymbal-pets/images/...</td>\n",
              "      <td>0.791354</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>157</td>\n",
              "      <td>Cozy Naps Catnip Toy Ball</td>\n",
              "      <td>Cozy Naps</td>\n",
              "      <td>Toys</td>\n",
              "      <td>This yarn is perfect for creating cozy and com...</td>\n",
              "      <td>4.8</td>\n",
              "      <td>gs://sample-data-and-media/cymbal-pets/images/...</td>\n",
              "      <td>0.797839</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-8b621b3e-1819-4cdd-b879-39d19dd8c853')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-8b621b3e-1819-4cdd-b879-39d19dd8c853 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-8b621b3e-1819-4cdd-b879-39d19dd8c853');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "    <div id=\"df-77218e41-1498-427a-89f0-9809a0d5a226\">\n",
              "      <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-77218e41-1498-427a-89f0-9809a0d5a226')\"\n",
              "                title=\"Suggest charts\"\n",
              "                style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "      </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "      <script>\n",
              "        async function quickchart(key) {\n",
              "          const quickchartButtonEl =\n",
              "            document.querySelector('#' + key + ' button');\n",
              "          quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "          quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "          try {\n",
              "            const charts = await google.colab.kernel.invokeFunction(\n",
              "                'suggestCharts', [key], {});\n",
              "          } catch (error) {\n",
              "            console.error('Error during call to suggestCharts:', error);\n",
              "          }\n",
              "          quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "          quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "        }\n",
              "        (() => {\n",
              "          let quickchartButtonEl =\n",
              "            document.querySelector('#df-77218e41-1498-427a-89f0-9809a0d5a226 button');\n",
              "          quickchartButtonEl.style.display =\n",
              "            google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "        })();\n",
              "      </script>\n",
              "    </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "text/plain": [
              "   product_id               product_name           brand category  \\\n",
              "0          27     Purrfect Perch Cat Toy  Purrfect Perch     Toys   \n",
              "1          33       Cozy Naps Catnip Toy       Cozy Naps     Toys   \n",
              "2         157  Cozy Naps Catnip Toy Ball       Cozy Naps     Toys   \n",
              "\n",
              "                                         description  average_rating  \\\n",
              "0  This cat tree provides multiple levels for cli...             4.0   \n",
              "1  This soft, plush cushion provides a cozy and c...             4.7   \n",
              "2  This yarn is perfect for creating cozy and com...             4.8   \n",
              "\n",
              "                                                 uri  distance  \n",
              "0  gs://sample-data-and-media/cymbal-pets/images/...  0.784780  \n",
              "1  gs://sample-data-and-media/cymbal-pets/images/...  0.791354  \n",
              "2  gs://sample-data-and-media/cymbal-pets/images/...  0.797839  "
            ]
          },
          "execution_count": 17,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "text_matches.head()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "J-Ev_nYsAjVI"
      },
      "source": [
        "### View search results\n",
        "Now you can use the helper function to display the images of the top 3 products the vector search returned. As you can see, the results are semantically related to \"kitten toy\" even if the exact words don't appear in the product names or descriptions."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "VlCaWeq78Aaz"
      },
      "outputs": [
        {
          "data": {
            "image/png": "[encoded data, 537380 characters]\n",
            "text/plain": [
              "<Figure size 1000x350 with 3 Axes>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "display_product_images(text_matches)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9ytmT_Q6-J2I"
      },
      "source": [
        "---\n",
        "\n",
        "\n",
        "## Perform text-to-image (multimodal) semantic search\n",
        "\n",
        "The previous example performed a text-to-text search. Now we can look at multimodal search. This allows you to use a text query to find products based on their visual characteristics (not using any written descriptions). Note that multimodal search also extends to audio, video, and more."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9Y31x-x_HC92"
      },
      "source": [
        "### Create remote model for multimodal embedding\n",
        "\n",
        "To work with images, you'll need a different embedding model. You'll now create a remote model pointing to [`multimodalembedding@001`](https://cloud.google.com/vertex-ai/generative-ai/docs/embeddings/get-multimodal-embeddings#supported-models). Unlike the previous text-only model, this one is **multimodal**, meaning it can generate a single vector embedding from content that's text, an image, a video, audio, or more."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HpJwFmAOGrXt"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "CREATE OR REPLACE MODEL `cymbal_pets.mm_embedding_model`\n",
        "  REMOTE WITH CONNECTION `us.cymbal_conn`\n",
        "  OPTIONS (ENDPOINT = 'multimodalembedding@001');"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "86UojNj1HLGB"
      },
      "source": [
        "### Generate multimodal embeddings for product images"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "C603D8_wBLpv"
      },
      "source": [
        "You need a new column in your table to store the embeddings generated from the product images."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "sLliKpuc-Xra"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "ALTER TABLE cymbal_pets.products\n",
        "ADD COLUMN mm_embedding ARRAY<FLOAT64>;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uqON6RSYBW-y"
      },
      "source": [
        "This step generates embeddings directly from product images. The process again uses [`ML.GENERATE_EMBEDDING`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-generate-embedding), but with the `mm_embedding_model`.\n",
        "\n",
        "The key difference is how you provide the image to the model. Instead of a text string, the model requires a temporary, secure URL for each image. Because we haven't stored [`ObjectRef`](https://cloud.google.com/bigquery/docs/reference/standard-sql/objectref_functions)s in our table, we can generate them on the fly by chaining three functions together:\n",
        "\n",
        "* [`OBJ.MAKE_REF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/objectref_functions#objmake_ref): First, it converts the GCS path from the `uri` column into a structured `ObjectRef`\n",
        "* [`OBJ.FETCH_METADATA`](https://cloud.google.com/bigquery/docs/reference/standard-sql/objectref_functions#objfetch_metadata): Next, it populates the `ObjectRef` with file metadata from Cloud Storage\n",
        "* [`OBJ.GET_ACCESS_URL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/objectref_functions#objget_access_url): Finally, it generates a signed URL from the metadata-rich `ObjectRef` that the model can access.\n",
        "\n",
        "The resulting vector embedding for each image is then saved to the `mm_embedding` column with an `UPDATE` statement."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "CLVJ0o9s-fEJ"
      },
      "outputs": [],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "UPDATE `cymbal_pets.products` AS t\n",
        "SET\n",
        "  t.mm_embedding = s.ml_generate_embedding_result\n",
        "FROM (\n",
        "  SELECT\n",
        "    product_id,\n",
        "    ml_generate_embedding_result\n",
        "  FROM\n",
        "    ML.GENERATE_EMBEDDING(\n",
        "      MODEL `cymbal_pets.mm_embedding_model`,\n",
        "      (\n",
        "        SELECT\n",
        "          product_id,\n",
        "          OBJ.GET_ACCESS_URL(OBJ.FETCH_METADATA(OBJ.MAKE_REF(uri, 'us.cymbal_conn')), 'r') AS content\n",
        "        FROM\n",
        "          `cymbal_pets.products`\n",
        "      ),\n",
        "      STRUCT(TRUE AS flatten_json_output)\n",
        "    )\n",
        ") AS s\n",
        "WHERE t.product_id = s.product_id;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YhJ9OzDmCANS"
      },
      "source": [
        " Inspect the table one more time to see your new `mm_embedding` column filled with multimodal embeddings, derived from product images."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7j2I3Hk3ANF6"
      },
      "outputs": [
        {
          "data": {
            "application/vnd.jupyter.widget-view+json": {
              "model_id": "479e8ba9507944f1b0e2c5ab13560d85",
              "version_major": 2,
              "version_minor": 0
            },
            "text/plain": [
              "Query is running:   0%|          |"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "application/vnd.jupyter.widget-view+json": {
              "model_id": "fdbf50cbe8274a29bde7c825a8fb107c",
              "version_major": 2,
              "version_minor": 0
            },
            "text/plain": [
              "Downloading:   0%|          |"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "summary": "{\n  \"name\": \"get_ipython()\",\n  \"rows\": 2,\n  \"fields\": [\n    {\n      \"column\": \"product_id\",\n      \"properties\": {\n        \"dtype\": \"Int64\",\n        \"num_unique_values\": 2,\n        \"samples\": [\n          9,\n          3\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"product_name\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 2,\n        \"samples\": [\n          \"AquaClear 20 Gallon Aquarium\",\n          \"AquaClear 50 Gallon Aquarium\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"description\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 2,\n        \"samples\": [\n          \"This spacious glass enclosure provides a comfortable and transparent habitat for your aquatic pets, allowing for easy observation of their behavior and interaction with their environment.  The sleek design complements any home decor while offering ample room for swimming, exploration, and creating a thriving underwater ecosystem.\",\n          \"This stunning aquarium provides a spacious and elegant home for your aquatic pets, allowing them to thrive in a crystal-clear environment.  The sleek design complements any home decor, while the large viewing area offers an unobstructed view of your fish and other aquatic life.  Its durable construction ensures a safe and long-lasting habitat for your underwater companions.\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"mm_embedding\",\n      \"properties\": {\n        \"dtype\": \"object\",\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}",
              "type": "dataframe"
            },
            "text/html": [
              "\n",
              "  <div id=\"df-4b050fbf-961a-485d-8c88-e30d8192c5dd\" class=\"colab-df-container\">\n",
              "    <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>product_id</th>\n",
              "      <th>product_name</th>\n",
              "      <th>description</th>\n",
              "      <th>mm_embedding</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>3</td>\n",
              "      <td>AquaClear 50 Gallon Aquarium</td>\n",
              "      <td>This stunning aquarium provides a spacious and...</td>\n",
              "      <td>[0.0190890729, 0.0193080865, -0.00221736147, -...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>9</td>\n",
              "      <td>AquaClear 20 Gallon Aquarium</td>\n",
              "      <td>This spacious glass enclosure provides a comfo...</td>\n",
              "      <td>[0.0341621898, 0.0419731215, -0.0227391645, 0....</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-4b050fbf-961a-485d-8c88-e30d8192c5dd')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-4b050fbf-961a-485d-8c88-e30d8192c5dd button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-4b050fbf-961a-485d-8c88-e30d8192c5dd');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "    <div id=\"df-cacf3e3a-c35a-4e3a-83ad-832706d74530\">\n",
              "      <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-cacf3e3a-c35a-4e3a-83ad-832706d74530')\"\n",
              "                title=\"Suggest charts\"\n",
              "                style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "      </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "      <script>\n",
              "        async function quickchart(key) {\n",
              "          const quickchartButtonEl =\n",
              "            document.querySelector('#' + key + ' button');\n",
              "          quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "          quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "          try {\n",
              "            const charts = await google.colab.kernel.invokeFunction(\n",
              "                'suggestCharts', [key], {});\n",
              "          } catch (error) {\n",
              "            console.error('Error during call to suggestCharts:', error);\n",
              "          }\n",
              "          quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "          quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "        }\n",
              "        (() => {\n",
              "          let quickchartButtonEl =\n",
              "            document.querySelector('#df-cacf3e3a-c35a-4e3a-83ad-832706d74530 button');\n",
              "          quickchartButtonEl.style.display =\n",
              "            google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "        })();\n",
              "      </script>\n",
              "    </div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "text/plain": [
              "   product_id                  product_name  \\\n",
              "0           3  AquaClear 50 Gallon Aquarium   \n",
              "1           9  AquaClear 20 Gallon Aquarium   \n",
              "\n",
              "                                         description  \\\n",
              "0  This stunning aquarium provides a spacious and...   \n",
              "1  This spacious glass enclosure provides a comfo...   \n",
              "\n",
              "                                        mm_embedding  \n",
              "0  [0.0190890729, 0.0193080865, -0.00221736147, -...  \n",
              "1  [0.0341621898, 0.0419731215, -0.0227391645, 0....  "
            ]
          },
          "execution_count": 22,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "%%bigquery --project {PROJECT_ID}\n",
        "\n",
        "SELECT product_id, product_name, description, mm_embedding\n",
        "FROM cymbal_pets.products\n",
        "LIMIT 2"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Yoy6iKuXCKI4"
      },
      "source": [
        "###Use `VECTOR SEARCH` to perform a text-to-image search\n",
        "\n",
        "The power of a multimodal model is that it places both text and images into the same \"embedding space.\" This allows you to use an embedding generated from a text string (like \"kitten toy\") and directly compare it against embeddings generated from images.\n",
        "\n",
        "The process for this text-to-image search mirrors the previous text-to-text search, with two key differences:\n",
        "* A text query (\"kitten toy\") is embedded using the multimodal `mm_embedding_model`\n",
        "* The search is performed against the image embedding column (`embedding_mm_image`) to find the most visually similar items"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "PiC0CLa2AAlT"
      },
      "outputs": [],
      "source": [
        "%%bigquery multimodal_matches --project {PROJECT_ID}\n",
        "\n",
        "SELECT base.product_id, base.product_name, base.brand, base.category, base.description, base.average_rating, base.uri, distance\n",
        "    FROM\n",
        "      VECTOR_SEARCH(\n",
        "        TABLE`cymbal_pets.products`,\n",
        "        'mm_embedding',\n",
        "        (\n",
        "        SELECT\n",
        "          ml_generate_embedding_result,\n",
        "          content AS query\n",
        "        FROM\n",
        "          ML.GENERATE_EMBEDDING(\n",
        "            MODEL `cymbal_pets.mm_embedding_model`,\n",
        "            (SELECT \"kitten toy\" AS content)\n",
        "          )\n",
        "        ),\n",
        "        'ml_generate_embedding_result',\n",
        "        top_k => 3)\n",
        "ORDER BY distance ASC;"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ma7A9jF0HbUg"
      },
      "source": [
        "### View search results"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "D1SRowjwCsda"
      },
      "source": [
        "Finally, let's visualize the results of the text-to-image search. You should see images of products that are conceptually related to \"kitten toy,\" demonstrating the power of multimodal embeddings to find relevant items based on visual similarity to a text description."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "BNOV5oYY3I74"
      },
      "outputs": [
        {
          "data": {
            "image/png": "[encoded data, 532632 characters]\n",
            "text/plain": [
              "<Figure size 1000x350 with 3 Axes>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "# Display the results\n",
        "display_product_images(multimodal_matches)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cleanup_md"
      },
      "source": [
        "---\n",
        "\n",
        "## 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"
      },
      "outputs": [],
      "source": [
        "# Delete the BigQuery table\n",
        "! bq rm --table -f cymbal_pets.products\n",
        "\n",
        "# Delete the remote models\n",
        "! bq rm --model -f cymbal_pets.text_embedding_model\n",
        "! bq rm --model -f cymbal_pets.mm_embedding_model\n",
        "\n",
        "# Delete the remote connection\n",
        "! bq rm --connection --project_id=$PROJECT_ID --location=us cymbal_conn\n",
        "\n",
        "# Delete the BigQuery dataset\n",
        "! bq rm -r -f $PROJECT_ID:cymbal_pets"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "bigquery_embeddings_vector_search.ipynb",
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}
