Get started with AI Function Service

This topic is for users who are new to AI Function Service. It guides you through the process of registering a model and running the first example with minimal steps, helping you quickly get started.

This topic only uses the AI_COMPLETE function to demonstrate the quick start process. For other functions, refer to Syntax and examples of AI Function Service.

Prerequisites

• You have deployed an OceanBase cluster, created a MySQL-compatible tenant, and connected to the database.
• You have the necessary permissions for AI functions. For details, see Permissions for AI Function Service.
• You have obtained the API Key of a third-party model service.

Quick start

Step 1: Register vendor configuration

Before using it for the first time, you need to register vendor configuration. This example uses the built-in vendor aliyun. You only need to fill in access_key. protocol and base_url can be omitted as the system will automatically fill in the default values. Please replace access_key with your actual API Key:

-- This example uses the API key of a text generation model.
CALL DBMS_AI_SERVICE.REGISTER_PROVIDER('aliyun', '{
  "access_key": "sk-xxxx"
}');

For more information about how to configure vendors, see the relevant documentation at the end of this topic.

Step 2: (Optional) Configure model parameters

If you need to set call parameters for a specific model, you can use ALTER_MODEL_PROFILE:

CALL DBMS_AI_SERVICE.ALTER_MODEL_PROFILE('aliyun/qwen-plus', '{
  "model_config": {
    "max_tokens": 4096,
    "temperature": 0
  }
}');

Step 3: Run an example

Call AI_COMPLETE in the provider/model format to perform sentiment analysis on a piece of text:

SELECT AI_COMPLETE('aliyun/qwen-plus', AI_PROMPT('Your task is to perform sentiment analysis on the provided text and determine whether its sentiment tendency is positive or negative.
    The following is the text to be analyzed:
    <text>
    {0}
    </text>
    The judgment criteria are as follows:
    If the text expresses positive sentiment, output 1; if the text expresses negative sentiment, output -1. Do not output anything else.', 'The weather is so nice!')) AS sentiment;

The expected return result is that sentiment is 1, indicating positive sentiment.

Step 4: (Optional) Use a gateway for routing

To enable weighted routing and disaster recovery among multiple model endpoints, you can create an AI gateway and configure endpoint weights and circuit-breaking parameters:

CALL DBMS_AI_SERVICE.CREATE_AI_GATEWAY('prod_gateway', '{
  "endpoints": [
    {"name":"ep1","model":"aliyun/qwen-plus","weight":70},
    {"name":"ep2","model":"deepseek/deepseek-chat","weight":30}
  ],
  "circuit_breaker": {
    "failure_rate_threshold": 50,
    "window_size_seconds": 60,
    "minimum_requests": 10,
    "break_duration_seconds": 60,
    "probe_requests": 3
  }
}');

After creation, you can call AI functions by using the gateway name. For more information about the gateway syntax, see CREATE_AI_GATEWAY.

Step 5: Build a comprehensive application (Optional)

Combine multiple AI functions to build a simple intelligent Q&A system.

1. Register required vendor configurations (Optional)

   This example requires embedding models, text generation models, and reordering models. If you have registered the aliyun vendor configuration in Step 1 and accessed the corresponding models through this vendor, skip this step. Otherwise, register configurations for other vendors:

   CALL DBMS_AI_SERVICE.REGISTER_PROVIDER('deepseek', '{
     "access_key": "sk-xxxx"
   }');
   

2. Prepare data and generate vectors

   CREATE TABLE knowledge_base (
       id INT AUTO_INCREMENT PRIMARY KEY,
       title VARCHAR(255),
       content TEXT,
       embedding TEXT
   );
   
   INSERT INTO knowledge_base (title, content) VALUES
       ('Introduction to OceanBase', 'OceanBase is a powerful database system that supports vector search and AI functions.');
       ('Vector search', 'Vector search can be used for semantic search to find similar content.');
       ('AI functions', 'AI functions allow you to directly call AI models in SQL statements.')
   
   UPDATE knowledge_base
   SET embedding = AI_EMBED('aliyun/text-embedding-v3', content);
   

3. Perform vector search and reordering

   SET @query = "What is vector search?";
   SET @query_vector = AI_EMBED('aliyun/text-embedding-v3', @query);
   
   -- Directly construct a string array format document list
   SET @candidate_docs = '["OceanBase is a powerful database system that supports vector search and AI functions.", "Vector search can be used for semantic search to find similar content."]';
   
   SELECT AI_RERANK('aliyun/gte-rerank-v2', @query, @candidate_docs) AS ranked_results;
   

   The returned result is as follows. index is the document index, and relevance_score is the relevance score:

   +-------------------------------------------------------------------------------------------------------------+
   | ranked_results                                                                                              |
   +-------------------------------------------------------------------------------------------------------------+
   | [{"index": 1, "relevance_score": 0.9904329776763916}, {"index": 0, "relevance_score": 0.16993996500968933}] |
   +-------------------------------------------------------------------------------------------------------------+
   1 row in set
   

4. Generate an answer

   Based on the retrieval and reordering results, generate an answer:

   SELECT AI_COMPLETE('aliyun/qwen-plus',
       AI_PROMPT('Based on the following document content, answer the user's question.
       User question: {0}
   
       Related document: {1}
   
       Please answer the user's question concisely and accurately based on the above document content.', @query, CAST(JSON_EXTRACT(@candidate_docs, '$[1]') AS CHAR))) AS answer;
   

   The return result is as follows:

   +--------------------------------------------------------------------------------------------------------------------------------------------+
   | answer                                                                                                                                     |
   +--------------------------------------------------------------------------------------------------------------------------------------------+
   | According to the provided document content, vector search is a semantic search technology designed to find similar content by comparing vector data.                                               |
   +--------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

   Through the steps above, you can quickly complete the entire AI application process within OceanBase Database: vectorization, search, reordering, and generating an answer.

References

• Register an AI model: Gateway and circuit breaker configuration, vendor information configuration, and registration method for legacy APIs.
• Syntax and examples of AI functions: Syntax, parameter tables, and more examples for each function.
• AI function service permissions: Permission granting and revocation.