Generative AI on Google Cloud

38 / 80·notebook

Chapter 38

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

NotebookPython 329 cells

In [ ]python · cell 1

python

# Copyright 2025 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     https://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

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

Author(s)
Jasper Xu, Haiyang Qi

Overview

This notebook showcases a simple end-to-end process for generating text embeddings using BigQuery in conjunction with both Gemini and OSS text embedding models. We use Google gemini-embedding-001 and the open-source multilingual-e5-small model as examples, and the process involves:

Deploying the multilingual-e5-small model from HuggingFace to Agent Platform.
Creating a remote model in BigQuery for against the Gemini embedding model, and the deployed OSS model endpoint.
Employing the ML.GENERATE_EMBEDDING function to generate embeddings from text data using both models.
Cleaning up deployed resources to manage costs.

Costs

This tutorial uses billable components of Google Cloud:

Agent Platform
BigQuery

Learn about Agent Platform pricing and BigQuery pricing, and use the Pricing Calculator to generate a cost estimate based on your projected usage.

Get started

Install Google Agent Platform SDK and other required packages

In [ ]python · cell 7

python

%pip install --upgrade google-cloud-aiplatform

Authenticate your notebook environment (Colab only)

If you're running this notebook on Google Colab, run the cell below to authenticate your environment.

In [ ]python · cell 9

python

import sys

if "google.colab" in sys.modules:
    from google.colab import auth

    auth.authenticate_user()

Set Google Cloud project information

To get started using Agent Platform and BigQuery, you must have an existing Google Cloud project and enable the Agent Platform API & BigQuery API.

In [ ]python · cell 11

python

# Use the environment variable if the user doesn't provide Project ID.
import os

# fmt: off
PROJECT_ID = "[your-project-id]"  # @param {type: "string", placeholder: "[your-project-id]", isTemplate: true}
if not PROJECT_ID or PROJECT_ID == "[your-project-id]":
    PROJECT_ID = str(os.environ.get("GOOGLE_CLOUD_PROJECT"))

LOCATION = "us-central1"  # @param {type: "string", placeholder: "[your-preferred-location]", isTemplate: true}
# fmt: on

import vertexai
from google.cloud import aiplatform

aiplatform.init(project=PROJECT_ID, location=LOCATION)

vertexai.init(
    project=PROJECT_ID,
    location=LOCATION,
)

from google.cloud import bigquery

bq_client = bigquery.Client()

Create a New BigQuery Dataset

This will house any tables and models created throughout this notebook

In [ ]python · cell 13

python

!bq mk --location={LOCATION} --dataset --project_id={PROJECT_ID} demo_dataset

Use Gemini Embedding Model in BigQuery

First, let's explore how to generate embeddings using the state-of-the-art Gemini embedding model directly in BigQuery. This process involves two simple steps: creating a remote model and then using it for inference.

Create a Remote Model in BigQuery

Before you can generate embeddings, you need to create a REMOTE MODEL against the gemini-embedding-001 in BigQuery, using the statement below:

In [ ]python · cell 16

python

%%bigquery --project $PROJECT_ID

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

Generate Embeddings

Once the model is created, you can call the ML.GENERATE_EMBEDDING function to generate embeddings. The following code will generate embeddings for 10,000 records from the public bigquery-public-data.hacker_news.full dataset.

In [ ]python · cell 18

python

%%bigquery --project $PROJECT_ID

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
    )
  );

Use an OSS Text Embedding Model in BigQuery

Now, let's walk through using an open-source model. This process gives you maximum flexibility and control over quality and scalability. Unlike using the managed Gemini model, this workflow involves hosting the model yourself on an Agent Platform endpoint.

Deploy an OSS Model to an Agent Platform Endpoint

First, you need to choose an open-source model from a repository like Hugging Face and deploy it to an Agent Platform endpoint. For this example, we use intfloat/multilingual-e5-small, which delivers respectable performance (ranking 38th on the Massive Text Embedding Benchmark) while being massively scalable and cost-effective. The following code will deploy the model, which creates prediction server with dedicated-resource for your use.

The model is served by default on a single g2-standard-12 machine replica with one NVIDIA_L4 GPU. You can adjust the min_replica_count, max_replica_count, and machine_type to balance scalability and cost.

In [ ]python · cell 21

python

from vertexai import model_garden

model = model_garden.OpenModel("publishers/intfloat/models/e5@multilingual-e5-small")

# BigQuery only support public shared endpoint currently. Dedicated endpoint is not supported
endpoint = model.deploy(dedicated_endpoint_disabled=True)

Create a Remote Model in BigQuery

Similar to the Gemini workflow, you need to create a remote model in BigQuery. However, this time the model will point to the URL of the Agent Platform endpoint you just created. This tells BigQuery where to send the data for embedding generation.

In [ ]python · cell 23

python

ENDPOINT_ID = f"https://{LOCATION}-aiplatform.googleapis.com/v1/projects/{PROJECT_ID}/locations/{LOCATION}/endpoints/{endpoint.name}"
print("Endpoint ID: ", ENDPOINT_ID)

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()

Generate Embeddings

With the model created, you can use the exact same ML.GENERATE_EMBEDDING function as before. For this particular E5 model with default deployment settings, it takes around 2 hour and 10 minutes to embed over 38M non-null rows in the Hacker News dataset.

