Java SDK interface

obvec_jdbc is a Java SDK specifically designed for OceanBase vector storage use cases and scenarios involving JSON table virtual tables. This topic explains how to use obvec_jdbc.

Installation

You can install obvec_jdbc by using the following method.

Maven dependency

Add the obvec_jdbc dependency to the pom.xml file of your project.

<dependency>
  <groupId>com.oceanbase</groupId>
  <artifactId>obvec_jdbc</artifactId>
  <version>1.0.6</version>
</dependency>

Source code installation

1. Install obvec_jdbc.

   # Clone the obvec_jdbc repository.
   git clone https://github.com/oceanbase/obvec_jdbc.git
   # Go to the obvec_jdbc directory.
   cd obvec_jdbc
   # Install obvec_jdbc.
   mvn install
   

2. Add the dependency.

   <dependency>
     <groupId>com.oceanbase</groupId>
     <artifactId>obvec_jdbc</artifactId>
     <version>1.0.6</version>
   </dependency>
   

API definition and usage

obvec_jdbc provides the ObVecClient object for working with OceanBase's vector search features and JSON table virtual table functionalities.

Use vector search

Create a client

You can use the following interface definition to construct an ObVecClient object:

# uri: the connection string, which contains the address, port, and name of the database to which you want to connect.
# user: the username.
# password: the password.
public ObVecClient(String uri, String user, String password);

Here is an example:

import com.oceanbase.obvec_jdbc.ObVecClient;

String uri = "jdbc:oceanbase://127.0.0.1:2881/test";
String user = "root@test";
String password = "";
String tb_name = "JAVA_TEST";

ObVecClient ob = new ObVecClient(uri, user, password);

ObFieldSchema class

This class is used to define the column schema of a table. The constructor is as follows:

# name: the column name.
# dataType: the data type.
public ObFieldSchema(String name, DataType dataType);

The following table describes the data types supported by the class.

The interface is defined as follows:

IndexParams/IndexParam

IndexParam is used to set a single index parameter. IndexParams is used to set a group of vector index parameters, which is used when multiple vector indexes are created on a table.

The constructor of IndexParam is as follows:

# vidx_name: the index name.
# vector_field_name: the name of the vector column.
public IndexParam(String vidx_name, String vector_field_name);

The interface is defined as follows:

The constructor of IndexParams is as follows:

public IndexParams();

The interface is defined as follows:

ObCollectionSchema class

When creating a table, you need to rely on the configuration of the ObCollectionSchema object. Below are its constructors and interfaces.

The constructor of ObCollectionSchema is as follows:

public ObCollectionSchema();

The interface is defined as follows:

Drop a table

The constructor is as follows:

# table_name: the name of the target table.
public void dropCollection(String table_name);

Check whether a table exists

The constructor is as follows:

# table_name: the name of the target table.
public boolean hasCollection(String table_name);

Create a table

The constructor is as follows:

# table_name: the name of the table to be created.
# collection: an ObCollectionSchema object that specifies the schema of the table.
public void createCollection(String table_name, ObCollectionSchema collection);

You can use ObFieldSchema, OCollectionSchema, and IndexParams to create a table. Here is an example:

import com.oceanbase.obvec_jdbc.DataType;
import com.oceanbase.obvec_jdbc.ObCollectionSchema;
import com.oceanbase.obvec_jdbc.ObFieldSchema;
import com.oceanbase.obvec_jdbc.IndexParam;
import com.oceanbase.obvec_jdbc.IndexParams;

# Define the schema of the table.
ObCollectionSchema collectionSchema = new ObCollectionSchema();
ObFieldSchema c1_field = new ObFieldSchema("c1", DataType.INT32);
c1_field.IsPrimary(true).IsAutoInc(true);
ObFieldSchema c2_field = new ObFieldSchema("c2", DataType.FLOAT_VECTOR);
c2_field.Dim(3).IsNullable(false);
ObFieldSchema c3_field = new ObFieldSchema("c3", DataType.JSON);
c3_field.IsNullable(true);
collectionSchema.addField(c1_field);
collectionSchema.addField(c2_field);
collectionSchema.addField(c3_field);

# Define the index.
IndexParams index_params = new IndexParams();
IndexParam index_param = new IndexParam("vidx1", "c2");
index_params.addIndex(index_param);
collectionSchema.setIndexParams(index_params);

ob.createCollection(tb_name, collectionSchema);

