Hologres:AI_EMBED

-- Text embedding
SELECT ai_embed('Hologres is a one-stop real-time data warehousing engine independently developed by Alibaba. It supports real-time ingestion, real-time updates, real-time processing, and real-time analytics over massive datasets.');

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

-- 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,...}

-- 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');

-- 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;

-- 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;

-- 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", "builder_params": { "base_quantization_type": "sq8_uniform", "max_degree": 64, "ef_construction": 400 } } }'
);

-- 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;

-- 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

-- 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

-- 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
            }
        }
    }'
);

-- 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;

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

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