MaxCompute:VECTOR_SEARCH

Prepare data

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

-- Base Table: A table of animal vectors.
CREATE TABLE IF NOT EXISTS animals (
  name      STRING,
  category  STRING,
  vec       VECTOR(FLOAT, 2)
  )
  TBLPROPERTIES (
  "table.format.version"="2"
  );

INSERT INTO animals VALUES
  ('dog',   'mammal', CAST(array(1.0, 2.0)   AS VECTOR(FLOAT, 2))),
  ('wolf',  'mammal', CAST(array(2.0, 4.0)   AS VECTOR(FLOAT, 2))),
  ('cat',   'mammal', CAST(array(1.0, 1.8)   AS VECTOR(FLOAT, 2))),
  ('lion',  'mammal', CAST(array(2.0, -2.5)  AS VECTOR(FLOAT, 2))),
  ('tiger', 'mammal', CAST(array(3.0, -2.0)  AS VECTOR(FLOAT, 2))),
  ('otter', 'mammal', CAST(array(-3.0, -1.0) AS VECTOR(FLOAT, 2))),
  ('whale', 'mammal', CAST(array(-5.0, -1.0) AS VECTOR(FLOAT, 2))),
  ('snake', 'reptile', CAST(array(-2.0, 3.0) AS VECTOR(FLOAT, 2))),
  ('lizard','reptile', CAST(array(-1.8, 2.8) AS VECTOR(FLOAT, 2))),
  ('eagle', 'bird',    CAST(array(5.0, -3.0) AS VECTOR(FLOAT, 2)));

-- Query Table: A table of animal vectors to query.
CREATE TABLE IF NOT EXISTS query_animals (
  query_name  STRING,
  tag       STRING,
  qvec      VECTOR(FLOAT, 2)
  )
  TBLPROPERTIES (
  "table.format.version"="2"
  );

INSERT INTO query_animals VALUES
  ('dog-like',  'mammal',  CAST(array(1.0, 2.0)  AS VECTOR(FLOAT, 2))),
  ('cat-like',  'mammal',  CAST(array(1.0, -1.0) AS VECTOR(FLOAT, 2))),
  ('snake-like','reptile', CAST(array(-2.0, 3.0) AS VECTOR(FLOAT, 2)));

For simplicity, the examples in this topic use a brute-force search. In a production environment, you should create and build a vector index on the vector column before performing a vector search to ensure optimal performance. For more information, see VECTOR INDEX. If no vector index is available, the system automatically falls back to a brute-force search, which can be slow.

Table-to-table search

Search the vec column of the animals table to find the two most similar vectors for each row in the qvec column of the query_animals table.

SELECT _base.name, _query.query_name, distance
FROM VECTOR_SEARCH(
  TABLE animals, vec,
  TABLE query_animals, qvec,
  2, EUCLIDEAN
);

-- Result:
+--------+------------+---------------------+
| name   | query_name | distance            |
+--------+------------+---------------------+
| dog    | dog-like   | 0.0                 |
| cat    | dog-like   | 0.04000001773238182 |
| lion   | cat-like   | 3.25                |
| tiger  | cat-like   | 5.0                 |
| snake  | snake-like | 0.0                 |
| lizard | snake-like | 0.08000003546476364 |
+--------+------------+---------------------+

Simple filter on the base table

SELECT _base.name, _query.query_name, distance
FROM VECTOR_SEARCH(
  (SELECT name, vec FROM animals WHERE category = 'mammal'), vec,
  TABLE query_animals, qvec,
  2, EUCLIDEAN
);

-- Result:
+------+------------+------------+
| name | query_name | distance   |
+------+------------+------------+
| dog  | dog-like   | 5.0        |
| wolf | dog-like   | 5.0        |
| dog  | cat-like   | 2.0        |
| wolf | cat-like   | 2.0        |
| dog  | snake-like | 13.0       |
| wolf | snake-like | 13.0       |
+------+------------+------------+

Simple subqueries for base and query tables

SELECT *
FROM VECTOR_SEARCH(
  (SELECT name, vec FROM animals WHERE category IN ('mammal', 'reptile')), vec,
  (SELECT query_name, qvec FROM query_animals WHERE tag = 'mammal'), qvec,
  2, EUCLIDEAN
);

-- Result:
+------------+---------+------+--------+------------+
| query_name | qvec    | name | vec    | distance   |
+------------+---------+------+--------+------------+
| dog-like   | [1, 2]  | dog  | [1, 2] | 5.0        |
| cat-like   | [1, -1] | dog  | [1, 2] | 2.0        |
| dog-like   | [1, 2]  | wolf | [2, 4] | 5.0        |
| cat-like   | [1, -1] | wolf | [2, 4] | 2.0        |
+------------+---------+------+--------+------------+

