The AI.SEARCH function
This document describes the AI.SEARCH function, which is a table-valued
function for semantic search and hybrid search on tables that have
autonomous embedding generation
enabled.
For example, you could use a query like the following to search a table of
product descriptions for anything described as a fun toy. In this example,
the product_description column has autonomous embedding generation enabled.
SELECT *
FROM AI.SEARCH(TABLE product_table, 'product_description', "A really fun toy");
You can also perform semantic search using an ObjectRef input if the base table
has autonomous embedding generation enabled for an ObjectRef column, as long as
that table's embedding generation model supports the ObjectRef data type:
SELECT *
FROM AI.SEARCH(
TABLE mydataset.images,
'ref',
'A really fun toy' -- Searching a table of images with a text query is
-- possible with a multimodal embedding model
);
Embeddings are high-dimensional numerical vectors that represent a given entity.
Embeddings encode semantics about entities
to make it easier to reason about and compare them. If two entities are
semantically similar, then their respective embeddings are located near each
other in the embedding vector space. The AI.SEARCH function embeds your
search query and searches the table that you provide for embeddings in the input
table that are close to it.
You can also perform a hybrid search that combines a semantic vector search
with a lexical (keyword) search. By default, the mode parameter is set to
AUTO which enables AI.SEARCH to automatically perform a hybrid search if
your table has a vector index configured with lexical search columns. If your
table has a vector index on the embedding column, then AI.SEARCH uses it to
optimize the search.
To improve the precision of your search results, you can enable semantic
reranking by setting the rerank
argument to TRUE. When reranking is enabled, AI.SEARCH retrieves an initial
set of candidate rows using semantic or hybrid search, and then evaluates
query_value and each row's column_to_search text together to score and
reorder the candidates by how well the text answers the search query, similar
to the
AI.RANK function.
You can use AI.SEARCH to help with the following tasks:
- Semantic search: search entities ranked by semantic similarity.
- Hybrid search: search entities combining semantic similarity and lexical keyword matching.
- Recommendation: return entities with attributes similar to a given entity.
- Classification: return the class of entities whose attributes are similar to the given entity.
- Clustering: cluster entities whose attributes are similar to a given entity.
- Outlier detection: return entities whose attributes are least related to the given entity.
Syntax
AI.SEARCH( { TABLE base_table | base_table_query }, column_to_search, query_value, mode => mode_value [, top_k => top_k_value ] [, distance_type => distance_type_value ] [, rerank => rerank_value ] [, options => options_value] )
Arguments
AI.SEARCH takes the following arguments:
base_table: The table to search for nearest neighbor embeddings. The table must have autonomous embedding generation enabled.base_table_query: A query that you can use to pre-filter the base table. OnlySELECT,FROM, andWHEREclauses are allowed in this query. Don't apply any filters to the embedding column. You can't use logical views in this query. Using a subquery might interfere with index usage or cause your query to fail. If the base table is indexed and theWHEREclause contains columns that are not stored in the index, thenAI.SEARCHuses post-filters on those columns instead. To learn more, see Store columns and pre-filter.column_to_search: ASTRINGliteral that contains the name of the column to search. This must be the name of the source column (of typeSTRINGorObjectRef) that the automatically generated embedding column is based on, but it's not the name of the generated embedding column itself. If the column has a vector index, BigQuery attempts to use it. To determine if an index was used in the vector search, see Vector index usage.query_value: A string literal orObjectRefvalue that represents the search query. This value is embedded at runtime using the same connection and endpoint specified for the base table's embedding generation. If the base table's embedding generation uses atask_typeofRETRIEVAL_DOCUMENT, whether by default or explicitly specified, thenAI.SEARCHuses atask_typeofRETRIEVAL_QUERYto embedquery_value. Otherwise,AI.SEARCHuses the sametask_typeconfigured for the base table. You must have the BigQuery Connection User role (roles/bigquery.connectionUser) on the connection that the base table uses for background embedding generation. If embedding generation fails forquery_value, then the whole query fails. Rows with missing embeddings in the base table are skipped during the search.mode(Preview): One of the following values:VECTOR,HYBRID, orAUTO. The default value isAUTO. When the mode is set toAUTO, a hybrid search is performed if a hybrid index exists. If no hybrid index exists, a semantic-only search is performed. To run a hybrid search, set the mode toHYBRID. To run a semantic-only search, set the mode toVECTOR.top_k: A named argument with anINT64value.top_k_valuespecifies the number of nearest neighbors to return. The default is10. If the value is negative, all values are counted as neighbors and returned.distance_type: A named argument with aSTRINGvalue.distance_type_valuespecifies the type of metric to use to compute the distance between two vectors. Supported distance types areEUCLIDEAN,COSINE, andDOT_PRODUCT. The default isEUCLIDEAN.If you don't specify
distance_type_valueand thecolumn_to_searchcolumn has a vector index that's used, thenAI.SEARCHuses the distance type specified in thedistance_typeoption of theCREATE VECTOR INDEXstatement.rerank: ABOOLvalue that specifies whether to rerank the retrieved candidates using the ranking API:TRUE: Enables semantic reranking.AI.SEARCHretrieves an initial set of candidates from the vector search or hybrid search stage and then evaluatesquery_valueand each candidate row'scolumn_to_searchtext together to score and reorder the candidates by relevance.FALSE(default): Disables semantic reranking.
When you set
reranktoTRUE, the following requirements apply:column_to_searchmust be of typeSTRING. Candidate rows withNULLor empty string values incolumn_to_searchare excluded prior to reranking.query_valuemust be a non-emptySTRINGliteral or query parameter.ObjectRefvalues aren't supported.- Reranking uses your
end-user credentials
to call the Agent Search ranking API in the project that runs the query.
You must enable the Discovery Engine API
(
discoveryengine.googleapis.com) in that project, and your account must have thediscoveryengine.rankingConfigs.rankpermission on the project. This permission is included in the Discovery Engine Viewer role (roles/discoveryengine.viewer). For more information, read about access control for Agent Search.
options: A named argument with a JSON-formattedSTRINGvalue.options_valueis a literal that specifies the following search options:fraction_lists_to_search: A JSON number that specifies the percentage of lists to search. For example,options => '{"fraction_lists_to_search":0.15}'. Thefraction_lists_to_searchvalue must be in the range0.0to1.0, exclusive.Specifying a higher percentage leads to higher recall and slower performance, and the converse is true when specifying a lower percentage.
fraction_lists_to_searchis only used when a vector index is also used. If you don't specify afraction_lists_to_searchvalue but an index is matched, an appropriate value is picked.The number of available lists to search is determined by the
num_listsoption in theivf_optionsoption or derived from theleaf_node_embedding_countoption in thetree_ah_optionsoption of theCREATE VECTOR INDEXstatement if specified. Otherwise, BigQuery calculates an appropriate number.You can't specify
fraction_lists_to_searchwhenuse_brute_forceis set totrue.use_brute_force: A JSON boolean that determines whether to use brute force search by skipping the vector index if one is available. For example,options => '{"use_brute_force":true}'. The default isfalse. If you specifyuse_brute_force=falseand there is no usable vector index available, brute force is used anyway.reranker: A JSON object that configures semantic reranking. You can only specifyrerankerwhenrerankis set toTRUE. For example,options => '{"reranker": {"num_candidates": 50}}'. Thererankerobject supports the following fields:num_candidates: A JSON integer that specifies the number of candidates retrieved from vector or hybrid search to pass to the reranker for reordering. For example,options => '{"reranker": {"num_candidates": 30}}'. The default istop_k * 5. Whentop_kis positive,num_candidatesmust be at leasttop_k, or negative to rerank all rows in the base table. Whentop_kis negative,num_candidatesmust be negative or omitted. The number of candidate rows passed to the reranker can't exceed 10,000,000.model: A JSON string that specifies the ranking model to use. If you don't specify this option,semantic-ranker-default@latestis used, which always points to the latest available ranking model. We recommend that you omit this option so that your queries pick up model improvements automatically.
optionsdefaults to'{}'to denote that all underlying options use their corresponding default values.
Details
You can optionally use AI.SEARCH with a
vector index. When
a vector index is used, AI.SEARCH uses the Approximate Nearest
Neighbor
search technique to help improve vector search performance, with
the trade-off of reducing
recall
and so returning more approximate
results. When a base table is large, the use of an index typically improves
performance without significantly sacrificing recall. Brute force is used to
return exact results when a vector index isn't available, and you can
choose to use brute force to get exact results even when a vector index
is available.
Output
The output includes the following columns:
base: ASTRUCTvalue that contains all columns frombase_tableor a subset of the columns frombase_tablethat you selected in thebase_table_queryquery.distance: AFLOAT64value that represents the distance between thequery_valueand the embedding incolumn_to_search.rerank_score: AFLOAT64value that contains the relevance of the text incolumn_to_searchtoquery_value, in the range of0.0to1.0. Higher values indicate greater relevance. This column is only included in the output whenrerankis set toTRUE, and the output rows are sorted byrerank_scorein descending order.The
rerank_scorevalue is intended for comparing rows within a single result set. Because scores aren't calibrated across queries, a static threshold (such asrerank_score > 0.5) might work well for one query while excluding relevant results for another. To constrain result sets, use thetop_kargument.
Rows that are missing a generated embedding are skipped during the search. When
rerank is set to TRUE, candidate rows with NULL or empty string values in
column_to_search are excluded prior to reranking without raising an error.
Examples
The following example shows how to create a table of products and descriptions with autonomous embedding enabled on the description column, add some data to the table, and then search it for products that would be fun to play with.
# Create a table of products and descriptions with a generated embedding column.
CREATE TABLE mydataset.products (
name STRING,
description STRING,
description_embedding STRUCT<result ARRAY<FLOAT64>, status STRING>
GENERATED ALWAYS AS (AI.EMBED(
description,
connection_id => 'us.example_connection',
endpoint => 'text-embedding-005'
))
STORED OPTIONS( asynchronous = TRUE )
);
# Insert product descriptions into the table.
# The description_embedding column is automatically updated.
INSERT INTO mydataset.products (name, description) VALUES
("Lounger chair", "A comfortable chair for relaxing in."),
("Super slingers", "An exciting board game for the whole family."),
("Encyclopedia set", "A collection of informational books.");
# Search for products that are fun to play with.
SELECT base.name, base.description, distance
FROM AI.SEARCH(TABLE mydataset.products, 'description', "A really fun toy");
/*------------------+----------------------------------------------+----------------------+
| name | description | distance |
+------------------+----------------------------------------------+----------------------+
| Super slingers | An exciting board game for the whole family. | 0.9147342919053343 |
| Lounger chair | A comfortable chair for relaxing in. | 0.9782074503966303 |
| Encyclopedia set | A collection of informational books. | 1.0836052217341476 |
+------------------+----------------------------------------------+----------------------*/
The following example uses a hybrid search to find products that are fun to play with.
SELECT base.name, base.description, distance
FROM
AI.SEARCH(
TABLE mydataset.products,
'description',
"A really fun toy",
mode => 'HYBRID');
/*------------------+----------------------------------------------+--------------------+
| name | description | distance |
+------------------+----------------------------------------------+--------------------+
| Super slingers | An exciting board game for the whole family. | 0.9677335415040333 |
| Lounger chair | A comfortable chair for relaxing in. | 0.9677335415040333 |
| Encyclopedia set | A collection of informational books. | 0.967741935483871 |
+------------------+----------------------------------------------+--------------------*/
The following example searches for products that are fun to play with and enables semantic reranking on the results.
SELECT base.name, base.description, distance, rerank_score
FROM
AI.SEARCH(
TABLE mydataset.products,
'description',
"A really fun toy",
rerank => TRUE);
/*------------------+----------------------------------------------+--------------------+---------------------+
| name | description | distance | rerank_score |
+------------------+----------------------------------------------+--------------------+---------------------+
| Super slingers | An exciting board game for the whole family. | 0.9147342919053343 | 0.2777999937534332 |
| Encyclopedia set | A collection of informational books. | 1.0836052217341476 | 0.03830000013113022 |
| Lounger chair | A comfortable chair for relaxing in. | 0.9782074503966303 | 0.03720000013709068 |
+------------------+----------------------------------------------+--------------------+---------------------*/
Related functions
The AI.SEARCH and
VECTOR_SEARCH
functions support overlapping use cases. In general, you should use AI.SEARCH
when your base table has autonomous embedding generation enabled and you want
to search for results close to a single string literal. It offers a simplified
syntax compared to VECTOR_SEARCH and doesn't require you to embed your
search query. You should use
VECTOR_SEARCH when you want to batch your search queries, when you want
to generate your
own embeddings as input, or if your base table doesn't use autonomous embedding
generation.
You can set rerank => TRUE in AI.SEARCH to semantically rerank candidate
rows as part of the search call, or use the
AI.RANK
function directly when you want to rerank the results of VECTOR_SEARCH, rank
an arbitrary table or query statement, or rank a column other than
column_to_search.
Locations
You can run AI.SEARCH in all of the
locations
that support Gemini Enterprise Agent Platform embedding models, and also in the US
and EU multi-regions.
Pricing
The AI.SEARCH function uses
BigQuery compute pricing
for vector and hybrid search. When rerank is set to TRUE, AI.SEARCH also
uses your credentials to call the Agent Search ranking API in the project that
runs the query. This usage is billed using
ranking API pricing.
Quotas and limits
See Generative AI functions quotas and limits.
When rerank is set to TRUE:
- Ranking requests count toward the Agent Search ranking API quota of the project that runs the query. For more information, see Agent Search quotas and limits.
- If a row's text in
column_to_searchis longer than the ranking model's token limit, the extra text is truncated. For each model's limit, see Supported models.
What's next
- Learn more about autonomous embedding generation.
- Learn more about creating and managing vector indexes.
- Learn more about embeddings and search.