In [ ]python · cell 25

python

%%bigquery --project $PROJECT_ID

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

Delete the Agent Platform Model and Endpoint

This is a critical step for cost management. Since you deployed an OSS model to an active endpoint, it will continue to incur costs even when idle. The following code will "undeploy" the model from the endpoint, which stops the billing.

For batch workloads, the most cost-effective pattern is to deploy the model, run your inference job, and immediately undeploy it, achieving by run these steps sequentially.

In [ ]python · cell 27

python

endpoint.undeploy_all()
endpoint.delete()

Cleaning up

To clean up all Google Cloud resources used in this project, you can delete the Google Cloud project you used for the tutorial.

Otherwise, you can delete BigQuery dataset created in this demo, assuming you have already ran the above code block to delete resources on the Agent Platform side:

In [ ]python · cell 29

python

!bq rm -r -f --dataset {PROJECT_ID}:demo_dataset

← 返回课程

Generative AI on Google Cloud

38 / 80·notebook

Chapter 38

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

NotebookPython 329 cells

In [ ]python · cell 1

python

# Copyright 2025 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     https://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

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

Author(s)
Jasper Xu, Haiyang Qi

Overview

Deploying the multilingual-e5-small model from HuggingFace to Agent Platform.
Creating a remote model in BigQuery for against the Gemini embedding model, and the deployed OSS model endpoint.
Employing the ML.GENERATE_EMBEDDING function to generate embeddings from text data using both models.
Cleaning up deployed resources to manage costs.

Costs

This tutorial uses billable components of Google Cloud:

Agent Platform
BigQuery

Learn about Agent Platform pricing and BigQuery pricing, and use the Pricing Calculator to generate a cost estimate based on your projected usage.

Get started

Install Google Agent Platform SDK and other required packages

In [ ]python · cell 7

python

%pip install --upgrade google-cloud-aiplatform

Authenticate your notebook environment (Colab only)

If you're running this notebook on Google Colab, run the cell below to authenticate your environment.

In [ ]python · cell 9

python

import sys

if "google.colab" in sys.modules:
    from google.colab import auth

    auth.authenticate_user()

Set Google Cloud project information

To get started using Agent Platform and BigQuery, you must have an existing Google Cloud project and enable the Agent Platform API & BigQuery API.

In [ ]python · cell 11

python

# Use the environment variable if the user doesn't provide Project ID.
import os

# fmt: off
PROJECT_ID = "[your-project-id]"  # @param {type: "string", placeholder: "[your-project-id]", isTemplate: true}
if not PROJECT_ID or PROJECT_ID == "[your-project-id]":
    PROJECT_ID = str(os.environ.get("GOOGLE_CLOUD_PROJECT"))

LOCATION = "us-central1"  # @param {type: "string", placeholder: "[your-preferred-location]", isTemplate: true}
# fmt: on

import vertexai
from google.cloud import aiplatform

aiplatform.init(project=PROJECT_ID, location=LOCATION)

vertexai.init(
    project=PROJECT_ID,
    location=LOCATION,
)

from google.cloud import bigquery

bq_client = bigquery.Client()

Create a New BigQuery Dataset

This will house any tables and models created throughout this notebook

In [ ]python · cell 13

python

!bq mk --location={LOCATION} --dataset --project_id={PROJECT_ID} demo_dataset

Use Gemini Embedding Model in BigQuery

Create a Remote Model in BigQuery

Before you can generate embeddings, you need to create a REMOTE MODEL against the gemini-embedding-001 in BigQuery, using the statement below:

In [ ]python · cell 16

python

%%bigquery --project $PROJECT_ID

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

Generate Embeddings

In [ ]python · cell 18

python

%%bigquery --project $PROJECT_ID

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
    )
  );

Use an OSS Text Embedding Model in BigQuery

Deploy an OSS Model to an Agent Platform Endpoint

In [ ]python · cell 21

python

from vertexai import model_garden

model = model_garden.OpenModel("publishers/intfloat/models/e5@multilingual-e5-small")

# BigQuery only support public shared endpoint currently. Dedicated endpoint is not supported
endpoint = model.deploy(dedicated_endpoint_disabled=True)

Create a Remote Model in BigQuery

In [ ]python · cell 23

python

ENDPOINT_ID = f"https://{LOCATION}-aiplatform.googleapis.com/v1/projects/{PROJECT_ID}/locations/{LOCATION}/endpoints/{endpoint.name}"
print("Endpoint ID: ", ENDPOINT_ID)

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()

Generate Embeddings

In [ ]python · cell 25

python

%%bigquery --project $PROJECT_ID

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

Delete the Agent Platform Model and Endpoint

For batch workloads, the most cost-effective pattern is to deploy the model, run your inference job, and immediately undeploy it, achieving by run these steps sequentially.

In [ ]python · cell 27

python

endpoint.undeploy_all()
endpoint.delete()

Cleaning up

To clean up all Google Cloud resources used in this project, you can delete the Google Cloud project you used for the tutorial.

Otherwise, you can delete BigQuery dataset created in this demo, assuming you have already ran the above code block to delete resources on the Agent Platform side:

In [ ]python · cell 29

python

!bq rm -r -f --dataset {PROJECT_ID}:demo_dataset