Experience client/server mode seekdb with SQL

seekdb can be used both with SDK and SQL. This topic describes how to use client/server mode seekdb with SQL.

info

For more information about how to use seekdb with SDK, see Experience embedded seekdb with Python SDK.

In this example, we perform the following operations:

Deploy client/server mode seekdb.
Connect to seekdb.
Create a database.
Create a table and insert data.
Perform hybrid search.
Optimize the search.
Clean up the environment.

Deployment modes

seekdb provides flexible deployment modes, from rapid prototyping to handling massive users, fully meeting your application needs.

Embedded mode

seekdb is embedded as a lightweight library in your application and supports one-click installation via pip. Suitable for personal learning, rapid prototyping, and efficient operation on various edge devices.
Client/server mode

Recommended for deployment in testing and production environments, it is lightweight and easy to use, ideal for providing stable and efficient services.

info

For more detailed and comprehensive deployment methods of seekdb, see Overview of deployment.

Step 1: Deploy seekdb in client/server mode

Prerequisites

Before deployment, ensure that your environment meets the following requirements:

Your environment is an RPM-based system. The following systems have been verified to be supported.
- Anolis OS 8.X (Linux kernel 3.10.0 or later)
- Alibaba Cloud Linux 2/3 (Linux kernel 3.10.0 or later)
- Red Hat Enterprise Linux Server 7.X, 8.X (Linux kernel 3.10.0 or later)
- CentOS Linux 7.X, 8.X (Linux kernel 3.10.0 or later)
- Debian 9.X or later (Linux kernel 3.10.0 or later)
- Ubuntu 20.X or later (Linux kernel 3.10.0 or later)
- SUSE/OpenSUSE 15.X or later (Linux kernel 3.10.0 or later)
- openEuler 22.03 and 24.03 (Linux kernel 5.10.0 or later)
- KylinOS V10
- UnionTech UOS 1020a/1021a/1021e/1001c
- NFSChina 4.0 or later
- Inspur KOS 5.8
The minimum CPU requirement for the current environment is 1 core.
The minimum available memory requirement for the current environment is 2 GB.
You have installed MySQL client in your environment.
The user you are using has permission to execute sudo commands.
Requirements for deploying using yum install:
- You have installed the jq command-line tool in your environment and correctly configured systemd as the system and service manager.
Requirements for deploying using Docker:
- You have installed Docker and started the Docker service.

Deploy seekdb using yum install

Add the seekdb image source.

sudo yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo

Install seekdb.
```
sudo yum install seekdb obclient
```
Start seekdb.
```
sudo systemctl start seekdb
```
Check the startup status of seekdb.
```
sudo systemctl status seekdb
```
If the status is Service is ready, seekdb has started normally.

Connect to seekdb.

mysql -h127.0.0.1 -uroot -P2881 -A oceanbase

Deploy seekdb in a container environment

If Docker is installed and the Docker service is running in your environment, you can also deploy seekdb in a Docker container. For more information about Docker deployment, see Deploy seekdb in a container environment.

Start a seekdb instance directly.

sudo docker run -d -p 2881:2881 oceanbase/seekdb

info

If you fail to pull the Docker image, you can also pull it from the quay.io or ghcr.io repositories. Simply replace oceanbase/seekdb in the pull command with quay.io/oceanbase/seekdb or ghcr.io/oceanbase/seekdb, for example, execute sudo docker run -d -p 2881:2881 quay.io/oceanbase/seekdb to pull the image from quay.io.

Step 2: Connect to seekdb

Use the MySQL client to connect to seekdb. The specific connection command is as follows:

mysql -h127.0.0.1 -uroot -P2881 -p****** -A

Step 3: Create a database

Create a database named my_test. You can also use the default test database of seekdb.

Create a database.
```
create database my_test;
```
Switch to the database my_test.
```
use my_test;
```

Step 4: Create a table and insert data

Create a document table containing scalar columns, vector columns, and full-text index columns to demonstrate full-text search with scalar filtering conditions and hybrid search.

CREATE TABLE doc_table(
    c1 INT,
    vector VECTOR(3),
    query VARCHAR(255),
    content VARCHAR(255),
    VECTOR INDEX idx1(vector) WITH (distance=l2, type=hnsw, lib=vsag),
    FULLTEXT INDEX idx2(query),
    FULLTEXT INDEX idx3(content)
)
ORGANIZATION HEAP;

Insert data.

INSERT INTO doc_table VALUES
(1, '[1,2,3]', "hello world", "seekdb Elasticsearch database"),
(2, '[1,2,1]', "hello world, what is your name", "seekdb database"),
(3, '[1,1,1]', "hello world, how are you", "seekdb mysql database"),
(4, '[1,3,1]', "real world, where are you from", "postgres mysql database"),
(5, '[1,3,2]', "real world, how old are you", "redis mysql database"),
(6, '[2,1,1]', "hello world, where are you from", "starrocks seekdb database");

