VECTOR_SEARCH

Limitations

VECTOR_SEARCH requires the input table to be a Delta table.

Syntax

VECTOR_SEARCH(
  { TABLE base_table | (base_table_query) },      -- The table containing the embedding vectors to search against.
  column_to_search,                               -- The column in the base table that contains the embedding vectors.
  { TABLE query_table | (query_table_query) },    -- The table providing the query embedding vectors.
  query_column_to_search,                         -- The column in the query table that contains the query embedding vectors.
  top_k                                           -- The number of nearest neighbors to return.
  [, distance_type]                               -- The metric for calculating distance.
  [, options]                                     -- A JSON string containing other configurable parameters.
)

Parameters

• base_table | (base_table_query): Required. The base table containing the embedding vectors to search against.

• column_to_search: Required. The name of the column in the base table that contains the embedding vectors.

• query_table | (query_table_query): Required. The table providing the query embedding vectors.

• query_column_to_search: Required. The name of the column in the query table that contains the embedding vectors.

• top_k: Required. The number of nearest neighbors to return. Must be a positive integer (≥ 1).

• distance_type: Optional. Specifies the metric for measuring the distance between two vectors. The following types are supported:

  • cosine: Measures the cosine similarity between two vectors. This is often used for scenarios such as text or image feature vectors where the direction of the vector is important.

  • euclidean (Default): Measures the Euclidean distance, which is the straight-line distance between two vectors in space.

  • dot_product: Calculates the dot product. This is suitable for normalized vectors, where a larger value indicates higher similarity.

• options: Optional. A JSON-formatted string containing additional configuration parameters. For example:

  • use_brute_force: A JSON boolean value to specify whether to use a brute-force search, which bypasses any available vector index. For example: '{"use_brute_force":true}'. The default value is false. If you specify use_brute_force=false and no vector index is available, the system automatically falls back to a brute-force search.

Return values

For each row in the query data, the function returns multiple rows from the base table that match the search criteria. The number of rows returned for each query row is equal to the value specified for top_k. The output order is not guaranteed.

• query: A STRUCT containing all selected columns from the query data. This column is included in the output only when using the batch search syntax. For a single vector search, this column is omitted.

• base: A STRUCT containing all columns from the base_table, or a subset of columns selected in the base_table_query.

• distance: A DOUBLE value that indicates the distance between the base data and the query data.

Examples

Data preparation

-- Enable the VECTOR data type.
set odps.sql.type.vector.enable=true;
set odps.sql.type.system.odps2=true;

-- Create a base table for nearest neighbor search and insert data.
CREATE OR REPLACE TABLE base_table
 (
   id STRING,
   my_embedding VECTOR(FLOAT,2)
 )
 TBLPROPERTIES (
  "table.format.version"="2"
 );

INSERT into base_table (id, my_embedding)
 VALUES('dog', CAST(array(1.0, 2.0) AS VECTOR(FLOAT,2))),
 ('wolf', CAST(array(2.0, 4.0) AS VECTOR(FLOAT,2))),
 ('snake', CAST(array(-2.0, 3.0) AS VECTOR(FLOAT,2))),
 ('lion', CAST(array(2.0, -2.5) AS VECTOR(FLOAT,2))),
 ('tiger', CAST(array(3.0, -2.0) AS VECTOR(FLOAT,2))),
 ('otter', CAST(array(-3.0, -1.0) AS VECTOR(FLOAT,2))),
 ('whale', CAST(array(-5.0, -1.0) AS VECTOR(FLOAT,2)));


-- Create a query table for the search vectors and insert data.
CREATE OR REPLACE TABLE query_table
(
  query_id STRING,
  embedding VECTOR(FLOAT,2)
)TBLPROPERTIES (
  "table.format.version"="2"
 );
 
INSERT INTO query_table (query_id, embedding)
VALUES('dog', CAST(array(1.0, 2.0) AS VECTOR(FLOAT,2))),
('cat', CAST(array(1.0, -1.0) AS VECTOR(FLOAT,2)));

This example finds the two nearest neighbors from the my_embedding column of base_table for each vector in the embedding column of query_table.

SELECT *
FROM
  VECTOR_SEARCH(
    TABLE base_table,
    my_embedding,
    (SELECT query_id, embedding FROM query_table),
    embedding,
    2);

-- Returns:
+----------+-----------+----+--------------+------------+
| query_id | embedding | id | my_embedding | distance   |
+----------+-----------+----+--------------+------------+
| dog      | [1, 2]    | dog | [1, 2]       | 0.0        |
| dog      | [1, 2]    | wolf | [2, 4]       | 5.0        |
| cat      | [1, -1]   | lion | [2, -2.5]    | 3.25       |
| cat      | [1, -1]   | tiger | [3, -2]      | 5.0        |
+----------+-----------+----+--------------+------------+