Create a vector index after data is added

The constructor is as follows:

# table_name: the name of the table.
# index_param: an IndexParam object that specifies the vector index parameters of the table.
public void createIndex(String table_name, IndexParam index_param)

Insert data

The constructor is as follows:

# table_name: the name of the target table.
# column_names: an array of column names in the target table.
# rows: the data rows. ArrayList<Sqlizable[]>, each row is an Sqlizable array. Sqlizable is a wrapper class that converts Java data types to SQL data types.
public void insert(String table_name, String[] column_names, ArrayList<Sqlizable[]> rows);

The supported data types for rows include:

• SqlInteger: wraps integer data.
• SqlFloat: wraps floating-point data.
• SqlDouble: wraps double-precision data.
• SqlText: wraps string data.
• SqlVector: wraps vector data.

Here is an example:

import com.oceanbase.obvec_jdbc.SqlInteger;
import com.oceanbase.obvec_jdbc.SqlText;
import com.oceanbase.obvec_jdbc.SqlVector;
import com.oceanbase.obvec_jdbc.Sqlizable;

ArrayList<Sqlizable[]> insert_rows = new ArrayList<>();
Sqlizable[] ir1 = { new SqlVector(new float[] {1.0f, 2.0f, 3.0f}), new SqlText("{\"doc\": \"oceanbase doc 1\"}") };
insert_rows.add(ir1);
Sqlizable[] ir2 = { new SqlVector(new float[] {1.1f, 2.2f, 3.3f}), new SqlText("{\"doc\": \"oceanbase doc 2\"}") };
insert_rows.add(ir2);
Sqlizable[] ir3 = { new SqlVector(new float[] {0f, 0f, 0f}), new SqlText("{\"doc\": \"oceanbase doc 3\"}") };
insert_rows.add(ir3);
ob.insert(tb_name, new String[] {"c2", "c3"}, insert_rows);

Delete data

The constructor is as follows:

# table_name: the name of the target table.
# primary_key_name: the name of the primary key column.
# primary_keys: an array of primary key column values for the target rows.
public void delete(String table_name, String primary_key_name, ArrayList<Sqlizable> primary_keys);

Here is an example:

ArrayList<Sqlizable> ids = new ArrayList<>();
ids.add(new SqlInteger(2));
ids.add(new SqlInteger(1));
ob.delete(tb_name, "c1", ids);

ANN queries

The constructor is as follows:

# table_name: the name of the target table.
# vec_col_name: the name of the vector column.
# metric_type: the type of the vector distance function. l2: corresponds to the L2 distance function. cosine: corresponds to the cosine distance function. ip: corresponds to the negative inner product distance function.
# qv: the vector value to be queried.
# topk: the number of the most similar results to be returned.
# output_fields: the projected columns, that is, the array of the fields to be returned.
# output_datatypes: the data types of the projected columns, that is, the data types of the fields to be returned, for direct conversion to Java data types.
# where_expr: the WHERE condition expression.
public ArrayList<HashMap<String, Sqlizable>> query(
      String table_name,
      String vec_col_name,
      String metric_type,
      float[] qv,
      int topk,
      String[] output_fields,
      DataType[] output_datatypes,
      String where_expr);

Here is an example:

ArrayList<HashMap<String, Sqlizable>> res = ob.query(tb_name, "c2", "l2", 
            new float[] {0f, 0f, 0f}, 10,
            new String[] {"c1", "c3", "c2"},
            new DataType[] {
            DataType.INT32,
            DataType.JSON,
            DataType.FLOAT_VECTOR,
            "c1 > 0"});
if (res != null) {
    for (int i = 0; i < res.size(); i++) {
        for (HashMap.Entry<String, Sqlizable> entry : res.get(i).entrySet()) {
            System.out.printf("%s : %s, ", entry.getKey(), entry.getValue().toString());
        }
        System.out.print("\n");
    }
} else {
    System.out.println("res is null");
}

Use the JSON table feature

The JSON table feature of obvec_jdbc relies on OceanBase Database's ability to handle JSON data types (including JSON_VALUE, JSON_TABLE, and JSON_REPLACE). This feature implements a virtual table mechanism, allowing multiple users (distinguished by user ID) to perform DDL or DML operations on virtual tables over the same physical table while ensuring data isolation between users. Admin users can perform DDL operations, while regular users can perform DML operations.

