Generative AI Google Brain

gemini-use-cases-applying-llms-to-data-ai-assisted-data-science-ai-assisted-data-science

4 min read

AI-Assisted Data Science Workflows in BigQuery

Source notebook

Repo path: gemini/use-cases/applying-llms-to-data/ai-assisted-data-science/ai-assisted-data-science.ipynb · Open on GitHub · advanced

Builds a BigQuery multimodal housing workflow with Gemini enrichment, BQML clustering, and vector search.

Summary

This notebook teaches an end-to-end SQL-native data science workflow in BigQuery using a housing listings dataset and images stored in Cloud Storage. It loads and cleans tabular data, creates BigQuery ObjectRefs for images, enriches listings with Gemini via BigQuery AI functions, trains and evaluates a K-means BQML model, and generates human-readable cluster descriptions. It then contrasts the manual workflow with the BigQuery Data Science Agent and builds multimodal text-to-image and image-to-image search using embeddings and VECTOR_SEARCH.

Key code patterns

BigQuery cloud connection

!bq mk --connection --location=us --connection_type=CLOUD_RESOURCE ai_connection
SERVICE_ACCT = !bq show --format=prettyjson --connection us.ai_connection | grep "serviceAccountId" | cut -d '"' -f 4
!gcloud projects add-iam-policy-binding $PROJECT_ID --member=serviceAccount:$SERVICE_ACCT_EMAIL --role='roles/storage.objectViewer'
!gcloud projects add-iam-policy-binding $PROJECT_ID --member=serviceAccount:$SERVICE_ACCT_EMAIL --role='roles/aiplatform.user'

The connection lets BigQuery read GCS images and call Vertex AI models through its service account.

ObjectRef image feature

CREATE OR REPLACE TABLE `housing_dataset.listings` AS
SELECT
  *,
  EXTRACT(YEAR FROM CURRENT_DATE()) - year_built AS property_age,
  OBJ.FETCH_METADATA(OBJ.MAKE_REF(house_uri, 'us.ai_connection')) AS image_ref
FROM `housing_dataset.listings`
WHERE sale_status = 'For Sale';

ObjectRefs bring unstructured image data into BigQuery queries alongside tabular features.

Gemini remote model

CREATE OR REPLACE MODEL `housing_dataset.gemini`
REMOTE WITH CONNECTION `us.ai_connection`
OPTIONS(ENDPOINT = 'gemini-2.5-flash');

A BigQuery remote model exposes Gemini to SQL functions such as AI.GENERATE_TABLE.

Structured multimodal enrichment

SELECT *
FROM AI.GENERATE_TABLE(
  MODEL `housing_dataset.gemini`,
  (SELECT ('Analyze the following image...', image_ref) AS prompt, *
   FROM `housing_dataset.listings`),
  STRUCT("near_water BOOL, number_windows INT64, prop_description STRING" AS output_schema)
);

The notebook constrains Gemini output to typed columns for downstream analytics and modeling.

BQML clustering with registry

CREATE OR REPLACE MODEL `housing_dataset.kmeans_clustering_model`
OPTIONS(model_type='KMEANS', num_clusters=3,
        model_registry='VERTEX_AI', VERTEX_AI_MODEL_ID='housing_clustering') AS
SELECT price, sq_ft, year_built, number_of_rooms,
       number_of_baths, acre_lot, property_age, near_water, number_windows
FROM `housing_dataset.listings_multimodal`;

The clustering model is trained in BigQuery and registered in Vertex AI Model Registry.

Gemini cluster descriptions

client = genai.Client(vertexai=True, project=PROJECT_ID, location="global")
response = client.models.generate_content(
    model="gemini-2.5-flash",
    contents=prompt,
)
display(Markdown(response.text))

Cluster statistics are converted into a prompt to produce real estate-oriented segment descriptions.

Multimodal embeddings

CREATE OR REPLACE MODEL housing_dataset.multimodal_embedding_model
REMOTE WITH CONNECTION DEFAULT
OPTIONS (ENDPOINT = 'multimodalembedding@001');
 
CREATE OR REPLACE TABLE housing_dataset.home_embeddings AS
SELECT id, image_ref, ml_generate_embedding_result AS mm_embedding
FROM ML.GENERATE_EMBEDDING(MODEL housing_dataset.multimodal_embedding_model,
  (SELECT *, image_ref AS content FROM housing_dataset.listings_multimodal),
  STRUCT(TRUE AS flatten_json_output));

Image ObjectRefs are converted into 1408-dimension embeddings stored in BigQuery.

Text-to-image vector search

SELECT base.image_ref.uri
FROM VECTOR_SEARCH(
  TABLE `housing_dataset.home_embeddings`, 'mm_embedding',
  (SELECT ml_generate_embedding_result, content AS query
   FROM ML.GENERATE_EMBEDDING(
     MODEL housing_dataset.multimodal_embedding_model,
     (SELECT "house near the ocean" AS content))),
  top_k => 3)
ORDER BY distance ASC;

A text embedding is compared with image embeddings to retrieve visually relevant houses.

Models & APIs used

  • Models: gemini-2.5-flash, multimodalembedding@001
  • APIs / services: BigQuery, BigQuery ML, Vertex AI, Cloud Storage, IAM, Colab Enterprise
  • SDKs / libraries: google-genai, google-cloud-storage, pandas, matplotlib, Pillow, seaborn, IPython

When to use this

Use this pattern when housing or other asset data combines structured records with images and needs SQL-native enrichment, segmentation, and similarity search.

Gotchas & caveats

  • The BigQuery cloud resource connection must be created in location us and referenced as us.ai_connection.
  • The connection service account needs roles/storage.objectViewer to read images and roles/aiplatform.user to call Vertex AI.
  • The notebook waits about 60 seconds for IAM propagation before running dependent cells.
  • The embedding remote model uses CONNECTION DEFAULT while the Gemini remote model uses us.ai_connection.
  • The VECTOR INDEX example may error because the sample has only about 80 records, too small to benefit from an index.
  • The Data Science Agent section is a placeholder requiring a live Colab Enterprise Notebook in BigQuery and prior API enablement.

Best practices

  • Create a dedicated BigQuery dataset to contain tutorial tables, views, and models.
  • Use ML.DESCRIBE_DATA for quick exploratory statistics before modeling.
  • Filter raw data and add engineered features before AI enrichment and clustering.
  • Define an explicit AI.GENERATE_TABLE output schema for reliable typed columns.
  • Inspect enriched rows and generated embeddings before downstream modeling or indexing.
  • Use ML.EVALUATE, ML.CENTROIDS, and ML.PREDICT to evaluate and interpret BQML clusters.
  • Register the BQML model in Vertex AI Model Registry using model_registry=‘VERTEX_AI’.
  • Clean up created BigQuery tables, models, connection, and dataset after the tutorial.

Graph View

Backlinks