This topic describes the batch file method of interacting with models supported by OceanBase AI Database. When creating a vector index, you can batch export the text to be embedded from tables to JSONL files, use the batch API of an AI service provider to asynchronously perform embedding calculations, and then write the vectors back into the index. This method is suitable for scenarios where indexes are created after a large amount of historical data has already been generated. Compared with making HTTP calls synchronously row by row, it significantly reduces network round-trip overhead and supports resumable uploads and higher throughput.
Note
This feature is available starting with V4.6.2.1.
Notice
Currently, this feature supports only the creation of semantic indexes.
Prerequisites
- You have the necessary permissions for AI models. For details, see AI Function Service Permissions.
- You have registered an embedding model and endpoint, and the AI service provider supports the batch API (currently OpenAI and Alibaba Cloud DashScope are supported). For registration examples, see the "Register a text embedding model" section in Semantic search. Semantic indexes must use the legacy APIs
CREATE_AI_MODEL/CREATE_AI_MODEL_ENDPOINT. - The semantic index feature is enabled for the tenant:
ALTER SYSTEM SET _enable_semantic_index = true;
Create an index using batch files
This section provides an example of creating an index using the batch file method, along with explanations for the parameters and principles.
Step 1: Prepare data
CREATE TABLE articles (
id INT PRIMARY KEY,
content VARCHAR(512)
);
INSERT INTO articles VALUES
(1, 'machine learning neural network'),
(2, 'deep learning transformer attention'),
(3, 'natural language processing embeddings');
Step 2: Create a semantic index
Specify ai_service_tier=BATCH in the WITH clause. The other parameters are the same as those for a semantic index.
-- Please replace `model` with the name of the registered embedding model.
CREATE VECTOR INDEX idx_articles ON articles(content)
WITH (distance=l2, type=hnsw, lib=vsag, model=ob_embed, dim=1024,
sync_mode=immediate, ai_service_tier=BATCH);
The following table describes parameters specific to the BatchFile method:
Parameters |
Default value |
Valid values |
Note |
|---|---|---|---|
ai_service_tier |
STANDARD |
STANDARD / BATCH |
The interaction mode of the AI model. STANDARD makes synchronous HTTP calls row by row; BATCH uses batch files and performs asynchronous processing only during index creation. |
allow_null_on_failure |
FALSE |
TRUE / FALSE |
The fault tolerance behavior when batch embedding fails. This parameter takes effect only when ai_service_tier=BATCH. If set to TRUE, the vectors of failed rows are set to NULL when the function returns and index construction continues; if set to FALSE, any row failure aborts the entire DDL task. For more information about the overall fault tolerance mechanism of batch files, see the "Retry and fault tolerance mechanisms" section below. |
You can also perform the following operations during execution:
- Monitor task progress through views. For details, see the "Monitor task progress" section below.
- Perform fault-tolerant downgrading. For details, see the "Retry and fault tolerance mechanisms" section below.
After the index is created, DML operations (such as INSERT, UPDATE, and DELETE) and background refreshes will use HTTP synchronous calls, resulting in low latency.
Step 3: Verify semantic search
After the index is ready, you can directly use semantic_distance for semantic search:
SELECT id FROM articles
ORDER BY semantic_distance(content, 'language model')
APPROX LIMIT 3;
Retry and fault tolerance mechanisms
In batch file mode, batch embedding results are not either all successful or all failed; they can be successful or failed row by row in the result file. Specifically, when a batch file contains a large number of data rows, the system first uses an automatic retry mechanism (for retryable situations such as HTTP network errors, rate limiting, and server-side errors) to handle temporary failures. If some rows still fail after retrying, you can control subsequent behavior by configuring the allow_null_on_failure index parameter: if set to TRUE, the system sets the vector result of failed rows to NULL, and the rest of the rows complete normally, continuing the overall index construction; if set to FALSE, any row failure causes the entire DDL task to fail.
In batch file mode, the timeout and retry behavior parameters for interacting with AI services are described as follows:
Parameters |
Type |
Default value |
Value range |
Note |
|---|---|---|---|---|
| model_request_timeout | Time | 60s |
[1s, ∞) |
The timeout period for a single HTTP request to the AI model. |
| model_max_retries | integer | 2 |
[0, ∞) |
The maximum number of retries after an AI interaction fails. A value of 0 indicates that the system always retries. |
Monitor task progress
Batch file index construction generates a trackable batch processing task. You can query the task status using the following system view:
View |
Note |
|---|---|
| DBA_OB_AI_BATCH_TASKS | Currently running batch file tasks |
| DBA_OB_AI_BATCH_TASK_HISTORY | The history of completed tasks, including additional information on token usage and wait time |
Query example:
SELECT TASK_ID, STATUS, PROGRESS_PERCENT, ERROR_DETAIL
FROM oceanbase.DBA_OB_AI_BATCH_TASKS
WHERE MODEL_NAME = 'ob_embed'
ORDER BY TASK_CREATE_TIME DESC;
Note
Completed or failed tasks are archived from DBA_OB_AI_BATCH_TASKS to DBA_OB_AI_BATCH_TASK_HISTORY after about 10 minutes. For long-term tracking, please query the historical view in a timely manner.
Considerations
Before formal use, please note the following:
- Supports only post-index creation:
ai_service_tier=BATCHdoes not support creating indexes on the fly with tables (inline definition of vector indexes inCREATE TABLE). - Depends on the Batch API: The model endpoint must be correctly configured, and the AI service provider's Batch API must be available. Batch tasks are processed asynchronously, and completion time depends on the service provider's load. Large volumes of data may take longer.
- Does not support sparse indexes:
ai_service_tierdoes not apply to sparse vector index types. - Remote file management: The JSONL files and result files uploaded to the AI service provider are managed by the provider. OceanBase AI Database only retains file ID references. Remote files are cleaned up within a fixed period after the task is completed.
References
- Semantic search: Creation of semantic indexes, search, and parameter descriptions such as
sync_mode. - Register an AI model: Registration of embedded models and endpoints.
- AI Function Service Permissions: Permission granting and revocation.