Generative AI Google Brain

gemini-use-cases-applying-llms-to-data-bigquery-embeddings-vector-search

3 min read

Text + multimodal embedding generation and vector search in BigQuery

Source notebook

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

Builds text and image embeddings in BigQuery for semantic product search.

Summary

This notebook teaches how to create a semantic search utility for a fictional e-commerce pet store using BigQuery and Vertex AI remote models. It loads product data from Cloud Storage, generates text embeddings from product names and descriptions, and runs BigQuery VECTOR_SEARCH for text-to-text matching. It then generates multimodal embeddings from product images using signed object access URLs and performs text-to-image vector search.

Key code patterns

Create BigQuery remote embedding model

CREATE OR REPLACE MODEL `cymbal_pets.text_embedding_model`
  REMOTE WITH CONNECTION `us.cymbal_conn`
  OPTIONS (ENDPOINT = 'gemini-embedding-001');

Lets BigQuery SQL call a Vertex AI hosted embedding model through a Cloud resource connection.

Generate and store text embeddings

UPDATE `cymbal_pets.products` AS t
SET t.text_embedding = s.ml_generate_embedding_result
FROM (
  SELECT product_id, ml_generate_embedding_result
  FROM ML.GENERATE_EMBEDDING(
    MODEL `cymbal_pets.text_embedding_model`,
    (SELECT product_id, CONCAT(product_name, ' ', description) AS content FROM `cymbal_pets.products`),
    STRUCT(TRUE AS flatten_json_output)
  )
) AS s
WHERE t.product_id = s.product_id;

Persists generated embeddings in an ARRAY column for later vector search.

Text-to-text vector search

SELECT base.product_id, base.product_name, distance
FROM VECTOR_SEARCH(
  TABLE `cymbal_pets.products`,
  'text_embedding',
  (SELECT ml_generate_embedding_result, content AS query
   FROM ML.GENERATE_EMBEDDING(
     MODEL `cymbal_pets.text_embedding_model`,
     (SELECT "kitten toy" AS content))),
  top_k => 3)
ORDER BY distance ASC;

Embeds a query string and compares it against stored product text embeddings.

Generate image embeddings from GCS URIs

SELECT
  product_id,
  OBJ.GET_ACCESS_URL(
    OBJ.FETCH_METADATA(OBJ.MAKE_REF(uri, 'us.cymbal_conn')), 'r'
  ) AS content
FROM `cymbal_pets.products`;

Creates temporary access URLs so BigQuery ML can embed product images stored in Cloud Storage.

Models & APIs used

  • Models: gemini-embedding-001, multimodalembedding@001
  • APIs / services: BigQuery, BigQuery ML, Vertex AI, BigQuery Connection, Cloud Storage
  • SDKs / libraries: google-cloud-storage, matplotlib, Pillow, pandas

When to use this

Use this pattern when product or media data already lives in BigQuery and you need SQL-native semantic search over text and images.

Gotchas & caveats

  • Billing must be enabled and BigQuery, BigQuery Connection, and Vertex AI APIs must be enabled.
  • A CLOUD_RESOURCE connection named cymbal_conn is created in the US location.
  • The connection service account needs roles/bigquery.connectionUser, roles/aiplatform.user, and roles/storage.objectViewer.
  • The notebook adds a 60 second pause for IAM permission propagation.
  • Image embedding requires Cloud Storage object access through OBJ.MAKE_REF, OBJ.FETCH_METADATA, and OBJ.GET_ACCESS_URL because ObjectRefs are not stored in the table.
  • Resources created by the notebook should be deleted or the project should be shut down to avoid ongoing costs.

Best practices

  • Create a dedicated BigQuery dataset before loading tutorial tables.
  • Use BigQuery remote models instead of exporting data to call Vertex AI separately.
  • Store embeddings in ARRAY columns for reuse with VECTOR_SEARCH.
  • Combine product_name and description before generating text embeddings to improve semantic context.
  • Order vector search results by distance ascending.
  • Clean up BigQuery tables, remote models, connections, and datasets after the tutorial.

Graph View

Backlinks