Generative AI Google Brain

gemini-use-cases-applying-llms-to-data-analyze-poster-images-in-bigquery-poster-image-analysis

3 min read

Analyzing movie posters in BigQuery with Gemini

Source notebook

Repo path: gemini/use-cases/applying-llms-to-data/analyze-poster-images-in-bigquery/poster_image_analysis.ipynb · Open on GitHub · intermediate

Analyzes movie poster images in BigQuery with Gemini, embeddings, and vector search.

Summary

This notebook teaches how to use Gemini models from BigQuery to analyze unstructured movie poster images stored in Cloud Storage. It creates a BigQuery object table, uses AI.GENERATE to extract movie title and release year from posters, then generates summaries. It also uses AI.EMBED and VECTOR_SEARCH to match poster-derived metadata to IMDB records and join ratings data for deeper analysis.

Key code patterns

Enable required APIs

!gcloud services enable aiplatform.googleapis.com bigqueryconnection.googleapis.com

BigQuery needs Vertex AI and connection APIs enabled before calling AI functions over external data.

Create cloud resource connection

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

The BigQuery connection lets an object table reference Cloud Storage and access Vertex AI models.

Create poster object table

CREATE OR REPLACE EXTERNAL TABLE `gemini_demo.movie_posters`
WITH CONNECTION `us.gemini_conn`
OPTIONS (
  object_metadata = 'SIMPLE',
  uris = ['gs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters/*']
);

Object tables expose Cloud Storage image objects as queryable BigQuery rows.

Generate structured image analysis

SELECT uri,
  STRING(OBJ.GET_ACCESS_URL(ref,'r').access_urls.read_url) AS signed_url,
  AI.GENERATE(
    prompt => ('What is the movie title and year of release for this poster?', OBJ.GET_ACCESS_URL(ref, 'r')),
    output_schema => 'title STRING, year INT64'
  ).* EXCEPT(full_response,status)
FROM `gemini_demo.movie_posters`;

AI.GENERATE can run multimodal analysis directly over object table image references and return typed fields.

Generate embeddings in SQL

SELECT title, year, uri,
  AI.EMBED(
    content => 'The movie titled ' || title || ' from the year ' || year,
    endpoint => 'gemini-embedding-001'
  ).result AS embedding
FROM `gemini_demo.movie_posters_results`;

AI.EMBED creates vector representations for title-year text inside BigQuery.

SELECT query.uri AS poster_uri,
  base.title AS imdb_title,
  base.movie_id AS imdb_movie_id,
  distance
FROM VECTOR_SEARCH(
  TABLE `gemini_demo.imdb_movies_embeddings`, 'embedding',
  TABLE `gemini_demo.movie_posters_results_embeddings`, 'embedding',
  top_k => 1,
  distance_type => 'COSINE'
);

VECTOR_SEARCH performs similarity joins between poster-derived embeddings and IMDB movie embeddings.

Models & APIs used

  • Models: gemini-embedding-001
  • APIs / services: Vertex AI API, BigQuery, BigQuery ML, BigQuery Connection API, Cloud Storage

When to use this

Use this pattern when unstructured Cloud Storage images need to be analyzed with Gemini and joined to structured BigQuery data.

Gotchas & caveats

  • The notebook uses billable BigQuery, BigQuery ML, and Vertex AI API services.
  • The Vertex AI API and BigQuery Connection API must be enabled.
  • A Cloud resource connection is created in location us and the dataset uses location US.
  • User accounts running Vertex AI model queries need BigQuery Job User and Vertex AI User roles.
  • For non-public Cloud Storage buckets, the connection service account needs the Storage Object Viewer role.
  • Cleanup requires deleting the project or removing the gemini_demo dataset.

Best practices

  • Create a dedicated dataset for the demo resources.
  • Use a Cloud resource connection for BigQuery access to Cloud Storage objects.
  • Use output_schema with AI.GENERATE when extracting structured fields.
  • Store intermediate Gemini analysis and embedding results in BigQuery tables.
  • Use public datasets and joins to enrich generated analysis with structured metadata.
  • Clean up created BigQuery resources after the tutorial.

Graph View

Backlinks