{ "cells": [ { "cell_type": "code", "execution_count": null, "id": "ht3MT6z2u4B6", "metadata": { "id": "ht3MT6z2u4B6" }, "outputs": [], "source": [ "# Copyright 2025 Google LLC\n", "#\n", "# Licensed under the Apache License, Version 2.0 (the \"License\");\n", "# you may not use this file except in compliance with the License.\n", "# You may obtain a copy of the License at\n", "#\n", "# https://www.apache.org/licenses/LICENSE-2.0\n", "#\n", "# Unless required by applicable law or agreed to in writing, software\n", "# distributed under the License is distributed on an \"AS IS\" BASIS,\n", "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n", "# See the License for the specific language governing permissions and\n", "# limitations under the License." ] }, { "cell_type": "markdown", "id": "9Ku6cXXnwATV", "metadata": { "id": "9Ku6cXXnwATV" }, "source": [ "# AI-Assisted Data Science Workflows in BigQuery\n", "\n", "\n", " \n", " \n", " \n", " \n", "
\n", " \n", " \"BigQuery
Open in BigQuery Studio\n", " \n", " 		\n", " \n", " \"Google
Open in Colab\n", " \n", " 		\n", " \n", " \"Google
Open in Colab Enterprise\n", " \n", " 		\n", " \n", " \"GitHub
View on GitHub\n", " \n", "
\n", "\n", "
" ] }, { "cell_type": "markdown", "id": "lANfIejdvZYQ", "metadata": { "id": "lANfIejdvZYQ" }, "source": [ "| Author |\n", "| --- |\n", "| [Jeff Nelson](https://github.com/jeffonelson) |" ] }, { "cell_type": "markdown", "id": "XJy67x0Cn4s4", "metadata": { "id": "XJy67x0Cn4s4" }, "source": [ "## Overview\n", "\n", "In this notebook, you'll explore a multimodal data science workflow in BigQuery. You will start with a raw dataset of house listings, enrich it with AI, build a clustering model to find customer segments, and finally, build a powerful text-to-image and image-to-image search engine.\n", "\n", "Next, you'll compare this SQL-native workflow with a modern, generative AI approach by using the [Data Science Agent](https://cloud.google.com/bigquery/docs/colab-data-science-agent) to automatically generate a Python-based clustering model from a simple text prompt.\n", "\n", "## Objectives\n", "\n", "* **Prepare data** and perform feature engineering on a raw dataset.\n", "* **Enrich data** by using BigQuery ML's AI functions to analyze images.\n", "* **Build and evaluate** a K-means clustering model using BQML.\n", "* **Automate model creation** by using the Data Science Agent to generate a Python clustering model.\n", "* **Generate embeddings** for house images using a multimodal model.\n", "* **Perform vector search** to find similar houses using text or image queries." ] }, { "cell_type": "markdown", "id": "B8vBqblSM_9w", "metadata": { "id": "B8vBqblSM_9w" }, "source": [ "## Setup\n", "\n", "Before you can begin the analysis, you need to set up your environment. This involves enabling the necessary APIs, creating a resource connection for BigQuery, and configuring permissions for the service account that connection will use." ] }, { "cell_type": "markdown", "id": "LjsOfBUTNMR5", "metadata": { "id": "LjsOfBUTNMR5" }, "source": [ "### Create BigQuery Cloud resource connection\n", "\n", "Create a [Cloud resource connection](https://cloud.google.com/bigquery/docs/create-cloud-resource-connection). This connection acts as a secure bridge between BigQuery and other Google Cloud services. It allows BigQuery to call the Gemini models hosted on Vertex AI for inference and data enrichment." ] }, { "cell_type": "code", "execution_count": null, "id": "mt4Xdf2uM73o", "metadata": { "id": "mt4Xdf2uM73o" }, "outputs": [], "source": [ "!bq mk --connection --location=us --connection_type=CLOUD_RESOURCE ai_connection" ] }, { "cell_type": "markdown", "id": "3Z1O6H-8NaSx", "metadata": { "id": "3Z1O6H-8NaSx" }, "source": [ "### Configure environment and grant permissions\n", "\n", "The next block of code handles the necessary setup for your environment. These cells perform several key actions:\n", "\n", "* **Capture variables:** Your `PROJECT_ID` and the connection's service account email are captured as Python variables.\n", "* **Initialize a GCS client:** A connection to Google Cloud Storage is created so you can access the house listing images.\n", "* **Grant permissions:** The necessary IAM roles ([`Storage Object Viewer`](https://cloud.google.com/storage/docs/access-control/iam-roles#storage.objectViewer) and [`Vertex AI User`](https://cloud.google.com/vertex-ai/docs/general/access-control#aiplatform.user)) are granted to the connection's service account, allowing it to read images and call Vertex AI models.\n", "* **Define a helper function:** The `display_images` function is defined, which you'll use to visualize images throughout the notebook.\n", "* **Enable interactive tables:** A Colab extension is enabled to render pandas DataFrames as interactive tables." ] }, { "cell_type": "code", "execution_count": null, "id": "jdSIMRCcNit8", "metadata": { "id": "jdSIMRCcNit8" }, "outputs": [], "source": [ "# Create Service Account\n", "SERVICE_ACCT = !bq show --format=prettyjson --connection us.ai_connection | grep \"serviceAccountId\" | cut -d '\"' -f 4\n", "SERVICE_ACCT_EMAIL = SERVICE_ACCT[-1]\n", "print(SERVICE_ACCT_EMAIL)" ] }, { "cell_type": "code", "execution_count": null, "id": "D9MC8XqQmD97", "metadata": { "id": "D9MC8XqQmD97" }, "outputs": [], "source": [ "# Set project id variables for Python and Shell\n", "PROJECT_ID = !gcloud config get-value project\n", "PROJECT_ID = PROJECT_ID[-1]\n", "%env PROJECT_ID=$PROJECT_ID" ] }, { "cell_type": "code", "execution_count": null, "id": "iRMV48N6XoYY", "metadata": { "id": "iRMV48N6XoYY" }, "outputs": [], "source": [ "from google.cloud import storage\n", "\n", "# Define bucket containing housing images\n", "bucket_name = \"drw001-data-science-with-notebooks\"\n", "# Create Cloud Storage client\n", "client = storage.Client(project=PROJECT_ID)\n", "bucket = client.bucket(bucket_name)\n", "\n", "%env BUCKET_NAME=$bucket_name" ] }, { "cell_type": "code", "execution_count": null, "id": "HvATOolaNmoQ", "metadata": { "id": "HvATOolaNmoQ" }, "outputs": [], "source": [ "import time\n", "\n", "# Grant permissions\n", "!gcloud projects add-iam-policy-binding --format=none $PROJECT_ID --member=serviceAccount:$SERVICE_ACCT_EMAIL --role='roles/storage.objectViewer'\n", "!gcloud projects add-iam-policy-binding --format=none $PROJECT_ID --member=serviceAccount:$SERVICE_ACCT_EMAIL --role='roles/aiplatform.user'\n", "\n", "# Wait ~60 seconds, to give IAM updates time to propagate. Otherwise, subsequent cells may fail.\n", "time.sleep(60)" ] }, { "cell_type": "code", "execution_count": null, "id": "_zLmwszkkJC4", "metadata": { "id": "_zLmwszkkJC4" }, "outputs": [], "source": [ "import io\n", "import math\n", "\n", "import PIL.Image\n", "import matplotlib.pyplot as plt\n", "import pandas as pd\n", "from google.cloud import storage\n", "\n", "# Helper function to display images in a DataFrame\n", "\n", "\n", "def display_images(\n", " df: pd.DataFrame, uri_column: str = \"uri\", title_column: str | None = None\n", ") -> None:\n", " \"\"\"Displays images from a DataFrame in a 3-column grid.\n", "\n", " Args:\n", " df (pd.DataFrame): DataFrame with a column containing GCS URIs.\n", " uri_column (str): The name of the column containing the GCS URIs.\n", " title_column (str, optional): The name of the column to use for the image title.\n", " If None, the image filename is used.\n", " \"\"\"\n", " # Calculate grid size\n", " num_images = len(df)\n", " if num_images == 0:\n", " print(\"No images to display.\")\n", " return\n", "\n", " cols = 3\n", " rows = math.ceil(num_images / cols)\n", "\n", " # Handle single image case for correct subplot array shape\n", " if num_images == 1:\n", " fig, axs = plt.subplots(1, 1, figsize=(3.5, 3.5))\n", " axs = [axs]\n", " else:\n", " fig, axs = plt.subplots(rows, cols, figsize=(10, rows * 3.5))\n", " axs = axs.flatten()\n", "\n", " for i, row in df.iterrows():\n", " if i >= len(axs):\n", " break\n", "\n", " # 1. Extract the image path from the full GCS URI\n", " image_uri = row[uri_column]\n", " image_path = image_uri.split(\"/\", 3)[-1]\n", "\n", " # 2. Get the image from GCS\n", " try:\n", " blob = bucket.blob(image_path)\n", " image_bytes = blob.download_as_bytes()\n", " image = PIL.Image.open(io.BytesIO(image_bytes))\n", " except Exception as e:\n", " print(f\"Could not load image {image_uri}: {e}\")\n", " continue\n", "\n", " # 3. Plot the image\n", " axs[i].imshow(image)\n", "\n", " # Determine the title\n", " if title_column and title_column in df.columns:\n", " title = f\"Cluster: {row[title_column]}\"\n", " else:\n", " title = image_path.split(\"/\")[-1]\n", "\n", " axs[i].set_title(title, fontsize=10)\n", " axs[i].axis(\"off\")\n", "\n", " # Hide any unused subplots\n", " for j in range(num_images, len(axs)):\n", " axs[j].axis(\"off\")\n", "\n", " plt.tight_layout()\n", " plt.show()" ] }, { "cell_type": "markdown", "id": "9YUTHxYpXGQH", "metadata": { "id": "9YUTHxYpXGQH" }, "source": [ "### Enable interactive tables for DataFrames\n", "\n", "For a better data exploration experience, you can enable a [Colab extension](https://colab.sandbox.google.com/notebooks/data_table.ipynb) that renders pandas DataFrames as interactive, sortable tables. This makes it much easier to inspect large amounts of data." ] }, { "cell_type": "code", "execution_count": null, "id": "h8DPEKFrXA2q", "metadata": { "id": "h8DPEKFrXA2q" }, "outputs": [], "source": [ "%load_ext google.colab.data_table" ] }, { "cell_type": "markdown", "id": "FONKhnbyWx-x", "metadata": { "id": "FONKhnbyWx-x" }, "source": [ "---\n", "## Data Preparation and Feature Engineering\n", "\n", "### Create a BigQuery dataset\n", "\n", "A [BigQuery dataset](https://cloud.google.com/bigquery/docs/datasets-intro) is a container that holds all of your other BigQuery objects, including tables, views, and models." ] }, { "cell_type": "code", "execution_count": null, "id": "L3Ih_zHiW5g8", "metadata": { "id": "L3Ih_zHiW5g8" }, "outputs": [], "source": [ "!bq mk --dataset housing_dataset" ] }, { "cell_type": "markdown", "id": "EYtdDkK-ZbD5", "metadata": { "id": "EYtdDkK-ZbD5" }, "source": [ "### Load the raw data into a BigQuery table\n", "\n", "You can now load your source data using the [`bq load`](https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv#loading_csv_data_into_a_table) command. It directly loads a CSV file from a public Google Cloud Storage bucket into a new BigQuery table named `listings`." ] }, { "cell_type": "code", "execution_count": null, "id": "1JqWUw49XxWN", "metadata": { "id": "1JqWUw49XxWN" }, "outputs": [], "source": [ "!bq load \\\n", " --autodetect \\\n", " --source_format=CSV \\\n", " --skip_leading_rows=1 \\\n", " housing_dataset.listings \\\n", " gs://$BUCKET_NAME/tabular/housing-data.csv" ] }, { "cell_type": "markdown", "id": "e6952daf", "metadata": { "id": "e6952daf" }, "source": [ "## Explore the data with `ML.DESCRIBE_DATA`\n", "\n", "Next, you'll perform exploratory data analysis to understand the data's characteristics. Instead of writing many individual `COUNT`, `AVG`, and `STDDEV` queries, you can use the [`ML.DESCRIBE_DATA`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-describe-data) function.\n", "\n", "The function returns descriptive statistics for each column in a table, giving you a quick overview of distributions, missing values, and potential outliers.\n" ] }, { "cell_type": "code", "execution_count": null, "id": "f1331e1c", "metadata": { "id": "f1331e1c" }, "outputs": [], "source": [ "%%bigquery --project {PROJECT_ID}\n", "\n", "SELECT *\n", "FROM\n", " ML.DESCRIBE_DATA((SELECT * FROM `housing_dataset.listings`));" ] }, { "cell_type": "markdown", "id": "gAOfp_g9IUbx", "metadata": { "id": "gAOfp_g9IUbx" }, "source": [ "### Clean, filter, and enrich the data\n", "\n", "Create a new, cleaned table from the raw data. The following query:\n", "\n", "1. **Filters the data** to include only listings that are currently 'For Sale'.\n", "2. **Creates a new feature**, `property_age`, by subtracting the `year_built` from the current year.\n", "3. **Creates an [`ObjectRef`](https://cloud.google.com/blog/products/data-analytics/new-objectref-data-type-brings-unstructured-data-into-bigquery?e=48754805)**, a BigQuery object that allows you to reference unstructured image data in Google Cloud Storage." ] }, { "cell_type": "code", "execution_count": null, "id": "dbfde5ec", "metadata": { "id": "dbfde5ec" }, "outputs": [], "source": [ "%%bigquery --project {PROJECT_ID}\n", "\n", "CREATE OR REPLACE TABLE `housing_dataset.listings` AS\n", "SELECT\n", " *,\n", " EXTRACT(YEAR FROM CURRENT_DATE()) - year_built AS property_age,\n", " OBJ.FETCH_METADATA(OBJ.MAKE_REF(house_uri, 'us.ai_connection')) AS image_ref\n", "FROM\n", " `housing_dataset.listings`\n", "WHERE sale_status = 'For Sale';" ] }, { "cell_type": "markdown", "id": "E19E_rcpcoXi", "metadata": { "id": "E19E_rcpcoXi" }, "source": [ "---\n", "## Multimodal Enrichment with AI Functions\n", "\n", "### Create a remote model for Gemini\n", "\n", "To use a like Gemini from within BigQuery, you need to create a [Remote Model](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-create-remote-model). This object acts as a secure pointer to the underlying model hosted on Vertex AI.\n", "\n", "Then, you can reference this remote model in your SQL queries, allowing you to use Gemini's powerful capabilities to analyze and enrich your data with functions like [`AI.GENERATE_TABLE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-generate-table)." ] }, { "cell_type": "code", "execution_count": null, "id": "muSly43kbGdt", "metadata": { "id": "muSly43kbGdt" }, "outputs": [], "source": [ "%%bigquery --project {PROJECT_ID}\n", "\n", "CREATE OR REPLACE MODEL `housing_dataset.gemini`\n", "REMOTE WITH CONNECTION `us.ai_connection`\n", " OPTIONS(ENDPOINT = 'gemini-2.5-flash');" ] }, { "cell_type": "markdown", "id": "DijByYGYIZIU", "metadata": { "id": "DijByYGYIZIU" }, "source": [ "### Enrich images with `AI.GENERATE_TABLE`\n", "\n", "You'll use the remote model with the `AI.GENERATE_TABLE` function. The following query contains:\n", "1. A **prompt** to the Gemini model, asking it to analyze the house image (`image_ref`) and extract specific features.\n", "2. An **output schema**. You can define exact column names and data types (e.g. `near_water BOOL`) you want it to return.\n", "\n", "The result is a new table, `listings_multimodal`, that contains all the columns plus the new, AI-generated features." ] }, { "cell_type": "code", "execution_count": null, "id": "7L0lP5SObaJr", "metadata": { "id": "7L0lP5SObaJr" }, "outputs": [], "source": [ "%%bigquery --project {PROJECT_ID}\n", "\n", "CREATE OR REPLACE TABLE `housing_dataset.listings_multimodal` AS (\n", "SELECT\n", "id,\n", "price,\n", "sq_ft,\n", "year_built,\n", "city,\n", "zipcode,\n", "number_of_rooms,\n", "number_of_baths,\n", "acre_lot,\n", "property_age,\n", "near_water,\n", "number_windows,\n", "prop_description,\n", "image_ref\n", "FROM AI.GENERATE_TABLE(\n", " MODEL `housing_dataset.gemini`,\n", " (\n", " SELECT (\n", " 'Analyze the following image to find whether it is near a body of water,'\n", " 'the number of windows, and give a brief description of the property.'\n", " , image_ref\n", " ) AS prompt,\n", " *\n", " FROM `housing_dataset.listings`\n", " ),\n", " STRUCT(\n", " \"near_water BOOL, number_windows INT64, prop_description STRING\" AS output_schema\n", " )\n", "));" ] }, { "cell_type": "markdown", "id": "6O5pFprsjIG_", "metadata": { "id": "6O5pFprsjIG_" }, "source": [ "### Review the enriched data\n", "\n", "Let's examine the new table. The last three columns (`near_water`, `number_windows`, `prop_description`) were generated by the Gemini model analyzing each house image." ] }, { "cell_type": "code", "execution_count": null, "id": "30HQkt6sjK7Z", "metadata": { "id": "30HQkt6sjK7Z" }, "outputs": [], "source": [ "%%bigquery --project {PROJECT_ID}\n", "\n", "SELECT *\n", "FROM `housing_dataset.listings_multimodal`\n", "LIMIT 2" ] }, { "cell_type": "markdown", "id": "e3522675", "metadata": { "id": "e3522675" }, "source": [ "---\n", "## Model Training with K-Means Clustering\n", "\n", "With your enriched and feature-engineered dataset, you can now build a machine learning model. Your goal is to segment the house listings into distinct groups based on their characteristics. K-means clustering is an unsupervised learning algorithm that groups data points based on their similarity.\n", "\n", "You'll use `CREATE MODEL` to train a K-means model with 3 clusters. By including the `model_registry='VERTEX_AI'` [option](https://cloud.google.com/bigquery/docs/managing-models-vertex), the model is also automatically registered in the [Vertex AI Model Registry](https://cloud.google.com/vertex-ai/docs/model-registry/introduction) upon completion. This makes it visible and deployable alongside your other custom models in Vertex AI." ] }, { "cell_type": "code", "execution_count": null, "id": "057ad575", "metadata": { "id": "057ad575" }, "outputs": [], "source": [ "%%bigquery --project {PROJECT_ID}\n", "\n", "CREATE OR REPLACE MODEL `housing_dataset.kmeans_clustering_model`\n", "OPTIONS(model_type='KMEANS', num_clusters=3,\n", " model_registry = 'VERTEX_AI', VERTEX_AI_MODEL_ID = 'housing_clustering') AS\n", "SELECT\n", " price,\n", " sq_ft,\n", " year_built,\n", " number_of_rooms,\n", " number_of_baths,\n", " acre_lot,\n", " property_age,\n", " near_water,\n", " number_windows\n", "FROM\n", " `housing_dataset.listings_multimodal`;" ] }, { "cell_type": "markdown", "id": "52e64434", "metadata": { "id": "52e64434" }, "source": [ "---\n", "## Model Evaluation and Prediction\n", "\n", "### Evaluate the clustering model\n", "\n", "Evaluate the model with [`ML.EVALUATE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-evaluate). This function returns key metrics like the Davies-Bouldin index to help you measure cluster quality and separation." ] }, { "cell_type": "code", "execution_count": null, "id": "26e0edd7", "metadata": { "id": "26e0edd7" }, "outputs": [], "source": [ "%%bigquery --project {PROJECT_ID}\n", "\n", "SELECT * FROM ML.EVALUATE(MODEL `housing_dataset.kmeans_clustering_model`);" ] }, { "cell_type": "markdown", "id": "rD6LwGszJ3bR", "metadata": { "id": "rD6LwGszJ3bR" }, "source": [ "### Analyze cluster centroids\n", "\n", "Inspect the cluster centroids with [`ML.CENTROIDS`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-centroids). Each centroid represents the \"average\" house in a cluster, making it easy to compare the defining characteristics of each segment." ] }, { "cell_type": "code", "execution_count": null, "id": "0R8ZGIjYJkg-", "metadata": { "id": "0R8ZGIjYJkg-" }, "outputs": [], "source": [ "%%bigquery --project {PROJECT_ID}\n", "\n", "SELECT * FROM ML.CENTROIDS(MODEL `housing_dataset.kmeans_clustering_model`)\n", "WHERE feature IN ( 'price', 'property_age' )\n", "ORDER BY feature, centroid_id;" ] }, { "cell_type": "markdown", "id": "wPrkl7Gcky_t", "metadata": { "id": "wPrkl7Gcky_t" }, "source": [ "### Assign listings to clusters with `ML.PREDICT`\n", "\n", "Use [`ML.PREDICT`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict) to assign each house to a cluster. The function returns a `CENTROID_ID` for each listing, indicating which cluster it belongs to. We'll rename `CENTROID_ID` to `cluster` for ease of use." ] }, { "cell_type": "code", "execution_count": null, "id": "bc9392ad", "metadata": { "id": "bc9392ad" }, "outputs": [], "source": [ "%%bigquery df --project {PROJECT_ID}\n", "\n", "SELECT\n", " CENTROID_ID AS cluster,\n", " * EXCEPT(CENTROID_ID)\n", "FROM\n", " ML.PREDICT(\n", " MODEL `housing_dataset.kmeans_clustering_model`,\n", " TABLE `housing_dataset.listings_multimodal`\n", ");" ] }, { "cell_type": "markdown", "id": "IiPMp9jkLGxj", "metadata": { "id": "IiPMp9jkLGxj" }, "source": [ "---\n", "\n", "\n", "## Visualize and Interpret Clusters\n", "\n", "With each property now assigned to a cluster, you can visualize the results to better understand the distinct housing segments." ] }, { "cell_type": "markdown", "id": "1FLU3-7fp6Ja", "metadata": { "id": "1FLU3-7fp6Ja" }, "source": [ "### Compare cluster characteristics\n", "\n", "Visualizations make it easy to compare the clusters. The following plots use:\n", "* **Box plots** to compare the distribution of numeric features like `price`.\n", "* **Bar charts** to compare the counts of boolean features like `near_water`." ] }, { "cell_type": "markdown", "id": "xU8jWif2HlPh", "metadata": { "id": "xU8jWif2HlPh" }, "source": [ "#### Box Plots for Numeric Features" ] }, { "cell_type": "code", "execution_count": null, "id": "hKjcSmkqpg7x", "metadata": { "id": "hKjcSmkqpg7x" }, "outputs": [], "source": [ "import seaborn as sns\n", "\n", "sns.set_style(\"whitegrid\")\n", "\n", "plt.figure(figsize=(14, 6))\n", "plt.suptitle(\"Sample Numeric Variable Distributions by Cluster\", fontsize=16)\n", "\n", "# Boxplot for Price\n", "plt.subplot(1, 2, 1)\n", "# Assign 'cluster' to hue and turn off the redundant legend\n", "sns.boxplot(\n", " x=\"cluster\", y=\"price\", data=df, hue=\"cluster\", palette=\"tab20\", legend=False\n", ")\n", "plt.title(\"Price Distribution per Cluster\")\n", "plt.ylabel(\"Price\")\n", "plt.xlabel(\"Cluster ID\")\n", "\n", "# Boxplot for Property Age\n", "plt.subplot(1, 2, 2)\n", "# Assign 'cluster' to hue and turn off the redundant legend\n", "sns.boxplot(\n", " x=\"cluster\", y=\"property_age\", data=df, hue=\"cluster\", palette=\"tab20\", legend=False\n", ")\n", "plt.title(\"Property Age Distribution per Cluster\")\n", "plt.ylabel(\"Property Age (Years)\")\n", "plt.xlabel(\"Cluster ID\")\n", "\n", "plt.tight_layout(rect=[0, 0, 1, 0.96])\n", "plt.show()" ] }, { "cell_type": "markdown", "id": "TvSRM4Xnp1Av", "metadata": { "id": "TvSRM4Xnp1Av" }, "source": [ "#### Bar Charts for Categorical/Boolean Features" ] }, { "cell_type": "code", "execution_count": null, "id": "omgSiqGpHHZ_", "metadata": { "id": "omgSiqGpHHZ_" }, "outputs": [], "source": [ "water_crosstab = pd.crosstab(df[\"cluster\"], df[\"near_water\"])\n", "\n", "water_proportions = water_crosstab.div(water_crosstab.sum(axis=1), axis=0)\n", "\n", "water_proportions.plot(kind=\"bar\", stacked=True, figsize=(10, 6), colormap=\"tab20\")\n", "\n", "plt.title(\"Proportion of Properties Near Water within Each Cluster\", fontsize=16)\n", "plt.xlabel(\"Cluster ID\")\n", "plt.ylabel(\"Proportion\")\n", "plt.xticks(rotation=0)\n", "plt.legend(title=\"Is Near Water?\", labels=[\"No\", \"Yes\"])\n", "plt.tight_layout()\n", "plt.show()" ] }, { "cell_type": "markdown", "id": "Nu7u6X7PlHUv", "metadata": { "id": "Nu7u6X7PlHUv" }, "source": [ "---\n", "## Generate Cluster Descriptions with Gemini\n", "\n", "While the centroids and visualizations are useful, you can use generative AI to create human-readable descriptions for each cluster.\n", "\n", "First, you'll aggregate the average statistics for each cluster into a pandas DataFrame." ] }, { "cell_type": "code", "execution_count": null, "id": "ECCeTbB7K9w4", "metadata": { "id": "ECCeTbB7K9w4" }, "outputs": [], "source": [ "%%bigquery df_cluster_stats --project {PROJECT_ID}\n", "\n", "SELECT\n", " centroid_id AS cluster,\n", " AVG(price) AS avg_price,\n", " AVG(sq_ft) AS avg_sq_ft,\n", " AVG(year_built) AS avg_year_built,\n", " AVG(number_of_rooms) AS avg_number_of_rooms,\n", " AVG(number_of_baths) AS avg_number_of_baths,\n", " AVG(acre_lot) AS avg_acre_lot,\n", " AVG(property_age) AS avg_property_age,\n", " AVG(number_windows) AS avg_number_windows,\n", " COUNT(*) AS cluster_size\n", "FROM\n", " ML.PREDICT(MODEL `housing_dataset.kmeans_clustering_model`,\n", " TABLE `housing_dataset.listings_multimodal`)\n", "GROUP BY\n", " cluster\n", "ORDER BY\n", " cluster;" ] }, { "cell_type": "markdown", "id": "XH2W6Ju7Lv24", "metadata": { "id": "XH2W6Ju7Lv24" }, "source": [ "### Prepare the prompt for Gemini\n", "\n", "Next, you'll convert the statistics DataFrame into a simple string and create a prompt template. This prompt instructs the model to act as a real estate professional and provides a clear structure for the desired output." ] }, { "cell_type": "code", "execution_count": null, "id": "w9Vzn69PgDgb", "metadata": { "id": "w9Vzn69PgDgb" }, "outputs": [], "source": [ "# Convert the df_cluster_stats to a string for input to a Gemini model\n", "cluster_info = df_cluster_stats.to_string()\n", "\n", "# Define the prompt template\n", "prompt = f\"\"\"You're a real estate professional. Come up with a description of each cluster.\n", "\n", "Clusters:\n", "{cluster_info}\n", "\n", "For each cluster, please return:\n", "1. A category for the cluster that describes the type of houses\n", "2. A couple of summary statistics - average price and square footage\n", "3. Key characteristics of the cluster\n", "4. A target buyer\n", "\"\"\"" ] }, { "cell_type": "markdown", "id": "EXWfa2Hsn2kY", "metadata": { "id": "EXWfa2Hsn2kY" }, "source": [ "### Generate cluster descriptions with a Gemini model\n", "\n", "With the prompt and data prepared, you can now send the request to a Gemini model through the Vertex AI SDK to generate the descriptions." ] }, { "cell_type": "code", "execution_count": null, "id": "gU9iZu-BgQdQ", "metadata": { "id": "gU9iZu-BgQdQ" }, "outputs": [], "source": [ "from IPython.display import Markdown\n", "from google import genai\n", "\n", "LOCATION = \"global\"\n", "\n", "client = genai.Client(vertexai=True, project=PROJECT_ID, location=LOCATION)\n", "response = client.models.generate_content(\n", " model=\"gemini-2.5-flash\",\n", " contents=prompt,\n", ")\n", "\n", "# Display the response\n", "display(Markdown(response.text))" ] }, { "cell_type": "markdown", "id": "t_E8RmodtbbP", "metadata": { "id": "t_E8RmodtbbP" }, "source": [ "### Visualize a sample from each cluster\n", "\n", "To complement the generated text descriptions, you can also visualize a sample image from each cluster. This gives a quick visual check on what a sample from each cluster looks like." ] }, { "cell_type": "code", "execution_count": null, "id": "tI4JfgONkqrV", "metadata": { "id": "tI4JfgONkqrV" }, "outputs": [], "source": [ "%%bigquery df_example_images --project {PROJECT_ID}\n", "\n", "# Select one row per centroid_id and generate signed URLs\n", "\n", "SELECT\n", " t1.centroid_id AS cluster,\n", " ANY_VALUE(t2.image_ref).uri AS uri,\n", " ANY_VALUE(t2.id) AS id\n", "FROM\n", " ML.PREDICT(MODEL `housing_dataset.kmeans_clustering_model`, TABLE `housing_dataset.listings_multimodal`) AS t1\n", "JOIN\n", " `housing_dataset.listings_multimodal` AS t2\n", "ON\n", " t1.id = t2.id\n", "GROUP BY\n", " cluster\n", "ORDER BY\n", " cluster" ] }, { "cell_type": "code", "execution_count": null, "id": "tNZEDhJhkr3H", "metadata": { "id": "tNZEDhJhkr3H" }, "outputs": [], "source": [ "display_images(df_example_images, uri_column=\"uri\", title_column=\"cluster\")" ] }, { "cell_type": "markdown", "id": "iUco9FZnl0iR", "metadata": { "id": "iUco9FZnl0iR" }, "source": [ "---\n", "## Automate Modeling with the Data Science Agent\n", "\n", "Now, you'll explore a different approach to the same problem. Instead of writing SQL and Python code manually, you will use the integrated [**Data Science Agent**](https://cloud.google.com/bigquery/docs/colab-data-science-agent) to automatically generate code for a K-means clustering model.\n", "\n", "This section is a placeholder to demonstrate where you would switch to the agent. In a live environment, you would open a new notebook and use the following prompt to have the agent build the model for you.\n", "\n", "To use the Data Science Agent, follow these steps:\n", "1. Follow [documentation steps](https://cloud.google.com/bigquery/docs/colab-data-science-agent#before_you_begin) to enable the proper APIs.\n", "2. Open a new Colab Enterprise Notebook in BigQuery.\n", "3. Open the Data Science Agent (it opens as a modal at the bottom of the screen or as a side pane).\n", "4. Write `@housing_dataset.listings_multimodal` to @ select the proper table for context.\n", "5. Give the Agent the following prompt and click-to-run the code that is generated: ```Use this table to generate a k-means clustering model to generate 3 clusters for housing listings. Then help me understand the characteristics of each of these clusters, so I can market to them as a real estate professional. Use Python.```\n", "6. Feel free to ask follow-up questions, ask for SQL or Spark instead of Python, or simply play around with the integrated agent.\n", "7. Once satisfied with the results, return back to this notebook for the next section." ] }, { "cell_type": "markdown", "id": "sfHY9-E2OxgG", "metadata": { "id": "sfHY9-E2OxgG" }, "source": [ "---\n", "## Multimodal Search with Embeddings and Vector Search\n", "\n", "In this final section, you will use a multimodal embedding model to convert images into [vector embeddings](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-generate-embedding#embeddings) and then use BigQuery's built-in [vector search](https://cloud.google.com/bigquery/docs/vector-search-intro) capabilities to find similar houses based on a text description or another image." ] }, { "cell_type": "markdown", "id": "64ORffYcmp_E", "metadata": { "id": "64ORffYcmp_E" }, "source": [ "### Create a remote model for embeddings\n", "\n", "Create a remote model that points to the [`multimodalembedding@001`](https://cloud.google.com/vertex-ai/generative-ai/docs/embeddings/get-multimodal-embeddings) model in Vertex AI. This model converts images and text into high-dimensional numeric vectors, known as embeddings." ] }, { "cell_type": "code", "execution_count": null, "id": "zXKyKu9yPLbn", "metadata": { "id": "zXKyKu9yPLbn" }, "outputs": [], "source": [ "%%bigquery --project {PROJECT_ID}\n", "\n", "CREATE OR REPLACE MODEL housing_dataset.multimodal_embedding_model\n", "REMOTE WITH CONNECTION DEFAULT\n", "OPTIONS (ENDPOINT = 'multimodalembedding@001');" ] }, { "cell_type": "markdown", "id": "7u3R0xGQm0Q7", "metadata": { "id": "7u3R0xGQm0Q7" }, "source": [ "### Generate image embeddings\n", "\n", "Use [`ML.GENERATE_EMBEDDING`](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-generate-embedding) to pass each house image to the model. This creates a new table, `home_embeddings`, containing the original image reference and its corresponding 1408-dimension vector embedding." ] }, { "cell_type": "code", "execution_count": null, "id": "1Z3WYs3sPf_D", "metadata": { "id": "1Z3WYs3sPf_D" }, "outputs": [], "source": [ "%%bigquery --project {PROJECT_ID}\n", "\n", "CREATE OR REPLACE TABLE housing_dataset.home_embeddings AS\n", " SELECT\n", " id,\n", " price,\n", " sq_ft,\n", " prop_description,\n", " image_ref,\n", " ml_generate_embedding_result AS mm_embedding\n", "FROM ML.GENERATE_EMBEDDING(\n", " MODEL housing_dataset.multimodal_embedding_model,\n", " (\n", " SELECT *, image_ref AS content from housing_dataset.listings_multimodal),\n", " STRUCT(TRUE AS flatten_json_output\n", " )\n", ");" ] }, { "cell_type": "markdown", "id": "mFW3MzIMSvvu", "metadata": { "id": "mFW3MzIMSvvu" }, "source": [ "### Review the generated embeddings\n", "\n", "Run a quick `SELECT` query to inspect the `home_embeddings` table. This is a good practice to verify that the `mm_embedding` column was created correctly before you build an index on it." ] }, { "cell_type": "code", "execution_count": null, "id": "xJzQpqNMRJOn", "metadata": { "id": "xJzQpqNMRJOn" }, "outputs": [], "source": [ "%%bigquery --project {PROJECT_ID}\n", "\n", "SELECT *\n", "FROM housing_dataset.home_embeddings\n", "LIMIT 2" ] }, { "cell_type": "markdown", "id": "VP9QOrB5T86e", "metadata": { "id": "VP9QOrB5T86e" }, "source": [ "### Create a vector index for efficient search\n", "\n", "To enable fast and scalable similarity searches, create a [`VECTOR INDEX`](https://cloud.google.com/bigquery/docs/vector-index) on the embedding column. The index allows BigQuery to find the nearest neighbors much more efficiently than a full table scan.\n", "\n", "Vector indexes are ideal for large datasets. Because we only have ~80 records here, we'll get an error that our data isn't large enough to benefit from an index." ] }, { "cell_type": "code", "execution_count": null, "id": "2lQL9YG1SJX-", "metadata": { "id": "2lQL9YG1SJX-" }, "outputs": [], "source": [ "%%bigquery --project $PROJECT_ID\n", "\n", "CREATE OR REPLACE\n", " VECTOR INDEX `house_images_index`\n", "ON\n", " housing_dataset.home_embeddings(mm_embedding)\n", " OPTIONS (\n", " index_type = 'IVF',\n", " distance_type = 'COSINE');" ] }, { "cell_type": "markdown", "id": "dVzjjsrqnt9T", "metadata": { "id": "dVzjjsrqnt9T" }, "source": [ "### Perform text-to-image search\n", "\n", "Now, perform a similarity search using [`VECTOR_SEARCH`](https://cloud.google.com/bigquery/docs/reference/standard-sql/search_functions#vector_search). A subquery first generates an embedding for the text \"house near the ocean\" and the main query then finds the 3 images whose embeddings are most similar to that text query.\n", "\n", "Note that we're searching text-to-image without using metadata tags on the images." ] }, { "cell_type": "code", "execution_count": null, "id": "PGFdnrHyUG-9", "metadata": { "id": "PGFdnrHyUG-9" }, "outputs": [], "source": [ "%%bigquery text_to_image_df --project {PROJECT_ID}\n", "\n", "SELECT base.image_ref.uri\n", " FROM\n", " VECTOR_SEARCH(\n", " TABLE `housing_dataset.home_embeddings`,\n", " 'mm_embedding',\n", " (\n", " -- GENERATE AN EMBEDDING AS A SUBQUERY\n", " SELECT\n", " ml_generate_embedding_result,\n", " content AS query\n", " FROM\n", " ML.GENERATE_EMBEDDING(\n", " MODEL housing_dataset.multimodal_embedding_model,\n", " ( SELECT \"house near the ocean\" AS content)\n", " )\n", " ),\n", " top_k => 3)\n", "ORDER BY distance ASC;" ] }, { "cell_type": "markdown", "id": "NG3VLVXkdCOx", "metadata": { "id": "NG3VLVXkdCOx" }, "source": [ "Next, let's visualize the top results returned from the `VECTOR_SEARCH`." ] }, { "cell_type": "code", "execution_count": null, "id": "7xrFhVGYWR0H", "metadata": { "id": "7xrFhVGYWR0H" }, "outputs": [], "source": [ "display_images(text_to_image_df)" ] }, { "cell_type": "markdown", "id": "S2XgHbMlnaNS", "metadata": { "id": "S2XgHbMlnaNS" }, "source": [ "### Perform image-to-image search\n", "\n", "The process for image-to-image search is nearly identical. Instead of using a text string, the subquery generates an embedding for a sample image URI, and `VECTOR_SEARCH` finds the most visually similar images." ] }, { "cell_type": "code", "execution_count": null, "id": "WqMDudECj5b7", "metadata": { "id": "WqMDudECj5b7" }, "outputs": [], "source": [ "# Get a sample house image from GCS\n", "sample_house = f\"gs://{bucket_name}/images/test_image/house_test_image.jpg\"\n", "image_df = pd.DataFrame({\"uri\": [sample_house]})\n", "display_images(image_df)" ] }, { "cell_type": "code", "execution_count": null, "id": "4jMgLYBmWVsr", "metadata": { "id": "4jMgLYBmWVsr" }, "outputs": [], "source": [ "%%bigquery image_to_image_df --project {PROJECT_ID}\n", "\n", "SELECT base.image_ref.uri\n", " FROM\n", " VECTOR_SEARCH(\n", " TABLE `housing_dataset.home_embeddings`,\n", " 'mm_embedding',\n", " (\n", " -- GENERATE AN EMBEDDING AS A SUBQUERY\n", " SELECT\n", " ml_generate_embedding_result,\n", " content AS query\n", " FROM\n", " ML.GENERATE_EMBEDDING(\n", " MODEL housing_dataset.multimodal_embedding_model,\n", " ( SELECT OBJ.FETCH_METADATA(OBJ.MAKE_REF('gs://drw001-data-science-with-notebooks/images/test_image/house_test_image.jpg', 'us.ai_connection')) AS content)\n", " )\n", " ),\n", " top_k => 3)\n", "ORDER BY distance ASC;" ] }, { "cell_type": "markdown", "id": "4heV5z3SdIE-", "metadata": { "id": "4heV5z3SdIE-" }, "source": [ "Visualize the results from the image-to-image vector search." ] }, { "cell_type": "code", "execution_count": null, "id": "YRG4GBtiX-5n", "metadata": { "id": "YRG4GBtiX-5n" }, "outputs": [], "source": [ "display_images(image_to_image_df)" ] }, { "cell_type": "markdown", "id": "rm4jpiP7HEhP", "metadata": { "id": "rm4jpiP7HEhP" }, "source": [ "---\n", "\n", "\n", "## Cleaning Up\n", "\n", "To clean up all Google Cloud resources used in this project, you can [delete the Google Cloud project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#shutting_down_projects) you used for the tutorial.\n", "\n", "Otherwise, you can delete the individual resources you created:" ] }, { "cell_type": "code", "execution_count": null, "id": "JSf6m7Z7j213", "metadata": { "id": "JSf6m7Z7j213" }, "outputs": [], "source": [ "# Delete the BigQuery tables\n", "!bq rm --table -f housing_dataset.listings\n", "!bq rm --table -f housing_dataset.listings_multimodal\n", "!bq rm --table -f housing_dataset.home_embeddings\n", "\n", "# Delete the remote model\n", "!bq rm --model -f housing_dataset.gemini\n", "!bq rm --model -f housing_dataset.kmeans_clustering_model\n", "!bq rm --model -f housing_dataset.multimodal_embedding_model\n", "\n", "# Delete the remote connection\n", "!bq rm --connection --project_id=$PROJECT_ID --location=us ai_connection\n", "\n", "# Delete the BigQuery dataset\n", "!bq rm -r -f $PROJECT_ID:housing_dataset" ] } ], "metadata": { "colab": { "collapsed_sections": [ "B8vBqblSM_9w" ], "name": "ai-assisted-data-science.ipynb", "toc_visible": true }, "kernelspec": { "display_name": "Python 3", "name": "python3" } }, "nbformat": 4, "nbformat_minor": 0 }