Vector functions

Version requirements

• Vector functions are a core AI capability introduced in SelectDB 5.0.0 (26.0.0) and are available in all subsequent releases. Note that cosine_similarity requires kernel version 26.1.0 or later.

• A vector is represented as an array (ARRAY). The array elements support numeric types such as TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, and DOUBLE. The two vectors involved in a calculation must have the same length.

• The embed function supports multimodal embedding (images, videos, and more) starting from SelectDB 26.1.5 (Apache Doris 4.1.5).

Supported functions

General notes

Except for embed, the vector distance functions behave consistently for boundary inputs:

• The two vectors must have the same length. Otherwise, an error is returned. For example, l2_distance([1,2,3],[1,2]) returns the error have different input element sizes of array: 3 and 2.

• If an argument is NULL, an error is returned. For example, l2_distance(NULL,[1,2]) returns the error First argument for function l2_distance cannot be null.

• If a vector contains a NULL element, an error is returned. For example, l2_distance([1,NULL],[1,2]) returns the error First argument for function l2_distance cannot have null.

• If both inputs are empty arrays, 0 is returned. For example, l2_distance([],[]) returns 0.

embed

Calls a large language model service to generate a semantic embedding vector based on the input text or multimodal content, and returns an array of the FLOAT type. This function is typically used to write text or images into a vector column or to build a query vector, and is combined with the distance functions above to implement similarity calculation, semantic search, and other scenarios.

Syntax

embed([<resource_name>], <input>)

Parameters

Multimodal content JSON object fields

When <input> is multimodal content, pass a JSON object with the following fields:

Return value

The return type is ARRAY, which represents the generated vector. If the input value is NULL, NULL is returned. The result is generated by a large language model, so the returned content is not fixed.

Notes

• Before you use embed, you must create an AI resource that connects to a large language model service (such as Alibaba Cloud Model Studio), and either specify <resource_name> in the argument or set a default resource by using SET default_ai_resource.

• If no resource is specified and no default resource is set, the following error is returned: Please specify the AI Resource in argument or session variable.

Examples

Example 1: Call the embed function

-- Set a default AI resource and then call the function
SET default_ai_resource = 'your_resource_name';
SELECT embed('hello world');

-- Or specify the resource directly in the argument
SELECT embed('your_resource_name', 'hello world');

Example 2: Write text vectors into a table

