Generative AI Google Brain

gemini-use-cases-applying-llms-to-data-multimodal-analysis-bigquery-analyze-multimodal-data-bigquery

4 min read

Analyze Multimodal Data in BigQuery

Source notebook

Repo path: gemini/use-cases/applying-llms-to-data/multimodal-analysis-bigquery/analyze_multimodal_data_bigquery.ipynb · Open on GitHub · intermediate

Shows BigQuery multimodal analysis over structured tables and GCS media using ObjectRefs and Gemini.

Summary

The notebook teaches how BigQuery ObjectRefs let SQL queries combine structured BigQuery rows with unstructured files in Google Cloud Storage. It demonstrates two workflows: creating ObjectRefs through an object table and creating them programmatically with OBJ.MAKE_REF and OBJ.FETCH_METADATA. It then runs multimodal AI queries with AI.GENERATE_BOOL, AI.GENERATE, and AI.GENERATE_TABLE to analyze audio, images, video, text, and arrays of ObjectRefs.

Key code patterns

Create cloud resource connection

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

Lets BigQuery access Vertex AI services through a managed connection.

Grant connection service account access

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 service account needs GCS object access and Vertex AI user permissions.

Create object table ObjectRefs

CREATE OR REPLACE EXTERNAL TABLE `bq_mm_tutorial.object_table`
WITH CONNECTION `us.test_connection`
OPTIONS (
  object_metadata = 'SIMPLE',
  uris = ['gs://sample-data-and-media/customer-support/calls/*.mp3']
);

An object table automatically creates a ref ObjectRef column for files in GCS.

Join structured rows to media refs

CREATE OR REPLACE TABLE `bq_mm_tutorial.calls_combined` AS
SELECT c.*, o.ref
FROM `bq_mm_tutorial.calls` AS c
LEFT JOIN `bq_mm_tutorial.object_table` AS o
ON c.call_id = REGEXP_EXTRACT(o.uri, r'calls/([^.]+)')

Builds one multimodal table containing business fields and a pointer to call audio.

Filter with multimodal boolean generation

SELECT company_name, customer_name, product_name
FROM `bq_mm_tutorial.calls_combined`
WHERE company_revenue > 15000000
AND AI.GENERATE_BOOL(
  prompt => ('Wants to buy something', ref),
  connection_id => 'us.test_connection').result

Combines normal SQL predicates with Gemini analysis of referenced audio.

Create refs from URI columns

OBJ.FETCH_METADATA(OBJ.MAKE_REF(m.audio_uri, 'us.test_connection')) AS audio_ref,
OBJ.FETCH_METADATA(OBJ.MAKE_REF(m.image_uri, 'us.test_connection')) AS image_ref,
OBJ.FETCH_METADATA(OBJ.MAKE_REF(m.video_uri, 'us.test_connection')) AS video_ref

Converts stored GCS URI strings into metadata-enriched ObjectRef fields.

Create a remote Gemini model

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

AI.GENERATE_TABLE uses this BigQuery remote model to call Gemini.

Generate structured multimodal output

SELECT ticket_id, issue, urgency_score, city_response_department
FROM AI.GENERATE_TABLE(
  MODEL `bq_mm_tutorial.gemini`,
  (SELECT (description, image_ref, audio_ref, video_ref) AS prompt,
          ticket_id, description
   FROM `bq_mm_tutorial.reports_mm`),
  STRUCT('issue STRING, urgency_score INT64, city_response_department STRING' AS output_schema)
)

Passes text, image, audio, and video refs into one model call and returns typed columns.

Models & APIs used

  • Models: gemini-2.5-flash
  • APIs / services: BigQuery, BigQuery ML, Vertex AI, BigQuery Connection, Cloud Storage
  • SDKs / libraries: google.colab.data_table

When to use this

Use this pattern when SQL teams need to analyze BigQuery tables together with GCS-hosted images, audio, or video without moving files into table rows.

Gotchas & caveats

  • Billing must be enabled and the BigQuery, BigQuery Connection, and Vertex AI APIs must be enabled.
  • The notebook creates the BigQuery connection and dataset in the US location and uses connection id us.test_connection.
  • The connection service account must have roles/storage.objectViewer and roles/aiplatform.user.
  • IAM changes may need about 60 seconds to propagate before later cells succeed.
  • Local JupyterLab use requires Cloud SDK setup and gcloud auth login.
  • AI.GENERATE_TABLE requires a BigQuery remote model before calling Gemini.

Best practices

  • Use ObjectRefs as secure pointers instead of storing unstructured file bytes in BigQuery tables.
  • Use object tables when every file in a GCS path should automatically become an ObjectRef row.
  • Use OBJ.MAKE_REF and OBJ.FETCH_METADATA when URI columns already exist in structured tables.
  • Join structured records with ObjectRef columns to create a reusable multimodal table.
  • Define output_schema for AI.GENERATE and AI.GENERATE_TABLE to return typed, queryable results.
  • Aggregate ObjectRefs with ARRAY_AGG when generating grouped summaries.
  • Clean up BigQuery tables, remote models, connections, and datasets after the tutorial.

Graph View

Backlinks