E-MapReduce:AI center

Kernel version requirements

• 3.3.20-2.1.1 and later

• 3.5.16-2.1.1 and later

Configure network access

StarRocks BE nodes need outbound internet access to reach external model service endpoints. Set up a NAT Gateway on the VPC where your cluster runs:

1. Create a NAT Gateway and bind an Elastic IP Address (EIP) to it.

2. Add an SNAT rule that routes traffic from the BE node CIDR block through the NAT Gateway.

3. Confirm that your VPC route tables and security group policies allow outbound traffic.

For setup details, see Internet NAT gateway.

ai_sentiment

Classifies the sentiment of input text.

Syntax

ai_sentiment(text)

Parameters

Returns

VARCHAR — 'positive', 'negative', 'neutral', 'mixed', or 'unknown'. Returns NULL if sentiment cannot be determined.

Examples

Single-value test:

SELECT ai_sentiment('I am happy');
-- Returns: 'positive'

Apply to a table column:

SELECT
  review_id,
  review_text,
  ai_sentiment(review_text) AS sentiment
FROM customer_reviews
LIMIT 10;

ai_classify

Assigns one label from a list you define.

Syntax

ai_classify(text, labels)

Parameters

Returns

JSON — contains the classification result. Returns NULL if classification fails.

Examples

Single-value test:

SELECT ai_classify('My password is leaked.', ['urgent', 'not urgent']);
-- Returns: {"labels": ["urgent"]}

Tag product descriptions in bulk:

SELECT
  product_id,
  description,
  ai_classify(description, ['clothing', 'shoes', 'accessories', 'furniture']) AS category
FROM products
LIMIT 10;

ai_extract

Extracts named entities from text and returns them as a JSON object.

Syntax

ai_extract(text, entity_labels)

Parameters

Returns

JSON object where keys are the entity types and values are the extracted text.

Example

SELECT ai_extract(
  'John Doe lives in New York and works for Acme Corp.',
  ['person', 'location', 'organization']
);
-- Returns: {"person":"John Doe","location":"New York","organization":"Acme Corp"}

ai_redact

Replaces specified PII categories with [REDACTED].

Syntax

ai_redact(text, categories)

Parameters

Returns

VARCHAR with matched entities replaced by [REDACTED].

Examples

Single-value test:

SELECT ai_redact(
  'John Doe lives in New York. His email is john.doe@example.com.',
  ['person', 'email']
);
-- Returns: "[REDACTED] lives in New York. His email is [REDACTED]."

Redact PII across a log table before export:

SELECT
  log_id,
  ai_redact(log_text, ['person', 'email', 'phone']) AS redacted_log
FROM audit_logs
LIMIT 10;

ai_fix_grammar

Corrects grammar and spelling in text.

Syntax

ai_fix_grammar(text)

Parameters

Returns

VARCHAR with corrected grammar and spelling.

Example

SELECT ai_fix_grammar('This sentence have some mistake');
-- Returns: "This sentence has some mistake"

ai_summarize

Generates a concise summary of the input text.

Syntax

ai_summarize(text)

Parameters

Returns

VARCHAR summary.

Example

SELECT ai_summarize(
  'Apache Spark is a unified analytics engine for large-scale data processing...',
  10
);
-- Returns: "Spark: unified engine for large-scale data processing with APIs and tools."

ai_translate

Translates text from one language to another.

Syntax

ai_translate(text, source_lang, target_lang)

Parameters

Common language codes:

Returns

Translated VARCHAR.

Example

SELECT ai_translate('Hello, how are you?', 'en', 'es');
-- Returns: "Hola, ¿cómo estás?"

ai_similarity

Calculates the semantic similarity between two texts.

Syntax

ai_similarity(text1, text2)

Parameters

Returns

FLOAT between 0 and 1. A score of 1.0 means the texts are identical. This score is primarily used for sorting.

Example

SELECT ai_similarity(
  'I enjoy hiking in the mountains.',
  'I love walking through mountain trails.'
);
-- Returns: 0.82

ai_complete

Generates content using a specified model. Supports two overloads: a simple form and a parameterized form.

Syntax

-- Overload 1: Simple
ai_complete(model, prompt)
-- Overload 2: With parameters
ai_complete(model, prompt, params)

Parameters

Returns

VARCHAR — the generated text.

Examples

Simple form:

SELECT ai_complete('qwen-plus', 'Write a catchy email subject for a summer bike sale with a 20% discount');
-- Returns: "Summer Cycling Carnival: 20% Off for a Limited Time!"

With parameters:

