Generative AI Google Brain

embeddings-bigquery-ml-gemini-and-oss-text-embedding

3 min read

Use Gemini and OSS Text-Embedding Models Against Your BigQuery Data

Source notebook

Repo path: embeddings/bigquery_ml_gemini_and_oss_text_embedding.ipynb · Open on GitHub · intermediate

Generate BigQuery embeddings with Gemini and a deployed OSS E5 model.

Summary

This notebook demonstrates two BigQuery ML embedding workflows over text from the public Hacker News dataset. It first creates a BigQuery remote model for gemini-embedding-001 and calls ML.GENERATE_EMBEDDING. It then deploys an OSS multilingual E5 embedding model to an Agent Platform endpoint, creates a BigQuery remote model pointing at that endpoint, generates embeddings with the same SQL function, and cleans up endpoint and dataset resources.

Key code patterns

Initialize clients

import vertexai
from google.cloud import aiplatform, bigquery
 
aiplatform.init(project=PROJECT_ID, location=LOCATION)
vertexai.init(project=PROJECT_ID, location=LOCATION)
bq_client = bigquery.Client()

Sets the project, region, Vertex AI SDK context, and BigQuery client used by later steps.

Gemini remote model

CREATE OR REPLACE MODEL demo_dataset.gemini_embedding_model
REMOTE WITH CONNECTION DEFAULT
OPTIONS(endpoint="gemini-embedding-001")

Creates a BigQuery remote model that targets the managed Gemini embedding endpoint.

Generate embeddings in SQL

SELECT *
FROM ML.GENERATE_EMBEDDING(
  MODEL demo_dataset.gemini_embedding_model,
  (
    SELECT text AS content
    FROM bigquery-public-data.hacker_news.full
    WHERE text IS NOT NULL
    LIMIT 10000
  )
);

Uses BigQuery ML to generate embeddings from table rows after mapping text into the required content column.

Deploy OSS embedding model

from vertexai import model_garden
 
model = model_garden.OpenModel(
    "publishers/intfloat/models/e5@multilingual-e5-small"
)
endpoint = model.deploy(dedicated_endpoint_disabled=True)

Deploys an open model to a public shared Agent Platform endpoint that BigQuery can call.

OSS endpoint remote model

ENDPOINT_ID = f"https://{LOCATION}-aiplatform.googleapis.com/v1/projects/{PROJECT_ID}/locations/{LOCATION}/endpoints/{endpoint.name}"
query = f"""
CREATE OR REPLACE MODEL demo_dataset.multilingual_e5_small
REMOTE WITH CONNECTION DEFAULT
OPTIONS(endpoint='{ENDPOINT_ID}');
"""
bq_client.query_and_wait(query).to_dataframe()

Registers the deployed Agent Platform endpoint as a BigQuery remote model.

Cleanup endpoint

endpoint.undeploy_all()
endpoint.delete()
!bq rm -r -f --dataset {PROJECT_ID}:demo_dataset

Stops endpoint billing and removes the demo BigQuery dataset.

Models & APIs used

  • Models: gemini-embedding-001, publishers/intfloat/models/e5@multilingual-e5-small
  • APIs / services: Agent Platform, BigQuery, BigQuery ML
  • SDKs / libraries: google-cloud-aiplatform, vertexai, google.cloud.bigquery

When to use this

Use this pattern when generating text embeddings directly from BigQuery data with either managed Gemini embeddings or a self-hosted OSS embedding endpoint.

Gotchas & caveats

  • Requires an existing Google Cloud project with Agent Platform API and BigQuery API enabled.
  • The tutorial uses billable Agent Platform and BigQuery components.
  • Colab requires auth.authenticate_user().
  • BigQuery currently supports only public shared endpoints for this OSS workflow; dedicated endpoint is not supported.
  • The OSS endpoint uses a single g2-standard-12 replica with one NVIDIA_L4 GPU by default unless deployment settings are adjusted.
  • The deployed OSS endpoint continues to incur costs while idle unless undeployed and deleted.
  • The E5 example is noted as taking around 2 hours and 10 minutes to embed over 38M non-null Hacker News rows with default deployment settings.

Best practices

  • Create a dedicated BigQuery dataset for demo tables and models.
  • Filter out NULL text values before calling ML.GENERATE_EMBEDDING.
  • Use the same ML.GENERATE_EMBEDDING interface for managed Gemini and deployed OSS remote models.
  • Adjust min_replica_count, max_replica_count, and machine_type to balance scalability and cost.
  • For batch workloads, deploy the OSS model, run inference, then immediately undeploy it.
  • Delete the Agent Platform endpoint and BigQuery dataset after the tutorial to manage costs.

Graph View

Backlinks