Query data with OBKV-Table|OceanBase Cloud

This topic describes OBKV-Table query capabilities. OBKV-Table supports point lookups (queries by primary key) and range scans. In range scans, you can specify filters, scan order, limit conditions, and more. OBKV-Table also supports simple aggregation. In aggregation queries, you can likewise specify filters, scan order, limit conditions, and more.

OBKV-Table does not support SQL-style ORDER BY, but you can use the natural ordering of stored data to read results in primary-key order or reverse order on the primary table or an index table. When you set ScanOrder, you do not need to specify sort columns; currently, ordering is limited to primary key columns only.

Point lookup

APIs

ObTableClient exposes the following APIs for point lookups:

API	Description
`Get get(String tableName)`	Query a single row by primary key

Examples

Prerequisites

Before you query data, complete the following steps:

Initialize your ObTableClient.

Create a relational table in your database. The examples below use this table:

CREATE TABLE IF NOT EXISTS `employees` (
`employeeId` int NOT NULL, 
`name` varchar(100) NOT NULL,
`department` varchar(50),
`position` varchar(50),
`workYear` int,
`comment` varchar(100),
PRIMARY KEY (`employeeId`)
) PARTITION BY KEY(`employeeId`) PARTITIONS 97;

Additional notes:

JSON is supported.

Single-row query

The example below queries one row with primary key employeeId equal to 1001 and returns the name column.

// Assume the following data already exists in the database
/*
+------------+----------+------------+----------+----------+---------+
| employeeId | name     | department | position | workYear | comment |
+------------+----------+------------+----------+----------+---------+
|       1001 | zhangsan | R&D        | dev      |       10 | NULL    |
+------------+----------+------------+----------+----------+---------+
*/
private static void getRow(ObTableClient obkvHandle) throws Exception {
    Row rowKey = row().add(colVal("employeeId", 1001));
    Map<String, Object> res = obkvHandle.get("employees")
            .setRowKey(rowKey)
            .select("name")
            .execute();
    System.out.println(res.get("name"));
}

Batch query

The example below queries two rows with primary keys employeeId equal to 1001 and 1002, and returns the name column for each.

// Assume the following data already exists in the database
/*
+------------+----------+------------+----------+----------+---------+
| employeeId | name     | department | position | workYear | comment |
+------------+----------+------------+----------+----------+---------+
|       1001 | zhangsan | R&D        | dev      |       10 | NULL    |
|       1002 | lisi     | R&D        | dev      |       10 | NULL    |
+------------+----------+------------+----------+----------+---------+
*/
private static void batchGetRow(ObTableClient obkvHandle) throws Exception {
    Row rowKey1 = row().add(colVal("employeeId", 1001));
    Row rowKey2 = row().add(colVal("employeeId", 1002));
    Get getOp1 = get().setRowKey(rowKey1).select("name");
    Get getOp2 = get().setRowKey(rowKey2).select("name");
    BatchOperationResult res = obkvHandle.batchOperation("employees")
            .addOperation(getOp1,getOp2)
            .execute();
    System.out.println(res.size());
    System.out.println(res.get(0).getOperationRow().get("name"));
    System.out.println(res.get(1).getOperationRow().get("name"));
}

Range scan

APIs

ObTableClient exposes the following APIs for range scans:

API	Description
`TableQuery query(String tableName)`	Query data over a range

Common `TableQuery` methods

Method	Description	Optional
`TableQuery select(String... columns)`	Columns to return to the client. If omitted, all columns are returned. We recommend specifying columns. Returning unnecessary columns adds overhead.	Yes
`TableQuery indexName(String indexName)`	Index to use for this query.	Yes
`TableQuery addScanRange(Object[] starts, boolean startEquals, Object[] ends, boolean endEquals)`	Primary key range for the query. start/end: start and end primary key values startEquals/endEquals: whether the range includes the start/end values. Prefer filtering with a primary key scan range for best performance.	Yes
`TableQuery setFilter(ObTableFilter filter)`	Filter for the query. The filter can be a single filter or a `FilterList`.	Yes
`TableQuery scanOrder(boolean forward)`	Scan direction. forward: default `true` for forward scan; `false` for reverse scan.	Yes
`TableQuery limit(int offset, int limit)`	Range of rows in the result set returned to the client. offset: starting position. Results begin at the offset-th row; default is the first row. limit: number of rows returned to the client.	Yes
`QueryResultSet execute()`	Submit the operation to the database.	No