SELECT ai_complete('qwen-plus', 'Summarize the benefits of cloud computing', map{'temperature':'0.7', 'max_tokens':'200'});
-- Returns: "Cloud computing offers scalable resources, cost efficiency..."

ai_filter

Evaluates a natural-language condition against text and returns TRUE or FALSE. Useful for filtering rows in WHERE clauses.

Syntax

ai_filter(text, condition)

Parameters

Returns

BOOLEAN — TRUE if the text satisfies the condition, FALSE otherwise.

Example

SELECT * FROM reviews WHERE ai_filter(review_text, 'mentions product quality issues');
-- Returns rows where the review text discusses product quality problems

ai_custom_query

Runs a query against a custom resource or knowledge base.

Syntax

ai_custom_query(resource, prompt)

Parameters

Returns

VARCHAR.

Example

SELECT ai_custom_query('my_knowledge_base', 'What is StarRocks?');
-- Returns: "StarRocks is a high-performance analytical data warehouse..."

ai_complete (understanding and generation)

A general-purpose model invocation interface that supports text inference and multimodal understanding. It is the most flexible function among the StarRocks AI Functions. It handles simple text inference as well as multimodal inputs such as images, videos, and multi-content fusion. For text generation, see ai_complete in the text functions section. This section describes multimodal understanding and generation.

Multimodal understanding and generation

You can pass in binary image data, a URL together with a modality, or a multi-content array to drive a multimodal LLM for understanding and generation.

Syntax

-- Binary image input
ai_complete(prompt, binary_image)
ai_complete(prompt, binary_image, extra_params)
ai_complete(model, prompt, binary_image)
ai_complete(model, prompt, binary_image, extra_params)

-- URL + modality input
ai_complete(model, prompt, input, modality)
ai_complete(model, prompt, input, modality, extra_params)

-- Multi-content fusion input
ai_complete(model, prompt, contents)
ai_complete(model, prompt, contents, extra_params)

Parameters

• model: Recommended to specify explicitly for multimodal generation (for example, qwen-vl-max). The URL/video and multi-content forms have no unambiguous default-model overload, so you must pass the model explicitly.

• prompt: Required. The prompt.

• binary_image: Binary image data of the VARBINARY type.

• input: A URL or base64-encoded input of the VARCHAR type. Must be used together with modality.

• modality: The input modality type. Valid values: image and text. Note: Passing video currently returns NULL (you need to extract frames externally and downgrade to image).

• contents: Multi-content fusion input of the ARRAY<MAP<VARCHAR,VARCHAR>> type. Supports passing multiple images or mixed content in a single call.

• extra_params: Optional. Generation parameters as a MAP.

Returns

Returns the model response of the VARCHAR type.

Examples

-- Understand an image from a URL
SELECT ai_complete('qwen-vl-max',
  'Describe the main content of this image',
  'https://bucket.oss-cn-hangzhou.aliyuncs.com/demo.jpg',
  'image');

-- Understand a binary image
SELECT ai_complete('qwen-vl-max', 'Recognize the text in the image', image_binary)
FROM scanned_docs LIMIT 10;

-- Multi-content fusion (image and text)
SELECT ai_complete('qwen-vl-max', 'Compare the differences between these two images',
  array[
    map{'type': 'image_url', 'image_url': 'https://bucket.oss-cn-hangzhou.aliyuncs.com/img1.jpg'},
    map{'type': 'image_url', 'image_url': 'https://bucket.oss-cn-hangzhou.aliyuncs.com/img2.jpg'}
  ]);

ai_embed

Converts text into a fixed-dimension semantic vector.

Syntax

ai_embed(text)
ai_embed(model, text)
ai_embed(model, text, extra_params)

Parameters

• model: Optional. The embedding model name.

• text: Required. The text to vectorize.

• extra_params: Optional (e.g., dimension).

Returns

ARRAY<FLOAT> vector. Returns NULL if text is NULL.

ai_custom_embedding

Calls a custom embedding model through a Resource to generate text vectors.

Syntax

ai_custom_embedding(resource_name, text)
ai_custom_embedding(resource_name, text, extra_params)

Returns

ARRAY<FLOAT> vector.

ai_embed_multimodal

Converts images, videos, or text into semantic vectors in a unified vector space, supporting cross-modal retrieval and multi-content fusion.

Syntax

ai_embed_multimodal(input, modality)
ai_embed_multimodal(model, input, modality)
ai_embed_multimodal(binary_image)
ai_embed_multimodal(contents)

Parameters

