Perform semantic search and retrieval-augmented generation

In this tutorial, you use a remote model with the AI.GENERATE_EMBEDDING function to generate text embeddings in a BigQuery table. You then create a vector index to index the embeddings to improve search performance.

You use the VECTOR_SEARCH function with the embeddings to search for similar text. Vector search is a technique to compare similar objects using embeddings, which are high-dimensional numerical vectors that represent a given entity, like a piece of text.

Finally, you perform retrieval-augmented generation (RAG) by generating text with the AI.GENERATE_TEXT function. RAG is an AI framework that combines the strengths of information retrieval systems (such as search and databases) with the capabilities of generative large language models (LLMs). By combining your data and world knowledge with LLM language skills, grounded generation is more accurate, up-to-date, and relevant to your specific needs.

This tutorial uses data from the Google Patents Research public dataset.

Objectives

  • Create a BigQuery ML remote model over a Gemini Enterprise Agent Platform embedding model.
  • Use the remote model with the AI.GENERATE_EMBEDDING function to generate embeddings from text in a BigQuery table.
  • Create a vector index to index the embeddings in order to improve search performance.
  • Use the VECTOR_SEARCH function with the embeddings to search for similar text.
  • Perform RAG by generating text with the AI.GENERATE_TEXT function, and using vector search results to augment the prompt input and improve results.

Costs

In this document, you use the following billable components of Google Cloud:

  • BigQuery ML: You incur costs for the data that you process in BigQuery.
  • Gemini Enterprise Agent Platform: You incur costs for calls to the Agent Platform service that's represented by the remote model.

To generate a cost estimate based on your projected usage, use the pricing calculator.

New Google Cloud users might be eligible for a free trial.

For more information, see the following pricing pages:

Before you begin

  1. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

  2. Verify that billing is enabled for your Google Cloud project.

  3. Enable the BigQuery, Cloud Storage, and Gemini Enterprise Agent Platform APIs.

    Roles required to enable APIs

    To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). Learn how to grant roles.

    Enable the APIs

Required roles

To get the permissions that you need to complete this tutorial, ask your administrator to grant you the following IAM roles:

For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

Create a dataset

To create a BigQuery dataset, select one of the following options:

Console

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. In the left pane, click Explorer:

    Highlighted button for the Explorer pane.

    If you don't see the left pane, click Expand left pane to open the pane.

  3. In Explorer, expand your project, and then click Datasets.

  4. On the Datasets page, click Create dataset.

  5. In the Create dataset pane, do the following:

    • For Dataset ID, enter bqml_tutorial.

    • For Data location, select US.

    Leave the remaining default settings as they are.

  6. Click Create dataset.

bq

To create a new dataset, use the bq mk --dataset command.

  1. Create a dataset named bqml_tutorial with the data location set to US:

    bq mk --dataset \
  --location=US \
  --description "BigQuery ML tutorial dataset." \
  bqml_tutorial

  2. Confirm that the dataset was created:

    bq ls

API

Call the datasets.insert method with a defined dataset resource:

{
  "datasetReference": {
     "datasetId": "bqml_tutorial"
  }
}

Create the remote model for text embedding generation

In this section, you create a remote model that represents a hosted Agent Platform text embedding generation model. When you create the model, you use the DEFAULT connection to call the Text embeddings API to get text embeddings using the text-embedding-005 model. If you don't have a default connection, the CREATE MODEL statement creates one for you.

To create the text embedding model, follow these steps:

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. To create the model, paste this command into the query editor, and then click Run:

    CREATE OR REPLACE MODEL `bqml_tutorial.embedding_model`
  REMOTE WITH CONNECTION DEFAULT
  OPTIONS (ENDPOINT = 'text-embedding-005');

    The query takes several seconds to complete, after which the model embedding_model can be accessed through the Explorer pane.

    You receive a confirmation message like the following: Successfully created model named embedding_model.

Generate text embeddings