Filter

OBKV-Table supports the following filters:

ObTableValueFilter
ObTableFilterList
ObTableInFilter

ObTableValueFilter

TableValueFilter is the most commonly used filter. It filters rows based on the relationship between a column value and a value you specify. Supported comparison operators are defined in the ObCompareOp enum.

enum ObCompareOp {
    LT,     // less than
    GT,     // greater than
    LE,     // less than or equal to
    GE,     // greater than or equal to
    NE,     // not equal to
    EQ,     // equal to
    IS,     // is null
    IS_NOT  // is not null
}

ObTableFilterList

ObTableFilterList combines multiple filters with AND or OR into a composite filter. The ObTableFilterList.operator enum is defined as follows:

enum ObTableFilterListOperator {
    AND, // logical AND
    OR   // logical OR
}

ObTableInFilter

ObTableInFilter is syntactic sugar provided by OBKV-Table with semantics similar to SQL IN:

ObTableInFilter is essentially an ObTableFilterList.
Each filter in the list is an ObTableValueFilter with ObCompareOp::EQ.

Index-organized table examples

Prerequisites

Before you query data, complete the following steps:

Initialize your ObTableClient.

Create an index-organized table in your database. Example:

CREATE TABLE IF NOT EXISTS `employees` (
`employeeId` int NOT NULL, 
`name` varchar(100) NOT NULL,
`department` varchar(50),
`position` varchar(50),
`workYear` int,
`comment` varchar(100),
PRIMARY KEY (`employeeId`)
) PARTITION BY KEY(`employeeId`) PARTITIONS 97;

Filter with a scan range

The example below performs a range scan. Range columns are primary keys employeeId and departmentId, the scan range is [{1000, 1},{1010, 1}], and the query returns the name column.

private static void queryWithScanRange(ObTableClient obkvHandle) throws Exception {
    TableQuery query = obkvHandle.query("employees");
    QueryResultSet res = query.setScanRangeColumns("employeeId","departmentId")
    .addScanRange(new Object[] { 1000, 1 }, new Object[] { 1010, 1 })
    .select("name")
    .execute();
    while (res.next()) {
        System.out.println(res.getRow().get("name"));
    }
}

Filter with a filter

The example below performs a range scan. Range columns are primary keys employeeId and departmentId, the scan range is [{1000, 1},{1010, 1}], and the query returns the name column. It also applies an ObTableValueFilter so only rows with workYear greater than or equal to 3 are returned.

private static void queryWithFilter(ObTableClient obkvHandle) throws Exception {
    TableQuery query = obkvHandle.query("employees");
    ObTableValueFilter filter = compareVal(ObCompareOp.GE, "workYear", 3);
    QueryResultSet res = query.setScanRangeColumns("employeeId","departmentId")
            .addScanRange(new Object[] { 1000, 1 }, new Object[] { 1010, 1 })
            .setFilter(filter)
            .select("name")
            .execute();
    while (res.next()) {
        System.out.println(res.getRow().get("name"));
    }
}

Query with an index

The example below performs a range scan. The range column is index key departmentId, the scan range is [1, 10], and the query returns the name column. It uses index idx_departmentId.

Notice

In OBKV-Table, a secondary index must include the partition key so you can determine the partition key range when specifying a secondary index scan range.

private static void queryWithIndex(ObTableClient obkvHandle) throws Exception {
    TableQuery query = obkvHandle.query("employees");
    QueryResultSet res = query.setScanRangeColumns("departmentId")
            .addScanRange(new Object[] { 1 }, new Object[] { 10 })
            .indexName("idx_departmentId")
            .select("name")
            .execute();
    while (res.next()) {
        System.out.println(res.getRow().get("name"));
    }
}

Heap-organized table examples

Prerequisites

Before you query data, complete the following steps:

Initialize your ObTableClient.

Create a heap-organized table in your database. Example:

