Exploring EMR Serverless StarRocks AI Functions: Multimodal Embedding, Semantic Aggregation, and Mixed Retrieval

StarRocks AI Functions embed multimodal embedding, LLM‑based aggregation, natural‑language filtering, and mixed vector‑full‑text retrieval directly in the SQL execution engine, eliminating external vector stores and orchestration scripts.

New Function Highlights

Multimodal Embedding – ai_embed_multimodal(input, modality) accepts image, video or text and maps the content to a unified 1024‑dimensional vector space based on qwen3‑vl‑embedding. It supports OSS URLs, base64 strings and VARBINARY columns; the engine passes the URL to the model API without downloading the binary data.

-- Image vectorization from OSS URL
SELECT ai_embed_multimodal(cover_url, 'image') AS visual_vec FROM clips;

-- Text‑to‑image search: retrieve video covers by a textual query
SELECT clip_id,
       cosine_similarity(visual_vec, ai_embed_multimodal('美妆主播讲防晒', 'text')) AS score
FROM clip_visual_emb
ORDER BY score DESC
LIMIT 20;

AI Aggregation – ai_agg(column, prompt) and ai_agg_summary(column, prompt) perform map‑reduce style LLM summarization across rows and can be used inside GROUP BY. This replaces the traditional export‑concatenate‑call workflow.

SELECT customer_id,
       ai_agg(ticket_content, '总结该客户的核心诉求和情绪特征') AS portrait
FROM support_tickets
GROUP BY customer_id;

Semantic Filter Predicate – ai_filter(text, condition) returns a boolean that can appear directly in a WHERE clause, enabling natural‑language business rules.

SELECT clip_id, asr_text
FROM live_clips
WHERE ai_filter(asr_text, '包含极限词、虚假比价或医疗功效承诺');

Mixed Retrieval – Vectors stored in Paimon or StarRocks tables can be indexed with HNSW (vector) and GIN (full‑text). A single SQL can combine cosine_similarity for semantic recall, MATCH for exact keyword hits, and structured predicates (date, category) to produce a fused ranking.

Default Model & Model Layering – All built‑in functions ship with a default model, so the model parameter can be omitted. Users may specify a different model per call (e.g., qwen3.6-plus for first‑pass filtering, qwen3.7-max for detailed review) without changing the execution plan.

Architectural Advantages

SQL‑Native, No Data Movement – Reading, filtering, AI computation and result writing occur within a single SQL execution chain, removing intermediate files and duplicate storage.

Asynchronous AI Pipeline – A dedicated AI Pipeline schedules inputs in chunks and sub‑batches, submits model requests asynchronously, and allows the execution thread to perform other work while awaiting responses. Predicate push‑down, limit push‑down and top‑N push‑down reduce the data volume before model invocation.

Bounded Resource Consumption – Multi‑layer limits (QPS limiter, max inflight, AIChunkBuffer, response‑size caps) provide back‑pressure when downstream processing slows, preventing unbounded memory growth. Query cancellation and timeout signals propagate to the AI call chain, releasing resources promptly.

Multi‑Account AI Gateway – All AI calls go through Alibaba Cloud AI Gateway, which can bind a single Model API to multiple Bailei accounts with weighted load‑balancing. On 429/5xx errors or stream timeouts, the gateway automatically falls back to a secondary account, keeping the SQL layer transparent.

Two‑Layer Fault Tolerance – The gateway performs a single‑round retry with multi‑account routing; only if the gateway still fails does StarRocks execute a budget‑constrained retry, separating gateway‑level and engine‑level fault handling.

End‑to‑End Observability – EXPLAIN shows AIProjection push‑down positions; PROFILE reports call counts, wait times, retries and token usage; AUDIT records query‑level cost and failure attribution; gateway logs expose backend status codes and fallback hits, enabling precise bottleneck identification across scanning, pipeline queuing, account throttling, gateway routing and model inference.

Scenario Practices

Scenario 1: Advertising Material Library

An OBJECT TABLE catalogs OSS‑stored assets. ai_embed_multimodal(url, 'image') vectorizes covers, enabling text‑to‑image and image‑to‑image search in a single SQL. ai_classify auto‑tags assets, ai_filter enforces compliance, and ai_agg_summary together with ai_complete generate new scripts at scale.

-- Text‑to‑image search
SELECT object_uri,
       cosine_similarity(embedding,
                         ai_embed_multimodal('真人出镜的赛车玩法，下雪场景', 'text')) AS score
FROM asset_img_emb
ORDER BY score DESC
LIMIT 20;

Scenario 2: Game Chat Processing

Millions of daily chat messages are processed with a single INSERT‑SELECT that applies ai_sentiment, ai_classify, ai_filter and ai_translate. The async pipeline and multi‑account gateway provide high‑throughput, automatic load‑balancing and isolated retries.

INSERT INTO chat_ai_results
SELECT message_id,
       ai_sentiment(message_text) AS sentiment,
       ai_classify(message_text, ['正常交流','辱骂攻击','广告引流','账号交易','投诉反馈']) AS category,
       ai_filter(message_text, '是否包含诈骗或站外引流风险') AS is_risky,
       ai_translate(message_text, '', 'Chinese') AS message_cn
FROM chat_messages
WHERE dt = '2026-07-15' AND message_text IS NOT NULL;

Scenario 3: Financial Text Retrieval & RAG

Contracts, announcements and reports are stored in the lake. ai_embed(document_text) creates vectors with HNSW indexes; GIN indexes handle exact keyword matches. A single SQL combines semantic recall, keyword filtering and structured predicates (date, product line). The resulting rows can be fed to ai_complete for Retrieval‑Augmented Generation.

-- Vectorize documents
INSERT INTO finance_docs (doc_id, embedding)
SELECT doc_id, ai_embed(document_text) FROM raw_docs;

-- Mixed search
SELECT doc_id,
       cosine_similarity(embedding, query_vec) AS score,
       MATCH(text) AS keyword_match
FROM finance_docs
WHERE date >= '2023-01-01' AND product = 'Loan';

Summary of Value

Unique capabilities : native multimodal embedding, cross‑row semantic aggregation, natural‑language filtering, and mixed vector‑full‑text retrieval.

Production‑ready architecture : async pipelines, bounded resources, multi‑account gateway with load‑balancing and fallback, and two‑layer fault tolerance ensure stable batch runs on millions of rows.

Minimal data movement : all operations stay on the lake; a single SQL performs ingestion, vectorization, tagging, retrieval and result write‑back, dramatically reducing operational complexity and cost.

Release Notes

Multimodal Embedding – Function: ai_embed_multimodal. Maps image, video and text to a unified vector space; supports OSS URL, base64 and VARBINARY.

Text Embedding – Function: ai_embed. Generates semantic vectors for plain text with configurable dimensions.

AI Aggregation – Functions: ai_agg / ai_agg_summary. Performs LLM summarization across rows within GROUP BY.

Multimodal Understanding – Function: ai_complete. Supports image, video, VARBINARY and mixed‑content inputs for generative completion.

Default Model – All built‑in functions omit the model parameter to use the platform’s default model.

Mixed Retrieval – Engine combines HNSW vector index with GIN full‑text index for semantic + keyword + structured filtering.

Execution Engine – AI Pipeline provides asynchronous chunk scheduling, QPS/Inflight limits, back‑pressure and cancellation propagation.

AI Gateway – Multi‑account load‑balancing and automatic fallback ensure a unified entry point with aggregated quota and fault‑tolerant routing.