This topic describes the syntax and parameters of AI functions and provides usage examples. Currently, the following AI functions are supported: AI_SPLIT_DOCUMENT, AI_EMBED, AI_COMPLETE, AI_PROMPT, and AI_RERANK.
AI functions integrate AI model capabilities directly into data processing within a database using SQL expressions. They greatly simplify operations such as data reading, analysis, summarization, and saving using large AI models, making them an important new feature in the field of databases and data warehouses. OceanBase AI Database provides AI model and endpoint management through the DBMS_AI_SERVICE package. It also includes several built-in AI function expressions and supports monitoring AI model calls via views.
Prerequisites (except for AI_SPLIT_DOCUMENT)
- You have the necessary permissions for AI models. For details, see AI Function Service Permissions.
- If you have not registered an AI model and endpoint, please complete the registration first by following Register an AI model.
Considerations (except for AI_SPLIT_DOCUMENT)
- The
CREATE AI MODELandDROP AI MODELoperations are synchronized between primary and standby tenants, but theCREATE AI MODEL ENDPOINT,ALTER AI MODEL ENDPOINT, andDROP AI MODEL ENDPOINToperations are not. Therefore, you must manually configure an AI model endpoint for a standby tenant to use AI function services. - Hybrid Search relies on the model management and embedding features of AI function services. When deleting an AI model, check whether it is referenced by any Hybrid Search instances to avoid potential issues.
Syntax and examples of functions
This section describes the syntax and examples of AI function services.
AI_SPLIT_DOCUMENT
The AI_SPLIT_DOCUMENT function splits text into multiple chunks to prepare it for further processing.
Syntax
The syntax is as follows:
AI_SPLIT_DOCUMENT(content TEXT, [parameters JSON]) [AS alias_name]
Parameter description:
Parameters |
Description |
Type |
Nullable |
|---|---|---|---|
| content | Text data entered by the user. Currently, only Markdown format and UTF-8 encoding are supported. | VARCHAR / TEXT | No. This parameter is required. If it is empty, an empty table is returned. |
| parameters | Optional parameters supported by the API. The value must be a JSON object or a string in JSON format. Valid values:
|
JSON | Yes |
| alias_name | Specifies an alias for the returned result. | VARCHAR(128) | Yes |
Return value:
- Returns a relation table containing four fields:
CHUNK_ID,CHUNK_OFFSET,CHUNK_LENGTH, andCHUNK_TEXT. Here,CHUNK_IDis the ID of the split chunk,CHUNK_OFFSETis the starting position of the split chunk,CHUNK_LENGTHis the length of the split chunk in bytes, andCHUNK_TEXTis the text content of the split chunk.
Limitations:
- Currently, only UTF-8 encoded data can be split.
- This function can only be used as a table function after the
FROMclause. It cannot be directly used as a scalar function in theSELECTlist.
Example
SELECT * FROM ai_split_document("Hello World",'{"max":1}');
The return result is as follows:
+----------+--------------+--------------+------------+
| CHUNK_ID | CHUNK_OFFSET | CHUNK_LENGTH | CHUNK_TEXT |
+----------+--------------+--------------+------------+
| 0 | 0 | 6 | Hello |
| 1 | 6 | 6 | World |
+----------+--------------+--------------+------------+
AI_EMBED
The AI_EMBED function uses model_key to specify a registered embedding model, converting user-provided text or image data into vector data. When the model supports multiple dimensions, you can use the dim parameter to specify the output vector dimensions. For image input, specify the input type by using the JSON parameter {"type":"image"}.
Syntax
The syntax is as follows:
AI_EMBED(model_key, input[, dim[, parameters]])
Parameter description:
Parameters |
Description |
Type |
Nullable |
|---|---|---|---|
| model_key | Model identifier in the provider/model format, for example, aliyun-dashscope/text-embedding-v3. |
VARCHAR(128) | No |
| input | The data to embed: text, an image URL (HTTP/HTTPS), or binary image data (for example, BLOB columns or FROM_BASE64(...)). |
VARCHAR / BLOB | No |
| dim | Specifies the output vector dimensionality. Some models support configuring multiple dimensions; this parameter is optional. If omitted, the model's default dimensionality is used. | INT64 | Yes |
| parameters | Optional configuration as a JSON object or a JSON string. It must be specified for image input ({"type":"image"}). If dim is omitted, you can pass this parameter directly as the third argument.
NoticeImage embedding is supported starting from V4.6.2.1. |
JSON | Yes |
If either model_key or input is specified as NULL, the function returns an error.
Return value:
- A string in vector format that represents the vector generated from the input data by the embedding model.
Examples
Embed a single row of data
SELECT AI_EMBED('aliyun-dashscope/text-embedding-v3', 'Hello world') AS embedding;The return result is as follows:
+----------------+ | embedding | +----------------+ | [0.1, 0.2, 0.3]| +----------------+Embed a column from a table
CREATE TABLE comments ( id INT AUTO_INCREMENT PRIMARY KEY, content TEXT ); INSERT INTO comments (content) VALUES ('hello world!'); SELECT AI_EMBED('aliyun-dashscope/text-embedding-v3', content) AS embedding FROM comments;The return result is as follows:
+----------------+ | embedding | +----------------+ | [0.1, 0.2, 0.3]| +----------------+Image embedding
SET @img_url = 'https://example.com/image.jpg'; -- Vectorize image URLs SELECT AI_EMBED('aliyun-dashscope/qwen2.5-vl-embedding', @img_url, '{"type":"image"}') AS embedding; -- Vectorize image binary data (BASE64) SELECT AI_EMBED('aliyun-dashscope/qwen2.5-vl-embedding', FROM_BASE64(@img_base64), '{"type":"image"}') AS embedding;Image search by image
Compare the similarity of image vectors using
cosine_distance:SET @query_vec = AI_EMBED('aliyun-dashscope/qwen2.5-vl-embedding', @img_url, '{"type":"image"}'); SELECT id, cosine_distance( AI_EMBED('aliyun-dashscope/qwen2.5-vl-embedding', image_url, '{"type":"image"}'), @query_vec ) AS distance FROM image_table WHERE image_url IS NOT NULL ORDER BY distance ASC LIMIT 3;
AI_COMPLETE and AI_PROMPT
The AI_COMPLETE function specifies a registered large language model (LLM) through the model_key parameter, processes the user-provided prompt and data, and returns text information generated by the LLM. You can customize the organization of prompts and the data format within the database in the prompt parameter. Combined with the image placeholders of AI_PROMPT, it can also process and analyze images. This approach not only supports diverse processing of textual data but also enables batch processing within the database, effectively avoiding the overhead of repeatedly copying data between the database and the LLM.
Considering that prompts in many AI application scenarios are highly structured and require dynamic injection of specific data, manually concatenating prompts with input content using functions like CONCAT is not only costly but also prone to formatting errors. To support prompt reuse and dynamic combination of prompts with data, OceanBase AI Database provides the AI_PROMPT function. AI_PROMPT upgrades prompts from "static text" to a "reusable, parametric" functional template form, which can be used directly in AI_COMPLETE to replace the prompt parameter, greatly simplifying the prompt construction process and improving development efficiency and accuracy.
AI_PROMPT function
The AI_PROMPT function is used to dynamically construct formatted prompts based on a prompt template, supporting dynamic data insertion.
Syntax
The syntax for the AI_PROMPT function is as follows:
AI_PROMPT('template', expr0 [ , expr1, ... ]);
Parameter description:
Parameters |
Description |
Type |
Nullable |
|---|---|---|---|
| template | The prompt template entered by the user. It supports text placeholders such as {0} and {1}, and image placeholders such as {img_0} and {img_1}. |
VARCHAR(max_length) | No |
| expr | The user-provided data. A text placeholder accepts a string. An image placeholder accepts an HTTPS URL or binary data returned by FROM_BASE64(...).
NoteImage processing is supported starting from V4.6.2.1. |
VARCHAR(max_length) | No |
The template and expr parameters are required and cannot be empty. The expr parameter supports only the VARCHAR type, not the JSON type.
Note
The index of parameter placeholders in a prompt template must be unique; otherwise, an error will occur. For example, in AI_PROMPT("tell me {0}+{0}=?", '10', '10'), the template contains multiple {0}, which is not allowed. The correct usage is to have each placeholder appear only once, for example, AI_PROMPT("tell me {0}+{1}=?", '10', '10').
Return value:
- The return value is the formatted prompt, in JSON format. URL images contain
type=imageandurlfields in the JSON; binary images containtype=image,format, anddatafields.
Examples
The AI_PROMPT function organizes template strings and dynamic data into JSON format, facilitating automatic replacement and reuse by downstream AI functions. Its usage is mainly divided into two scenarios: text placeholder and image placeholder, which support inserting text and image parameters into the prompt template, respectively.
Example of text placeholder usage
Text placeholders are marked in the template with
{0},{1}, etc., and are automatically replaced with the corresponding text in parameter order. They are commonly used to dynamically generate prompts with specific data (such as quantities and item names).SELECT AI_PROMPT('Recommend {0} of the most popular {1} to me.', 'ten', 'mobile phones');The result is as follows:
{ "template": "Recommend {0} of the most popular {1} to me.", "args": ["ten", "mobile phones"] }Based on the previous example, use the
AI_PROMPTfunction in theAI_COMPLETEfunction:SELECT AI_COMPLETE("ob_complete", AI_PROMPT('Recommend {0} of the most popular {1} to me.just output name in json array format', 'two', 'mobile phones')) AS ans;The result is as follows:
+--------------------------------------------------+ | ans | +--------------------------------------------------+ | ["iPhone 15 Pro Max","Samsung Galaxy S24 Ultra"] | +--------------------------------------------------+Example of image placeholder usage
In the template, you can use placeholders such as
{img_0}and{img_1}to mark image placeholders, and pass the corresponding image URL or binary data as parameters. This enables multimodal large models to understand image content.The rules for image placeholders are as follows:
- If all parameters are images, simply use markers like
{img_0}and{img_1}in sequence. Each image parameter corresponds to a number starting from 0, which has nothing to do with the numbers of text placeholders{0}and{1}. - If you mix image and text parameters, then N in
{img_N}indicates the position (index, starting from 0) of this parameter among all parameters, which matches the number of the text placeholder{N}. For example, if the second parameter (index 1) is an image, write{img_1}; if the fourth parameter (index 3) is an image, write{img_3}. There's no need to assign a separate number based on which image it is.
Here is an example:
-- Single Image URL SELECT AI_PROMPT('Please describe this image {img_0}', @img_url) AS result; -- Multi-image comparison SELECT AI_PROMPT( 'Compare the differences between these two images {img_0} and {img_1}', @img_url_1, @img_url_2 ) AS result; -- Mixed text and image (the image is at subscript 1, so use {img_1}) SELECT AI_PROMPT( 'Please describe this image {img_1} in {0} sentences.', '3', @img_url ) AS result; -- Text and image alternate (the second image is at index 3, hence {img_3}) SELECT AI_PROMPT( 'Compare the image in {0} format {img_1} with the image in {2} format {img_3}, and summarize the differences in one sentence.', 'JPEG', @img_jpeg, 'PNG', @img_png ) AS result;- If all parameters are images, simply use markers like
AI_COMPLETE function
Syntax
The syntax for the AI_COMPLETE function is as follows:
AI_COMPLETE(model_key, prompt[, parameters])
-- If you use AI_PROMPT, replace the prompt argument with an AI_PROMPT expression. For examples, see the AI_PROMPT section.
AI_COMPLETE(model_key, AI_PROMPT(prompt_template, data))
Parameter description:
Parameters |
Description |
Type |
Nullable |
|---|---|---|---|
| model_key | Model identifier in the provider/model format, for example, aliyun/qwen-plus. |
VARCHAR(128) | No |
| prompt | The prompt information entered by the user. | VARCHAR/TEXT(LONGTEXT) | No |
| parameters | Used to specify optional parameters supported by the API. The optional fields of the model are directly included in the generated message body, which may vary by provider. Common optional parameters include temperature, top_p, and max_tokens. In most cases, you do not need to specify a configuration; the default configuration is sufficient. |
JSON | Yes |
If either model_key or prompt is specified as NULL, the function returns an error.
Return value:
- A text string generated by the large language model based on the prompt.
Examples
Sentiment analysis example
SELECT AI_COMPLETE("ob_complete","Your task is to perform sentiment analysis on the provided text and determine whether it is positive or negative. The text to be analyzed is as follows: <text> The weather is so nice. </text> The judgment criteria are as follows: If the text expresses a positive sentiment, output 1; if the text expresses a negative sentiment, output -1. Do not output anything else.\n") AS ans;The return result is as follows:
+-----+ | ans | +-----+ | 1 | +-----+Translation example
CREATE TABLE comments ( id INT AUTO_INCREMENT PRIMARY KEY, content TEXT ); INSERT INTO comments (content) VALUES ('hello world!'); -- Use a table column in the concatenation expression to process table data in batches without moving the data out of the database. SELECT AI_COMPLETE("ob_complete", concat("You are a translator. Translate the following English text into French: <text>", content, "</text>")) AS ans FROM comments;The return result is as follows:
+--------------+ | ans | +--------------+ | Bonjour ! | +--------------+Classification example
SELECT AI_COMPLETE("ob_complete","You are a classification assistant. Classify the following text into one of these categories: [\"Hardware Department\",\"Software Department\",\"Other\"]. The text to analyze is as follows: <text> The quality of this screen is really poor. </text>") AS res;The return result is as follows:
+--------+ | res | +--------+ | Hardware Department | +--------+Image processing
Note
This feature is supported starting from V4.6.2.1.
SET @img_url = 'https://example.com/image.jpg'; -- Understand a single image from an image URL. SELECT AI_COMPLETE( 'aliyun-dashscope/qwen3.5-plus', AI_PROMPT('Please describe this image {img_0}', @img_url) ) AS result; -- Understand a single image from Base64-encoded data. SELECT AI_COMPLETE( 'aliyun-dashscope/qwen3.5-plus', AI_PROMPT('Please describe this image {img_0}', FROM_BASE64(@img_base64)) ) AS result; -- Multi-image comparison SELECT AI_COMPLETE( 'aliyun-dashscope/qwen3.5-plus', AI_PROMPT( 'Compare the image in {0} format {img_1} with the image in {2} format {img_3}, and summarize the differences in one sentence.', 'JPEG', @img_jpeg, 'PNG', @img_png ) ) AS result;
AI_RERANK
The AI_RERANK function uses model_key to specify a registered reranking model. It formats the user-provided query and document list according to the provider's requirements, sends them to the model, and parses the returned ranking results. This function is suitable for reranking in RAG scenarios.
Usage
The syntax is as follows:
AI_RERANK(model_key, query, documents[, document_key])
The following table describes the parameters.
Parameters |
Description |
Type |
Nullable |
|---|---|---|---|
| model_key | Model identifier in the provider/model format, for example, aliyun/gte-rerank-v2. |
VARCHAR(128) | No |
| query | The query text entered by the user. | VARCHAR(1024) | No |
| documents | The document list entered by the user must be a JSON array.
|
JSON ARRAY | No |
| document_key | Optional. When documents is an array of objects, specifies the name of the text field used for reranking. The function extracts the string value of this key from each object and passes it to the model. If this parameter is omitted or set to NULL, documents is treated as an array of strings. |
VARCHAR | Yes |
The model_key, query, and documents parameters are required. If any of them is NULL, the function returns an error.
Return value:
- A JSON array containing the documents returned by the reranked model and their relevance scores, sorted in descending order by relevance score.
Examples
-- documents is a string array (document_key is omitted).
SELECT AI_RERANK("aliyun/gte-rerank-v2","Apple",'["apple","banana","fruit","vegetable"]');
-- The `documents` parameter is an array of objects. Use the `document_key` parameter to specify the text field used for sorting.
SELECT AI_RERANK(
"aliyun/gte-rerank-v2",
"Apple",
'[{"text":"apple","id":1},{"text":"banana","id":2},{"text":"fruit","id":3}]',
'text'
);
The return result is as follows (string array example):
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ai_rerank("aliyun/gte-rerank-v2","Apple",'["apple","banana","fruit","vegetable"]') |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"index": 0, "document": {"text": "apple"}, "relevance_score": 0.9912109375}, {"index": 1, "document": {"text": "banana"}, "relevance_score": 0.0033512115478515625}, {"index": 2, "document": {"text": "fruit"}, "relevance_score": 0.0003669261932373047}, {"index": 3, "document": {"text": "vegetable"}, "relevance_score": 0.00001996755599975586}] |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
View AI model information
OceanBase AI Database supports viewing registered AI models and AI model endpoint information through views. For details, see:
- oceanbase.DBA_OB_AI_MODELS: View AI model information.
- oceanbase.DBA_OB_AI_MODEL_ENDPOINTS: View AI model endpoint information.
References
- Register an AI model: Complete instructions for registering a provider configuration.
- Get started with AI Function Service: A first-time user guide, covering the minimum steps from registration to running your first example.
- Overview of Vector Embedding Technology
- Permission classification