Perform semantic search and retrieval-augmented generation
This tutorial guides you through the end-to-end process of creating and using text embeddings, including using vector indexes to improve search performance.
This tutorial covers the following tasks:
- Creating a BigQuery ML remote model over a Vertex AI embedding model.
- Using the remote model with the
ML.GENERATE_EMBEDDING
function to generate embeddings from text in a BigQuery table. - Creating a vector index to index the embeddings.
- Using the
VECTOR_SEARCH
function with the embeddings to search for similar text. - Perform retrieval-augmented generation (RAG) by generating text with the
ML.GENERATE_TEXT
function, and using vector search results to augment the prompt input and improve results.
This tutorial uses the BigQuery public table
patents-public-data.google_patents_research.publications
.
Required roles and permissions
To create a connection, you need membership in the following Identity and Access Management (IAM) role:
roles/bigquery.connectionAdmin
To grant permissions to the connection's service account, you need the following permission:
resourcemanager.projects.setIamPolicy
The IAM permissions needed in this tutorial for the remaining BigQuery operations are included in the following two roles:
- BigQuery Data Editor (
roles/bigquery.dataEditor
) to create models, tables, and indexes. - BigQuery User (
roles/bigquery.user
) to run BigQuery jobs.
- BigQuery Data Editor (
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.
- Vertex AI: You incur costs for calls to the Vertex AI service that's represented by the remote model.
To generate a cost estimate based on your projected usage,
use the pricing calculator.
For more information about BigQuery pricing, see BigQuery pricing in the BigQuery documentation.
For more information about Vertex AI pricing, see the Vertex AI pricing page.
Before you begin
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
Enable the BigQuery, BigQuery Connection, and Vertex AI APIs.
Create a dataset
Create a BigQuery dataset to store your ML model:
In the Google Cloud console, go to the BigQuery page.
In the Explorer pane, click your project name.
Click
View actions > Create dataset.On the Create dataset page, do the following:
For Dataset ID, enter
bqml_tutorial
.For Location type, select Multi-region, and then select US (multiple regions in United States).
The public datasets are stored in the
US
multi-region. For simplicity, store your dataset in the same location.Leave the remaining default settings as they are, and click Create dataset.
Create a connection
Create a Cloud resource connection and get the connection's service account. Create the connection in the same location as the dataset you created in the previous step.
Select one of the following options:
Console
Go to the BigQuery page.
To create a connection, click
Add, and then click Connections to external data sources.In the Connection type list, select Vertex AI remote models, remote functions and BigLake (Cloud Resource).
In the Connection ID field, enter a name for your connection.
Click Create connection.
Click Go to connection.
In the Connection info pane, copy the service account ID for use in a later step.
bq
In a command-line environment, create a connection:
bq mk --connection --location=REGION --project_id=PROJECT_ID \ --connection_type=CLOUD_RESOURCE CONNECTION_ID
The
--project_id
parameter overrides the default project.Replace the following:
REGION
: your connection regionPROJECT_ID
: your Google Cloud project IDCONNECTION_ID
: an ID for your connection
When you create a connection resource, BigQuery creates a unique system service account and associates it with the connection.
Troubleshooting: If you get the following connection error, update the Google Cloud SDK:
Flags parsing error: flag --connection_type=CLOUD_RESOURCE: value should be one of...
Retrieve and copy the service account ID for use in a later step:
bq show --connection PROJECT_ID.REGION.CONNECTION_ID
The output is similar to the following:
name properties 1234.REGION.CONNECTION_ID {"serviceAccountId": "connection-1234-9u56h9@gcp-sa-bigquery-condel.iam.gserviceaccount.com"}
Terraform
Append the following section into your main.tf
file.
## This creates a cloud resource connection. ## Note: The cloud resource nested object has only one output only field - serviceAccountId. resource "google_bigquery_connection" "connection" { connection_id = "CONNECTION_ID" project = "PROJECT_ID" location = "REGION" cloud_resource {} }Replace the following:
CONNECTION_ID
: an ID for your connectionPROJECT_ID
: your Google Cloud project IDREGION
: your connection region
Grant the service account access
Grant the connection's service account the Vertex AI User role. You must grant this role in the same project you created or selected in the
Before you begin section. Granting the role in a different project results in the error bqcx-1234567890-xxxx@gcp-sa-bigquery-condel.iam.gserviceaccount.com does not have the permission to access resource
.
To grant the role, follow these steps:
Go to the IAM & Admin page.
Click
Grant Access.In the New principals field, enter the service account ID that you copied earlier.
In the Select a role field, choose Vertex AI, and then select Vertex AI User role.
Click Save.
Create the remote model for text embedding generation
Create a remote model that represents a hosted Vertex AI text embedding generation model:
In the Google Cloud console, go to the BigQuery page.
In the query editor, run the following statement:
CREATE OR REPLACE MODEL `bqml_tutorial.embedding_model` REMOTE WITH CONNECTION `LOCATION.CONNECTION_ID` OPTIONS (ENDPOINT = 'text-embedding-004');
Replace the following:
LOCATION
: the connection locationCONNECTION_ID
: the ID of your BigQuery connectionWhen you view the connection details in the Google Cloud console, the
CONNECTION_ID
is the value in the last section of the fully qualified connection ID that is shown in Connection ID, for exampleprojects/myproject/locations/connection_location/connections/myconnection
The query takes several seconds to complete, after which the model
embedding_model
appears in thebqml_tutorial
dataset in the Explorer pane. Because the query uses aCREATE MODEL
statement to create a model, there are no query results.
Generate text embeddings
Generate text embeddings from patent abstracts using the
ML.GENERATE_EMBEDDING
function,
and then write them to a BigQuery table so that they can be
searched.
In the Google Cloud console, go to the BigQuery page.
In the query editor, run the following statement:
CREATE OR REPLACE TABLE `bqml_tutorial.embeddings` AS SELECT * FROM ML.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(ml_generate_embedding_status) = 0;
Embedding generation using the
ML.GENERATE_EMBEDDING
function
might fail due to Vertex AI LLM quotas
or service unavailability. Error details are returned in the
ml_generate_embedding_status
column. An empty ml_generate_embedding_status
column indicates successful embedding generation.
For alternative text embedding generation methods in BigQuery, see the Embed text with pretrained TensorFlow models tutorial.
Create a vector index
To create a vector index, use the
CREATE VECTOR INDEX
data definition language (DDL) statement:
Go to the BigQuery page.
In the query editor, run the following SQL statement:
CREATE OR REPLACE VECTOR INDEX my_index ON `bqml_tutorial.embeddings`(ml_generate_embedding_result) OPTIONS(index_type = 'IVF', distance_type = 'COSINE', ivf_options = '{"num_lists":500}')
Verify vector index creation
The vector index is populated asynchronously. You can check whether the index is
ready to be used by querying the
INFORMATION_SCHEMA.VECTOR_INDEXES
view
and verifying that the coverage_percentage
column value is greater than 0
and the last_refresh_time
column value isn't NULL
.
Go to the BigQuery page.
In the query editor, run the following SQL statement:
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.
Perform a text similarity search using the vector index
Use the
VECTOR_SEARCH
function
to search for the top 5 relevant patents that match embeddings generated from a
text query. 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 are
comparing against, otherwise the search results won't be accurate.
Go to the BigQuery page.
In the query editor, run the following SQL statement:
SELECT query.query, base.publication_number, base.title, base.abstract FROM VECTOR_SEARCH( TABLE `bqml_tutorial.embeddings`, 'ml_generate_embedding_result', ( SELECT ml_generate_embedding_result, content AS query FROM ML.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
Create a remote model that represents a hosted Vertex AI text generation model:
In the Google Cloud console, go to the BigQuery page.
In the query editor, run the following statement:
CREATE OR REPLACE MODEL `bqml_tutorial.text_model` REMOTE WITH CONNECTION `LOCATION.CONNECTION_ID` OPTIONS (ENDPOINT = 'text-bison-32k');
Replace the following:
LOCATION
: the connection locationCONNECTION_ID
: the ID of your BigQuery connectionWhen you view the connection details in the Google Cloud console, the
CONNECTION_ID
is the value in the last section of the fully qualified connection ID that is shown in Connection ID, for exampleprojects/myproject/locations/connection_location/connections/myconnection
The query takes several seconds to complete, after which the model
text_model
appears in thebqml_tutorial
dataset in the Explorer pane. Because the query uses aCREATE MODEL
statement to create a model, there are no query results.
Generate text augmented by vector search results
Feed the search results as prompts to generate text with the
ML.GENERATE_TEXT
function
In the Google Cloud console, go to the BigQuery page.
In the query editor, run the following statement:
SELECT ml_generate_text_llm_result AS generated, prompt FROM ML.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`, 'ml_generate_embedding_result', ( SELECT ml_generate_embedding_result, content AS query FROM ML.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, TRUE AS flatten_json_output));
The output is similar to the following:
+------------------------------------------------+------------------------------------------------------------+ | generated | prompt | +------------------------------------------------+------------------------------------------------------------+ | **Project Ideas to Improve User Password | Propose some project ideas to improve user password | | Security** | security using the context below: patent title: Active | | | new password entry dialog with compact visual indication | | 1. **Develop a password manager that uses | of adherence to password policy, patent abstract: | | visual clues to indicate password strength.** | An active new password entry dialog provides a compact | | This could be done by using a color-coded... | visual indication of adherence to password policies. A | | 2. **Create a system that generates random | visual indication of progress towards meeting all | | passwords for users.** This would help to | applicable password policies is included in the display | | ensure that users are not using easily... | and updated as new password characters are being... | +------------------------------------------------+------------------------------------------------------------+
Clean up
- In the Google Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.