• input: URL or Base64-encoded input. Must be used with modality.

• modality: One of image, video, or text.

• binary_image: Binary image data, VARBINARY type.

• contents: Multi-content fusion input, ARRAY<MAP<VARCHAR,VARCHAR>>.

Returns

ARRAY<FLOAT> vector.

ai_custom_multimodal_embedding

Calls a custom multimodal embedding model through a Resource.

Syntax

ai_custom_multimodal_embedding(resource_name, input, modality)
ai_custom_multimodal_embedding(resource_name, binary_image)
ai_custom_multimodal_embedding(resource_name, contents)

Returns

ARRAY<FLOAT> vector.

ai_agg

Performs LLM-based aggregation on a group of texts using a prompt (GROUP BY aggregate function).

Syntax

ai_agg(text, instruction)
ai_agg(model, text, instruction)

Parameters

• text: Required. The text column to aggregate.

• instruction: Required. The aggregation instruction.

Returns

VARCHAR — the aggregated result (one per group).

ai_agg_summary

Generates an aggregate summary for a group of texts — a convenience wrapper around ai_agg for summarization.

Syntax

ai_agg_summary(text)
ai_agg_summary(model, text)

Returns

VARCHAR — the aggregate summary (one per group).

Semantic filtering + classification routing

Use ai_filter in WHERE to select semantically relevant records, then apply ai_classify to assign labels — enabling semantic routing for tickets or reviews. Ideal for automatic ticket dispatch, user feedback categorization, and similar scenarios.

-- Step 1: Semantically filter complaint tickets
-- Step 2: Classify complaints by business line
SELECT ticket_id, content,
       ai_classify(content, ['logistics', 'product quality', 'after-sales', 'pricing dispute']) AS category
FROM tickets
WHERE ai_filter(content, 'the content expresses a customer complaint or dissatisfaction');

Multimodal retrieval dataset construction

Use ai_embed_multimodal to vectorize images/videos at scale, write them to vector columns, and combine vector indexes with full-text indexes for hybrid retrieval. Data never leaves the lakehouse. Ideal for visual product search, video content retrieval, and multimodal RAG knowledge base construction.

-- Batch-vectorize product images
INSERT INTO product_vectors (product_id, image_vec)
SELECT product_id,
       ai_embed_multimodal('qwen3-vl-embedding', image_url, 'image') AS image_vec
FROM products;

-- Text-to-image search: find products most similar to a description
SELECT product_id, image_url
FROM product_vectors
ORDER BY cosine_similarity(image_vec,
  ai_embed_multimodal('qwen3-vl-embedding', 'red summer dress', 'text'))
DESC LIMIT 10;

Customer/product-level AI summarization

Use ai_agg / ai_agg_summary in GROUP BY to consolidate multiple texts per entity into a profile or summary, producing analysis-ready output columns. Ideal for customer profiling, product review summarization, and meeting note extraction.

-- Generate a problem profile for each customer
SELECT customer_id,
       ai_agg(content, 'Summarize all tickets for this customer in one sentence, covering main issues and sentiment') AS customer_profile
FROM support_tickets
GROUP BY customer_id;

-- Aggregate product review summaries
SELECT product_id,
       ai_agg_summary(review_text) AS review_digest
FROM product_reviews
GROUP BY product_id;

PII redaction before analysis

Use ai_redact to anonymize text before storing it for analysis — balancing compliance with usability. Ideal for dev/test data preparation, audit log sanitization, and privacy compliance scenarios.

-- Redact user feedback and write to an analysis table
INSERT INTO feedback_anonymized (ticket_id, content_clean)
SELECT ticket_id,
       ai_redact(content, ['name', 'phone', 'ID number', 'email', 'bank card']) AS content_clean
FROM user_feedback;

Batch content generation and translation

Use ai_complete for batch marketing copy generation, combined with ai_translate for multilingual localization — all within SQL, no external tools needed.

-- Batch-generate marketing slogans and translate to English
SELECT product_name,
       ai_complete('qwen3.6-plus',
         concat('Write a promotional slogan (15 words max) for: ', product_name)) AS slogan,
       ai_translate(
         ai_complete('qwen3.6-plus',
           concat('Write a promotional slogan (15 words max) for: ', product_name)),
         'en', 'ja') AS slogan_ja
FROM products
WHERE category = 'summer_new';

Model connection

Batch sizes

Each function sends requests to the model in batches. Larger batches reduce the number of model calls and can shorten total response time.

Prompt templates

Each function uses a configurable prompt template. Template placeholders use $0, $1, etc.