-- 1. Create a table with a vector column
CREATE TABLE articles (
    id INT,
    content TEXT,
    embedding ARRAY<FLOAT>
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1;

-- 2. Use embed to convert text into a vector and write it into the table
INSERT INTO articles VALUES (
    1,
    'ApsaraDB for SelectDB is a high-performance analytical database',
    embed('ApsaraDB for SelectDB is a high-performance analytical database')
);

-- 3. Query the written vector
SELECT id, content, embedding FROM articles WHERE id = 1;

-- 4. Perform semantic search by combining the distance function with embed
SELECT id, content,
       cosine_distance(embedding, embed('analytical database')) AS distance
FROM articles
ORDER BY distance
LIMIT 10;

Example 3: Multimodal embedding (images/videos)

Starting from SelectDB 26.1.5 (Apache Doris 4.1.5), the embed function supports multimodal embedding for content such as images and videos.

Generate an image embedding via HTTPS URL

SELECT ARRAY_SIZE(
    embed(
        'multimodal_embed_resource',
        CAST('{
            "uri": "https://example.com/images/product.png",
            "content_type": "image/png"
        }' AS JSON)
    )) AS image_embedding_dimension;

Generate a video embedding via S3 (AK/SK)

SELECT ARRAY_SIZE(
    embed(
        'multimodal_embed_resource',
        CAST('{
            "uri": "s3://example-bucket/videos/demo.mp4",
            "content_type": "video/mp4",
            "endpoint": "s3.us-east-1.amazonaws.com",
            "region": "us-east-1",
            "ak": "<access_key>",
            "sk": "<secret_key>"
        }' AS JSON)
    )) AS video_embedding_dimension;

Generate an image embedding via S3 (IAM Role)

SELECT ARRAY_SIZE(
    embed(
        'multimodal_embed_resource',
        CAST('{
            "uri": "s3://example-bucket/images/product.png",
            "content_type": "image/png",
            "endpoint": "s3.us-east-1.amazonaws.com",
            "region": "us-east-1",
            "role_arn": "arn:aws:iam::<account_id>:role/<role_name>",
            "external_id": "<external_id>"
        }' AS JSON)
    )) AS image_embedding_dimension;

l1_distance

Calculates the L1 distance (Manhattan distance) between two equal-length vectors, that is, the sum of the absolute differences across all dimensions.

Syntax

l1_distance(<array1>, <array2>)

Parameters

Return value

Returns the distance between the two vectors in L1 space. The return type is FLOAT. If either input vector is NULL or contains a NULL element, an error is returned.

Example

SELECT l1_distance([4,5],[6,8]);
-- Returns 5

l2_distance

Calculates the L2 distance (Euclidean distance) between two equal-length vectors, that is, the square root of the sum of the squared differences across all dimensions.

Syntax

l2_distance(<array1>, <array2>)

Parameters

Return value

Returns the distance between the two vectors in Euclidean space. The return type is FLOAT. If either input vector is NULL or contains a NULL element, an error is returned.

Example

SELECT l2_distance([4,5],[6,8]);
-- Returns 3.605551

SELECT l2_distance([1,2,3],[4,5,6]);
-- Returns 5.196152

l2_distance_approximate

The approximate version of l2_distance. It calculates the approximate L2 distance between two equal-length vectors. If an ANN index is created on the related array column, the result can be obtained directly from the index. In performance-first scenarios such as large-scale vector search, it trades a small amount of precision for higher computational efficiency.

Syntax

l2_distance_approximate(<array1>, <array2>)

Parameters

Return value

Returns the approximate distance between the two vectors in Euclidean space. The return type is FLOAT. If either input vector is NULL or contains a NULL element, an error is returned.

Example

SELECT l2_distance_approximate([4,5],[6,8]);
-- Returns 3.605551

cosine_distance

Calculates the cosine distance between two equal-length vectors. The value ranges from 0 to 2. A smaller value indicates that the two vectors point in more similar directions. Cosine distance = 1 − cosine similarity.

Syntax

cosine_distance(<array1>, <array2>)

Parameters

Return value

Returns the cosine distance between the two vectors. The return type is FLOAT. If either input vector is NULL or contains a NULL element, an error is returned. The cosine distance is 0 for vectors with the same direction, 1 for orthogonal vectors, and 2 for vectors with opposite directions. If one of the vectors is a zero vector, 2 (the upper bound of the cosine distance) is returned.

Example

SELECT cosine_distance([1,2,3],[1,2,3]);
-- Returns 0

SELECT cosine_distance([1,0],[0,1]);
-- Returns 1

SELECT cosine_distance([1,2],[2,3]);
-- Returns 0.007722139

SELECT cosine_distance([0,0],[1,2]);
-- Returns 2

cosine_similarity

Calculates the cosine similarity between two equal-length vectors. The value ranges from −1 to 1. A larger value indicates that the two vectors point in more similar directions. Cosine similarity = 1 − cosine distance.

Syntax

cosine_similarity(<array1>, <array2>)

Parameters

Return value

Returns the cosine similarity between the two vectors. The return type is FLOAT. If an input vector is NULL or contains a NULL element, an error is returned. If the two arrays have different numbers of elements, an error is returned. If an array is empty or either vector is a zero vector, 0.0 is returned.

Example

SELECT cosine_similarity([1,2,3],[1,2,3]);
-- Returns 1

inner_product

Calculates the inner product (dot product) of two equal-length vectors, that is, the sum of the element-wise products across all dimensions.

Syntax

inner_product(<array1>, <array2>)

Parameters

Return value

Returns the inner product of the two equal-length vectors. The return type is FLOAT. If either input vector is NULL or contains a NULL element, an error is returned.

Example

SELECT inner_product([1,2],[2,3]);
-- Returns 8

inner_product_approximate

The approximate version of inner_product. It calculates the approximate inner product of two equal-length vectors. If an ANN index is created on the related array column, the result can be obtained directly from the index. In performance-first scenarios such as large-scale vector search, it trades a small amount of precision for higher computational efficiency.

Syntax

inner_product_approximate(<array1>, <array2>)

Parameters

Return value

Returns the approximate inner product of the two equal-length vectors. The return type is FLOAT. If either input vector is NULL or contains a NULL element, an error is returned.

Example

SELECT inner_product_approximate([1,2],[2,3]);
-- Returns 8