AI function syntax and examples

This topic is the reference for the AI function service. It describes the functionality of AI functions in OceanBase Database, the syntax and parameters of each function, and various usage examples.

AI functions allow you to directly integrate AI model capabilities into data processing within the database through SQL expressions. This greatly simplifies operations such as data extraction, analysis, summarization, and storage using large AI models. It is a significant new feature in the database and data warehouse fields. In MySQL-compatible mode, OceanBase Database provides the DBMS_AI_SERVICE package for managing AI models and endpoints, introduces several built-in AI functions, and supports monitoring AI model calls through views.

Prerequisites

• You have the permissions required for AI models. For details, see AI function service permissions.
• If you have not yet registered AI models and endpoints, complete registration as described in AI model registration.

Considerations

• CREATE AI MODEL and DROP AI MODEL operations are synchronized between primary and standby tenants, but CREATE AI MODEL ENDPOINT, ALTER AI MODEL ENDPOINT, and DROP AI MODEL ENDPOINT operations are not. Therefore, standby tenants must manually configure AI model endpoints 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 if it is referenced by hybrid search to avoid potential issues.

AI function expressions

OceanBase Database supports the following AI function expressions, so you can call AI models from within the database using SQL and simplify the calling process:

AI_COMPLETE

The AI_COMPLETE function specifies a registered large language model (LLM) by model_key, processes the prompt and data you provide, and returns the text generated by the LLM. You can customize how the prompt and data from the database are combined in the prompt parameter. This supports a variety of text-processing tasks and batch processing inside the database, and avoids the overhead of copying data between the database and the LLM.

Usage

The syntax is as follows:

AI_COMPLETE(model_key, prompt[, parameters])

Parameters:

Both model_key and prompt must be specified. If either is NULL, the function returns an error.

Return value:

• Text generated by the LLM from the prompt.

Examples

1. Sentiment Analysis Example

   SELECT AI_COMPLETE("ob_complete","Your task is to perform sentiment analysis on the provided text to determine if it is positive or negative.
   The text to be analyzed is:
   <text>
   The weather is really nice.
   </text>
   The judgment criteria are as follows:
   If the text expresses a positive sentiment, output 1; if it expresses a negative sentiment, output -1. Do not output anything else.\n") AS ans;
   

   Result:

   +-----+
   | ans |
   +-----+
   | 1   |
   +-----+
   

2. Translation Example

   CREATE TABLE comments (
       id INT AUTO_INCREMENT PRIMARY KEY,
       content TEXT
   );
   
   INSERT INTO comments (content) VALUES ('hello world!');
   
   -- Using a concat expression to replace the processed data with column names from the database table allows for natural batch processing of database data without the need to copy data to the LLM and back.
   SELECT AI_COMPLETE("ob_complete",
   concat("You are a translation master. You need to translate the following Chinese text into English. The text to be translated is:<text>",
       content,
       "</text>")) AS ans FROM comments;
   

   Result:

   +-------------+
   | ans         |
   +-------------+
   | Hello, world! |
   +-------------+
   

3. Categorization Example

   SELECT AI_COMPLETE("ob_complete","You are a categorization master. You will receive a set of question texts and need to classify them into the following categories: [\"Hardware Department\",\"Software Department\",\"Other\"]. The text to be analyzed is:
   <text>
   The screen quality is really poor.
   </text>") AS res;
   

   Result:

   +--------+
   | res    |
   +--------+
   | Hardware Department |
   +--------+
   

AI_EMBED

The AI_EMBED function takes a registered embedding model specified by model_key and converts the provided text data into vector data. When the model supports multiple dimensions, you can use the dim parameter to specify the desired output dimensions.

Usage

The syntax is as follows:

AI_EMBED(model_key, input, [dim])

Parameters:

The function will return an error if model_key or input is NULL.

Return value:

• A string in vector format, representing the converted vector from the embedding model.

Examples

1. Embedding single-row data.

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

   The result is as follows:

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

2. Embedding data in 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 result is as follows:

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

AI_RERANK

The AI_RERANK function specifies a registered reranking model using the model_key parameter. It sends the user's query and document list to the specified model according to the vendor's rules, parses the model's response, and returns the sorted results. This function is suitable for reranking scenarios in RAG (Retrieval-Augmented Generation).

Usage

The syntax is as follows:

AI_RERANK(model_key, query, documents[, document_key])

Parameters:

You must specify model_key, query, and documents. If any of them is NULL, the function will return an error.

Return value:

• A JSON array containing the documents returned by the reranking model and their relevance scores, sorted in descending order of relevance scores.

Example

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

You can view registered AI models and AI model endpoints through system views. For details, see:

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

Related topics

• AI model registration: Full syntax for registering models and endpoints.
• AI function service quick start: Get started from registration to running your first example.
• Vector embedding technology
• Permission classification in MySQL-compatible mode