vector_search function

Applies to: Databricks SQL

The vector_search() function allows you to query a Mosaic AI Vector Search index using SQL.

Requirements

• This function is not available on classic SQL warehouses.
• For more information, see Databricks SQL pricing page.
• This function is available in regions where Mosaic AI Vector Search is supported.

Syntax

In Databricks Runtime 15.3 and above, use query_text or query_vector to specify what to search for in the index.

SELECT * FROM vector_search(
  index,
  { query_text | query_vector },
  [ num_results ]
)

In Databricks Runtime 15.2 and below, use query to specify what to search for in the index.

SELECT * FROM vector_search(
  index, query, num_results
)

Arguments

All arguments must be passed by name, like vector_search(index => indexName, query_text => queryText).

• index: A STRING constant, the fully qualified name of an existing vector search index in the same workspace for invocations. The definer must have Select permission on the index.
• Use one of the following to specify the expression to search for in the index:
  • For Databricks Runtime 15.3 or above, use query_text to search for a specific string of text in the embedding source column of your Delta Sync Index. The query must be a STRING expression of the string to search for in the index.
  • For Databricks Runtime 15.3 or above, use query_vector to search for a specific vector in the embedding vector column of your Delta Sync Index. This argument is Required for searching a Delta Sync Index using self-managed vectors. The query must be an ARRAY<FLOAT>, or ARRAY<DOUBLE>, or ARRAY<DECIMAL(_, _)> expression of the embedding vector to search for in the index.
  • For Databricks Runtime 15.2 or below, use query to specify the string to search for in your index.
• num_results (optional): An integer constant, the max number of records to return. Defaults to 10.
• query_type(optional): The type of search you want to perform on your vector search index. Defaults to ANN if not explicitly specified.
  • If the query_type is ANN , then either query_text or query_vector must be specified to perform an approximate nearest neighbor search or similarity search.
  • If the query_type is HYBRID, then query_text must be specified. You can specify both query_text and query_vector for a hybrid search. Hybrid search in this instance means a combination of similarity search and keyword search where a keyword search uses the literal text as the target.

The following table summarizes which arguments can be used when you have a Delta Sync index with an embedding model:

The following table describes the different scenarios and the arguments that can be used when you have a Delta sync index without an embedding model:

Returns

A table of the top matching records from the index. All the columns of the index are included.

Examples

The following sections show example SQL queries for different index searches.

Hybrid search queries

The following hybrid search example combines the following search types to find the provided terms in text or metadata of the vector search index:

• Vector similarity search: To find similar semantic meaning for Wi-Fi issues.
• Keyword search: To find Wi-Fi issues LMP-9R2 on a keyword index.

SELECT * FROM vector_search(
  index => 'main.support_docs.index',
  query_text => 'Wi-Fi issues LMP-9R2',
  query_type => 'HYBRID',
  num_results => 3)

The following hybrid search example specifies both query_text and query_vector for the term, Wi-Fi issues LMP-9R2. In this example, keyword search performs better on proprietary terms unique to a company (like “LMP-9R2” in this case), whereas vector search, which are typically trained on public datasets, does not recognize terms like “LMP-9R2.”

SELECT * FROM vector_search(
  index => 'main.support_docs.index',
  query_text => 'Wi-Fi issues LMP-9R2',
  query_vector => array( 0.0213, 0.1045, 0.0871, 0.0562, 0.1459, ... 0.0131),-- a self computed embedding of the `query_text` param
  query_type => 'HYBRID',
  num_results => 3 )

Text queries on indexes with embedding source columns

Search over an index of product SKUs to find similar products by name. The following example uses query_text which is only supported in Databricks Runtime 15.3 and above. For Databricks Runtime 15.2 and below, use query instead of query_text.

SELECT * FROM VECTOR_SEARCH(index => "main.db.my_index", query_text => "iphone", num_results => 2)

The following example searches for multiple terms at the same time by using a LATERAL subquery.

SELECT
  query_txt,
  query_id,
  search.*
FROM
  query_table,
  LATERAL(
SELECT * FROM VECTOR_SEARCH(index => "main.db.my_index", query_text => query_txt, num_results => 2)
  ) as search

Text queries on indexes with embedding source columns

Search over an index of images with pre-computed embeddings to find similar images by embedding. The following example uses query_vector which is only supported in Databricks Runtime 15.3 and above. For Databricks Runtime 15.2 and below, use query instead of query_vector.

SELECT * FROM VECTOR_SEARCH(index => "main.db.my_index", query_vector => ARRAY(0.45, -0.35, 0.78, 0.22), num_results => 3)

SELECT * FROM VECTOR_SEARCH(index => "main.db.my_index", query_vector => ARRAY(0.45F, -0.35F, 0.78F, 0.22F), num_results => 3)

SELECT * FROM VECTOR_SEARCH(index => "main.db.my_index", query_vector => ARRAY(0.45D, -0.35D, 0.78D, 0.22D), num_results => 3)

The following example searches for multiple terms at the same time by using a LATERAL subquery.

SELECT
  query_embedding,
  search.*
FROM
  query_table,
  LATERAL(
SELECT * FROM VECTOR_SEARCH(index => "main.db.my_index", query_vector => image_embedding, num_results => 1)
  ) as search

Limitations

The following limitations apply during the preview:

• Querying DIRECT_ACCESS index types are not supported.
• Input parameters filters_json or columns are not supported.
• Vector Search with num_results greater than 100 are not supported.
• vector_search cannot be used with model serving endpoints using Foundation Model APIs provisioned throughput.