Generative AI Google Brain

gemini-use-cases-applying-llms-to-data-bigquery-ai-operators

4 min read

Semantic Analysis in BigQuery with AI Functions

Source notebook

Repo path: gemini/use-cases/applying-llms-to-data/bigquery_ai_operators.ipynb · Open on GitHub · intermediate

Uses BigQuery AI functions with Gemini to rank, classify, filter, join, and enrich pet product data.

Summary

This notebook teaches semantic analysis directly in BigQuery using managed AI functions and general-purpose AI functions. It creates a Cymbal Pets dataset from Cloud Storage, builds an object table for images, then uses SQL AI functions for ranking, classification, multimodal filtering, semantic joins, and row-level enrichment. It demonstrates when to use managed functions like AI.IF versus prompt-controlled functions like AI.GENERATE_BOOL, AI.GENERATE_INT, and AI.GENERATE_DOUBLE.

Key code patterns

Create BigQuery connection

!bq mk --connection --location=us \
    --connection_type=CLOUD_RESOURCE test_connection

A Cloud resource connection lets BigQuery interact with Vertex AI services.

Grant connection permissions

!gcloud projects add-iam-policy-binding --format=none $PROJECT_ID \
  --member=serviceAccount:$SERVICE_ACCT_EMAIL \
  --role='roles/aiplatform.user'
time.sleep(60)

The connection service account needs Vertex AI access, and IAM propagation can delay execution.

Load product and image data

CREATE SCHEMA IF NOT EXISTS cymbal_pets;
LOAD DATA OVERWRITE cymbal_pets.products
FROM FILES(format = 'avro', uris = ['gs://.../products_*.avro']);
CREATE OR REPLACE EXTERNAL TABLE cymbal_pets.product_images
WITH CONNECTION `us.test_connection`
OPTIONS (object_metadata = 'SIMPLE', uris = ['gs://.../*.png']);

The workflow combines structured product rows with an object table of product images.

Semantic ranking

SELECT product_name, description,
  AI.SCORE(('How "giftable" is this product for a pet owner? ',
    description, 'Use a scale from 1-10.'),
    connection_id => 'us.test_connection') AS giftability_score
FROM `cymbal_pets.products`
ORDER BY giftability_score DESC

AI.SCORE ranks rows by a natural-language criterion not present as a column.

Categorical classification

AI.CLASSIFY(
  ('What animal is this product for?', product_name, ' ', description),
  categories => ["Dog", "Cat", "Bird", "Fish", "Small Animal", "All Pets"],
  connection_id => 'us.test_connection') AS animal_type

AI.CLASSIFY assigns rows to explicit categories using product text.

Multimodal filtering

SELECT STRING(OBJ.GET_ACCESS_URL(ref, 'r').access_urls.read_url) AS signed_url,
  uri, metadata
FROM `cymbal_pets.product_images`
WHERE AI.IF(('Does this product image contain a ball? ', ref),
  connection_id => 'us.test_connection')

AI.IF evaluates a natural-language condition over image object references.

Semantic join

INNER JOIN `cymbal_pets.product_images` AS images
ON AI.IF(('You will be provided an image of a pet product. ',
  'Determine if the image is of the following pet toy: ',
  products.product_name, products.description, images.ref),
  connection_id => 'us.test_connection')

AI.IF can join text product descriptions to semantically matching product images.

Typed row enrichment

AI.GENERATE_BOOL(
  ('Does this product require electricity, batteries, or a power source to operate?',
   product_name, ' ', description),
  connection_id => 'us.test_connection',
  endpoint => 'gemini-2.5-flash').* EXCEPT(full_response)

General-purpose AI functions provide prompt control and typed outputs for SELECT enrichment.

Models & APIs used

  • Models: gemini-2.5-flash
  • APIs / services: BigQuery, BigQuery ML, Vertex AI, BigQuery Connection, Cloud Storage
  • SDKs / libraries: pandas, IPython.display, googleapis bigquery-magics

When to use this

Use this pattern when analysts need semantic ranking, filtering, joining, extraction, or enrichment inside BigQuery SQL.

Gotchas & caveats

  • Billing must be enabled for the Google Cloud project.
  • BigQuery, BigQuery Connection, and Vertex AI APIs must be enabled.
  • A Cloud resource connection in location us is required for the shown queries.
  • The connection service account needs roles/bigquery.connectionUser and roles/aiplatform.user.
  • The notebook waits 60 seconds for IAM propagation before running dependent cells.
  • AI.GENERATE_BOOL, AI.GENERATE_INT, and AI.GENERATE_DOUBLE use endpoint gemini-2.5-flash in the examples.
  • If endpoint is omitted for AI.GENERATE_BOOL, BigQuery selects a recent stable Gemini version.
  • AI.IF returns NULL for rows with errors, while AI.GENERATE_BOOL records errors in output status.

Best practices

  • Use managed AI functions for analysts who want prompt optimization handled by BigQuery.
  • Use AI.SCORE for semantic ranking by subjective criteria.
  • Use AI.CLASSIFY with explicit categories and a fallback category such as All Pets.
  • Use AI.IF for semantic filtering in WHERE clauses and semantic joins in JOIN ON clauses.
  • Use AI.GENERATE_BOOL for SELECT-clause enrichment when full prompt control is needed.
  • Use typed general-purpose functions to return BOOL, INT, or DOUBLE instead of free-form text.
  • Exclude full_response when only result and status fields are needed.
  • Use OBJ.GET_ACCESS_URL to display read-only URLs for object table images.

Graph View

Backlinks