CREATE TABLE test (
    c1 bigint, 
    c2 varchar(20), 
    c3 bigint,
    UNIQUE INDEX idx_unique_c1 (c1) -- Creating a unique index can speed up queries
  ) ORGANIZATION = HEAP PARTITION BY KEY(c1) PARTITIONS 97; -- Use the ORGANIZATION parameter to specify a heap table

Query with an index

The example below performs a range scan. The range column is primary key c1, the scan range is [1000, 1003], and the query returns columns c1, c2, and c3. It uses index idx_unique_c1.

private static void queryWithIndex(ObTableClient obkvHandle) throws Exception {
    try {
        // Use BatchOperation for batch insert
        BatchOperation batch = client.batchOperation(tableName);
        // Add multiple insert operations
        // You must set the partition key c1 via setPartitionKey
        // You must also set c1 via addMutateColVal
        for (int i = 1; i <= 3; i++) {
            Insert insert = new Insert();
            insert.setPartitionKey(row(colVal("c1", (long)(1000 + i))))
                .addMutateColVal(colVal("c1", (long)(1000 + i)), colVal("c2", "batch_test"), colVal("c3", (long)(3000 + i)));
            batch.addOperation(insert);
        }
        batch.execute();
        System.out.println("Batch insert successful: 3 operations executed");

        // Specify index scan range [1000, 1003]
        // Specify index idx_unique_c1
        TableQuery query = client.query(tableName)
            .addScanRange(1000L, 1003L)
            .setScanRangeColumns("c1")
            .indexName("idx_unique_c1")
            .select("c1", "c2", "c3");
        QueryResultSet res = query.execute();
        int count = 0;
        while (res.next()) {
            count++;
            Map<String, Object> valueMap = res.getRow();
            long c1 = (long)valueMap.get("c1");
            String c2 = (String)valueMap.get("c2");
            long c3 = (long)valueMap.get("c3");
            assertEquals("c1 should match", c1 >= 1000 && c1 <= 1003, true);
            assertEquals("c2 should match", c2.equals("batch_test"), true);
            assertEquals("c3 should match", c3 >= 3000 && c3 <= 3003, true);
        }
        assertEquals("Should have 3 records", 3, count);
    } finally {
        truncateTable();
    }
}

Point lookup

APIs

ObTableClient exposes the following APIs for point lookups:

API	Description
`Get get(String tableName)`	Query a single row by primary key

Examples

Prerequisites

Before you query data, complete the following steps:

Initialize your ObTableClient.

Create a relational table in your database. The examples below use this table:

CREATE TABLE IF NOT EXISTS `employees` (
`employeeId` int NOT NULL, 
`name` varchar(100) NOT NULL,
`department` varchar(50),
`position` varchar(50),
`workYear` int,
`comment` varchar(100),
PRIMARY KEY (`employeeId`)
) PARTITION BY KEY(`employeeId`) PARTITIONS 97;

Additional notes:

JSON is supported.

Single-row query

The example below queries one row with primary key employeeId equal to 1001 and returns the name column.

// Assume the following data already exists in the database
/*
+------------+----------+------------+----------+----------+---------+
| employeeId | name     | department | position | workYear | comment |
+------------+----------+------------+----------+----------+---------+
|       1001 | zhangsan | R&D        | dev      |       10 | NULL    |
+------------+----------+------------+----------+----------+---------+
*/
private static void getRow(ObTableClient obkvHandle) throws Exception {
    Row rowKey = row().add(colVal("employeeId", 1001));
    Map<String, Object> res = obkvHandle.get("employees")
            .setRowKey(rowKey)
            .select("name")
            .execute();
    System.out.println(res.get("name"));
}

Batch query

The example below queries two rows with primary keys employeeId equal to 1001 and 1002, and returns the name column for each.

