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 from a third-party model service.

Quick start

Step 1: Register a model provider

Before using it for the first time, you must register a model provider. This example uses the built-in provider aliyun. You only need to enter the 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 on configuring providers, see the related 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 analyze:
    <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 result is that sentiment equals 1, indicating positive sentiment.

Step 4: Comprehensive application (Optional)

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

1. Register the required providers (optional)

   This example requires an embedding model, a text generation model, and a reranking model. If you registered the aliyun provider in Step 1 and access all corresponding models through this provider, skip this step. Otherwise, register other providers:

   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 reranking

   SET @query = "What is vector search?";
   SET @query_vector = AI_EMBED('aliyun/text-embedding-v3', @query);
   
   -- Construct a string array directly
   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, where 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

   Generate an answer based on the retrieval and reranking results:

   SELECT AI_COMPLETE('aliyun/qwen-plus',
       AI_PROMPT('Based on the following document content, answer the user's question.
       User question: {0}
   
       Related documents: {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 result is as follows:

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

   By following the steps above, you can quickly complete the entire AI application process within OceanBase Database: vectorization, search, reranking, and answer generation.

References

• Register AI models: Provider and model registration.
• Syntax and examples of AI functions: Syntax, parameter tables, and more examples for each function.
• AI function service permissions: Permission granting and revocation.