Syntax and examples of AI functions

This topic describes the syntax and parameters of AI functions and provides examples of how to use them in OceanBase Database. 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 into in-database data processing through SQL expressions. You can use these functions to read, analyze, summarize, and store data with large language models. In MySQL-compatible mode, OceanBase Database provides the DBMS_AI_SERVICE package for managing AI models and endpoints, built-in AI function expressions for calling models, and views for monitoring model calls.

Prerequisites (except for AI_SPLIT_DOCUMENT)

• You have the necessary permissions for AI models. For details, see Permissions for AI function services.
• If you have not registered an AI model with an endpoint, please complete the registration first by following Register an AI model.

Considerations (except for AI_SPLIT_DOCUMENT)

• The CREATE AI MODEL and DROP AI MODEL operations are synchronized between primary and standby tenants, but the CREATE AI MODEL ENDPOINT, ALTER AI MODEL ENDPOINT, and DROP AI MODEL ENDPOINT operations 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 a 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:

Return value:

• Returns a relation table containing four fields: CHUNK_ID, CHUNK_OFFSET, CHUNK_LENGTH, and CHUNK_TEXT. Here, CHUNK_ID is the ID of the split chunk, CHUNK_OFFSET is the starting position of the split chunk, CHUNK_LENGTH is the length of the split chunk in bytes, and CHUNK_TEXT is 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 FROM clause. It cannot be directly used as a scalar function in the SELECT list.

Examples

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 and converts user-provided text or image data into vector data. If the model supports multiple output dimensions, use the dim parameter to specify the output vector dimension. For image input, specify the input type by setting the JSON parameter to {"type":"image"}.

Syntax

The syntax is as follows:

AI_EMBED(model_key, input, [dim_or_parameters])

Parameter description:

Both model_key and input are required. If either parameter is 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

1. Embed single-row data

   SELECT AI_EMBED("ob_embed","Hello world") AS embedding;
   

   The return result is as follows:

   +----------------+
   | embedding      |
   +----------------+
   | [0.1, 0.2, 0.3]|
   +----------------+
   

2. Embed columns from a table

   CREATE TABLE comments (
       id INT AUTO_INCREMENT PRIMARY KEY,
       content TEXT
   );
   
   INSERT INTO comments (content) VALUES ('hello world!');
   
   SELECT AI_EMBED("ob_embed",content) AS embedding FROM comments;
   

   The return result is as follows:

   +----------------+
   | embedding      |
   +----------------+
   | [0.1, 0.2, 0.3]|
   +----------------+
   

3. Image embedding

   SET @img_url = 'https://example.com/image.jpg';
   
   -- Embed an image URL.
   SELECT AI_EMBED('ob_vl_embed', @img_url, '{"type":"image"}') AS embedding;
   
   -- Embed Base64-encoded binary image data.
   SELECT AI_EMBED('ob_vl_embed', FROM_BASE64(@img_base64), '{"type":"image"}') AS embedding;
   

4. Image search by image

   Compare the similarity of image vectors using cosine_distance:

   SET @query_vec = AI_EMBED('ob_vl_embed', @img_url, '{"type":"image"}');
   SELECT
       id,
       cosine_distance(
           AI_EMBED('ob_vl_embed', 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 model_key, 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 using the prompt parameter. Combined with the image placeholders of AI_PROMPT, it can also understand 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.

Prompts are often structured and contain dynamic data. Manually combining a prompt with input data by using a function such as CONCAT is repetitive and prone to formatting errors. The AI_PROMPT function lets you define reusable, parameterized prompt templates and dynamically insert data. You can pass its return value directly as the prompt argument of AI_COMPLETE.

AI_PROMPT function

The AI_PROMPT function is used to dynamically construct formatted prompts based on a prompt template, supporting the dynamic insertion of data.

Syntax

The syntax for the AI_PROMPT function is as follows:

AI_PROMPT('template', expr0 [ , expr1, ... ]);

Parameter description:

The template and expr parameters are required and cannot be empty. The expr parameter supports the VARCHAR and BLOB data types, but not the JSON data type.

Return value:

• The return value is the formatted prompt, in JSON format. URL images contain the type=image and url fields in the JSON; binary images contain the type=image, format, and data fields.

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. Examples are provided below.

1. 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 order. It is commonly used to dynamically generate prompts with specific data (such as quantities, item names, etc.).

   Example:

   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_PROMPT function in the AI_COMPLETE function:

   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 return result is as follows:

   +--------------------------------------------------+
   | ans                                              |
   +--------------------------------------------------+
   | ["iPhone 15 Pro Max","Samsung Galaxy S24 Ultra"] |
   +--------------------------------------------------+
   

2. Examples of using image placeholders

   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 image parameters and text parameters are used together, then N in {img_N} indicates the position of this parameter among all parameters (the index, starting from 0), 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:

   -- Use a single image URL.
   SELECT AI_PROMPT('Please describe this image {img_0}', @img_url) AS result;
   
   -- Compare multiple images.
   SELECT AI_PROMPT(
       'Compare the differences between these two images {img_0} and {img_1}',
       @img_url_1,
       @img_url_2
   ) AS result;
   
   -- Text and image mixed (the image is at index 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;
   

Using this format, developers can flexibly construct structured, multimodal AI prompts to achieve batch automated intelligent processing.

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:

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

1. Sentiment analysis example

   SELECT AI_COMPLETE("ob_complete","Your task is to perform sentiment analysis on the provided text and determine whether its sentiment orientation is positive or negative.
   The following is the text to be analyzed:
   <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   |
   +-----+
   

2. 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 !    |
   +--------------+
   

3. 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 |
   +--------+
   

4. Image understanding

   Note

   This feature is supported starting from V5.0.1.

   SET @img_url = 'https://example.com/image.jpg';
   
   -- Understand a single image from an image URL.
   SELECT AI_COMPLETE(
       'ob_vl_complete',
       AI_PROMPT('Please describe this image {img_0}', @img_url)
   ) AS result;
   
   -- Understand a single image from Base64-encoded data.
   SELECT AI_COMPLETE(
       'ob_vl_complete',
       AI_PROMPT('Please describe this image {img_0}', FROM_BASE64(@img_base64))
   ) AS result;
   
   -- Compare multiple images.
   SELECT AI_COMPLETE(
       'ob_vl_complete',
       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)

Parameter description:

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 reranking model and their relevance scores, sorted in descending order by relevance score.

Examples

SELECT AI_RERANK("ob_rerank","Apple",'["apple","banana","fruit","vegetable"]');

The return result is as follows:

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ai_rerank("ob_rerank","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 Database supports viewing registered AI models and AI model endpoint information through views. For details, see:

• CDB/DBA_OB_AI_MODELS: View AI model information.
• CDB/DBA_OB_AI_MODEL_ENDPOINTS: View AI model endpoint information.

References

• Register an AI model: The complete command for registering a model and its endpoint.
• Quick start with AI Function Service: A first-use guide covering the minimum steps from registration to running your first example.
• Vector embedding technology
• Privilege types in MySQL-compatible mode