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 a sample to help you quickly get started.

Prerequisites

• An OceanBase cluster has been deployed and a MySQL tenant has been created, and you have connected to the database.
• You have the required permissions for AI functions. For more information, see AI function service permissions.

Quick start

Step 1: Register the model and endpoint

Before you use AI functions for the first time, you need to register the AI model and its endpoint. You can refer to the template provided in Register an AI model and copy the sample code for the corresponding AI model provider. You only need to replace the API Key with your actual API Key. Here is an example of registering the AI_COMPLETE sentiment analysis model (Alibaba Cloud, compatible with OpenAI format):

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 the example

Here is an example of using the AI_COMPLETE sentiment analysis model to determine the sentiment of a piece of text:

SELECT AI_COMPLETE("ob_complete", AI_PROMPT('Your task is to perform sentiment analysis on the provided text and determine whether it is positive or negative.
    The text to be analyzed is as follows:
    <text>
    {0}
    </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.', 'The weather is really nice!')) AS sentiment;

The expected result is that the sentiment field is 1, indicating a positive sentiment.

Step 3: Comprehensive application (optional)

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

1. Register all required models and endpoints (optional)

   This example requires the use of an embedding model, a text generation model, and a reranking model. Make sure you have registered the corresponding models and endpoints. For more information, see Step 1.

   Note

   If you have already registered the corresponding models in the previous steps, you can skip the registration steps and proceed to the next step.

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
       ('OceanBase Overview', '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 can be directly called in SQL.');
   
   UPDATE knowledge_base
   SET embedding = AI_EMBED("ob_embed", content);
   

3. Perform vector search and reranking

   SET @query = "What is vector search?";
   SET @query_vector = AI_EMBED("ob_embed", @query);
   
   -- Directly build a string array of documents
   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("ob_rerank", @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

   Based on the search results from Step 1 and the reranking results from Step 2, generate an answer:

   SELECT AI_COMPLETE("ob_complete",
       AI_PROMPT('Based on the following document content, answer the user's question.
       User question: {0}
   
       Relevant documents: {1}
   
       Please provide a concise and accurate answer based on the above document content.', @query, CAST(JSON_EXTRACT(@candidate_docs, '$[1]') AS CHAR))) AS answer;
   

   The returned result is as follows:

   +--------------------------------------------------------------------------------------------------------------------------------------------+
   | answer                                                                                                                                     |
   +--------------------------------------------------------------------------------------------------------------------------------------------+
   | Based on the provided document content, vector search is a technique used for semantic search, aiming to find similar content by comparing vector data. |
   +--------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

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

References

• Register an AI model: Complete commands for registering a model with an endpoint.
• Syntax and examples of AI functions: Reference documentation with function syntax, parameter tables, and more examples.
• Grant and revoke AI function service permissions: Grant and revoke permissions.