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 functions.
Note
This document uses the new interface REGISTER_PROVIDER for model registration, which is supported starting from V4.6.2.1. The old interfaces such as CREATE_AI_MODEL and other PL system subprograms are fully retained, and existing usage can continue to work.
Prerequisites
- You have deployed OceanBase AI Database, created a user tenant, and connected to the database.
- You have the necessary permissions for AI functions. For details, see AI Function Service Permissions.
- You have obtained the API Key of a third-party model service.
Quick start
Step 1: Register a provider configuration
Before you use AI functions for the first time, you need to register a provider configuration. This example uses the built-in provider aliyun. You only need to fill in the access_key. The protocol and base_url can be omitted as the system will automatically fill in the default values. 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 providers, 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 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 the text expresses a negative sentiment, output -1. Do not output anything else.', 'The weather is so nice')) AS sentiment;
The expected result is that the sentiment field is 1, indicating a positive sentiment.
Step 4: (optional) Use a gateway for routing
To enable weighted routing and disaster recovery between 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.
Register the required provider configurations (optional)
This example requires embedding, text generation, and reranking models. If you have registered the
aliyunprovider configuration in Step 1 and the required models are accessed through this provider, skip this step. Otherwise, register configurations for other providers:CALL DBMS_AI_SERVICE.REGISTER_PROVIDER('deepseek', '{ "access_key": "sk-xxxx" }');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 Database', 'OceanBase Database 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);Perform vector search and reranking
SET @query = "What is vector search?"; SET @query_vector = AI_EMBED('aliyun/text-embedding-v3', @query); -- Directly construct a document list as a string array 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
indexis the document index andrelevance_scoreis the relevance score:+-------------------------------------------------------------------------------------------------------------+ | ranked_results | +-------------------------------------------------------------------------------------------------------------+ | [{"index": 1, "relevance_score": 0.9904329776763916}, {"index": 0, "relevance_score": 0.16993996500968933}] | +-------------------------------------------------------------------------------------------------------------+ 1 row in setGenerate an answer
Based on the retrieval and reranking 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 provide a concise and accurate answer based on the above document content.', @query, CAST(JSON_EXTRACT(@candidate_docs, '$[1]') AS CHAR))) AS answer;The return 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 setBy following these steps, you can quickly complete the entire AI application process in OceanBase AI Database: vectorization, search, reranking, and answer generation.
References
- Register an AI model: Gateway and circuit breaker configuration, as well as provider configuration.
- Syntax and examples of AI functions: Syntax, parameter tables, and more examples for each function.
- AI Function Service Permissions: Permission granting and revocation.