// Assume the following data already exists in the database
/*
+------------+----------+------------+----------+----------+---------+
| employeeId | name     | department | position | workYear | comment |
+------------+----------+------------+----------+----------+---------+
|       1001 | zhangsan | R&D        | dev      |       10 | NULL    |
|       1002 | lisi     | R&D        | dev      |       10 | NULL    |
+------------+----------+------------+----------+----------+---------+
*/
private static void batchGetRow(ObTableClient obkvHandle) throws Exception {
    Row rowKey1 = row().add(colVal("employeeId", 1001));
    Row rowKey2 = row().add(colVal("employeeId", 1002));
    Get getOp1 = get().setRowKey(rowKey1).select("name");
    Get getOp2 = get().setRowKey(rowKey2).select("name");
    BatchOperationResult res = obkvHandle.batchOperation("employees")
            .addOperation(getOp1,getOp2)
            .execute();
    System.out.println(res.size());
    System.out.println(res.get(0).getOperationRow().get("name"));
    System.out.println(res.get(1).getOperationRow().get("name"));
}

Range scan

APIs

ObTableClient exposes the following APIs for range scans:

API	Description
`TableQuery query(String tableName)`	Query data over a range

Common `TableQuery` methods

Method	Description	Optional
`TableQuery select(String... columns)`	Columns to return to the client. If omitted, all columns are returned. We recommend specifying columns. Returning unnecessary columns adds overhead.	Yes
`TableQuery indexName(String indexName)`	Index to use for this query.	Yes
`TableQuery addScanRange(Object[] starts, boolean startEquals, Object[] ends, boolean endEquals)`	Primary key range for the query. start/end: start and end primary key values startEquals/endEquals: whether the range includes the start/end values. Prefer filtering with a primary key scan range for best performance.	Yes
`TableQuery setFilter(ObTableFilter filter)`	Filter for the query. The filter can be a single filter or a `FilterList`.	Yes
`TableQuery scanOrder(boolean forward)`	Scan direction. forward: default `true` for forward scan; `false` for reverse scan.	Yes
`TableQuery limit(int offset, int limit)`	Range of rows in the result set returned to the client. offset: starting position. Results begin at the offset-th row; default is the first row. limit: number of rows returned to the client.	Yes
`QueryResultSet execute()`	Submit the operation to the database.	No

Filter

OBKV-Table supports the following filters:

ObTableValueFilter
ObTableFilterList
ObTableInFilter

ObTableValueFilter

enum ObCompareOp {
    LT,     // less than
    GT,     // greater than
    LE,     // less than or equal to
    GE,     // greater than or equal to
    NE,     // not equal to
    EQ,     // equal to
    IS,     // is null
    IS_NOT  // is not null
}

ObTableFilterList

ObTableFilterList combines multiple filters with AND or OR into a composite filter. The ObTableFilterList.operator enum is defined as follows:

enum ObTableFilterListOperator {
    AND, // logical AND
    OR   // logical OR
}

ObTableInFilter

ObTableInFilter is syntactic sugar provided by OBKV-Table with semantics similar to SQL IN:

ObTableInFilter is essentially an ObTableFilterList.
Each filter in the list is an ObTableValueFilter with ObCompareOp::EQ.

Index-organized table examples

Prerequisites

Before you query data, complete the following steps:

Initialize your ObTableClient.

Create an index-organized table in your database. Example:

CREATE TABLE IF NOT EXISTS `employees` (
`employeeId` int NOT NULL, 
`name` varchar(100) NOT NULL,
`department` varchar(50),
`position` varchar(50),
`workYear` int,
`comment` varchar(100),
PRIMARY KEY (`employeeId`)
) PARTITION BY KEY(`employeeId`) PARTITIONS 97;

Filter with a scan range

The example below performs a range scan. Range columns are primary keys employeeId and departmentId, the scan range is [{1000, 1},{1010, 1}], and the query returns the name column.

private static void queryWithScanRange(ObTableClient obkvHandle) throws Exception {
    TableQuery query = obkvHandle.query("employees");
    QueryResultSet res = query.setScanRangeColumns("employeeId","departmentId")
    .addScanRange(new Object[] { 1000, 1 }, new Object[] { 1010, 1 })
    .select("name")
    .execute();
    while (res.next()) {
        System.out.println(res.getRow().get("name"));
    }
}

Filter with a filter

private static void queryWithFilter(ObTableClient obkvHandle) throws Exception {
    TableQuery query = obkvHandle.query("employees");
    ObTableValueFilter filter = compareVal(ObCompareOp.GE, "workYear", 3);
    QueryResultSet res = query.setScanRangeColumns("employeeId","departmentId")
            .addScanRange(new Object[] { 1000, 1 }, new Object[] { 1010, 1 })
            .setFilter(filter)
            .select("name")
            .execute();
    while (res.next()) {
        System.out.println(res.getRow().get("name"));
    }
}

