AI_EMBED

Text embedding

-- Text embedding
SELECT ai_embed('Hologres is a real-time data warehouse engine developed by Alibaba Cloud.');

-- Result (1024-dimension vector example)
{-0.020090256,-0.009496426,-0.01584659,0.014317295,...}

Image embedding (FILE object)

-- Image embedding via an OSS FILE object
SELECT ai_embed('qwen3_vl_embedding', to_file('oss://****', 'oss-cn-hangzhou-internal.aliyuncs.com', 'acs:ram::****'));

-- Result (2560-dimension vector)
{-0.000733634,-0.00160808,0.0354091,...}

Image embedding (BYTEA binary)

Method 1: Pass binary data directly

-- Convert an image to BYTEA and compute its embedding (supports webp, jpg, png, and other formats)
SELECT ai_embed('qwen3_vl_embedding',
                decode('/9j/4AAQSkZJRgABAQEAYABgAAD...', 'base64'),
                'jpg');

Method 2: Read BYTEA data from a Hologres internal table

-- Create an image storage table
CREATE TABLE image_table (
    image_name TEXT,
    image_bin BYTEA
);

-- Insert image data
INSERT INTO image_table VALUES
('concert.webp', decode('...webp_binary...', 'base64')),
('cat.png', decode('...png_binary...', 'base64'));

-- Generate embeddings using ai_embed
SELECT image_name,
       ai_embed('qwen3_vl_embedding', image_bin,
                CASE
                  WHEN image_name LIKE '%.webp' THEN 'webp'
                  WHEN image_name LIKE '%.png' THEN 'png'
                  WHEN image_name LIKE '%.jpg' THEN 'jpg'
                END) as embedding
FROM image_table;

Method 3: Read BLOB data from a Paimon external table (Hologres V4.2 and later)

-- Paimon external table (with a BLOB column)
CREATE EXTERNAL TABLE paimon_db.image_blob_table (
    id INT,
    image_name TEXT,
    picture BYTEA  -- Paimon BLOB is mapped to BYTEA
)
WITH (...);

-- Generate embeddings directly from Paimon BLOB data
SELECT id, image_name,
       ai_embed('qwen3_vl_embedding', picture, 'jpg') as embedding
FROM paimon_db.image_blob_table;

Scenario 1: Image vector search with Hologres internal tables

This scenario deploys the qwen3-vl-embedding model and demonstrates the full workflow of image embedding, image-to-image similarity search, and text-to-image search using vector indexing.

Step 1: Prepare data

-- 0. Create a source image table (stores raw image binary data)
CREATE TABLE source_image_table (
    image_id SERIAL PRIMARY KEY,
    image_name TEXT,
    image_bin BYTEA,
    image_format TEXT
);

-- Insert image data (example: read from local files and insert)
INSERT INTO source_image_table (image_name, image_bin, image_format) VALUES
('concert.webp', decode('UklGRiIAAABXRUJQVlA4IBYAAAAwAQCdASo...', 'base64'), 'webp'),
('cat.png', decode('iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mNk+M9QDwADhgGAWjR9awAAAABJRU5ErkJggg==', 'base64'), 'png'),
('dog.jpg', decode('/9j/4AAQSkZJRgABAQEAYABgAAD//gA7Q1JFQVRPUjogZ2QtanBlZyB2MS4wICh1c2luZyBJSkcgSlBFRyB2ODAp', 'base64'), 'jpg');

-- 1. Create a target table with a vector index
CREATE TABLE image_search_table (
    image_id SERIAL PRIMARY KEY,
    image_name TEXT,
    image_bin BYTEA,
    image_format TEXT,
    embedding float4[] CHECK (array_ndims(embedding) = 1 AND array_length(embedding, 1) = 2560)
) WITH (
    vectors = '{ "embedding": { "algorithm": "HGraph", "distance_method": "Euclidean" } }'
);

-- 2. Insert images from the source table and generate vectors
INSERT INTO image_search_table (image_name, image_bin, image_format, embedding)
SELECT image_name, image_bin, image_format,
       ai_embed('qwen3_vl_embedding', image_bin, image_format)
FROM source_image_table;

Step 2: Image similarity search

-- 3. Image-to-image similarity search (approximate nearest neighbor)
SELECT a.image_name as query_image,
       b.image_name as result_image,
       approx_euclidean_distance(a.embedding, b.embedding) as distance
