AI function service quick start

This guide walks you through using the AI function service for the first time. You will register a model, run your first example with minimal steps, and get the full flow working quickly.

Prerequisites

• An OceanBase cluster is deployed, a MySQL-compatible tenant is created, and you are connected to the database.
• You have the permissions required for the AI function service. For details, see AI function service permissions.

Quick start

Step 1: Register a model and endpoint

Before you use the AI function service, register an AI model and its access endpoint. You can use the templates in AI model registration: copy the example for your AI model provider and replace the API key with your real API key. The following example registers the AI_COMPLETE sentiment analysis model (Alibaba Cloud, OpenAI-compatible):

CALL DBMS_AI_SERVICE.DROP_AI_MODEL ('ob_complete');
CALL DBMS_AI_SERVICE.DROP_AI_MODEL_ENDPOINT ('ob_complete_endpoint');

CALL DBMS_AI_SERVICE.CREATE_AI_MODEL(
'ob_complete', '{
    "type": "completion",
    "model_name": "THUDM/GLM-4-9B-0414"
}');

CALL DBMS_AI_SERVICE.CREATE_AI_MODEL_ENDPOINT (
'ob_complete_endpoint', '{
    "ai_model_name": "ob_complete",
    "url": "https://dashscope.aliyuncs.com/compatible-mode/v1/chat/completions",
    -- Replace with your actual access_key
    "access_key": "sk-xxxxxxxxxxxxxxxxxxxxxxxxxxx",
    "provider": "aliyun-openAI"
}');

Step 2: Run an example

This example uses the AI_COMPLETE sentiment analysis model to classify a short text as positive or negative:

SELECT AI_COMPLETE("ob_complete", "Your task is to perform sentiment analysis on the given text and determine whether the sentiment is positive or negative.
    Here is the text to analyze:
    <text>
    The weather is so nice today.
    </text>
    Use the following rules:
    If the text expresses positive sentiment, output 1; if it expresses negative sentiment, output -1. Do not output anything else.") AS sentiment;

The expected result has sentiment equal to 1, meaning positive sentiment.

Step 3: End-to-end example (optional)

You can combine all three AI functions to build a simple question-answering flow in three steps.

1. Register all required models and endpoints (optional).

   This example uses an embedding model, a text-generation model, and a rerank model. Ensure that the corresponding models and endpoints are registered as described in Step 1.

   Note

   If you already registered the models in the previous steps, skip the registration steps here and go to the next step.

2. Prepare data and generate embeddings.

   CREATE TABLE knowledge_base (
       id INT AUTO_INCREMENT PRIMARY KEY,
       title VARCHAR(255),
       content TEXT,
       embedding TEXT
   );
   
   INSERT INTO knowledge_base (title, content) VALUES
       ('About OceanBase', 'OceanBase is a powerful database system that supports vector search and AI functions.'),
       ('Vector search', 'Vector search enables semantic search to find similar content.'),
       ('AI functions', 'AI functions let you call AI models directly from SQL.');
   
   UPDATE knowledge_base 
   SET embedding = AI_EMBED("ob_embed", content);
   

3. Run vector search and rerank.

   SET @query = "What is vector search?";
   SET @query_vector = AI_EMBED("ob_embed", @query);
   
   -- Build the document list as a JSON string array
   SET @candidate_docs = '["OceanBase is a powerful database system that supports vector search and AI functions.", "Vector search enables semantic search to find similar content."]';
   
   SELECT AI_RERANK("ob_rerank", @query, @candidate_docs) AS ranked_results;
   

   The result includes index (document index) and relevance_score (relevance score):

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

4. Generate the answer.

   Using the query from step 2 and the reranked results from step 3, generate an answer:

   SELECT AI_COMPLETE("ob_complete",
       CONCAT('Answer the user question based on the following document. User question: ', @query,
           ' Relevant document: ', CAST(JSON_EXTRACT(@candidate_docs, '$[1]') AS CHAR),
           ' Answer the question concisely and accurately based on the document above.')) AS answer;
   

   Example result:

   +--------------------------------------------------------------------------------------------------------------------------------------------+
   | answer                                                                                                                                     |
   +--------------------------------------------------------------------------------------------------------------------------------------------+
   | Based on the document, vector search is a technique for semantic search that finds similar content by comparing vector representations.    |
   +--------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

   With these three steps, you can run a full AI workflow inside OceanBase: embed, search, rerank, and generate answers.

Related topics

• AI model registration: Full syntax for registering models and endpoints.
• AI function syntax and examples: Reference for function syntax, parameters, and more examples.
• AI function service permissions: Granting and revoking permissions.