OceanBase AI Database supports vector search using the SQL language. This topic describes how to get started quickly.
Quick start
Create a vector column and index
When creating a table, you can use the VECTOR(dim) data type to declare a specified column as a vector column and specify its dimension. A vector index must be created on a vector column and requires at least two parameters: type and distance.
In the example, a vector column named embedding is created with a dimension of 3. An HNSW index is created on the embedding column, specifying the L2 distance algorithm.
CREATE TABLE t1(
id INT PRIMARY KEY,
doc VARCHAR(200),
embedding VECTOR(3),
VECTOR INDEX idx1(embedding) WITH (distance=L2, type=hnsw)
);
For large datasets, it is recommended to import the data first and then create the vector index.
Write vector data
To simulate a vector search scenario, you need to construct some vector data first. Each row of data includes a description and its corresponding vector. In the example, it is assumed that 'apple' corresponds to the vector '[1.2,0.7,1.1]', 'carrot' corresponds to the vector '[5.3,4.8,5.4]', and so on.
INSERT INTO t1
VALUES (1, 'apple', '[1.2,0.7,1.1]'),
(2, 'banana', '[0.6,1.2,0.8]'),
(3, 'orange','[1.1,1.1,0.9]'),
(4, 'carrot', '[5.3,4.8,5.4]'),
(5, 'spinach', '[4.9,5.3,4.8]'),
(6, 'tomato','[5.2,4.9,5.1]');
For ease of demonstration, this example uses only 3-dimensional vectors, which are manually generated. In real applications, you need to use an embedding model to generate vectors for real text, and the dimensions can be hundreds or even thousands.
You can query the table data to check if the write was successful.
SELECT * FROM t1;
The expected return result is as follows:
+----+-----------+---------------+
| id | doc | embedding |
+----+-----------+---------------+
| 1 | apple | [1.2,0.7,1.1] |
| 2 | banana | [0.6,1.2,0.8] |
| 3 | orange | [1.1,1.1,0.9] |
| 4 | carrot | [5.3,4.8,5.4] |
| 5 | spinach | [4.9,5.3,4.8] |
| 6 | tomato | [5.2,4.9,5.1] |
+----+-----------+---------------+
6 rows in set
Perform vector search
Vector search requires a vector as the search condition. Suppose we need to find all documents containing the word 'fruit', which corresponds to the vector [0.9, 1.0, 0.9]. The corresponding SQL query is:
SELECT id, doc FROM t1
ORDER BY l2_distance(embedding, '[0.9, 1.0, 0.9]')
APPROXIMATE LIMIT 3;
The expected return result is as follows:
+----+--------+
| id | doc |
+----+--------+
| 3 | orange |
| 2 | banana |
| 1 | apple |
+----+--------+
3 rows in set
References
- For more information about vector data, see Vector data types overview.
- For more information about vector indexes, see Overview of dense indexes.