AnalyticDB:AI Function

ai_filter

Checks whether the input text is factual or compliant. Returns true if the content is credible or compliant, false otherwise.

ai_filter(text)
ai_filter(model_name, text)

Parameters

Return type: BOOLEAN

Example:

SELECT ai_filter('Is Zhejiang in China?');
-- Returns: 1

SELECT ai_filter('qwen_plus_external', 'Is Zhejiang in China?');
-- Returns: 1

ai_translate

Translates input text into the specified language.

ai_translate(text, targetLang)
ai_translate(model_name, text, targetLang)

Parameters

Return type: VARCHAR

Example:

SELECT ai_translate(
  'AnalyticDB for MySQL is a data analytics platform based on a lakehouse architecture.',
  'cn'
) AS translate_text;

ai_classify

Categorizes text into one of the predefined labels.

ai_classify(text, labels)
ai_classify(model_name, text, labels)

Parameters

Return type: VARCHAR

Classify a single value (string-format labels):

SELECT ai_classify(
  'Last night, I tried a new pasta recipe in the kitchen. The sauce was rich, and my family loved it!',
  "['travel', 'cooking', 'reading', 'driving']"
) AS predicted_label;
-- Returns: cooking

Classify a single value (array-format labels):

SELECT ai_classify(
  'Last night, I tried a new pasta recipe in the kitchen. The sauce was rich, and my family loved it!',
  ARRAY['travel', 'cooking', 'reading', 'driving']
) AS predicted_label;
-- Returns: cooking

Classify a column in a table (batch usage):

SELECT id, comment,
  ai_classify('qwen_plus_external', comment, ARRAY['complaint', 'inquiry', 'compliment']) AS category
FROM customer_feedback
WHERE dt = '20260101';

ai_extract

Extracts structured fields from unstructured text.

ai_extract(text, labels)
ai_extract(model_name, text, labels)

Parameters

Return type: VARCHAR

Extract fields from a single value:

SELECT ai_extract(
  'Yesterday (June 15, 2024), I bought an iPhone 15 Pro on Taobao. Its titanium body and A17 chip are truly amazing!',
  "['product_name', 'date', 'key_feature']"
) AS result;
-- Returns: product_name=iPhone 15 Pro, date=June 15, 2024, key_feature=titanium body and A17 chip

Extract fields from a table column (batch usage):

SELECT id, feedback,
  ai_extract('qwen_plus_external', feedback, "['product_name', 'issue', 'contact_info']") AS extracted
FROM customer_feedback
WHERE dt = '20260101';

ai_generate

Generates a text response from the input prompt.

ai_generate(text)
ai_generate(model_name, text)

Parameters

Return type: VARCHAR

Example:

SELECT ai_generate('Introduce the TPC-H test set in one sentence.') AS result;
-- Returns: TPC-H is a standard benchmark test set used to evaluate the decision support capabilities of databases.

ai_sentiment

Analyzes the sentiment of the input text. Returns one of: positive, negative, neutral, or mixed.

ai_sentiment(text)
ai_sentiment(model_name, text)

Parameters

Return type: VARCHAR

Example:

SELECT ai_sentiment(
  'The sound quality of these headphones is excellent, and they are comfortable to wear. Highly recommended!'
) AS sentiment;
-- Returns: positive

ai_similarity

Calculates the semantic similarity between two text segments. Returns a score from 0 to 10 — higher means more similar.

ai_similarity(text1, text2)
ai_similarity(model_name, text1, text2)

Parameters

Return type: VARCHAR

Example:

SELECT ai_similarity(
  'How do I reset my account password?',
  'I forgot my logon password. How can I retrieve it?'
) AS result;
-- Returns: 9.3

ai_mask

Masks sensitive information in the input text. Detected values are replaced with [MSKED].

ai_mask(text, labels)
ai_mask(model_name, text, labels)

Parameters

Return type: VARCHAR

Example:

SELECT ai_mask(
  'Contact me: 1381234****, email is user@example.com, ID card number 110101199003072316',
  "['phone', 'email', 'id_card']"
) AS result;
-- Returns: Contact me: [MSKED], email is [MSKED], ID card number [MSKED]

ai_summarize

Compresses long text into a concise, accurate summary.

ai_summarize(text)
ai_summarize(model_name, text)

Parameters

Return type: VARCHAR

Example:

SELECT ai_summarize(
  'On the winding lotus pond, what meets the eye are lush lotus leaves...'
) AS result;
-- Returns a concise summary of the input text.

ai_audio_transcribe

Transcribes an audio file into text. Returns transcribed text as JSON or a URL to a file saved in Object Storage Service (OSS).

ai_audio_transcribe(url)
ai_audio_transcribe(model_name, url)
ai_audio_transcribe(model_name, url, options)

Parameters

options parameters

Return type: VARCHAR

Example 1 — Transcribe with default settings:

SELECT ai_audio_transcribe(
  'https://dashscope.oss-cn-beijing.aliyuncs.com/samples/audio/paraformer/hello_world_female2.wav'
) AS result;

Example 2 — Transcribe with sentence-level segmentation:

SELECT ai_audio_transcribe(
  'tingwu',
  'https://dashscope.oss-cn-beijing.aliyuncs.com/samples/audio/paraformer/hello_world_female2.wav',
  "{'diarization_mode':'sentence'}"
) AS result;

ai_audio_embed

Converts an audio file into an embedding vector. Optionally specify start and end times to embed a segment of the audio.

ai_audio_embed(text)
ai_audio_embed(model_name, text)
ai_audio_embed(model_name, text, options)

Parameters

options parameters

Return type: Array\<FLOAT\>

Example:

SELECT ai_audio_embed(
  'https://dashscope.oss-cn-beijing.aliyuncs.com/samples/audio/paraformer/hello_world_female2.wav'
);
-- Returns an array of FLOAT values representing the embedding vector.

Text retrieval for RAG

In Retrieval-Augmented Generation (RAG) and customer service applications, use ai_embedd with cosine_similarity to find the most semantically relevant text segments for a given query.

SELECT cosine_similarity(ai_embedd('<query text>'), ai_embedd(text)) AS cos
FROM text_table
ORDER BY cos DESC
LIMIT 10;

To optimize vector search performance at scale, see AISearch.

Voiceprint retrieval

In meeting recordings or conversation analysis, transcribe audio by speaker, then compare audio embeddings against a voice dataset to identify speakers.

1. Transcribe and segment by speaker:

   SELECT AI_AUDIO_TRANSCRIBE('oss://xxx', "{'diarization_mode':'speaker'}");

2. Retrieve the 10 closest voiceprint matches using l2_distance:

   SELECT l2_distance(input_embedding, origin_embedding) AS ld
   FROM (
     SELECT ai_audio_embed('oss://xxx') AS input_embedding
     JOIN
     SELECT ai_audio_embed(url) AS origin_embedding
     FROM audio_table
   )
   ORDER BY ld
   LIMIT 10;

For the complete solution, see Voiceprint Retrieval.

Filter dissatisfied customer reviews

Use ai_sentiment in a WHERE clause to identify negative feedback from a reviews table.

In this example, the qwen_plus_external model (backed by the qwen-plus model in Alibaba Cloud Model Studio) is created in advance.

SELECT name, comment
FROM customer_comment
WHERE ai_sentiment('qwen_plus_external', comment) = 'negative'
AND dt = '20260101';