wehub-resource-sync
1033 lines
51 KiB
Plaintext
1033 lines
51 KiB
Plaintext
{
|
||
"cells": [
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "cMQCs0oQf5Jo"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"# Copyright 2026 Google LLC\n",
|
||
"#\n",
|
||
"# Licensed under the Apache License, Version 2.0 (the \"License\");\n",
|
||
"# you may not use this file except in compliance with the License.\n",
|
||
"# You may obtain a copy of the License at\n",
|
||
"#\n",
|
||
"# https://www.apache.org/licenses/LICENSE-2.0\n",
|
||
"#\n",
|
||
"# Unless required by applicable law or agreed to in writing, software\n",
|
||
"# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
|
||
"# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
|
||
"# See the License for the specific language governing permissions and\n",
|
||
"# limitations under the License."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "iR0jdheRGG89"
|
||
},
|
||
"source": [
|
||
"# Semantic Analysis in BigQuery with AI Functions"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "Lt2rldOmcJg9"
|
||
},
|
||
"source": [
|
||
"<table align=\"left\">\n",
|
||
" <td style=\"text-align: center\">\n",
|
||
" <a href=\"https://colab.research.google.com/github/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\">\n",
|
||
" <img width=\"32px\" src=\"https://www.gstatic.com/pantheon/images/bigquery/welcome_page/colab-logo.svg\" alt=\"Google Colaboratory logo\"><br> Open in Colab\n",
|
||
" </a>\n",
|
||
" </td>\n",
|
||
" <td style=\"text-align: center\">\n",
|
||
" <a href=\"https://console.cloud.google.com/agent-platform/colab/import/https:%2F%2Fraw.githubusercontent.com%2FGoogleCloudPlatform%2Fgenerative-ai%2Fmain%2Fgemini%2Fuse-cases%2Fapplying-llms-to-data%2Fbigquery_ai_operators.ipynb\">\n",
|
||
" <img width=\"32px\" src=\"https://lh3.googleusercontent.com/JmcxdQi-qOpctIvWKgPtrzZdJJK-J3sWE1RsfjZNwshCFgE_9fULcNpuXYTilIR2hjwN\" alt=\"Google Cloud Colab Enterprise logo\"><br> Open in Colab Enterprise\n",
|
||
" </a>\n",
|
||
" </td>\n",
|
||
" <td style=\"text-align: center\">\n",
|
||
" <a href=\"https://console.cloud.google.com/agent-platform/workbench/instances?download_url=https://raw.githubusercontent.com/GoogleCloudPlatform/generative-ai/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\">\n",
|
||
" <img width=\"32px\" src=\"https://storage.googleapis.com/github-repo/workbench-icon.svg\" alt=\"Workbench logo\"><br> Open in Workbench\n",
|
||
" </a>\n",
|
||
" </td>\n",
|
||
" <td style=\"text-align: center\">\n",
|
||
" <a href=\"https://console.cloud.google.com/bigquery/import?url=https://github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\">\n",
|
||
" <img width=\"32px\" src=\"https://www.gstatic.com/images/branding/gcpiconscolors/bigquery/v1/32px.svg\" alt=\"BigQuery Studio logo\"><br> Open in BigQuery Studio\n",
|
||
" </a>\n",
|
||
" </td>\n",
|
||
" <td style=\"text-align: center\">\n",
|
||
" <a href=\"https://github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\">\n",
|
||
" <img width=\"32px\" src=\"https://raw.githubusercontent.com/primer/octicons/refs/heads/main/icons/mark-github-24.svg\" alt=\"GitHub logo\"><br> View on GitHub\n",
|
||
" </a>\n",
|
||
" </td>\n",
|
||
"</table>\n",
|
||
"\n",
|
||
"<div style=\"clear: both;\"></div>\n",
|
||
"\n",
|
||
"<p>\n",
|
||
"<b>Share to:</b>\n",
|
||
"\n",
|
||
"<a href=\"https://www.linkedin.com/sharing/share-offsite/?url=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\" target=\"_blank\">\n",
|
||
" <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/8/81/LinkedIn_icon.svg\" alt=\"LinkedIn logo\">\n",
|
||
"</a>\n",
|
||
"\n",
|
||
"<a href=\"https://bsky.app/intent/compose?text=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\" target=\"_blank\">\n",
|
||
" <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/7/7a/Bluesky_Logo.svg\" alt=\"Bluesky logo\">\n",
|
||
"</a>\n",
|
||
"\n",
|
||
"<a href=\"https://twitter.com/intent/tweet?url=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\" target=\"_blank\">\n",
|
||
" <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/5/5a/X_icon_2.svg\" alt=\"X logo\">\n",
|
||
"</a>\n",
|
||
"\n",
|
||
"<a href=\"https://reddit.com/submit?url=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\" target=\"_blank\">\n",
|
||
" <img width=\"20px\" src=\"https://redditinc.com/hubfs/Reddit%20Inc/Brand/Reddit_Logo.png\" alt=\"Reddit logo\">\n",
|
||
"</a>\n",
|
||
"\n",
|
||
"<a href=\"https://www.facebook.com/sharer/sharer.php?u=https%3A//github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb\" target=\"_blank\">\n",
|
||
" <img width=\"20px\" src=\"https://upload.wikimedia.org/wikipedia/commons/5/51/Facebook_f_logo_%282019%29.svg\" alt=\"Facebook logo\">\n",
|
||
"</a>\n",
|
||
"</p>"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "5h3O5b6P8WEx"
|
||
},
|
||
"source": [
|
||
"| Author |\n",
|
||
"| --- |\n",
|
||
"| [Alicia Williams](https://github.com/aliciawilliams) |"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "intro_md_new"
|
||
},
|
||
"source": [
|
||
"## Overview\n",
|
||
"\n",
|
||
"This tutorial will guide you through the powerful AI functions available in BigQuery. You'll get hands-on experience using two collections of functions that integrate directly with powerful Gemini models, allowing you to perform sophisticated AI-driven analysis on your data right within your familiar SQL environment.\n",
|
||
"\n",
|
||
"1. **managed AI functions ([`AI.SCORE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-score), [`AI.CLASSIFY`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-classify), [`AI.IF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if), [`AI.AGG`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-agg))**: These are high-level, easy-to-use functions for common tasks like semantic ranking, classification, filtering, joining, and aggregation. BigQuery uses prompt engineering and can select the appropriate model and parameters to use for the specific task to optimize the quality and consistency of your results, making them ideal for data analysts who are not necessarily prompt engineers or ML practitioners. Furthermore, BigQuery supports **optimized mode** for `AI.IF` and `AI.CLASSIFY`, utilizing proxy models and model distillation to process large datasets at significantly reduced cost and latency.\n",
|
||
"\n",
|
||
"2. **general-purpose AI functions ([`AI.GENERATE_BOOL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool), [`AI.GENERATE_DOUBLE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-double), [`AI.GENERATE_INT`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-int))**: These are inference functions for power-users who want full control over the prompt. They are perfect for row-level AI tasks, especially enriching data in a `SELECT` clause, and returning a specific data type (`BOOL`, `DOUBLE`, or `INT`)."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "SwQLISjqGTHd"
|
||
},
|
||
"source": [
|
||
"### Objectives\n",
|
||
"\n",
|
||
"We'll cover how to:\n",
|
||
"\n",
|
||
"* **Perform semantic ranking** with the managed `AI.SCORE` function.\n",
|
||
"* **Perform classification** with the managed `AI.CLASSIFY` function.\n",
|
||
"* **Perform semantic filtering** with the managed `AI.IF` function.\n",
|
||
"* **Perform semantic joins** with the managed `AI.IF` function.\n",
|
||
"* **Perform semantic aggregation** over unstructured text and images with the managed `AI.AGG` function.\n",
|
||
"* **Optimize AI query costs and performance** for large-scale datasets using optimized mode in `AI.IF` and `AI.CLASSIFY`.\n",
|
||
"* **Perform powerful, row-level analysis** using general-purpose inference functions like `AI.GENERATE_BOOL`, `AI.GENERATE_DOUBLE`, and `AI.GENERATE_INT`."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "costs_md"
|
||
},
|
||
"source": [
|
||
"### Services and Costs\n",
|
||
"\n",
|
||
"This tutorial uses the following billable components of Google Cloud:\n",
|
||
"\n",
|
||
"* **BigQuery**: [Pricing](https://cloud.google.com/bigquery/pricing)\n",
|
||
"\n",
|
||
"* **BigQuery ML**: [Pricing](https://cloud.google.com/bigquery/pricing#bqml)\n",
|
||
"\n",
|
||
"* **Gemini Enterprise Agent Platform**: [Pricing](https://cloud.google.com/gemini-enterprise-agent-platform/generative-ai/pricing)\n",
|
||
"\n",
|
||
"You can use the [Pricing Calculator](https://cloud.google.com/products/calculator) to generate a cost estimate based on your projected usage."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "nkoCFoFVSPii"
|
||
},
|
||
"source": [
|
||
"---\n",
|
||
"\n",
|
||
"## Before you begin"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "setup_md_1"
|
||
},
|
||
"source": [
|
||
"### Setting up your Google Cloud project\n",
|
||
"**The following steps are required, regardless of your notebook environment.**\n",
|
||
"\n",
|
||
"1. [Select or create a Google Cloud project](https://console.cloud.google.com/cloud-resource-manager). When you first create an account, you get a $300 free credit towards your compute/storage costs.\n",
|
||
"\n",
|
||
"2. [Make sure that billing is enabled for your project](https://cloud.google.com/billing/docs/how-to/modify-project).\n",
|
||
"\n",
|
||
"3. [Enable the BigQuery, BigQuery Connection, and Agent Platform APIs](https://console.cloud.google.com/flows/enableapi?apiid=bigquery.googleapis.com,bigqueryconnection.googleapis.com,aiplatform.googleapis.com).\n",
|
||
"\n",
|
||
"4. If you are running this notebook locally, you need to install the [Cloud SDK](https://cloud.google.com/sdk)."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "YQ3g-h7uTaSf"
|
||
},
|
||
"source": [
|
||
"### Setting your project ID"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "set_project_id"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"PROJECT_ID = \"\" # @param {type:\"string\"}\n",
|
||
"\n",
|
||
"# Set the project id\n",
|
||
"! gcloud config set project {PROJECT_ID}"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "auth_md"
|
||
},
|
||
"source": [
|
||
"### Authenticating to your Google Cloud account\n",
|
||
"\n",
|
||
"Depending on your Jupyter environment, you may have to manually authenticate. Follow the relevant instructions below."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "V6NjZRCXU5Ro"
|
||
},
|
||
"source": [
|
||
"**1. Colab Enterprise or BigQuery Studio Notebooks**\n",
|
||
"* Do nothing as you are already authenticated."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "l0dV1hvAU1ed"
|
||
},
|
||
"source": [
|
||
"**2. Colab, uncomment and run:**"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "auth_code"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"# from google.colab import auth\n",
|
||
"#\n",
|
||
"# auth.authenticate_user()"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "3JiX7KA5uVl3"
|
||
},
|
||
"source": [
|
||
"###Creating a helper function to view images\n",
|
||
"This is a helpful utility function that you'll use later in the tutorial. It takes the results of your search query (stored in a pandas DataFrame) and displays the corresponding product images in a nice grid format, making it easy to see the results of a query that contain images."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "DVncmYt5ufC9"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"## Code created with Gemini\n",
|
||
"\n",
|
||
"import base64\n",
|
||
"import html\n",
|
||
"import mimetypes\n",
|
||
"from io import BytesIO\n",
|
||
"\n",
|
||
"import pandas as pd\n",
|
||
"from IPython.display import HTML, display\n",
|
||
"from PIL import Image\n",
|
||
"from google.cloud import storage\n",
|
||
"\n",
|
||
"def display_image_grid(\n",
|
||
" df: pd.DataFrame, url_column: str = \"uri\", image_width: int = 220\n",
|
||
"):\n",
|
||
" \"\"\"Renders a grid of cards, each with an image and its corresponding\n",
|
||
" DataFrame row details. Automatically resizes GCS images to prevent\n",
|
||
" exceeding notebook output size limits.\n",
|
||
"\n",
|
||
" Args:\n",
|
||
" df (pd.DataFrame): DataFrame containing image URLs and other metadata.\n",
|
||
" url_column (str): The name of the column that contains the image URLs.\n",
|
||
" image_width (int): The width of each card in pixels.\n",
|
||
" \"\"\"\n",
|
||
" # --- Validate Input ---\n",
|
||
" if not isinstance(df, pd.DataFrame) or df.empty:\n",
|
||
" print(\"Input is not a valid or non-empty DataFrame. Nothing to display.\")\n",
|
||
" return\n",
|
||
"\n",
|
||
" # Auto-detect URL column if the default/specified column is not found\n",
|
||
" if url_column not in df.columns:\n",
|
||
" for fallback_col in [\"uri\", \"signed_url\", \"url\", \"image_uri\", \"gcs_uri\"]:\n",
|
||
" if fallback_col in df.columns:\n",
|
||
" url_column = fallback_col\n",
|
||
" break\n",
|
||
"\n",
|
||
" if url_column not in df.columns:\n",
|
||
" print(f\"Error: Column '{url_column}' not found in the DataFrame.\")\n",
|
||
" return\n",
|
||
"\n",
|
||
" # Get a list of all columns that are NOT the url_column\n",
|
||
" detail_columns = [col for col in df.columns if col != url_column]\n",
|
||
"\n",
|
||
" # Initialize GCS client lazily if needed\n",
|
||
" gcs_client = None\n",
|
||
"\n",
|
||
" # --- Build HTML for each card ---\n",
|
||
" card_html_list = []\n",
|
||
" for index, row in df.iterrows():\n",
|
||
" # Strip any leading/trailing single or double quotes from the URL\n",
|
||
" url = str(row[url_column]).strip(\"'\\\"\")\n",
|
||
"\n",
|
||
" # Handle raw gs:// URIs via GCS client, PIL Thumbnailing & Base64\n",
|
||
" if url.startswith(\"gs://\"):\n",
|
||
" try:\n",
|
||
" if gcs_client is None:\n",
|
||
" gcs_client = storage.Client()\n",
|
||
" # Parse bucket and blob name from gs://bucket_name/blob_path\n",
|
||
" parts = url[5:].split(\"/\", 1)\n",
|
||
" bucket = gcs_client.bucket(parts[0])\n",
|
||
" blob = bucket.blob(parts[1])\n",
|
||
" image_bytes = blob.download_as_bytes()\n",
|
||
"\n",
|
||
" # --- NEW: Thumbnail and compress image using PIL ---\n",
|
||
" try:\n",
|
||
" img = Image.open(BytesIO(image_bytes))\n",
|
||
" if img.mode != \"RGB\":\n",
|
||
" img = img.convert(\"RGB\")\n",
|
||
" # Resize to 2x card width for sharp Retina display while keeping file size tiny\n",
|
||
" max_dim = image_width * 2\n",
|
||
" img.thumbnail((max_dim, max_dim), Image.Resampling.LANCZOS)\n",
|
||
"\n",
|
||
" buffer = BytesIO()\n",
|
||
" img.save(buffer, format=\"JPEG\", quality=75)\n",
|
||
" image_bytes = buffer.getvalue()\n",
|
||
" mime_type = \"image/jpeg\"\n",
|
||
" except Exception:\n",
|
||
" # Fall back to original bytes if PIL processing fails\n",
|
||
" mime_type, _ = mimetypes.guess_type(url)\n",
|
||
" if not mime_type:\n",
|
||
" mime_type = \"image/png\"\n",
|
||
"\n",
|
||
" b64_data = base64.b64encode(image_bytes).decode(\"utf-8\")\n",
|
||
" url = f\"data:{mime_type};base64,{b64_data}\"\n",
|
||
" except Exception as e:\n",
|
||
" print(f\"Failed to load GCS image {url}: {e}\")\n",
|
||
"\n",
|
||
" # Create an HTML block for the other details\n",
|
||
" details_html = \"\"\n",
|
||
" for col in detail_columns:\n",
|
||
" # Escape data to prevent HTML rendering issues\n",
|
||
" value = html.escape(str(row[col]))\n",
|
||
" col_name = html.escape(col.replace(\"_\", \" \").title())\n",
|
||
" details_html += f'<p style=\"margin: 4px 0; font-size: 14px;\"><strong>{col_name}:</strong> {value}</p>'\n",
|
||
"\n",
|
||
" # Assemble the full card\n",
|
||
" card_html_list.append(f\"\"\"\n",
|
||
" <div style=\"width: {image_width}px; margin: 10px; border-radius: 8px;\n",
|
||
" box-shadow: 0 4px 8px 0 rgba(0,0,0,0.2); font-family: sans-serif;\n",
|
||
" text-align: left; background-color: white;\">\n",
|
||
" <img src=\"{url}\"\n",
|
||
" alt=\"Product Image\"\n",
|
||
" style=\"width: 100%; height: {image_width - 40}px; object-fit: cover;\n",
|
||
" border-top-left-radius: 8px; border-top-right-radius: 8px;\"\n",
|
||
" onerror=\"this.onerror=null;this.src='https://placehold.co/{image_width}x{image_width - 40}/eee/ccc?text=Image+Expired';\"\n",
|
||
" >\n",
|
||
" <div style=\"padding: 10px 15px;\">\n",
|
||
" {details_html}\n",
|
||
" </div>\n",
|
||
" </div>\n",
|
||
" \"\"\")\n",
|
||
"\n",
|
||
" # --- Display the final grid ---\n",
|
||
" all_cards_string = \"\".join(card_html_list)\n",
|
||
" final_html = f\"\"\"\n",
|
||
" <p><strong>Displaying {len(card_html_list)} result(s):</strong></p>\n",
|
||
" <div style=\"display: flex; flex-wrap: wrap; justify-content: flex-start;\">\n",
|
||
" {all_cards_string}\n",
|
||
" </div>\n",
|
||
" \"\"\"\n",
|
||
" display(HTML(final_html))"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "SMU-CoJcr8ls"
|
||
},
|
||
"source": [
|
||
"### Exploring the sample data\n",
|
||
"\n",
|
||
"This tutorial uses data from a fictional e-commerce pet supply company called **Cymbal Pets**, which is available as a public dataset in BigQuery: `bigquery-public-data.cymbal_pets`."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "BNVKfg6BN7yg"
|
||
},
|
||
"source": [
|
||
"Let's take a peek at a few rows of each table to get familiar with the data."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "9IXviWAgPe-j"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"%%bigquery --project {PROJECT_ID}\n",
|
||
"\n",
|
||
"SELECT *\n",
|
||
"FROM bigquery-public-data.cymbal_pets.products\n",
|
||
"LIMIT 2"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "5usXj1u2Phuy"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"%%bigquery --project {PROJECT_ID}\n",
|
||
"\n",
|
||
"SELECT *\n",
|
||
"FROM bigquery-public-data.cymbal_pets.product_images\n",
|
||
"LIMIT 2"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "aiqe_md_intro"
|
||
},
|
||
"source": [
|
||
"---\n",
|
||
"\n",
|
||
"## BigQuery managed AI functions\n",
|
||
"\n",
|
||
"The [**BigQuery managed AI functions**](https://docs.cloud.google.com/bigquery/docs/generative-ai-overview#managed_ai_functions) ([`AI.SCORE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-score), [`AI.CLASSIFY`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-classify), [`AI.IF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if), and [`AI.AGG`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-agg)) extend traditional SQL with natural language conditions and aggregation.\n",
|
||
"\n",
|
||
"These functions are designed to be accessible to all users. They enhance quality by applying **prompt rewrite strategies** automatically, allowing you to write simple prompts while achieving accurate results.\n",
|
||
"\n",
|
||
"We'll run through a few examples of using these functions for analysis with the **`bigquery-public-data.cymbal_pets`** dataset."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "aiqe_score_md"
|
||
},
|
||
"source": [
|
||
"###Using `AI.SCORE`: Ranking by \"giftability\"\n",
|
||
"\n",
|
||
"[`AI.SCORE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-score) is a function that accepts text input and uses a Gemini model to rate those inputs based on a scoring system that you describe as part of the prompt. If you do not provide a scoring system, the function automatically rewrites your prompt to generate a scoring rubric.\n",
|
||
"\n",
|
||
"It is perfect for ranking items based on semantic criteria that are not explicitly in the data. Let's apply it to a potential marketing use case: determining how suitable a product is as a gift for a pet owner."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "aiqe_score_code"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"%%bigquery --project {PROJECT_ID}\n",
|
||
"\n",
|
||
"SELECT\n",
|
||
" product_name,\n",
|
||
" description,\n",
|
||
" AI.SCORE(\n",
|
||
" ('How \"giftable\" is this product for a pet owner? ', description,\n",
|
||
" 'Use a scale from 1-10.')\n",
|
||
" ) AS giftability_score\n",
|
||
"FROM\n",
|
||
" `bigquery-public-data.cymbal_pets.products`\n",
|
||
"ORDER BY\n",
|
||
" giftability_score DESC"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "aiqe_classify_md"
|
||
},
|
||
"source": [
|
||
"### Using `AI.CLASSIFY`: Classifying by intended animal\n",
|
||
"\n",
|
||
"[`AI.CLASSIFY`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-classify) uses a Gemini model to classify inputs into categories that you provide. `AI.CLASSIFY` accepts multimodal input (text, image, video, etc), and can be used for tasks such as classifying reviews by sentiment, classifying support tickets or emails by topic, or\n",
|
||
"classifying an image by its style or contents.\n",
|
||
"\n",
|
||
"Let's use it to assign each toy product to an animal type using the product name and description. We'll define a set of target categories and ask the model to assign each product to the most appropriate one. Notice we include a fallback \"All Pets\" category."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "aiqe_classify_code"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"%%bigquery --project {PROJECT_ID}\n",
|
||
"\n",
|
||
"SELECT\n",
|
||
" product_name,\n",
|
||
" AI.CLASSIFY(\n",
|
||
" ('What animal is this product for?',product_name,' ',description),\n",
|
||
" categories => [\"Dog\", \"Cat\", \"Bird\", \"Fish\", \"Small Animal\", \"All Pets\"]\n",
|
||
" ) AS animal_type\n",
|
||
"FROM\n",
|
||
" `bigquery-public-data.cymbal_pets.products`\n",
|
||
"WHERE category = \"Toys\"\n",
|
||
"LIMIT 20"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "aiqe_if_md"
|
||
},
|
||
"source": [
|
||
"### Using `AI.IF`: Filtering product images\n",
|
||
"\n",
|
||
"[`AI.IF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if) uses a Gemini model to evaluate a condition described in natural language and returns a `BOOL`. Similar to `AI.CLASSIFY`, it can also accept multimodal input. If you haven't yet worked with multimodal data in BigQuery, you can learn more in the [Analyzing Multimodal Data in BigQuery](https://github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/multimodal-analysis-bigquery/analyze_multimodal_data_bigquery.ipynb) notebook.\n",
|
||
"\n",
|
||
"Let's use `AI.IF` to perform a visual filtering task on our `product_images` table. We'll use it to find all product images that contain a ball by using `AI.IF` within the `WHERE` clause.\n",
|
||
"\n",
|
||
"> **Note on Image URLs:** Since we are querying a public dataset without a bound connection service account, we rely on End User Credentials (EUC) and pass the `uri` to the helper function directly to view the image. Normally, when working with your own private datasets and a Cloud resource connection, you would generate a temporary read-only URL by selecting `OBJ.GET_READ_URL(ref).url AS signed_url` (shown commented out in the queries below)."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "F3EQDlngy3Fb"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"%%bigquery images_df --project {PROJECT_ID}\n",
|
||
"\n",
|
||
"SELECT\n",
|
||
" uri,\n",
|
||
" -- OBJ.GET_READ_URL(ref).url AS signed_url,\n",
|
||
" metadata\n",
|
||
"FROM\n",
|
||
" `bigquery-public-data.cymbal_pets.product_images`\n",
|
||
"WHERE\n",
|
||
" AI.IF(\n",
|
||
" ('Does this product image contain a ball? ',ref)\n",
|
||
" );"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "sesnAbr-vQMP"
|
||
},
|
||
"source": [
|
||
"The results of the previous query are now stored in a pandas DataFrame called `images_df` (a parameter added to the [`%%bigquery magic` utility](https://googleapis.dev/python/bigquery-magics/latest/) in the prior cell).\n",
|
||
"\n",
|
||
"Let's take a look at the results."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "DJyw9P67vTDg"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"images_df"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "Jmt9B-Y3vUMa"
|
||
},
|
||
"source": [
|
||
"Now, let's view these images using the helper function created in the **Before you begin** section of this notebook."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "JGe1xdhcvaWx"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"display_image_grid(images_df)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "FQyhHKaMdTHD"
|
||
},
|
||
"source": [
|
||
"### Using `AI.IF`: Performing a semantic join of product images with product table\n",
|
||
"\n",
|
||
"We can also use [`AI.IF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if) to perform semantic joins. In this next query, we will use it within the `JOIN` clause to join the `products` table (text) with the `product_images` table (image). The join will only succeed if the image *semantically matches* the product description."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "yZMPtc-HG8D4"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"%%bigquery join_df --project {PROJECT_ID}\n",
|
||
"\n",
|
||
"SELECT\n",
|
||
" product_name,\n",
|
||
" description,\n",
|
||
" brand,\n",
|
||
" images.uri AS uri\n",
|
||
" -- OBJ.GET_READ_URL(images.ref).url AS signed_url\n",
|
||
"FROM\n",
|
||
" `bigquery-public-data.cymbal_pets.products` as products\n",
|
||
"INNER JOIN\n",
|
||
" `bigquery-public-data.cymbal_pets.product_images` as images\n",
|
||
"ON\n",
|
||
" AI.IF(\n",
|
||
" ('You will be provided an image of a pet product. ',\n",
|
||
" 'Determine if the image is of the following pet toy: ',\n",
|
||
" products.product_name,\n",
|
||
" products.description,\n",
|
||
" images.ref\n",
|
||
" )\n",
|
||
" )\n",
|
||
"WHERE\n",
|
||
" products.category = \"Toys\" AND\n",
|
||
" products.brand = \"Fluffy Buns\""
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "kLA2ol2N4OBp"
|
||
},
|
||
"source": [
|
||
"Let's take a look at the resulting DataFrame."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "ZEBuaLcowCzH"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"join_df"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "AJAHROEJ4m2B"
|
||
},
|
||
"source": [
|
||
"Now, let's view the results including images using the helper function."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "slbCltSr4vF0"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"display_image_grid(join_df)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "903e146a"
|
||
},
|
||
"source": [
|
||
"### Using `AI.AGG`: Summarizing and extracting insights across rows\n",
|
||
"\n",
|
||
"While functions like `AI.CLASSIFY` and `AI.IF` analyze individual rows, analyzing unstructured data at scale often requires synthesizing information across multiple rows. The [`AI.AGG`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-agg) function allows you to use natural-language instructions within a single line of SQL to summarize or synthesize information over millions of rows of unstructured or multimodal data.\n",
|
||
"\n",
|
||
"For example, let's say we want to discover the major product categories across the `bigquery-public-data.cymbal_pets` catalog. With `AI.AGG`, we can ask the model to analyze the raw product names and descriptions across all rows to identify the overarching categories:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "cf90b2ba"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"%%bigquery --project {PROJECT_ID}\n",
|
||
"\n",
|
||
"SELECT\n",
|
||
" AI.AGG(\n",
|
||
" ('Product: ', product_name, ' - Description: ', description),\n",
|
||
" 'What are the major categories of these products?'\n",
|
||
" ) AS category_description\n",
|
||
"FROM\n",
|
||
" `bigquery-public-data.cymbal_pets.products`"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "5818f537"
|
||
},
|
||
"source": [
|
||
"### Optimizing `AI.IF` and `AI.CLASSIFY` with optimized mode\n",
|
||
"\n",
|
||
"When processing large datasets containing thousands or even billions of rows, calling a remote LLM for every row can result in high token consumption and query latency. To solve this, BigQuery provides an **[optimized mode](https://docs.cloud.google.com/bigquery/docs/optimize-ai-functions)** for `AI.IF` and `AI.CLASSIFY`, which leverages **[proxy models and on-the-fly model distillation](https://cloud.google.com/blog/products/data-analytics/more-than-100x-faster-and-cheaper-llm-powered-sql-queries-with-proxy-models)** to deliver over 100x faster and cheaper queries.\n",
|
||
"\n",
|
||
"#### How optimized mode works:\n",
|
||
"1. **Sampling and labeling**: BigQuery automatically selects a small representative sample of your data and calls Gemini to provide labels.\n",
|
||
"2. **Distilled model training**: A local, ultra-lightweight distilled model (such as a logistic regression proxy model) is trained just-in-time on CPU using the LLM labels and data embeddings as features.\n",
|
||
"3. **Quality check**: BigQuery evaluates the distilled model's accuracy against the LLM's results. If it meets the quality threshold, it is used to process the majority of the dataset.\n",
|
||
"4. **Inference**: The proxy model processes the remaining rows locally at ultra-low latency and cost.\n",
|
||
"\n",
|
||
"> **Minimum row count**: Official documentation recommends an input dataset containing approximately **3,000 rows or more** to ensure robust model distillation.\n",
|
||
"\n",
|
||
"The following example demonstrates how to enable optimized mode using `optimization_mode => 'MINIMIZE_COST'` with `AI.IF` in a `WHERE` clause to filter news articles. We use the public dataset `bigquery-public-data.bbc_news.fulltext`, which contains over 2,200 news articles. While slightly below the 3,000-row guideline, this example is able to exceed the absolute minimum threshold required to trigger model distillation:"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "ac276e00"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"%%bigquery --project {PROJECT_ID}\n",
|
||
"\n",
|
||
"SELECT\n",
|
||
" title,\n",
|
||
" body\n",
|
||
"FROM\n",
|
||
" `bigquery-public-data.bbc_news.fulltext`\n",
|
||
"WHERE\n",
|
||
" AI.IF(\n",
|
||
" ('The following news story is about a natural disaster: ', body),\n",
|
||
" embeddings => AI.EMBED(body, endpoint => 'text-embedding-005', task_type => 'CLASSIFICATION').result,\n",
|
||
" optimization_mode => 'MINIMIZE_COST'\n",
|
||
" );"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "hBpgd1tg3eb6"
|
||
},
|
||
"source": [
|
||
"After running the query above, you can verify exactly how many rows were optimized by inspecting your job history in the Google Cloud Console:\n",
|
||
"\n",
|
||
"1. Go to the **[BigQuery Console](https://console.cloud.google.com/bigquery)** and open **Job history** from the **Explorer** pane.\n",
|
||
"2. Find the query job you just executed, click the three-dot menu (**⋮** More actions) at the end of the row, and select **Show job details**.\n",
|
||
"3. In the job details panel, scroll to the **Gen AI Function Optimizations**) section. You will see a summary similar to:\n",
|
||
" > `AI.IF('The following news s'): 875 out of 2225 rows optimized. Cost Optimization successfully applied.`\n",
|
||
"This confirms that BigQuery successfully trained a lightweight proxy model on a sample of your data and used it to process the remaining rows locally at zero LLM token cost!"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "scalar_md_new"
|
||
},
|
||
"source": [
|
||
"---\n",
|
||
"\n",
|
||
"## General-purpose AI functions\n",
|
||
"\n",
|
||
"BigQuery's general-purpose AI functions ([`AI.GENERATE_BOOL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool), [`AI.GENERATE_DOUBLE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-double), and [`AI.GENERATE_INT`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-int)) are another set of capabilities that bring the power of LLMs for AI-driven data extraction and inference tasks directly within your SQL queries.\n",
|
||
"\n",
|
||
"These functions are considered \"general-purpose\" because they provide full control over the prompt and are designed for power-users. Similar to the managed functions, they can be used alongside your standard SQL in `SELECT` and `WHERE` clauses, giving you the power to do complex analysis with natural language."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "generate_bool_md"
|
||
},
|
||
"source": [
|
||
"### Using `AI.GENERATE_BOOL`: Enriching product details\n",
|
||
"\n",
|
||
"The [`AI.GENERATE_BOOL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool) function allows you to analyze any combination of text and unstructured data and returns a `BOOL` value for each row in the table.\n",
|
||
"\n",
|
||
"Let's use `AI.GENERATE_BOOL` for a data enrichment task. We'll find the products in our catalog that require a power source (like electricity or batteries) to operate and add a clear attribute that isn't already available in our `products` table."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "generate_bool_code"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"%%bigquery --project {PROJECT_ID}\n",
|
||
"\n",
|
||
"SELECT\n",
|
||
" product_name,\n",
|
||
" category,\n",
|
||
" description,\n",
|
||
" AI.GENERATE_BOOL(\n",
|
||
" ('Does this product require electricity, batteries, ',\n",
|
||
" 'or a power source to operate?',product_name,' ',description),\n",
|
||
" endpoint => 'gemini-2.5-flash').* EXCEPT(full_response)\n",
|
||
"FROM\n",
|
||
" `bigquery-public-data.cymbal_pets.products`"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "I7QyOnztBpCs"
|
||
},
|
||
"source": [
|
||
"A few items to notice from the query text:\n",
|
||
"* `AI.GENERATE_BOOL` accepts an [`endpoint` argument](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool#arguments). This allows you to specify any [generally available](https://cloud.google.com/vertex-ai/generative-ai/docs/models#generally_available_models) or [preview](https://cloud.google.com/vertex-ai/generative-ai/docs/models#preview_models) Gemini model. If you don't specify an `endpoint` value, BigQuery selects a recent stable version of Gemini to use.\n",
|
||
"* `AI.GENERATE_BOOL` allows you to [specify output options](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool#output):\n",
|
||
" * `result` - the `BOOL` value containing the model's response to the prompt\n",
|
||
" * `full_response` - a JSON value containing all fields from the [response](https://cloud.google.com/vertex-ai/docs/reference/rest/v1/GenerateContentResponse)\n",
|
||
" * `status` - a `STRING` value that contains the API response status for the corresponding row (this value is empty if the operation was successful)\n",
|
||
"\n",
|
||
"In this query, we chose Gemini 2.5 Flash as the `endpoint` and specified the `result` and `status` fields be returned (by using the `*` wildcard after the function and adding an `EXCEPT` to skip returning the `full_response`)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "PfG0QtYmQnyi"
|
||
},
|
||
"source": [
|
||
"---\n",
|
||
"### Comparison: `AI.GENERATE_BOOL` vs. `AI.IF`\n",
|
||
"\n",
|
||
"While both [`AI.GENERATE_BOOL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool) and [`AI.IF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if) use generative AI models to evaluate a condition and return a boolean value, they are optimized for different workflows and capabilities:\n",
|
||
"\n",
|
||
"\n",
|
||
"| Feature | `AI.GENERATE_BOOL` | `AI.IF` |\n",
|
||
"| :--- | :--- | :--- |\n",
|
||
"| **Optimized Mode** | **Not supported**<br>Calls the remote LLM for every row. | **Supported**<br>Supports on-the-fly model distillation<br>(`MINIMIZE_COST`) to scale efficiently. |\n",
|
||
"| **Prompt Optimization** | **Direct Passthrough**<br>The model evaluates your exact prompt<br>as written without modification. | **Automatic Optimization**<br>Automatically structures and enhances<br>your prompt to improve output quality<br>and consistency. |\n",
|
||
"| **Model Parameters** | **Customizable**<br>Allows specifying custom `model_params`<br>(such as temperature, top_p, and top_k). | **Automated**<br>Model parameters are managed<br>automatically for optimal evaluation.<br>*(Note: Both functions allow specifying<br>a custom model via `endpoint`).* |\n",
|
||
"| **Output & Metadata** | **STRUCT Output**<br>Returns a `STRUCT` containing the `BOOL`<br>result along with detailed metadata<br>(safety ratings, citations, API status). | **Scalar BOOL Output**<br>Returns a simple scalar `BOOL`, making<br>it much easier and cleaner to use in<br>SQL predicates and join conditions. |\n",
|
||
"| **Error Handling** | **Recorded in Output**<br>Records detailed error information<br>inside the output `STRUCT`. | **Returns NULL**<br>If an error occurs for any input row,<br>the function cleanly returns `NULL`. |\n",
|
||
"\n",
|
||
"**Key Takeaway:**\n",
|
||
"* Use `AI.GENERATE_BOOL` for use cases where you want full control, or when using it to augment data in the `SELECT` clause.\n",
|
||
"* Use `AI.IF` for smart, semantic filtering in the `WHERE` clause, for joining with the `JOIN ON` clause, or when you want to take advantage of **optimized mode**.\n",
|
||
"---"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "generate_double_md_corrected"
|
||
},
|
||
"source": [
|
||
"### Using `AI.GENERATE_INT`: Counting mentioned ingredients\n",
|
||
"\n",
|
||
"[`AI.GENERATE_INT`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-int) is similar to `AI.GENERATE_BOOL`, but differs in that it will return an integer value.\n",
|
||
"\n",
|
||
"Let's use `AI.GENERATE_INT` to perform an extraction task, specifically to count how many distinct ingredients or food items it can identify in the food product descriptions. While the descriptions are high-level marketing text and not detailed ingredient lists, this will test the model's ability to infer ingredients from a product's name and general description."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "generate_int_code"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"%%bigquery --project {PROJECT_ID}\n",
|
||
"\n",
|
||
"SELECT\n",
|
||
" product_name,\n",
|
||
" description,\n",
|
||
" AI.GENERATE_INT(\n",
|
||
" ('Based on the text, how many distinct food ingredients ',\n",
|
||
" 'can you identify? If none are listed, return 0.',\n",
|
||
" product_name,' ',description),\n",
|
||
" endpoint => 'gemini-2.5-flash').result AS ingredient_count\n",
|
||
"FROM\n",
|
||
" `bigquery-public-data.cymbal_pets.products`\n",
|
||
"WHERE\n",
|
||
" category = 'Food'\n",
|
||
"ORDER BY\n",
|
||
" ingredient_count DESC"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "generate_int_md_corrected"
|
||
},
|
||
"source": [
|
||
"### Using `AI.GENERATE_DOUBLE`: Estimating shipping weight\n",
|
||
"\n",
|
||
"[`AI.GENERATE_DOUBLE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-double) can be used for similar tasks as `AI.GENERATE_INT`, but differs in that it will return a decimal value. Both functions can be used for *extraction* tasks, as we saw in the last example. In this example, we will see an *inference* task.\n",
|
||
"\n",
|
||
"Since our product data doesn't include shipping weights, let's ask the model to estimate the weight in pounds with decimal precision. It will have to infer this based on the product's name and description (e.g., a \"50 Gallon Aquarium\" is much heavier than a \"Dog Bone\"). This is a great way to generate missing data."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "code",
|
||
"execution_count": null,
|
||
"metadata": {
|
||
"id": "xT9vakyw1PZ2"
|
||
},
|
||
"outputs": [],
|
||
"source": [
|
||
"%%bigquery --project {PROJECT_ID}\n",
|
||
"\n",
|
||
"SELECT\n",
|
||
" product_name,\n",
|
||
" description,\n",
|
||
" AI.GENERATE_DOUBLE(\n",
|
||
" ('Based on this product description, what is a rough ',\n",
|
||
" 'estimated weight of the product for shipping in pounds (lbs)?',\n",
|
||
" product_name,' ',description),\n",
|
||
" endpoint => 'gemini-2.5-flash').result AS estimated_shipping_weight\n",
|
||
"FROM\n",
|
||
" `bigquery-public-data.cymbal_pets.products`"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "hyl5LncS0UBh"
|
||
},
|
||
"source": [
|
||
"## Recap\n",
|
||
"\n",
|
||
"In this notebook, you explored the powerful suite of AI functions in BigQuery to perform advanced data analysis using natural language.\n",
|
||
"\n",
|
||
"You learned how to:\n",
|
||
"* **Perform semantic analysis with managed AI functions**, which are ideal for all users:\n",
|
||
" * Used [`AI.SCORE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-score) to **rank** products based on a subjective quality like \"giftability.\"\n",
|
||
" * Used [`AI.CLASSIFY`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-classify) to **categorize** products into predefined animal types.\n",
|
||
" * Used [`AI.IF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if) for powerful **multimodal filtering** (finding balls in product images) and **semantic joins** (matching products to their images).\n",
|
||
" * Used [`AI.AGG`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-agg) to **summarize and synthesize** insights across millions of rows of unstructured or multimodal data.\n",
|
||
" * Leveraged **optimized mode** (`optimization_mode => 'MINIMIZE_COST'`) to process large datasets faster and cheaper using on-the-fly model distillation and proxy models.\n",
|
||
"* **Execute row-level tasks with general-purpose AI functions**, which give power-users full control:\n",
|
||
" * Used [`AI.GENERATE_BOOL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool) to **enrich data** to determine which products require power based on a yes/no condition.\n",
|
||
" * Used [`AI.GENERATE_INT`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-int) to **extract** structured data (a count of ingredients) from unstructured text.\n",
|
||
" * Used [`AI.GENERATE_DOUBLE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-double) to **infer** and create new data points (estimating shipping weights) that were not present in the original dataset."
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "U4Zv6BqE4ko6"
|
||
},
|
||
"source": [
|
||
"## Next Steps\n",
|
||
"Continue your learning with the following notebooks:\n",
|
||
"* [Introduction to Generative AI functions in BigQuery](https://github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_generative_ai_intro.ipynb)\n",
|
||
"* [Analyze Multimodal Data in BigQuery](https://github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/multimodal-analysis-bigquery/analyze_multimodal_data_bigquery.ipynb)\n",
|
||
"* [Text + multimodal embedding generation and vector search in BigQuery](https://github.com/GoogleCloudPlatform/generative-ai/blob/main/gemini/use-cases/applying-llms-to-data/bigquery_embeddings_vector_search.ipynb)\n",
|
||
"\n",
|
||
"Take a look at the product documentation:\n",
|
||
"* the [Generative AI Overview](https://cloud.google.com/bigquery/docs/generative-ai-overview) landing page\n",
|
||
"* the [Optimize AI functions with model distillation](https://cloud.google.com/bigquery/docs/optimize-ai-functions) guide\n",
|
||
"* the [`AI.SCORE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-score) function documentation\n",
|
||
"* the [`AI.CLASSIFY`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-classify) function documentation\n",
|
||
"* the [`AI.IF`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if) function documentation\n",
|
||
"* the [`AI.AGG`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-agg) function documentation\n",
|
||
"* the [`AI.GENERATE_BOOL`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-bool) function documentation\n",
|
||
"* the [`AI.GENERATE_INT`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-int) function documentation\n",
|
||
"* the [`AI.GENERATE_DOUBLE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-generate-double) function documentation\n",
|
||
"\n",
|
||
"Read more about these features on the Google Cloud Blog:\n",
|
||
"* [Deep dive into BigQuery AI.AGG function](https://cloud.google.com/blog/products/data-analytics/deep-dive-into-bigquery-ai-agg-function)\n",
|
||
"* [More than 100x faster and cheaper LLM-powered SQL queries with proxy models](https://cloud.google.com/blog/products/data-analytics/more-than-100x-faster-and-cheaper-llm-powered-sql-queries-with-proxy-models)\n",
|
||
"* [Deep Dive: Categorize Unstructured Data with BigQuery’s AI.CLASSIFY](https://medium.com/google-cloud/deep-dive-categorize-unstructured-data-with-bigquerys-ai-classify-6e760ac7ebed)"
|
||
]
|
||
},
|
||
{
|
||
"cell_type": "markdown",
|
||
"metadata": {
|
||
"id": "cleanup_md"
|
||
},
|
||
"source": [
|
||
"# Cleaning Up\n",
|
||
"\n",
|
||
"Running the examples in this notebook did not create any new resources in your Google Cloud project. However, if you created a project solely to run through this notebook, you can [delete the project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#shutting_down_projects)."
|
||
]
|
||
}
|
||
],
|
||
"metadata": {
|
||
"colab": {
|
||
"name": "bigquery_ai_operators.ipynb",
|
||
"toc_visible": true
|
||
},
|
||
"kernelspec": {
|
||
"display_name": "Python 3",
|
||
"name": "python3"
|
||
}
|
||
},
|
||
"nbformat": 4,
|
||
"nbformat_minor": 0
|
||
}
|