Search with a single vector literal

SELECT _base.name, distance
FROM VECTOR_SEARCH(
  TABLE animals, vec,
  vector(1.0f, 2.0f) AS qt(qvec),
  qvec,
  3, COSINE
);

-- Result:
+------+-----------------------+
| name | distance              |
+------+-----------------------+
| dog  | 5.960464477539063e-8  |
| wolf | 5.960464477539063e-8  |
| cat  | 0.0009437799453735352 |
+------+-----------------------+

Use qualified column names to access results

If you do not explicitly specify an alias for the base and query tables, the system automatically generates default qualifiers: _base for the base table and _query for the query table. Use qualified column names, such as _base.id and _query.id, to access the results. Referencing a column name that exists in both tables without a qualifier is ambiguous and results in an error.

-- Not recommended: If both tables have a 'name' column, this query causes an ambiguity error.
SELECT name
FROM VECTOR_SEARCH(TABLE animals, vec, TABLE animals, vec, 10, COSINE);

-- Error:
FAILED: ODPS-0130071:[1,8] Semantic analysis exception - name is ambiguous, can be both _query.name or _base.name

-- Recommended: Use the default qualifiers.
SELECT _base.name, _query.query_name, distance
FROM VECTOR_SEARCH(
  TABLE animals, vec,
  TABLE query_animals, qvec,
  2, COSINE
);
-- Result:
+--------+------------+------------------------+
| name   | query_name | distance               |
+--------+------------+------------------------+
| dog    | dog-like   | 5.960464477539063e-8   |
| wolf   | dog-like   | 5.960464477539063e-8   |
| lion   | cat-like   | 0.006116271018981934   |
| tiger  | cat-like   | 0.019419312477111816   |
| snake  | snake-like | -1.1920928955078125e-7 |
| lizard | snake-like | 0.00013881921768188477 |
+--------+------------+------------------------+

-- You can also explicitly specify an alias.
SELECT b.name, q.query_name, distance
FROM VECTOR_SEARCH(
  TABLE animals AS b, vec,
  TABLE query_animals AS q, qvec,
  2, COSINE
);
-- Result:
+--------+------------+------------------------+
| name   | query_name | distance               |
+--------+------------+------------------------+
| dog    | dog-like   | 5.960464477539063e-8   |
| wolf   | dog-like   | 5.960464477539063e-8   |
| lion   | cat-like   | 0.006116271018981934   |
| tiger  | cat-like   | 0.019419312477111816   |
| snake  | snake-like | -1.1920928955078125e-7 |
| lizard | snake-like | 0.00013881921768188477 |
+--------+------------+------------------------+

Unsupported scenarios

SELECT *
FROM VECTOR_SEARCH(
  (SELECT name, vec FROM animals VERSION AS OF 1), vec,
  TABLE query_animals, qvec,
  10, COSINE
);

-- Error:
FAILED: ODPS-0130071:[3,4] Semantic analysis exception - VECTOR_SEARCH base table does not support time travel

SELECT *
FROM VECTOR_SEARCH(
  (SELECT a.name, a.vec FROM animals a JOIN query_animals b ON a.name = b.query_name), vec,
  TABLE query_animals, qvec,
  10, COSINE
);

-- Error:
FAILED: ODPS-0130071:[] Semantic analysis exception - VECTOR_SEARCH input must be a simple plan containing only SELECT, FILTER, and TABLE SCAN operators

-- Create a query table that is a regular table, not a Delta Table.
CREATE Table query_table_animal AS
SELECT query_name, qvec FROM query_animals;

SELECT *
FROM VECTOR_SEARCH(
  TABLE animals, vec,
  TABLE query_table_animal, qvec,
  10, COSINE
);

-- Error:
FAILED: ODPS-0130071:[] Semantic analysis exception - VECTOR_SEARCH input table must be an append delta table

SELECT *
FROM VECTOR_SEARCH(
  TABLE animals, vec,
  (
    SELECT query_name, qvec
    FROM VALUES
    ('q1', vector(1.0f, 2.0f)),
    ('q2', vector(4.0f, 5.0f)) AS q(query_name, qvec)
  ),
  qvec,
  10, COSINE
);

-- Error:
FAILED: ODPS-0130071:[] Semantic analysis exception - VECTOR_SEARCH input table must be an append delta table