This topic describes the query capabilities of OBKV-Table. OBKV-Table supports single-point queries (based on the primary key) and range queries. In a range query, you can specify filter conditions, scan order, and limit conditions. Additionally, OBKV-Table supports simple aggregation. In an aggregation query, you can also specify filter conditions, scan order, and limit conditions.
OBKV-Table does not support an SQL-like ORDER BY function. However, you can use its ordered storage feature to retrieve data in ascending or descending order based on the primary key of the primary table or index table. Therefore, when you specify ScanOrder, you do not need to provide the columns for sorting. Currently, sorting is limited to primary key columns.
Single-point query
API
The following APIs of ObTableClient support single-point queries:
API |
Description |
|---|---|
Get get(String tableName) |
Queries a row of data based on the primary key |
Examples
Prerequisites
Before you query data, make sure that the following prerequisites are met:
- Your ObTableClient has been initialized.
- You have created a relational table in your database. The following examples 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;
Other notes:
- JSON data types are supported. For details and write examples, see Data types supported by OBKV-Table.
Single-row query
The sample code queries one row of data by specifying the primary key employeeId as 1001 and selects 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 sample code queries two rows of data by specifying the primary keys employeeId as 1001 and 1002, and selects 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 |
| 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 query
API
The following APIs of ObTableClient support range queries:
API |
Description |
|---|---|
TableQuery query(String tableName) |
Queries data based on ranges |
Common methods of TableQuery
Method |
Description |
Optional |
|---|---|---|
TableQuery select(String... columns) |
Specifies the columns to return to the client. If this parameter is omitted, all columns are returned.
|
Yes |
TableQuery indexName(String indexName) |
The index to be used for this query. | Yes |
TableQuery addScanRange(Object[] starts, boolean startEquals, Object[] ends, boolean endEquals) |
Sets the primary key range for the query.
|
Yes |
TableQuery setFilter(ObTableFilter filter) |
Sets the query filter.
|
Yes |
TableQuery scanOrder(boolean forward) |
Specifies the scanning order.
|
Yes |
TableQuery limit(int offset, int limit) |
Specifies the range of records to return to the client from the query result set.
|
Yes |
QueryResultSet execute() |
Submits the operation to the database. | No |
Filter
OBKV-Table supports the following filters:
- ObTableValueFilter
- ObTableFilterList
- ObTableInFilter
ObTableValueFilter
ObTableValueFilter is the most commonly used filter. It filters data based on the relationship between column values in a data table and user-specified values. The supported relational operations 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, // Empty
IS_NOT // Not empty
}
ObTableFilterList
ObTableFilterList concatenates multiple filters using AND/OR to form a composite filter. The following table lists the operators of ObTableFilterList.
enum ObTableFilterListOperator {
AND, // Logical AND
OR // Logical OR
}
ObTableInFilter
ObTableInFilter is a syntactic sugar provided by OBKV-Table, offering semantics similar to the IN operator in SQL:
- ObTableInFilter is essentially an ObTableFilterList.
- Each filter in it is an ObTableValueFilter with an ObCompareOp::EQ relationship.
Example for index-organized tables
Prerequisites
Before you query data, make sure that the following prerequisites are met:
- Your ObTableClient has been initialized.
- You have created 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 data by query range
This example shows a range query. The query specifies the range columns as the primary keys employeeId and departmentId, sets the scan range to [{1000, 1},{1010, 1}], and specifies the query columns as name.
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 data by using filters
The example shows a range query. The query specifies the range columns as the primary keys employeeId and departmentId, sets the scan range to [{1000, 1},{1010, 1}], and specifies the query columns as name. The query also specifies an ObTableValueFilter condition, so only records 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"));
}
}
Filter data by using indexes
The example shows a range query. The query specifies the range column as the index key departmentId, sets the scan range to [1, 10], and specifies the query columns as name. The query also specifies to use the index idx_departmentId.
Notice
In OBKV-Table, a secondary index must include the partitioning key so that the partition range can be determined when the secondary index range is specified.
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"));
}
}
Example for heap-organized tables
Prerequisites
Before you query data, make sure that the following prerequisites are met:
- Your ObTableClient has been initialized.
- You have created 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; -- Specify the heap-organized table by using the ORGANIZATION parameter
Query by using indexes
The example shows a range query. The query specifies the range column as the primary key c1, sets the scan range to [1000, 1003], and specifies the query columns as c1, c2, and c3. The query also specifies to use the index idx_unique_c1.
private static void queryWithIndex(ObTableClient obkvHandle) throws Exception {
try {
// Perform batch inserts by using BatchOperation
BatchOperation batch = client.batchOperation(tableName);
// Add multiple insert operations
// Note that you need to set the value of partitioning key c1 using setPartitionKey.
// You also need to set the value of c1 using 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 the 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();
}
}