Step 5: Perform hybrid search

Set the search parameters.

SET @parm = '{
    "query": {
        "query_string": {
            "fields": ["query", "content"],
            "query": "hello oceanbase"
        }
    },
    "knn" : {
        "field": "vector",
        "k": 5,
        "query_vector": [1,2,3]
    }
}';

Execute the query and return the query results.

SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm)); 

The returned results are as follows:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [
  {
    "c1": 1,
    "query": "hello world",
    "_score": 1.3716216216216217,
    "vector": "[1,2,3]",
    "content": "seekdb Elasticsearch database"
  },
  {
    "c1": 2,
    "query": "hello world, what is your name",
    "_score": 0.6646586312048193,
    "vector": "[1,2,1]",
    "content": "seekdb database"
  },
  {
    "c1": 3,
    "query": "hello world, how are you",
    "_score": 0.6593354613375797,
    "vector": "[1,1,1]",
    "content": "seekdb mysql database"
  },
  {
    "c1": 5,
    "query": "real world, how old are you",
    "_score": 0.41421356,
    "vector": "[1,3,2]",
    "content": "redis mysql database"
  },
  {
    "c1": 6,
    "query": "hello world, where are you from",
    "_score": 0.3503184713375797,
    "vector": "[2,1,1]",
    "content": "starrocks seekdb database"
  },
  {
    "c1": 4,
    "query": "real world, where are you from",
    "_score": 0.30901699,
    "vector": "[1,3,1]",
    "content": "postgres mysql database"
  }
] |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Step 6: Optimize the search

In hybrid search, you can use the boost parameter to adjust the weight ratio between full-text search and vector search to optimize the search results. For example, you can increase the weight of full-text search.

Set the search parameters.

SET @parm = '{
    "query": {
        "query_string": {
            "fields": ["query", "content"],
            "query": "hello oceanbase",
            "boost": 2.0
        }
    },
    "knn" : {
        "field": "vector",
        "k": 5,
        "query_vector": [1,2,3],
        "boost": 1.0
    }
}';

Execute the query and return the query results.

SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm));

The returned results are as follows:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [
  {
    "c1": 1,
    "query": "hello world",
    "_score": 1.7432432432432434,
    "vector": "[1,2,3]",
    "content": "seekdb Elasticsearch database"
  },
  {
    "c1": 3,
    "query": "hello world, how are you",
    "_score": 1.0096539326751595,
    "vector": "[1,1,1]",
    "content": "seekdb mysql database"
  },
  {
    "c1": 2,
    "query": "hello world, what is your name",
    "_score": 0.9959839324096386,
    "vector": "[1,2,1]",
    "content": "seekdb database"
  },
  {
    "c1": 6,
    "query": "hello world, where are you from",
    "_score": 0.7006369426751594,
    "vector": "[2,1,1]",
    "content": "starrocks seekdb database"
  },
  {
    "c1": 5,
    "query": "real world, how old are you",
    "_score": 0.41421356,
    "vector": "[1,3,2]",
    "content": "redis mysql database"
  },
  {
    "c1": 4,
    "query": "real world, where are you from",
    "_score": 0.30901699,
    "vector": "[1,3,1]",
    "content": "postgres mysql database"
  }
] |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

By adjusting the boost parameter, you can control the weights of keyword search and semantic search in the final sorting. For example, if you prioritize keyword matching, you can increase the boost value of query_string; if you prioritize semantic similarity, you can increase the boost value of knn.

Step 7: Clean up the environment

If you no longer need the sample database and tables, you can delete them by performing the following steps.

Delete the sample table.
```
DROP TABLE doc_table;
```
Delete the custom database.
```
DROP DATABASE my_test;
```

More operations

You can further explore the AI Native features of seekdb and try building AI applications based on seekdb:

Deployment modes​

Step 1: Deploy seekdb in client/server mode​

Prerequisites​

Deploy seekdb using yum install​

Deploy seekdb in a container environment​

Step 2: Connect to seekdb​

Step 3: Create a database​

Step 4: Create a table and insert data​

Step 5: Perform hybrid search​

Step 6: Optimize the search​

Step 7: Clean up the environment​

More operations​

Contents

Deployment modes

Step 1: Deploy seekdb in client/server mode

Prerequisites

Deploy seekdb using yum install

Deploy seekdb in a container environment

Step 2: Connect to seekdb

Step 3: Create a database

Step 4: Create a table and insert data

Step 5: Perform hybrid search

Step 6: Optimize the search

Step 7: Clean up the environment

More operations