Generate text embeddings from patent abstracts using the AI.GENERATE_EMBEDDING function, and then write them to a BigQuery table so that they can be searched.

Embedding generation using the AI.GENERATE_EMBEDDING function might fail due to Agent Platform LLM quotas or service unavailability. If it fails, error details are returned in the status column in the query results.

For alternative text embedding generation methods in BigQuery, see the Embed text with pretrained TensorFlow models tutorial.

To generate text embeddings, paste this command into the query editor, and then click Run:

  CREATE OR REPLACE TABLE bqml_tutorial.embeddings AS
  SELECT * FROM AI.GENERATE_EMBEDDING(
    MODEL bqml_tutorial.embedding_model,
    (
      SELECT *, abstract AS content
      FROM patents-public-data.google_patents_research.publications
      WHERE LENGTH(abstract) > 0 AND LENGTH(title) > 0 AND country = 'Singapore'
    )
  )
  WHERE LENGTH(status) = 0;

This query takes several minutes to complete. You receive a confirmation message like the following:This statement created a new table named embeddings.

Create a vector index

If you create a vector index on an embedding column, a vector search that's performed on that column uses the Approximate Nearest Neighbor search technique. This technique improves vector search performance and returns more approximate results, but recall is reduced.

To create a vector index, you use the CREATE VECTOR INDEX data definition language (DDL) statement. To verify that the index is available, you query the INFORMATION_SCHEMA.VECTOR_INDEXES view to verify that the coverage_percentage column value is greater than 0, and the last_refresh_time column value isn't NULL.

To create and verify the vector index, follow these steps:

  1. To create the vector index, paste this command into the query editor, and then click Run:

    CREATE OR REPLACE VECTOR INDEX my_index
ON `bqml_tutorial.embeddings`(embedding)
OPTIONS(index_type = 'IVF',
  distance_type = 'COSINE',
  ivf_options = '{"num_lists":500}');

    You receive a confirmation message like the following: The vector index creation on table bqml_tutorial.embeddings was initiated. Please query bqml_tutorial.INFORMATION_SCHEMA.VECTOR_INDEXES to check the progress of the index.

    Creating a vector index typically takes only a few seconds. It takes another two to three minutes for the vector index to be populated asynchronously.

  2. To verify that the index is ready to be used, paste this command into the query editor, and then click Run:

    SELECT table_name, index_name, index_status,
coverage_percentage, last_refresh_time, disable_reason
FROM `PROJECT_ID.bqml_tutorial.INFORMATION_SCHEMA.VECTOR_INDEXES`;

    Replace PROJECT_ID with your project ID.

    After you run the query, the index is available if the index_status column in the results shows that the index is ACTIVE, and if the coverage_percentage value is 100.

Perform a text similarity search using the vector index

Use the VECTOR_SEARCH function to search for relevant patents that match embeddings generated from a text query.

The top_k argument determines the number of matches to return, in this case five. The fraction_lists_to_search option determines the percentage of vector index lists to search. The vector index you created has 500 lists, so the fraction_lists_to_search value of .01 indicates that this vector search scans five of those lists. A lower fraction_lists_to_search value as shown here provides lower recall and faster performance.

For more information about vector index lists, see the num_lists vector index option.

The model you use to generate the embeddings in this query must be the same as the one you use to generate the embeddings in the table you're comparing against, otherwise the search results won't be accurate.

To perform a text similarity search, paste this command into the query editor, and then click Run:

  SELECT query.query, base.publication_number, base.title, base.abstract
  FROM VECTOR_SEARCH(
    TABLE bqml_tutorial.embeddings, 'embedding',
    (
    SELECT embedding, content AS query
    FROM AI.GENERATE_EMBEDDING(
    MODEL bqml_tutorial.embedding_model,
    (SELECT 'improving password security' AS content))
    ),
    top_k => 5, options => '{"fraction_lists_to_search": 0.01}');

The output is similar to the following:

+-----------------------------+--------------------+-------------------------------------------------+-------------------------------------------------+
|            query            | publication_number |                       title                     |                      abstract                   |
+-----------------------------+--------------------+-------------------------------------------------+-------------------------------------------------+
| improving password security | SG-120868-A1       | Data storage device security method and a...    | Methods for improving security in data stora... |
| improving password security | SG-10201610585W-A  | Passsword management system and process...      | PASSSWORD MANAGEMENT SYSTEM AND PROCESS ...     |
| improving password security | SG-148888-A1       | Improved system and method for...               | IMPROVED SYSTEM AND METHOD FOR RANDOM...        |
| improving password security | SG-194267-A1       | Method and system for protecting a password...  | A system for providing security for a...        |
| improving password security | SG-120868-A1       | Data storage device security...                 | Methods for improving security in data...       |
+-----------------------------+--------------------+-------------------------------------------------+-------------------------------------------------+

Create the remote model for text generation

To create a remote model that represents a hosted Agent Platform text generation model, paste this command into the query editor, and then click Run:

  CREATE OR REPLACE MODEL bqml_tutorial.text_model
    REMOTE WITH CONNECTION DEFAULT
    OPTIONS (ENDPOINT = 'gemini-2.5-flash');

You receive a confirmation message similar to the following: Successfully created model named text_model.

Generate text augmented by vector search results

Feed the search results as prompts to generate text with the AI.GENERATE_TEXT function.

To augment the vector search results, paste this command into the query editor, and then click Run:

  SELECT result AS generated, prompt
  FROM AI.GENERATE_TEXT(
    MODEL bqml_tutorial.text_model,
    (
      SELECT CONCAT(
        'Propose some project ideas to improve user password security using the context below: ',
        STRING_AGG(
          FORMAT("patent title: %s, patent abstract: %s", base.title, base.abstract),
          ',\n')
        ) AS prompt,
      FROM VECTOR_SEARCH(
        TABLE bqml_tutorial.embeddings, 'embedding',
        (
          SELECT embedding, content AS query
          FROM AI.GENERATE_EMBEDDING(
            MODEL bqml_tutorial.embedding_model,
            (SELECT 'improving password security' AS content)
          )
        ),
      top_k => 5, options => '{"fraction_lists_to_search": 0.01}')
    ),
    STRUCT(600 AS max_output_tokens));

The output is similar to the following:

+------------------------------------------------+------------------------------------------------------------+
|            generated                           | prompt                                                     |
+------------------------------------------------+------------------------------------------------------------+
| These patents suggest several project ideas to | Propose some project ideas to improve user password        |
| improve user password security.  Here are      | security using the context below: patent title: Active     |
| some, categorized by the patent they build     | new password entry dialog with compact visual indication   |
| upon:                                          | of adherence to password policy, patent abstract:          |
|                                                | An active new password entry dialog provides a compact     |
| **I. Projects based on "Active new password    | visual indication of adherence to password policies. A     |
| entry dialog with compact visual indication of | visual indication of progress towards meeting all          |
| adherence to password policy":**               | applicable password policies is included in the display    |
|                                                | and updated as new password characters are being...        |
+------------------------------------------------+------------------------------------------------------------+

Clean up

To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, either delete the project that contains the resources, or keep the project and delete the individual resources.

  1. In the Google Cloud console, go to the Manage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then click Delete.
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

Alternatively, to keep the project and delete the resources used in this tutorial, follow these steps:

  1. Go to the BigQuery page.

    Go to BigQuery

  2. In the left pane, expand your project, and then click Datasets.

  3. For the bqml_tutorial dataset, click Open actions > Delete.

  4. In the Delete dataset dialog, click Delete to confirm.

  5. In the left pane, click Connections.

  6. For the __default_cloudresource_connection__ connection, click Open actions > Delete.

  7. In the Delete connection dialog, enter delete, and then click Delete to confirm.

What's next