Query with an index

The example below performs a range scan. The range column is index key departmentId, the scan range is [1, 10], and the query returns the name column. It uses index idx_departmentId.

Notice

In OBKV-Table, a secondary index must include the partition key so you can determine the partition key range when specifying a secondary index scan range.

private static void queryWithIndex(ObTableClient obkvHandle) throws Exception {
    TableQuery query = obkvHandle.query("employees");
    QueryResultSet res = query.setScanRangeColumns("departmentId")
            .addScanRange(new Object[] { 1 }, new Object[] { 10 })
            .indexName("idx_departmentId")
            .select("name")
            .execute();
    while (res.next()) {
        System.out.println(res.getRow().get("name"));
    }
}

Heap-organized table examples

Prerequisites

Before you query data, complete the following steps:

Initialize your ObTableClient.

Create a heap-organized table in your database. Example:

CREATE TABLE test (
    c1 bigint, 
    c2 varchar(20), 
    c3 bigint,
    UNIQUE INDEX idx_unique_c1 (c1) -- Creating a unique index can speed up queries
  ) ORGANIZATION = HEAP PARTITION BY KEY(c1) PARTITIONS 97; -- Use the ORGANIZATION parameter to specify a heap table

Query with an index

The example below performs a range scan. The range column is primary key c1, the scan range is [1000, 1003], and the query returns columns c1, c2, and c3. It uses index idx_unique_c1.

private static void queryWithIndex(ObTableClient obkvHandle) throws Exception {
    try {
        // Use BatchOperation for batch insert
        BatchOperation batch = client.batchOperation(tableName);
        // Add multiple insert operations
        // You must set the partition key c1 via setPartitionKey
        // You must also set c1 via addMutateColVal
        for (int i = 1; i <= 3; i++) {
            Insert insert = new Insert();
            insert.setPartitionKey(row(colVal("c1", (long)(1000 + i))))
                .addMutateColVal(colVal("c1", (long)(1000 + i)), colVal("c2", "batch_test"), colVal("c3", (long)(3000 + i)));
            batch.addOperation(insert);
        }
        batch.execute();
        System.out.println("Batch insert successful: 3 operations executed");

        // Specify index scan range [1000, 1003]
        // Specify index idx_unique_c1
        TableQuery query = client.query(tableName)
            .addScanRange(1000L, 1003L)
            .setScanRangeColumns("c1")
            .indexName("idx_unique_c1")
            .select("c1", "c2", "c3");
        QueryResultSet res = query.execute();
        int count = 0;
        while (res.next()) {
            count++;
            Map<String, Object> valueMap = res.getRow();
            long c1 = (long)valueMap.get("c1");
            String c2 = (String)valueMap.get("c2");
            long c3 = (long)valueMap.get("c3");
            assertEquals("c1 should match", c1 >= 1000 && c1 <= 1003, true);
            assertEquals("c2 should match", c2.equals("batch_test"), true);
            assertEquals("c3 should match", c3 >= 3000 && c3 <= 3003, true);
        }
        assertEquals("Should have 3 records", 3, count);
    } finally {
        truncateTable();
    }
}

OceanBase

Customer Stories

Documentation

Query data with OBKV-Table

Point lookup

APIs

Examples

Prerequisites

Single-row query

Batch query

Range scan

APIs

Common TableQuery methods

Filter

ObTableValueFilter

ObTableFilterList

ObTableInFilter

Index-organized table examples

Prerequisites

Filter with a scan range

Filter with a filter

Query with an index

Notice

Heap-organized table examples

Prerequisites

Query with an index

Query data with OBKV-Table

Point lookup

APIs

Examples

Prerequisites

Single-row query

Batch query

Range scan

APIs

Common TableQuery methods

Filter

ObTableValueFilter

ObTableFilterList

ObTableInFilter

Index-organized table examples

Prerequisites

Filter with a scan range

Filter with a filter

Query with an index

Notice

Heap-organized table examples

Prerequisites

Query with an index

Common `TableQuery` methods

Common `TableQuery` methods