This design combines the structured management capabilities of relational databases with the flexibility of JSON, showcasing OceanBase Database's multi-model capabilities. Users can enjoy the power and ease of use of SQL while also handling semi-structured data, meeting the diverse data model requirements of modern applications. Although operations are still performed on "tables," data is stored in a more flexible JSON format at the underlying level, better supporting complex and varied application scenarios.

How it works

Detailed explanation:

1. Users interact with the system using familiar standard SQL statements such as CREATE TABLE to define table structures, INSERT to add data, and SELECT to query data. They do not need to worry about how data is stored at the underlying level. The tables created by users using SQL statements are logical tables, which correspond to two physical tables (meta_json_t and data_json_t) within OceanBase Database.

2. JSON table SDK: Within the application, there is a JSON table SDK (Software Development Kit). This SDK serves as the bridge between the user's SQL operations and the actual storage in OceanBase Database. When an SQL statement is executed, the SDK intercepts the request and intelligently converts it into read and write operations on OceanBase Database's internal tables meta_json_t and data_json_t.

3. How data is stored in OceanBase Database:

   • meta_json_t (stores table schema): stores the metadata of the logical tables created by the user, which is the schema information of the table (such as the columns created and the data types of each column). When the CREATE TABLE statement is executed, the SDK records the schema information in meta_json_t.
   • data_json_t (stores row data as JSON): stores the actual inserted data. Unlike traditional relational databases that directly store row data, the JSON table feature encapsulates each row of inserted data into a JSON object and stores it in a column of the data_json_t table. This allows for efficient storage even with flexible data structures.

4. Data query: When a query operation such as SELECT is executed, the SDK reads the JSON data from data_json_t and combines it with the schema information from meta_json_t to re-parse and present the JSON data in a familiar tabular format to your application.

The meta_json_t table stores the metadata of the JSON table, which is the logical table schema defined by the user using the CREATE TABLE statement. It records the column information of each logical table, with the following schema:

When a user executes the CREATE TABLE statement, the JSON table SDK parses and inserts the column definition information into the meta_json_t table.

The data_json_t table stores the actual data of the JSON table, which is the data inserted by the user using the INSERT statement. It records the row data of each logical table, with the following schema:

Examples

1. Create a client

   The constructor is as follows:

   # uri: the connection string, which contains the address, port, and name of the database to which you want to connect.
   # user: the username.
   # password: the password.
   # user_id: the user ID.
   # log_level: the log level.
   public ObVecJsonClient(String uri, String user, String password, String user_id, Level log_level);
   

   Here is an example:

   import com.oceanbase.obvec_jdbc.ObVecJsonClient;
   
   String uri = "jdbc:oceanbase://127.0.0.1:2881/test";
   String user = "root@test";
   String password = "";
   ObVecJsonClient client = new ObVecJsonClient(uri, user, password, 0, Level.INFO);
   

2. Execute DDL statements

   You can directly call the parseJsonTableSQL2NormalSQL interface and pass in the specific SQL statements.

   • Create a table

     String sql = "CREATE TABLE `t2` (c1 INT NOT NULL DEFAULT 10, c2 VARCHAR(30) DEFAULT 'ca', c3 VARCHAR NOT NULL, c4 DECIMAL(10, 2), c5 TIMESTAMP DEFAULT CURRENT_TIMESTAMP);";
     client.parseJsonTableSQL2NormalSQL(sql);
     

   • ALTER TABLE CHANGE COLUMN

     sql = "ALTER TABLE t2 CHANGE COLUMN c2 changed_col INT";
     client.parseJsonTableSQL2NormalSQL(sql);
     

   • ALTER TABLE ADD COLUMN

     sql = "ALTER TABLE t2 ADD COLUMN email VARCHAR(100) default 'example@example.com'";
     client.parseJsonTableSQL2NormalSQL(sql);
     

   • ALTER TABLE MODIFY COLUMN

     sql = "ALTER TABLE t2 MODIFY COLUMN changed_col TIMESTAMP NOT NULL DEFAULT current_timestamp";
     client.parseJsonTableSQL2NormalSQL(sql);
     

   • ALTER TABLE DROP COLUMN

     sql = "ALTER TABLE t2 DROP c1";
     client.parseJsonTableSQL2NormalSQL(sql);
     

   • ALTER TABLE RENAME

     sql = "ALTER TABLE t2 RENAME TO alter_test";
     client.parseJsonTableSQL2NormalSQL(sql);