FROM image_search_table a,
     image_search_table b
WHERE a.image_id = 1
ORDER BY distance ASC;

-- Sample result
--  query_image  | result_image | distance
-- --------------+--------------+----------
--  concert.webp | concert.webp |        0
--  concert.webp | cat.png      | 15.23457
--  concert.webp | dog.jpg      | 18.45679

Step 3: Text-to-image search

-- 4. Text-to-image search (multimodal retrieval)
SELECT image_id, image_name,
       approx_euclidean_distance(
           embedding,
           ai_embed('qwen3_vl_embedding', 'concert photo')
       ) as text_img_distance
FROM image_search_table
ORDER BY text_img_distance ASC;

-- Sample result
--  image_id | image_name   | text_img_distance
-- ----------+--------------+-------------------
--         1 | concert.webp |          8.123456
--         3 | dog.jpg      |         22.345678
--         2 | cat.png      |         25.678901

Scenario 2: Offline vector indexing from Paimon external tables

This scenario reads the BLOB type from Paimon and demonstrates the full workflow of embedding and searching Paimon BLOB data, enabling accelerated queries on unstructured data.

Step 1: Prepare data

-- 0. Create an External Database to connect to the Paimon data source
CREATE EXTERNAL DATABASE paimon_blob_bj
WITH (
    catalog_type 'paimon'
    metastore_type 'dlf-rest'
    dlf_catalog 'paimon_bj'
    dlf_access_id 'xxxx'
    dlf_access_key '***'
);
-- Create an external schema
CREATE EXTERNAL SCHEMA paimon_blob_bj.test_paimon_db;

-- 1. Create a Paimon external table (with a BLOB column)
CREATE EXTERNAL TABLE paimon_blob_bj.test_paimon_db.image_table (
    id INT,
    image_name TEXT,
    picture BYTEA  -- Paimon BLOB is mapped to BYTEA
)
WITH (
   "table_format" = 'paimon',
    "file_format" = 'orc',
    "bucket" = '1',
    "bucket-key" = 'id'
);

-- 2. Create a Hologres target vector table (with a vector index)
CREATE TABLE public.image_vector_table (
    id SERIAL PRIMARY KEY,
    image_name TEXT,
    picture BYTEA,
    image_format TEXT,
    embedding float4[] CHECK (array_ndims(embedding) = 1 AND array_length(embedding, 1) = 2560)
) WITH (
    vectors = '{
        "embedding": {
            "algorithm": "HGraph",
            "distance_method": "Euclidean",
            "builder_params": {
                "base_quantization_type": "sq8_uniform",
                "max_degree": 64,
                "ef_construction": 400
            }
        }
    }'
);

Step 2: Load Paimon data into the Hologres vector table (optional)

-- 3. Read BLOB data from the Paimon external table, generate vectors, and write to Hologres
INSERT INTO image_vector_table (id, image_name, picture, image_format, embedding)
SELECT
    id,
    image_name,
    picture,
    'jpg',
    ai_embed('qwen3_vl_embedding', picture, 'jpg')
FROM paimon_blob_bj.test_paimon_db.image_table;

Step 3: Image similarity search

SELECT
    a.id as query_id,
    a.image_name as query_image,
    b.id as result_id,
    b.image_name as result_image,
    approx_euclidean_distance(a.embedding, b.embedding) as distance
FROM image_vector_table a,
     image_vector_table b
WHERE a.id = 1
ORDER BY distance ASC
LIMIT 5;

-- Sample result
--  query_id | query_image  | result_id | result_image | distance
-- ----------+--------------+-----------+--------------+----------
--         1 | concert.webp |         1 | concert.webp |        0
--         1 | concert.webp |         3 | dog.jpg      | 15.23457
--         1 | concert.webp |         2 | cat.png      | 18.45679

Step 4: Text-to-image search

SELECT
    id,
    image_name,
    approx_euclidean_distance(
        embedding,
        ai_embed('qwen3_vl_embedding', 'concert photo')
    ) as text_img_distance
FROM image_vector_table
ORDER BY text_img_distance ASC
LIMIT 5;

-- Sample result
--  id | image_name   | text_img_distance
-- ----+--------------+-------------------
--   1 | concert.webp |          8.123456
--   3 | dog.jpg      |         22.345678
--   2 | cat.png      |         25.678901