notebooks/generative_ai/bq_dataframes_llm_vector_search.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "TpJu6BBeooES"
      },
      "outputs": [],
      "source": [
        "# Copyright 2023 Google LLC\n",
        "#\n",
        "# Licensed under the Apache License, Version 2.0 (the \"License\");\n",
        "# you may not use this file except in compliance with the License.\n",
        "# You may obtain a copy of the License at\n",
        "#\n",
        "#     https://www.apache.org/licenses/LICENSE-2.0\n",
        "#\n",
        "# Unless required by applicable law or agreed to in writing, software\n",
        "# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
        "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
        "# See the License for the specific language governing permissions and\n",
        "# limitations under the License."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EQbZKS7_ooET"
      },
      "source": [
        "## Build a Vector Search application using BigQuery DataFrames (aka BigFrames)\n",
        "\n",
        "<table align=\"left\">\n",
        "\n",
        "  <td>\n",
        "    <a href=\"https://colab.research.google.com/github/googleapis/python-bigquery-dataframes/blob/main/notebooks/generative_ai/bq_dataframes_llm_kmeans.ipynb\">\n",
        "      <img src=\"https://cloud.google.com/ml-engine/images/colab-logo-32px.png\" alt=\"Colab logo\"> Run in Colab\n",
        "    </a>\n",
        "  </td>\n",
        "  <td>\n",
        "    <a href=\"https://github.com/googleapis/python-bigquery-dataframes/tree/main/notebooks/generative_ai/bq_dataframes_llm_kmeans.ipynb\">\n",
        "      <img src=\"https://cloud.google.com/ml-engine/images/github-logo-32px.png\" alt=\"GitHub logo\">\n",
        "      View on GitHub\n",
        "    </a>\n",
        "  </td>\n",
        "  <td>\n",
        "    <a href=\"https://console.cloud.google.com/vertex-ai/workbench/deploy-notebook?download_url=https://raw.githubusercontent.com/googleapis/python-bigquery-dataframes/tree/main/notebooks/generative_ai/bq_dataframes_llm_kmeans.ipynb\">\n",
        "      <img src=\"https://lh3.googleusercontent.com/UiNooY4LUgW_oTvpsNhPpQzsstV5W8F7rYgxgGBD85cWJoLmrOzhVs_ksK_vgx40SHs7jCqkTkCk=e14-rj-sc0xffffff-h130-w32\" alt=\"Vertex AI logo\">\n",
        "      Open in Vertex AI Workbench\n",
        "    </a>\n",
        "  </td>\n",
        "  <td>\n",
        "    <a href=\"https://console.cloud.google.com/bigquery/import?url=https://github.com/googleapis/python-bigquery-dataframes/blob/main/notebooks/generative_ai/bq_dataframes_llm_kmeans.ipynb\">\n",
        "      <img src=\"https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcTW1gvOovVlbZAIZylUtf5Iu8-693qS1w5NJw&s\" alt=\"BQ logo\" width=\"35\">\n",
        "      Open in BQ Studio\n",
        "    </a>\n",
        "  </td>\n",
        "</table>\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vFMjpPBo9aVv"
      },
      "source": [
        "**Author:** Sudipto Guha (Google)\n",
        "\n",
        "**Last updated:** March 16th 2025"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SHQ3Gx-oooEU"
      },
      "source": [
        "## Overview\n",
        "\n",
        "This notebook will guide you through a practical example of using [BigFrames](https://github.com/googleapis/python-bigquery-dataframes/issues) to perform [vector search](https://cloud.google.com/bigquery/docs/vector-search-intro) and analysis on a patent dataset within BigQuery. We will leverage Python and BigFrames to efficiently process, analyze, and gain insights from a large-scale dataset without moving data from BigQuery.\n",
        "\n",
        "Here's a breakdown of what we'll cover:\n",
        "\n",
        "1. **Data Ingestion and Embedding Generation:**\n",
        "We will start by reading a public patent dataset directly from BigQuery into a BigFrames DataFrame.\n",
        "We'll demonstrate how to use BigFrames' `TextEmbeddingGenerator` to create text embeddings for the patent abstracts. This process converts the textual data into numerical vectors that capture the semantic meaning of each abstract.\n",
        "We'll show how BigFrames efficiently performs this embedding generation within BigQuery, avoiding data transfer to the client-side.\n",
        "Finally, we'll store the generated embeddings back into a new BigQuery table for subsequent analysis.\n",
        "\n",
        "2. **Indexing and Similarity Search:**\n",
        "Here we'll create a vector index using BigFrames to enable fast and scalable similarity searches.\n",
        "We'll demonstrate how to create an IVF index for efficient approximate nearest neighbor searches.\n",
        "We'll then perform a vector search using a sample query string to find patents that are semantically similar to the query. This showcases how vector search goes beyond keyword matching to find relevant results based on meaning.\n",
        "\n",
        "3. **AI-Powered Summarization with Retrieval Augmented Generation (RAG):**\n",
        "To further enhance the analysis, we'll implement a RAG pipeline.\n",
        "We'll retrieve the top most similar patents based on the vector search results from step 2.\n",
        "We'll use BigFrames' `GeminiTextGenerator` to create a prompt for an LLM to generate a concise summary of the retrieved patents.\n",
        "This demonstrates how to combine vector search with generative AI to extract and synthesize meaningful insights from complex patent data.\n",
        "\n",
        "\n",
        "We will tie these pieces together in Python using BigQuery DataFrames. [Click here](https://cloud.google.com/bigquery/docs/dataframes-quickstart) to learn more about BigQuery DataFrames!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EHjmqb-0ooEU"
      },
      "source": [
        "### Dataset\n",
        "\n",
        "This notebook uses the [BQ Patents Public Dataset](https://bigquery.cloud.google.com/dataset/patents-public-data:patentsview)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AqdihIDJooEU"
      },
      "source": [
        "### Costs\n",
        "\n",
        "This tutorial uses billable components of Google Cloud:\n",
        "\n",
        "* BigQuery (compute)\n",
        "* BigQuery ML\n",
        "* Generative AI support on Vertex AI\n",
        "\n",
        "Learn about [BigQuery compute pricing](https://cloud.google.com/bigquery/pricing#analysis_pricing_models), [Generative AI support on Vertex AI pricing](https://cloud.google.com/vertex-ai/pricing#generative_ai_models),\n",
        "and [BigQuery ML pricing](https://cloud.google.com/bigquery/pricing#bqml),\n",
        "and use the [Pricing Calculator](https://cloud.google.com/products/calculator/)\n",
        "to generate a cost estimate based on your projected usage."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GqLjnm1hsKGU"
      },
      "source": [
        "## Setup & initialization\n",
        "\n",
        "Make sure you have the required roles and permissions listed below:\n",
        "\n",
        "For [Vector embedding generation](https://cloud.google.com/bigquery/docs/generate-text-embedding#required_roles)\n",
        "\n",
        "For [Vector Index creation](https://cloud.google.com/bigquery/docs/vector-index#roles_and_permissions)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Z-mvYJUCooEV"
      },
      "source": [
        "## Before you begin\n",
        "\n",
        "Complete the tasks in this section to set up your environment."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xn-v3mSvooEV"
      },
      "source": [
        "### Set up your Google Cloud project\n",
        "\n",
        "**The following steps are required, regardless of your notebook environment.**\n",
        "\n",
        "1. [Select or create a Google Cloud project](https://console.cloud.google.com/cloud-resource-manager). When you first create an account, you get a $300 credit towards your compute/storage costs.\n",
        "\n",
        "2. [Make sure that billing is enabled for your project](https://cloud.google.com/billing/docs/how-to/modify-project).\n",
        "\n",
        "3. [Click here](https://console.cloud.google.com/flows/enableapi?apiid=bigquery.googleapis.com,bigqueryconnection.googleapis.com,aiplatform.googleapis.com) to enable the following APIs:\n",
        "\n",
        "  * BigQuery API\n",
        "  * BigQuery Connection API\n",
        "  * Vertex AI API\n",
        "\n",
        "4. If you are running this notebook locally, install the [Cloud SDK](https://cloud.google.com/sdk)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ioydzb_8ooEV"
      },
      "source": [
        "#### Set your project ID\n",
        "\n",
        "**If you don't know your project ID**, see the support page: [Locate the project ID](https://support.google.com/googleapi/answer/7014113)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "executionInfo": {
          "elapsed": 2,
          "status": "ok",
          "timestamp": 1742191597773,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "b8bKCfIiooEV"
      },
      "outputs": [],
      "source": [
        "# set your project ID below\n",
        "PROJECT_ID = \"\"  # @param {type:\"string\"}\n",
        "\n",
        "# set your region\n",
        "REGION = \"US\"  # @param {type: \"string\"}\n",
        "\n",
        "# Set the project id in gcloud\n",
        "#! gcloud config set project {PROJECT_ID}"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GbUgWr6LooEV"
      },
      "source": [
        "#### Authenticate your Google Cloud account\n",
        "\n",
        "Depending on your Jupyter environment, you might have to manually authenticate. Follow the relevant instructions below."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "U7ChP8jUooEV"
      },
      "source": [
        "**Vertex AI Workbench**\n",
        "\n",
        "Do nothing, you are already authenticated."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VfHOYcZZooEW"
      },
      "source": [
        "**Local JupyterLab instance**\n",
        "\n",
        "Uncomment and run the following cell:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "3cGhUVM0ooEW"
      },
      "outputs": [],
      "source": [
        "# ! gcloud auth login"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AoHnXlg-ooEW"
      },
      "source": [
        "**Colab**\n",
        "\n",
        "Uncomment and run the following cell:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "executionInfo": {
          "elapsed": 2,
          "status": "ok",
          "timestamp": 1742191608487,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "j3lmnsh7ooEW",
        "outputId": "eb68daf5-5558-487a-91d2-4b4f9e476da0"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "WARNING: google.colab.auth.authenticate_user() is not supported in Colab Enterprise.\n"
          ]
        }
      ],
      "source": [
        "# from google.colab import auth\n",
        "# auth.authenticate_user()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "a9gsyttuooEW"
      },
      "source": [
        "Now we are ready to use BigQuery DataFrames!"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xckgWno6ouHY"
      },
      "source": [
        "## Step 1: Data Ingestion and Embedding Generation"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Hjg9jDN-ooEW"
      },
      "source": [
        "Install libraries"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 33,
      "metadata": {
        "executionInfo": {
          "elapsed": 947,
          "status": "ok",
          "timestamp": 1742195413800,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "R7STCS8xB5d2"
      },
      "outputs": [],
      "source": [
        "import bigframes.pandas as bf\n",
        "import bigframes.ml as bf_ml\n",
        "import bigframes.bigquery as bf_bq\n",
        "import bigframes.ml.llm as bf_llm\n",
        "\n",
        "\n",
        "from google.cloud import bigquery\n",
        "from google.cloud import storage\n",
        "\n",
        "# Construct a BigQuery client object.\n",
        "client = bigquery.Client()\n",
        "\n",
        "import pandas as pd\n",
        "from IPython.display import Image, display\n",
        "from PIL import Image as PILImage\n",
        "import io\n",
        "\n",
        "import json\n",
        "from IPython.display import Markdown\n",
        "\n",
        "# Note: The project option is not required in all environments.\n",
        "# On BigQuery Studio, the project ID is automatically detected.\n",
        "bf.options.bigquery.project = PROJECT_ID\n",
        "bf.options.bigquery.location = REGION\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "iOFF9hrvs5WE"
      },
      "source": [
        "Partial ordering mode allows BigQuery DataFrames to push down many more row and column filters. On large clustered and partitioned tables, this can greatly reduce the number of bytes scanned and computation slots used. This [blog post](https://medium.com/google-cloud/introducing-partial-ordering-mode-for-bigquery-dataframes-bigframes-ec35841d95c0) goes over it in more detail."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "metadata": {
        "executionInfo": {
          "elapsed": 2,
          "status": "ok",
          "timestamp": 1742191620533,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "9Gil1Oaas7KA"
      },
      "outputs": [],
      "source": [
        "bf.options.bigquery.ordering_mode = \"partial\""
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XGaGyyZsooEW"
      },
      "source": [
        "If you want to reset the location of the created DataFrame or Series objects, reset the session by executing `bf.close_session()`. After that, you can reuse `bf.options.bigquery.location` to specify another location."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "v6FGschEowht"
      },
      "source": [
        "Data Input - read the data from a publicly available BigQuery dataset"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "executionInfo": {
          "elapsed": 468,
          "status": "ok",
          "timestamp": 1742192516923,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "zDSwoBo1CU3G",
        "outputId": "83edbc2f-5a23-407b-8890-f968eb31be44"
      },
      "outputs": [
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/IPython/core/interactiveshell.py:3553: UserWarning: \u001b[93mReading cached table from 2025-03-17 06:07:09.526507+00:00 to avoid\n",
            "incompatibilies with previous reads of this table. To read the latest\n",
            "version, set `use_cache=False` or close the current session with\n",
            "Session.close() or bigframes.pandas.close_session().\u001b[0m\n",
            "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
          ]
        }
      ],
      "source": [
        "publications = bf.read_gbq('patents-public-data.google_patents_research.publications')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "executionInfo": {
          "elapsed": 6697,
          "status": "ok",
          "timestamp": 1742192524632,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "tYDoaKgJChiq",
        "outputId": "9174da29-a051-4a99-e38f-6a2b09cfe4e9"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job 6f15ad71-cc7b-49c1-90e9-274bea7afbb9 is DONE. 477.4 GB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:6f15ad71-cc7b-49c1-90e9-274bea7afbb9&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "## create patents base table (subset of 10k out of ~110M records)\n",
        "\n",
        "keep = (publications.embedding_v1.str.len() > 0) & (publications.title.str.len() > 0) & (publications.abstract.str.len() > 30)\n",
        "\n",
        "## Choose 10000 random rows to analyze\n",
        "publications = publications[keep].peek(10000)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 556
        },
        "executionInfo": {
          "elapsed": 6,
          "status": "ok",
          "timestamp": 1742191801044,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "XmqdJInztzPl",
        "outputId": "ae05f3a6-edeb-423a-c061-c416717e1ec5"
      },
      "outputs": [
        {
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "repr_error": "Function 'unique' has no kernel matching input types (list<item: struct<code: string, inventive: bool, first: bool, tree: list<item: string> not null>>)",
              "type": "dataframe",
              "variable_name": "publications"
            },
            "text/html": [
              "\n",
              "  <div id=\"df-1209a5c8-dd2f-4582-8c75-54ce4f38e93a\" 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>publication_number</th>\n",
              "      <th>title</th>\n",
              "      <th>title_translated</th>\n",
              "      <th>abstract</th>\n",
              "      <th>abstract_translated</th>\n",
              "      <th>cpc</th>\n",
              "      <th>cpc_low</th>\n",
              "      <th>cpc_inventive_low</th>\n",
              "      <th>top_terms</th>\n",
              "      <th>similar</th>\n",
              "      <th>url</th>\n",
              "      <th>country</th>\n",
              "      <th>publication_description</th>\n",
              "      <th>cited_by</th>\n",
              "      <th>embedding_v1</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>AU-338190-S</td>\n",
              "      <td>Compressor wheel</td>\n",
              "      <td>False</td>\n",
              "      <td>Newness and distinctiveness is claimed in the ...</td>\n",
              "      <td>False</td>\n",
              "      <td>[]</td>\n",
              "      <td>[]</td>\n",
              "      <td>[]</td>\n",
              "      <td>['compressor wheel' 'newness' 'distinctiveness...</td>\n",
              "      <td>[{'publication_number': 'AU-338190-S', 'applic...</td>\n",
              "      <td>https://patents.google.com/patent/AU338190S</td>\n",
              "      <td>Australia</td>\n",
              "      <td>Design</td>\n",
              "      <td>[]</td>\n",
              "      <td>[ 5.2067090e-02 -1.5462303e-01 -1.3415462e-01 ...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>CN-100525651-C</td>\n",
              "      <td>Method for processing egg products</td>\n",
              "      <td>False</td>\n",
              "      <td>The invention discloses a processing method of...</td>\n",
              "      <td>False</td>\n",
              "      <td>[]</td>\n",
              "      <td>[]</td>\n",
              "      <td>[]</td>\n",
              "      <td>['egg' 'processing method' 'egg body' 'pack' '...</td>\n",
              "      <td>[{'publication_number': 'CN-101396133-B', 'app...</td>\n",
              "      <td>https://patents.google.com/patent/CN100525651C</td>\n",
              "      <td>China</td>\n",
              "      <td>Granted Patent</td>\n",
              "      <td>[]</td>\n",
              "      <td>[-0.05154578 -0.00437102  0.01365495 -0.168424...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>TW-I725505-B</td>\n",
              "      <td>Improved carbon molecular sieve adsorbent</td>\n",
              "      <td>False</td>\n",
              "      <td>Disclosed herein are rapid cycle pressure swin...</td>\n",
              "      <td>False</td>\n",
              "      <td>[{'code': 'B01D2253/116', 'inventive': False, ...</td>\n",
              "      <td>['B01D2253/116' 'B01D2253/10' 'B01D2253/00' 'B...</td>\n",
              "      <td>['B01D2253/116' 'B01D2253/10' 'B01D2253/00' 'B...</td>\n",
              "      <td>['swing adsorption' 'pressure swing' 'molecula...</td>\n",
              "      <td>[{'publication_number': 'EP-1867379-B1', 'appl...</td>\n",
              "      <td>https://patents.google.com/patent/TWI725505B</td>\n",
              "      <td>Taiwan</td>\n",
              "      <td>Granted Patent or patent of addition</td>\n",
              "      <td>[]</td>\n",
              "      <td>[ 0.0163008  -0.20972364  0.02052403 -0.003073...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>EP-0248026-B1</td>\n",
              "      <td>A system for supplying strip to a processing line</td>\n",
              "      <td>False</td>\n",
              "      <td>A system (10) for supplying strip material (S)...</td>\n",
              "      <td>False</td>\n",
              "      <td>[{'code': 'B65H2701/37', 'inventive': False, '...</td>\n",
              "      <td>['B65H2701/37' 'B65H2701/30' 'B65H2701/00' 'B6...</td>\n",
              "      <td>['B65H2701/37' 'B65H2701/30' 'B65H2701/00' 'B6...</td>\n",
              "      <td>['strip material' 'assembly' 'coil' 'take' 'pr...</td>\n",
              "      <td>[{'publication_number': 'EP-0248026-B1', 'appl...</td>\n",
              "      <td>https://patents.google.com/patent/EP0248026B1</td>\n",
              "      <td>European Patent Office</td>\n",
              "      <td>Granted patent</td>\n",
              "      <td>[]</td>\n",
              "      <td>[-0.04377723  0.04111805 -0.0929429   0.043924...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>MY-135762-A</td>\n",
              "      <td>Method for producing acrylic acid</td>\n",
              "      <td>False</td>\n",
              "      <td>A PROCESS FOR THE FRACTIONAL CONDENSATION OF A...</td>\n",
              "      <td>False</td>\n",
              "      <td>[{'code': 'C07C51/50', 'inventive': True, 'fir...</td>\n",
              "      <td>['C07C51/50' 'C07C51/42' 'C07C51/00' 'C07C' 'C...</td>\n",
              "      <td>['C07C51/50' 'C07C51/42' 'C07C51/00' 'C07C' 'C...</td>\n",
              "      <td>['acrylic acid' 'producing acrylic' 'stabilize...</td>\n",
              "      <td>[{'publication_number': 'SG-157371-A1', 'appli...</td>\n",
              "      <td>https://patents.google.com/patent/MY135762A</td>\n",
              "      <td>Malaysia</td>\n",
              "      <td>Granted patent / Utility model</td>\n",
              "      <td>[]</td>\n",
              "      <td>[ 0.10407669  0.01262973 -0.22623734 -0.171453...</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-1209a5c8-dd2f-4582-8c75-54ce4f38e93a')\"\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-1209a5c8-dd2f-4582-8c75-54ce4f38e93a 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-1209a5c8-dd2f-4582-8c75-54ce4f38e93a');\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-630cf4ef-355f-47bb-b1df-ce189e00558b\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-630cf4ef-355f-47bb-b1df-ce189e00558b')\"\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-630cf4ef-355f-47bb-b1df-ce189e00558b 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": [
              "  publication_number                                              title  \\\n",
              "0        AU-338190-S                                   Compressor wheel   \n",
              "1     CN-100525651-C                 Method for processing egg products   \n",
              "2       TW-I725505-B          Improved carbon molecular sieve adsorbent   \n",
              "3      EP-0248026-B1  A system for supplying strip to a processing line   \n",
              "4        MY-135762-A                  Method for producing acrylic acid   \n",
              "\n",
              "   title_translated                                           abstract  \\\n",
              "0             False  Newness and distinctiveness is claimed in the ...   \n",
              "1             False  The invention discloses a processing method of...   \n",
              "2             False  Disclosed herein are rapid cycle pressure swin...   \n",
              "3             False  A system (10) for supplying strip material (S)...   \n",
              "4             False  A PROCESS FOR THE FRACTIONAL CONDENSATION OF A...   \n",
              "\n",
              "   abstract_translated                                                cpc  \\\n",
              "0                False                                                 []   \n",
              "1                False                                                 []   \n",
              "2                False  [{'code': 'B01D2253/116', 'inventive': False, ...   \n",
              "3                False  [{'code': 'B65H2701/37', 'inventive': False, '...   \n",
              "4                False  [{'code': 'C07C51/50', 'inventive': True, 'fir...   \n",
              "\n",
              "                                             cpc_low  \\\n",
              "0                                                 []   \n",
              "1                                                 []   \n",
              "2  ['B01D2253/116' 'B01D2253/10' 'B01D2253/00' 'B...   \n",
              "3  ['B65H2701/37' 'B65H2701/30' 'B65H2701/00' 'B6...   \n",
              "4  ['C07C51/50' 'C07C51/42' 'C07C51/00' 'C07C' 'C...   \n",
              "\n",
              "                                   cpc_inventive_low  \\\n",
              "0                                                 []   \n",
              "1                                                 []   \n",
              "2  ['B01D2253/116' 'B01D2253/10' 'B01D2253/00' 'B...   \n",
              "3  ['B65H2701/37' 'B65H2701/30' 'B65H2701/00' 'B6...   \n",
              "4  ['C07C51/50' 'C07C51/42' 'C07C51/00' 'C07C' 'C...   \n",
              "\n",
              "                                           top_terms  \\\n",
              "0  ['compressor wheel' 'newness' 'distinctiveness...   \n",
              "1  ['egg' 'processing method' 'egg body' 'pack' '...   \n",
              "2  ['swing adsorption' 'pressure swing' 'molecula...   \n",
              "3  ['strip material' 'assembly' 'coil' 'take' 'pr...   \n",
              "4  ['acrylic acid' 'producing acrylic' 'stabilize...   \n",
              "\n",
              "                                             similar  \\\n",
              "0  [{'publication_number': 'AU-338190-S', 'applic...   \n",
              "1  [{'publication_number': 'CN-101396133-B', 'app...   \n",
              "2  [{'publication_number': 'EP-1867379-B1', 'appl...   \n",
              "3  [{'publication_number': 'EP-0248026-B1', 'appl...   \n",
              "4  [{'publication_number': 'SG-157371-A1', 'appli...   \n",
              "\n",
              "                                              url                 country  \\\n",
              "0     https://patents.google.com/patent/AU338190S               Australia   \n",
              "1  https://patents.google.com/patent/CN100525651C                   China   \n",
              "2    https://patents.google.com/patent/TWI725505B                  Taiwan   \n",
              "3   https://patents.google.com/patent/EP0248026B1  European Patent Office   \n",
              "4     https://patents.google.com/patent/MY135762A                Malaysia   \n",
              "\n",
              "                publication_description cited_by  \\\n",
              "0                                Design       []   \n",
              "1                        Granted Patent       []   \n",
              "2  Granted Patent or patent of addition       []   \n",
              "3                        Granted patent       []   \n",
              "4        Granted patent / Utility model       []   \n",
              "\n",
              "                                        embedding_v1  \n",
              "0  [ 5.2067090e-02 -1.5462303e-01 -1.3415462e-01 ...  \n",
              "1  [-0.05154578 -0.00437102  0.01365495 -0.168424...  \n",
              "2  [ 0.0163008  -0.20972364  0.02052403 -0.003073...  \n",
              "3  [-0.04377723  0.04111805 -0.0929429   0.043924...  \n",
              "4  [ 0.10407669  0.01262973 -0.22623734 -0.171453...  "
            ]
          },
          "execution_count": 11,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "## take a look at the sample dataset\n",
        "\n",
        "publications.head(5)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Wl2o-NYMoygb"
      },
      "source": [
        "Generate the text embeddings"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "executionInfo": {
          "elapsed": 4528,
          "status": "ok",
          "timestamp": 1742192047236,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "li38q8FzDDMu",
        "outputId": "b8c1bd38-b484-4f71-bd38-927c8677d0c5"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job 127fb090-1c9e-4d7a-acdd-86f077a87b07 is DONE. 0 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:127fb090-1c9e-4d7a-acdd-86f077a87b07&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "from bigframes.ml.llm import TextEmbeddingGenerator\n",
        "\n",
        "text_model = TextEmbeddingGenerator() # No connection id needed"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 19,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 139
        },
        "executionInfo": {
          "elapsed": 126632,
          "status": "ok",
          "timestamp": 1742192656608,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "b5HHZob_u61B",
        "outputId": "c9ecc5fd-5d11-4fd8-f59b-9dce4e12e371"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Load job b8079d70-7d99-4198-898f-2921915f305f is DONE. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:b8079d70-7d99-4198-898f-2921915f305f&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job 17338b11-420c-4d3d-bd55-0bba1247f705 is DONE. 8.9 MB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:17338b11-420c-4d3d-bd55-0bba1247f705&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/bigframes/core/array_value.py:114: PreviewWarning: \u001b[93mJSON column interpretation as a custom PyArrow extention in\n",
            "`db_dtypes` is a preview feature and subject to change.\u001b[0m\n",
            "  warnings.warn(msg, bfe.PreviewWarning)\n"
          ]
        },
        {
          "data": {
            "text/html": [
              "Query job ebf3eb36-3199-4551-ad07-5fa5abb200be is DONE. 20.0 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:ebf3eb36-3199-4551-ad07-5fa5abb200be&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job 9e9c5aae-9045-4750-a34e-c98493369a90 is DONE. 20.0 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:9e9c5aae-9045-4750-a34e-c98493369a90&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "## rename abstract column to content as the desired column on which embedding will be generated\n",
        "publications = publications[[\"publication_number\", \"title\", \"abstract\"]].rename(columns={'abstract': 'content'})\n",
        "\n",
        "## generate the embeddings\n",
        "## takes ~2-3 mins to run\n",
        "embedding = text_model.predict(publications)[[\"publication_number\", \"title\", \"content\", \"ml_generate_embedding_result\",\"ml_generate_embedding_status\"]]\n",
        "\n",
        "## filter out rows where the embedding generation failed. the embedding status value is empty if the embedding generation was successful\n",
        "embedding = embedding[~embedding[\"ml_generate_embedding_status\"].isnull()]\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 464
        },
        "executionInfo": {
          "elapsed": 6715,
          "status": "ok",
          "timestamp": 1742192727525,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "OIT5FbqAwqG5",
        "outputId": "d04c994a-a0c8-44b0-e897-d871036eeb1f"
      },
      "outputs": [
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/bigframes/core/array_value.py:238: AmbiguousWindowWarning: \u001b[93mWindow ordering may be ambiguous, this can cause unstable results.\u001b[0m\n",
            "  warnings.warn(msg, bfe.AmbiguousWindowWarning)\n",
            "/usr/local/lib/python3.10/dist-packages/bigframes/core/array_value.py:262: AmbiguousWindowWarning: \u001b[93mWindow ordering may be ambiguous, this can cause unstable results.\u001b[0m\n",
            "  warnings.warn(msg, category=bfe.AmbiguousWindowWarning)\n"
          ]
        },
        {
          "data": {
            "text/html": [
              "Query job 1bc3517f-df67-456c-8d31-14a6432b8629 is DONE. 70.4 MB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:1bc3517f-df67-456c-8d31-14a6432b8629&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job ae92602b-0eab-437f-a02d-102a4defa99a is DONE. 31.3 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:ae92602b-0eab-437f-a02d-102a4defa99a&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "<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>publication_number</th>\n",
              "      <th>title</th>\n",
              "      <th>content</th>\n",
              "      <th>ml_generate_embedding_result</th>\n",
              "      <th>ml_generate_embedding_status</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>5753</th>\n",
              "      <td>HN-1996000102-A</td>\n",
              "      <td>NEW PESTICIDES</td>\n",
              "      <td>THE PRESENT INVENTION REFERS TO</td>\n",
              "      <td>[-0.02709213  0.0366395   0.03931784 -0.003942...</td>\n",
              "      <td></td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8115</th>\n",
              "      <td>AU-325874-S</td>\n",
              "      <td>Baby sling</td>\n",
              "      <td>Adjustable baby sling with velcro.</td>\n",
              "      <td>[ 6.44167811e-02 -2.01051459e-02 -3.39564607e-...</td>\n",
              "      <td></td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5415</th>\n",
              "      <td>AU-2016256863-A1</td>\n",
              "      <td>Microbial compositions and methods for denitri...</td>\n",
              "      <td>The present invention provides compositions an...</td>\n",
              "      <td>[-5.90537786e-02  2.38401629e-03  7.22754598e-...</td>\n",
              "      <td></td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8886</th>\n",
              "      <td>FR-2368509-A1</td>\n",
              "      <td>NEW DEODORANTS OR FRESHENERS AND COMPOSITIONS ...</td>\n",
              "      <td>Polyanionic polyamide salts comprising a conca...</td>\n",
              "      <td>[-3.44522446e-02  5.64815439e-02 -1.35829514e-...</td>\n",
              "      <td></td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5661</th>\n",
              "      <td>US-2006051255-A1</td>\n",
              "      <td>Gas generator</td>\n",
              "      <td>A gas generator insulated by a vacuum-jacket v...</td>\n",
              "      <td>[-1.50892800e-02  6.56989636e-03  2.34969519e-...</td>\n",
              "      <td></td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 5 columns</p>\n",
              "</div>[5 rows x 5 columns in total]"
            ],
            "text/plain": [
              "     publication_number                                              title  \\\n",
              "5753    HN-1996000102-A                                     NEW PESTICIDES   \n",
              "8115        AU-325874-S                                         Baby sling   \n",
              "5415   AU-2016256863-A1  Microbial compositions and methods for denitri...   \n",
              "8886      FR-2368509-A1  NEW DEODORANTS OR FRESHENERS AND COMPOSITIONS ...   \n",
              "5661   US-2006051255-A1                                      Gas generator   \n",
              "\n",
              "                                                content  \\\n",
              "5753                    THE PRESENT INVENTION REFERS TO   \n",
              "8115                 Adjustable baby sling with velcro.   \n",
              "5415  The present invention provides compositions an...   \n",
              "8886  Polyanionic polyamide salts comprising a conca...   \n",
              "5661  A gas generator insulated by a vacuum-jacket v...   \n",
              "\n",
              "                           ml_generate_embedding_result  \\\n",
              "5753  [-0.02709213  0.0366395   0.03931784 -0.003942...   \n",
              "8115  [ 6.44167811e-02 -2.01051459e-02 -3.39564607e-...   \n",
              "5415  [-5.90537786e-02  2.38401629e-03  7.22754598e-...   \n",
              "8886  [-3.44522446e-02  5.64815439e-02 -1.35829514e-...   \n",
              "5661  [-1.50892800e-02  6.56989636e-03  2.34969519e-...   \n",
              "\n",
              "     ml_generate_embedding_status  \n",
              "5753                               \n",
              "8115                               \n",
              "5415                               \n",
              "8886                               \n",
              "5661                               \n",
              "\n",
              "[5 rows x 5 columns]"
            ]
          },
          "execution_count": 20,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "embedding.head(5)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 53
        },
        "executionInfo": {
          "elapsed": 6590,
          "status": "ok",
          "timestamp": 1742192833667,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "GP3ZqX_bxLGq",
        "outputId": "fb823ea2-e47c-415f-84d4-543dd3291e15"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job 7370fb69-9589-4a9a-a5cf-7f7c8d50c53c is DONE. 70.3 MB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:7370fb69-9589-4a9a-a5cf-7f7c8d50c53c&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            },
            "text/plain": [
              "'bqml_llm_trial.patent_embedding_BF-n'"
            ]
          },
          "execution_count": 21,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# store embeddings in a BQ table\n",
        "DATASET_ID = \"\"  # @param {type:\"string\"}\n",
        "TEXT_EMBEDDING_TABLE_ID = \"\" # @param {type:\"string\"}\n",
        "embedding.to_gbq(f\"{DATASET_ID}.{TEXT_EMBEDDING_TABLE_ID}\", if_exists='replace')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "OUZ3NNbzo1Tb"
      },
      "source": [
        "## Step 2: Indexing and Similarity Search"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mvJH2FCmynMm"
      },
      "source": [
        "### [Create a Vector Index](https://cloud.google.com/python/docs/reference/bigframes/latest/bigframes.bigquery#bigframes_bigquery_create_vector_index) using BigFrames\n",
        "\n",
        "\n",
        "**Index Type**\n",
        "\n",
        "The algorithm to use to build the vector index.\n",
        "The supported values are IVF and TREE_AH."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 22,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "executionInfo": {
          "elapsed": 3882,
          "status": "ok",
          "timestamp": 1742193028877,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "6SBVdv6gyU5A",
        "outputId": "6583e113-de27-4b44-972d-c1cc061e3c76"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job 775f872d-ea2d-48f3-8b65-a85ed573dac0 is DONE. 61.4 MB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:775f872d-ea2d-48f3-8b65-a85ed573dac0&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "## create vector index (note only works of tables >5000 rows)\n",
        "\n",
        "bf_bq.create_vector_index(\n",
        "    table_id = f\"{DATASET_ID}.{TEXT_EMBEDDING_TABLE_ID}\",\n",
        "    column_name = \"ml_generate_embedding_result\",\n",
        "    replace= True,\n",
        "    index_name = \"bf_python_index\",\n",
        "    distance_type=\"cosine\",\n",
        "    index_type= \"ivf\"\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bo8mBbRLzCOA"
      },
      "source": [
        "### Vector Search (semantic search) using Vector Index\n",
        "\n",
        "ANN (approx nearest neighbor) search using the created vector index"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "metadata": {
        "executionInfo": {
          "elapsed": 639,
          "status": "ok",
          "timestamp": 1742194606771,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "v19BJm_wzPdZ"
      },
      "outputs": [],
      "source": [
        "## Set variable for vector search\n",
        "\n",
        "TEXT_SEARCH_STRING = \"Chip assemblies employing solder bonds to back-side lands including an electrolytic nickel layer\"  ## replace with whatever search string you want to use for the vector search\n",
        "FRACTION_LISTS_TO_SEARCH = 0.01"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 24,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 121
        },
        "executionInfo": {
          "elapsed": 6927,
          "status": "ok",
          "timestamp": 1742194625774,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "pAQY1ejpzPap",
        "outputId": "485698ad-ac6e-4c93-844e-5d0f30aff13a"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job 3a352b3b-b968-4347-80fe-6a9ef9045358 is DONE. 0 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:3a352b3b-b968-4347-80fe-6a9ef9045358&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/bigframes/core/array_value.py:114: PreviewWarning: \u001b[93mJSON column interpretation as a custom PyArrow extention in\n",
            "`db_dtypes` is a preview feature and subject to change.\u001b[0m\n",
            "  warnings.warn(msg, bfe.PreviewWarning)\n"
          ]
        },
        {
          "data": {
            "text/html": [
              "Query job 0e6d609b-9818-45fe-b26d-7247722bbea4 is DONE. 2 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:0e6d609b-9818-45fe-b26d-7247722bbea4&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job 66602cee-78a8-4955-96fb-6a2d603d5d7d is DONE. 2 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:66602cee-78a8-4955-96fb-6a2d603d5d7d&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "# convert search string to dataframe\n",
        "TEXT_SEARCH_DF = bf.DataFrame([TEXT_SEARCH_STRING], columns=['search_string'])\n",
        "\n",
        "#generate embedding of search query\n",
        "search_query = bf.DataFrame(text_model.predict(TEXT_SEARCH_DF))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 25,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 104
        },
        "executionInfo": {
          "elapsed": 5110,
          "status": "ok",
          "timestamp": 1742194670801,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "sx0AGAdn5FYX",
        "outputId": "551ebac3-594f-4303-ca97-5301dfee72bb"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job 4768061f-d5a6-4638-8396-5c15a098ad7b is RUNNING. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:4768061f-d5a6-4638-8396-5c15a098ad7b&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job e6175f9a-6bbd-4cbe-967b-b04421b33b02 is DONE. 132.7 MB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:e6175f9a-6bbd-4cbe-967b-b04421b33b02&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/bigframes/core/array_value.py:114: PreviewWarning: \u001b[93mJSON column interpretation as a custom PyArrow extention in\n",
            "`db_dtypes` is a preview feature and subject to change.\u001b[0m\n",
            "  warnings.warn(msg, bfe.PreviewWarning)\n"
          ]
        }
      ],
      "source": [
        "## search the base table for the user's query\n",
        "\n",
        "vector_search_results = bf_bq.vector_search(\n",
        "                  base_table=f\"{DATASET_ID}.{TEXT_EMBEDDING_TABLE_ID}\",\n",
        "                  column_to_search=\"ml_generate_embedding_result\",\n",
        "                  query=search_query,\n",
        "                  distance_type=\"COSINE\",\n",
        "                  query_column_to_search=\"ml_generate_embedding_result\",\n",
        "                  top_k=5)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 27,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 270
        },
        "executionInfo": {
          "elapsed": 3511,
          "status": "ok",
          "timestamp": 1742195090670,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "px1v4iJM5L0c",
        "outputId": "d107b6e3-a362-42db-c0c2-084d02acd244"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job 61c1138d-f4da-4971-a7dd-aa7150bafe50 is DONE. 0 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:61c1138d-f4da-4971-a7dd-aa7150bafe50&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "<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>query</th>\n",
              "      <th>publication_number</th>\n",
              "      <th>title (relevant match)</th>\n",
              "      <th>abstract (relevant match)</th>\n",
              "      <th>distance</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Chip assemblies employing solder bonds to back...</td>\n",
              "      <td>KR-102569815-B1</td>\n",
              "      <td>electronic device package</td>\n",
              "      <td>An electronic device package technology is dis...</td>\n",
              "      <td>0.357673</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Chip assemblies employing solder bonds to back...</td>\n",
              "      <td>US-8962389-B2</td>\n",
              "      <td>Microelectronic packages including patterned d...</td>\n",
              "      <td>Embodiments of microelectronic packages and me...</td>\n",
              "      <td>0.344263</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Chip assemblies employing solder bonds to back...</td>\n",
              "      <td>TW-I256279-B</td>\n",
              "      <td>Substrate for electrical device and methods of...</td>\n",
              "      <td>Substrate for electrical devices and methods o...</td>\n",
              "      <td>0.3687</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Chip assemblies employing solder bonds to back...</td>\n",
              "      <td>US-2005230147-A1</td>\n",
              "      <td>Wiring board, and electronic device with an el...</td>\n",
              "      <td>An electronic device is mounted on a wiring bo...</td>\n",
              "      <td>0.304293</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Chip assemblies employing solder bonds to back...</td>\n",
              "      <td>US-6686652-B1</td>\n",
              "      <td>Locking lead tips and die attach pad for a lea...</td>\n",
              "      <td>An assembly and method suitable for use in pac...</td>\n",
              "      <td>0.364334</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 5 columns</p>\n",
              "</div>[5 rows x 5 columns in total]"
            ],
            "text/plain": [
              "                                               query publication_number  \\\n",
              "0  Chip assemblies employing solder bonds to back...    KR-102569815-B1   \n",
              "0  Chip assemblies employing solder bonds to back...      US-8962389-B2   \n",
              "0  Chip assemblies employing solder bonds to back...       TW-I256279-B   \n",
              "0  Chip assemblies employing solder bonds to back...   US-2005230147-A1   \n",
              "0  Chip assemblies employing solder bonds to back...      US-6686652-B1   \n",
              "\n",
              "                              title (relevant match)  \\\n",
              "0                          electronic device package   \n",
              "0  Microelectronic packages including patterned d...   \n",
              "0  Substrate for electrical device and methods of...   \n",
              "0  Wiring board, and electronic device with an el...   \n",
              "0  Locking lead tips and die attach pad for a lea...   \n",
              "\n",
              "                           abstract (relevant match)  distance  \n",
              "0  An electronic device package technology is dis...  0.357673  \n",
              "0  Embodiments of microelectronic packages and me...  0.344263  \n",
              "0  Substrate for electrical devices and methods o...    0.3687  \n",
              "0  An electronic device is mounted on a wiring bo...  0.304293  \n",
              "0  An assembly and method suitable for use in pac...  0.364334  \n",
              "\n",
              "[5 rows x 5 columns]"
            ]
          },
          "execution_count": 27,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "## View the returned results based on simalirity with the user's query\n",
        "\n",
        "vector_search_results[['content', 'publication_number',\n",
        "       'title', 'content_1', 'distance']].rename(columns={'content': 'query', 'content_1':'abstract (relevant match)' , 'title':'title (relevant match)'})"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 28,
      "metadata": {
        "executionInfo": {
          "elapsed": 1622,
          "status": "ok",
          "timestamp": 1742195139318,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "5fb_O-ne5cvH"
      },
      "outputs": [],
      "source": [
        "## Brute force result (for comparison)\n",
        "\n",
        "\n",
        "brute_force_result = bf_bq.vector_search(\n",
        "                  table_id = f\"{DATASET_ID}.{TEXT_EMBEDDING_TABLE_ID}\",\n",
        "                  column_to_search=\"ml_generate_embedding_result\",\n",
        "                  query=search_query,\n",
        "                  top_k=5,\n",
        "                  distance_type=\"COSINE\",\n",
        "                  use_brute_force=True)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "21rNsFMHo8hO"
      },
      "source": [
        "## Step 3: AI-Powered Summarization with Retrieval Augmented Generation (RAG)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "K3pIQrzB7T_G"
      },
      "source": [
        "Patent documents can be dense and time-consuming to digest. AI-Powered Patent Summarization utilizes Retrieval Augmented Generation (RAG) to streamline this process. By retrieving relevant patent information through vector search and then synthesizing it with a large language model, we can generate concise, human-readable summaries, saving valuable time and effort. The code sample below walks through how to set this up continuing with the same user query as the previous use case."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 37,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "executionInfo": {
          "elapsed": 4827,
          "status": "ok",
          "timestamp": 1742195565658,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "jb5rueqU7T5J",
        "outputId": "43732836-ebae-4fb3-b28e-bfea51146c72"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job 093debfb-08f1-4bba-8b39-c3da575793a4 is DONE. 0 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:093debfb-08f1-4bba-8b39-c3da575793a4&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        }
      ],
      "source": [
        "## gemini model\n",
        "\n",
        "llm_model = bf_llm.GeminiTextGenerator(model_name = \"gemini-1.5-flash-002\") ## replace with other model as needed"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "41e12JTf70sr"
      },
      "source": [
        "We will use the same user query from Section 2, and pass the list of abstracts returned by the vector search into the prompt for the RAG application"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 35,
      "metadata": {
        "executionInfo": {
          "elapsed": 1474,
          "status": "ok",
          "timestamp": 1742195536109,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "EyP-ZFJK8h-2"
      },
      "outputs": [],
      "source": [
        "TEMPERATURE = 0.4"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 34,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 72
        },
        "executionInfo": {
          "elapsed": 3371,
          "status": "ok",
          "timestamp": 1742195421813,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "eP99R6SV7Tug",
        "outputId": "c34bc931-5be8-410e-ac1f-604df31ef533"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Query job f77bbae5-ea1f-4ba9-92bc-bfc7bc474cd9 is DONE. 0 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:f77bbae5-ea1f-4ba9-92bc-bfc7bc474cd9&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "['{\"abstract\": \"Substrate for electrical devices and methods of fabricating such substrate are disclosed. An embodiment for an electrical device with substrate comprised of a chip having an active surface; a substrate being coupled with the chip; and a plurality of conductive wires (bumps) electrically connecting the chip to the substrate. In additionally, the present invention of the substrate for electrical devices may be comprised of an adhesive mean or a submember as required, and furthermore, by mean of using substrate, the present invention may be capable of affording a number of advantages, it is possible to include a thinner electrical device thickness, enhanced reliability, and a decreased cost in production.\"}', '{\"abstract\": \"An electronic device is mounted on a wiring board, which includes: a substrate having through holes, and lands extending on surfaces of the substrate and adjacent to openings of the through holes. Further, at least one coating layer is provided, which coats at least one part of an outer peripheral region of the at least one land, in order to cause that the at least one part is separated from a lead-less solder, thereby preventing any peel of the land from the surface of the substrate.\"}', '{\"abstract\": \"An assembly and method suitable for use in packaging integrated circuits including a support substrate for supporting an integrated circuit die embedded in a molded encapsulating cap. The substrate includes a conductive die attach pad adapted to be molded into the encapsulating cap. The pad includes an interior facing support surface and a spaced-apart exterior facing exposed surface defined by a peripheral edge. The support surface is adapted to support the embedded die, while the exposed surface is to be exposed from the encapsulating cap. The attach pad further includes a locking ledge portion extending outward peripherally beyond at least a portion of the exposed surface peripheral edge. This ledge is adapted to be subtended in the encapsulating cap in a manner substantially preventing a pull-out of the attach pad in a direction away from the encapsulating cap.\"}', '{\"abstract\": \"Embodiments of microelectronic packages and methods for fabricating microelectronic packages are provided. In one embodiment, the fabrication method includes printing a patterned die attach material onto the backside of a wafer including an array of non-singulated microelectronic die each having an interior keep-out area, such as a central keep-out area. The die attach material, such as a B-stage epoxy, is printed onto the wafer in a predetermined pattern such that the die attach material does not encroaching into the interior keep-out areas. The wafer is singulated to produce singulated microelectronic die each including a layer of die attach material. The singulated microelectronic die are then placed onto leadframes or other package substrates with the die attach material contacting the package substrates. The layer of die attach material is then fully cured to adhere an outer peripheral portion of the singulated microelectronic die to its package substrate.\"}', '{\"abstract\": \"An electronic device package technology is disclosed. An electronic device package may include a substrate. The electronic device package may also include a first electronic component and a second electronic component in a stacked configuration. Each of the first electronic component and the second electronic component can include electrical interconnections exposed toward the substrate. The electronic device package may further include a mold compound encapsulating the first electronic component and the second electronic component. Additionally, the electronic device package can include electrically conductive posts extending through the mold compound between the electrical interconnection of at least one of the first electronic component and the second electronic component and the substrate. Related systems and methods are also disclosed.\"}']\n"
          ]
        }
      ],
      "source": [
        "# Extract strings into a list of JSON strings\n",
        "json_strings = [json.dumps({'abstract': s}) for s in vector_search_results['content_1']]\n",
        "ALL_ABSTRACTS = json_strings\n",
        "\n",
        "# Print the result (optional)\n",
        "print(ALL_ABSTRACTS)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 41,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "collapsed": true,
        "executionInfo": {
          "elapsed": 1620,
          "status": "ok",
          "timestamp": 1742195587180,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "kSNSi1GV8OAD",
        "outputId": "37fbc822-1160-4fbd-c7d6-ecb4a16db394"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\n",
            "You are an expert patent analyst. I will provide you the abstracts of the top 5 patents in json format retrieved by a vector search based on a user's query.\n",
            "Your task is to analyze these abstracts and generate a concise, coherent summary that encapsulates the core innovations and concepts shared among them.\n",
            "\n",
            "In your output, share the original user query.\n",
            "Then output the concise, coherent summary that encapsulates the core innovations and concepts shared among the top 5 abstracts. The heading for this section should\n",
            "be : Summary of the top 5 abstracts that are semantically closest to the user query.\n",
            "\n",
            "User Query: Chip assemblies employing solder bonds to back-side lands including an electrolytic nickel layer\n",
            "Top 5 abstracts: ['{\"abstract\": \"Substrate for electrical devices and methods of fabricating such substrate are disclosed. An embodiment for an electrical device with substrate comprised of a chip having an active surface; a substrate being coupled with the chip; and a plurality of conductive wires (bumps) electrically connecting the chip to the substrate. In additionally, the present invention of the substrate for electrical devices may be comprised of an adhesive mean or a submember as required, and furthermore, by mean of using substrate, the present invention may be capable of affording a number of advantages, it is possible to include a thinner electrical device thickness, enhanced reliability, and a decreased cost in production.\"}', '{\"abstract\": \"An electronic device is mounted on a wiring board, which includes: a substrate having through holes, and lands extending on surfaces of the substrate and adjacent to openings of the through holes. Further, at least one coating layer is provided, which coats at least one part of an outer peripheral region of the at least one land, in order to cause that the at least one part is separated from a lead-less solder, thereby preventing any peel of the land from the surface of the substrate.\"}', '{\"abstract\": \"An assembly and method suitable for use in packaging integrated circuits including a support substrate for supporting an integrated circuit die embedded in a molded encapsulating cap. The substrate includes a conductive die attach pad adapted to be molded into the encapsulating cap. The pad includes an interior facing support surface and a spaced-apart exterior facing exposed surface defined by a peripheral edge. The support surface is adapted to support the embedded die, while the exposed surface is to be exposed from the encapsulating cap. The attach pad further includes a locking ledge portion extending outward peripherally beyond at least a portion of the exposed surface peripheral edge. This ledge is adapted to be subtended in the encapsulating cap in a manner substantially preventing a pull-out of the attach pad in a direction away from the encapsulating cap.\"}', '{\"abstract\": \"Embodiments of microelectronic packages and methods for fabricating microelectronic packages are provided. In one embodiment, the fabrication method includes printing a patterned die attach material onto the backside of a wafer including an array of non-singulated microelectronic die each having an interior keep-out area, such as a central keep-out area. The die attach material, such as a B-stage epoxy, is printed onto the wafer in a predetermined pattern such that the die attach material does not encroaching into the interior keep-out areas. The wafer is singulated to produce singulated microelectronic die each including a layer of die attach material. The singulated microelectronic die are then placed onto leadframes or other package substrates with the die attach material contacting the package substrates. The layer of die attach material is then fully cured to adhere an outer peripheral portion of the singulated microelectronic die to its package substrate.\"}', '{\"abstract\": \"An electronic device package technology is disclosed. An electronic device package may include a substrate. The electronic device package may also include a first electronic component and a second electronic component in a stacked configuration. Each of the first electronic component and the second electronic component can include electrical interconnections exposed toward the substrate. The electronic device package may further include a mold compound encapsulating the first electronic component and the second electronic component. Additionally, the electronic device package can include electrically conductive posts extending through the mold compound between the electrical interconnection of at least one of the first electronic component and the second electronic component and the substrate. Related systems and methods are also disclosed.\"}']\n",
            "\n",
            "Instructions:\n",
            "\n",
            "Focus on identifying the common themes and key technological advancements described in the abstracts.\n",
            "Synthesize the information into a clear and concise summary, approximately 150-200 words.\n",
            "Avoid simply copying phrases from the abstracts. Instead, aim to provide a cohesive overview of the shared concepts.\n",
            "Highlight the potential applications and benefits of the described inventions.\n",
            "Maintain a professional and objective tone.\n",
            "Do not mention the individual patents by number, focus on summarizing the shared concepts.\n",
            "\n"
          ]
        }
      ],
      "source": [
        "## Setup the LLM prompt\n",
        "\n",
        "prompt = f\"\"\"\n",
        "You are an expert patent analyst. I will provide you the abstracts of the top 5 patents in json format retrieved by a vector search based on a user's query.\n",
        "Your task is to analyze these abstracts and generate a concise, coherent summary that encapsulates the core innovations and concepts shared among them.\n",
        "\n",
        "In your output, share the original user query.\n",
        "Then output the concise, coherent summary that encapsulates the core innovations and concepts shared among the top 5 abstracts. The heading for this section should\n",
        "be : Summary of the top 5 abstracts that are semantically closest to the user query.\n",
        "\n",
        "User Query: {TEXT_SEARCH_STRING}\n",
        "Top 5 abstracts: {ALL_ABSTRACTS}\n",
        "\n",
        "Instructions:\n",
        "\n",
        "Focus on identifying the common themes and key technological advancements described in the abstracts.\n",
        "Synthesize the information into a clear and concise summary, approximately 150-200 words.\n",
        "Avoid simply copying phrases from the abstracts. Instead, aim to provide a cohesive overview of the shared concepts.\n",
        "Highlight the potential applications and benefits of the described inventions.\n",
        "Maintain a professional and objective tone.\n",
        "Do not mention the individual patents by number, focus on summarizing the shared concepts.\n",
        "\"\"\"\n",
        "\n",
        "print(prompt)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 38,
      "metadata": {
        "executionInfo": {
          "elapsed": 1,
          "status": "ok",
          "timestamp": 1742195567707,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "njiQdfkT8Y7V"
      },
      "outputs": [],
      "source": [
        "## Define a function that will take the input propmpt and run the LLM\n",
        "\n",
        "def predict(prompt: str, temperature: float = TEMPERATURE) -> str:\n",
        "    # Create dataframe\n",
        "    input = bf.DataFrame(\n",
        "        {\n",
        "            \"prompt\": [prompt],\n",
        "        }\n",
        "    )\n",
        "\n",
        "    # Return response\n",
        "    return llm_model.predict(input, temperature=temperature).ml_generate_text_llm_result.iloc[0]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 42,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 426
        },
        "executionInfo": {
          "elapsed": 14425,
          "status": "ok",
          "timestamp": 1742195608280,
          "user": {
            "displayName": "",
            "userId": ""
          },
          "user_tz": -480
        },
        "id": "OYYkVYbs8Y0P",
        "outputId": "def839e3-3dee-4320-9cb5-cac855ddea6b"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "Load job 53681d07-ddc6-4f62-a170-ac5cafc1c7af is DONE. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:53681d07-ddc6-4f62-a170-ac5cafc1c7af&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job 259907b0-1bae-402f-be4f-d45e478832f1 is DONE. 5.3 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:259907b0-1bae-402f-be4f-d45e478832f1&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/bigframes/core/array_value.py:114: PreviewWarning: \u001b[93mJSON column interpretation as a custom PyArrow extention in\n",
            "`db_dtypes` is a preview feature and subject to change.\u001b[0m\n",
            "  warnings.warn(msg, bfe.PreviewWarning)\n"
          ]
        },
        {
          "data": {
            "text/html": [
              "Query job f3e6dca3-7674-41f6-a4ba-0daec387e25e is DONE. 2 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:f3e6dca3-7674-41f6-a4ba-0daec387e25e&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/html": [
              "Query job e06bc512-d746-433b-b431-3e7426b6cd9c is DONE. 2 Bytes processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:e06bc512-d746-433b-b431-3e7426b6cd9c&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "name": "stderr",
          "output_type": "stream",
          "text": [
            "/usr/local/lib/python3.10/dist-packages/bigframes/core/array_value.py:238: AmbiguousWindowWarning: \u001b[93mWindow ordering may be ambiguous, this can cause unstable results.\u001b[0m\n",
            "  warnings.warn(msg, bfe.AmbiguousWindowWarning)\n"
          ]
        },
        {
          "data": {
            "text/html": [
              "Query job bcf9e83c-a420-4282-86b1-d005244c97f2 is DONE. 1.5 kB processed. <a target=\"_blank\" href=\"https://console.cloud.google.com/bigquery?project=bqml-demo-sudipto&j=bq:US:bcf9e83c-a420-4282-86b1-d005244c97f2&page=queryresults\">Open Job</a>"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {},
          "output_type": "display_data"
        },
        {
          "data": {
            "text/markdown": [
              "User Query: Chip assemblies employing solder bonds to back-side lands including an electrolytic nickel layer\n",
              "\n",
              "Summary of the top 5 abstracts that are semantically closest to the user query.\n",
              "\n",
              "The top five patent abstracts describe advancements in microelectronic packaging, focusing on improved chip-to-substrate interconnection and enhanced reliability.  A common thread is the development of novel substrate designs and assembly methods to facilitate robust electrical connections.  Several abstracts highlight techniques for creating reliable connections between chips and substrates, emphasizing the use of conductive materials and adhesives to ensure strong and durable bonds.  These methods aim to improve the overall reliability and performance of electronic devices.  The innovations include improved techniques for preventing delamination or peeling of conductive lands, leading to more robust assemblies.  The use of encapsulating materials and specialized die-attach methods are also prominent, suggesting a focus on protecting the chip and its connections from environmental factors.  These advancements collectively contribute to the creation of thinner, more reliable, and cost-effective electronic devices, with applications spanning various consumer electronics and other industries.  While the abstracts don't explicitly mention electrolytic nickel layers, the focus on improved solder bond reliability and substrate design suggests that such a layer could be a complementary enhancement to the described technologies.\n"
            ],
            "text/plain": [
              "<IPython.core.display.Markdown object>"
            ]
          },
          "execution_count": 42,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Invoke LLM with prompt\n",
        "response = predict(prompt, temperature = TEMPERATURE)\n",
        "\n",
        "# Print results as Markdown\n",
        "Markdown(response)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sy82XLDfooEb"
      },
      "source": [
        "# Summary and next steps\n",
        "\n",
        "Ready to dive deeper and explore the endless possibilities? Start building your own vector search applications with BigFrames and BigQuery today! Check out our [documentation](https://cloud.google.com/python/docs/reference/bigframes/latest/bigframes.bigquery#bigframes_bigquery_vector_search), explore our sample [notebooks](https://github.com/googleapis/python-bigquery-dataframes/tree/main/notebooks), and unleash the power of vector analytics on your data.\n",
        "The BigFrames team would also  love to hear from you. If you would like to reach out, please send an email to: bigframes-feedback@google.com or by filing an issue at the [open source BigFrames repository](https://github.com/googleapis/python-bigquery-dataframes/issues). To receive updates about BigFrames, subscribe to the BigFrames email list."
      ]
    }
  ],
  "metadata": {
    "colab": {
      "name": "bq_dataframes_llm_kmeans",
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3 (ipykernel)",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.10.9"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}