Vector Type (In Beta)

Create a table with a VECTOR column

The following example creates the table vector_items. In this table, id is the primary key column, and embedding is a 4-dimensional VECTOR column. The example table enables strong consistency to facilitate creating a search index later.

CREATE TABLE vector_items (
  id VARCHAR,
  name VARCHAR,
  embedding VECTOR(4),
  PRIMARY KEY(id)
) WITH (CONSISTENCY='strong');

You can run the following statement to view the table schema:

DESCRIBE vector_items;

A sample result is as follows:

Add a VECTOR column

The following example adds a 4-dimensional VECTOR column to an existing table:

ALTER TABLE vector_items ADD COLUMN image_embedding VECTOR(4);

Write vector data

When writing VECTOR data, we recommend using the string form enclosed in square brackets: '[v1, v2, ..., vn]'. Each element must be a value that can be converted to a floating-point number, and the number of elements must match the dimension defined for the column.

The following example writes two rows of data:

UPSERT INTO vector_items (id, name, embedding)
VALUES
  ('item_001', 'Active Noise-Cancelling Bluetooth Headphones', '[0.12, 0.35, 0.48, 0.91]'),
  ('item_002', 'Open-Ear Sports Earbuds',   '[0.09, 0.27, 0.62, 0.83]');

Update vector data

For an existing primary key, you can use UPSERT to overwrite the value of the VECTOR column:

UPSERT INTO vector_items (id, embedding)
VALUES ('item_001', '[0.15, 0.33, 0.51, 0.88]');

If the dimension of the written vector does not match the column definition, the statement fails. For example, you cannot write a 3-dimensional vector to a VECTOR(4) column.

Query vector data

The following example queries the vector value of a specific row:

SELECT id, name, embedding
FROM vector_items
WHERE id = 'item_001';

A sample result is as follows:

Query the vector dimension and string representation

SELECT
  id,
  VECTOR_DIM(embedding) AS dimensions,
  VECTOR_TO_STRING(embedding) AS embedding_text
FROM vector_items
WHERE id = 'item_001';

A sample result is as follows:

STRING_TO_VECTOR

STRING_TO_VECTOR function converts a string to a VECTOR value. It returns NULL when the input is NULL.

• Code example:

  SELECT STRING_TO_VECTOR('[0.1, 0.2, 0.3, 0.4]') AS embedding;

• Result:

  [0.1, 0.2, 0.3, 0.4]

VECTOR_TO_STRING

VECTOR_TO_STRING function converts a vector to a string in the form '[v1, v2, ..., vn]', and returns NULL when the input is NULL.

SELECT VECTOR_TO_STRING(embedding) AS embedding_text
FROM vector_items
WHERE id = 'item_001';

VECTOR_DIM

VECTOR_DIM function returns the dimension of a VECTOR value.

• Code example:

  SELECT VECTOR_DIM(embedding) AS dimensions
  FROM vector_items
  WHERE id = 'item_001';

• Result:

  4

VECTOR_DISTANCE

VECTOR_DISTANCE function is used for vector search and must be executed by a vector index or search index that includes the target vector column.

The following example queries the 10 rows closest to the target vector:

SELECT id, name
FROM vector_items
ORDER BY VECTOR_DISTANCE(embedding, '[0.11, 0.34, 0.50, 0.89]')
LIMIT 10;

You can also apply scalar filter conditions at the same time:

SELECT id, name
FROM vector_items
WHERE id >= 'item_001' AND id <= 'item_999'
ORDER BY VECTOR_DISTANCE(embedding,'[0.11, 0.34, 0.50, 0.89]')
LIMIT 10;

VECTOR_DISTANCE supports an optional third parameter that specifies the distance algorithm. Valid values include EUCLIDEAN, COSINE, and DOT. When this parameter is omitted, the distance algorithm configured by default when the vector index was created is used. When it is specified explicitly, it must match the distance algorithm configured for the index.

SELECT id, name
FROM vector_items
ORDER BY VECTOR_DISTANCE(
  embedding,'[0.11, 0.34, 0.50, 0.89]','COSINE'
)
LIMIT 10;

The following example returns rows whose vector distance is less than or equal to a specified threshold:

SELECT id, name
FROM vector_items
WHERE VECTOR_DISTANCE(
  embedding,
  '[0.11, 0.34, 0.50, 0.89]'
) <= 0.2
LIMIT 10;

VECTOR_EMBEDDING

VECTOR_EMBEDDING function calls an embedding model to convert text into a vector. This function supports the following two forms:

• Syntax:

  VECTOR_EMBEDDING(model_name, text)
  VECTOR_EMBEDDING(model_name, text, dimensions)

• Parameters:

  Parameter	Required	Description
  model_name	Yes	The name of the embedding model, for example, text-embedding-v4. Cannot be empty.
  text	Yes	The text to convert into a vector. The function returns NULL when the input is NULL.
  dimensions	No	The expected dimension of the vector returned by the model. Must be a positive integer. When omitted, the model's default output dimension is used. Whether this parameter is available and the values it supports depend on the specific model.

• Code example:

  The following example uses text-embedding-v4 to convert text into a 1,024-dimensional vector:

  SELECT VECTOR_EMBEDDING('text-embedding-v4','Wireless noise-cancelling headphones, great for commuting and the office',
    1024
  ) AS query_vector;

  The vector returned by the function can be written directly to a VECTOR column. The dimension of the target column must match the dimension of the vector actually returned by the function.

  CREATE TABLE text_vectors (
    id VARCHAR,
    content VARCHAR,
    embedding VECTOR(1024),
    PRIMARY KEY(id)
  ) WITH (CONSISTENCY='strong');
  
  UPSERT INTO text_vectors (id, content, embedding)
  VALUES (
    'doc_001',
    'Lindorm is a cloud-native multi-model database for massive-scale data scenarios',
    VECTOR_EMBEDDING(
      'text-embedding-v4',
      'Lindorm is a cloud-native multi-model database for massive-scale data scenarios',
      1024
    )
  );

  You can also use VECTOR_EMBEDDING to generate the query vector in a vector search statement:

  SELECT id, content
  FROM text_vectors
  ORDER BY VECTOR_DISTANCE(
    embedding,
    VECTOR_EMBEDDING(
      'text-embedding-v4',
      'A cloud-native database for massive-scale data',
      1024
    )
  )
  LIMIT 10;