OBKV-Table supports single operations, batch operations, and query operations.
APIs not supported for heap-organized tables
Heap-organized tables do not support the following APIs:
putbatchPutupdatebatchUpdateincrementbatchIncrementappendbatchAppenddeletegetbatchGetbatchDeletecheckAndInsUpcheckAndInsUpBatch
Single operations
Each API for a single operation processes at most one record, and you must specify a rowkey.
Basic single operations
Action |
Description |
|---|---|
| insert | Inserts a single row of data. You must specify the primary key column: 1. If the row does not exist, it is inserted. 2. If the row exists, the insertion fails. |
| put | Provides overwrite semantics. You must specify the primary key column: 1. If the row does not exist, it is inserted. 2. If the row exists, it is overwritten. |
| get | Retrieves a single row of data. You must specify the primary key column: If the select column is not specified, all columns are queried by default. |
| update | Updates a single row of data. You must specify the primary key column. Conditional updates are supported: you can specify conditions on non-primary key columns, and the row is updated only if the conditions are met. |
| delete | Deletes a single row of data. You must specify the primary key column. Conditional deletion is supported: you can specify conditions on non-primary key columns, and the row is deleted only if the conditions are met. |
Compound single operations
Action |
Description |
|---|---|
| insertOrUpdate | Provides both insert and update semantics: 1. You must specify the primary key column. If the row does not exist, it is inserted. 2. If the row exists, the existing row is updated. |
| increment | Adds a specified value to an integer column. You must specify the primary key column and the column whose value is to be incremented. Conditions are supported: if you specify conditions, the increment operation is performed only if the row meets the conditions: 1. If the specified row does not exist, the row is inserted and the column to be incremented is initialized with the increment value. 2. If the row exists and the current value of the column to be incremented is NULL, the column is updated with the increment value. |
| append | Appends a string to a character column. You must specify the primary key column and the column to which the string is to be appended. Conditions are supported: if you specify conditions, the append operation is performed only if the row meets the conditions: 1. If the specified row does not exist, the row is inserted and the column to which the string is to be appended is initialized with the string. 2. If the row exists and the current value of the column to which the string is to be appended is NULL, the column is updated with the string. |
| checkAndInsertup | Provides conditional update semantics: 1. When the checkExist parameter is true, if a row that meets the conditions exists, the specified InsertUp operation is performed; otherwise, nothing is done.2. When the checkExist parameter is false, if no row that meets the conditions exists, the specified InsertUp operation is performed; otherwise, nothing is done.3. When the rollbackWhenCheckFailed parameter is true, if the check fails, an ObTableException is thrown with the following error message: [-10518][OB_KV_CHECK_FAILED][Check failed in checkAndInsUp]. |
Batch operations
OBKV-Table supports batching of the preceding basic and compound single operations:
- A batch can contain only one type of operation, such as batch inserts.
- A batch can contain a mix of the different single operations listed above.
Query operations
In OBKV-Table, a query can access multiple rows of data, whereas a single operation can access at most one row. A typical query consists of the following parts:
- Select: The columns that are actually required by the business scenario. We recommend that you select only the necessary columns instead of returning too many unnecessary columns, which consumes CPU, memory, and network resources.
- ScanRange: The query range.
- Index: The index to be used. You must specify it explicitly.
- Filter: The filter condition for the query.
- Limit: The number of rows to return for the query.
Indexes
OBKV-Table supports local indexes and global indexes.
Indexes and queries
- Indexes can only be used in queries.
- When querying rows based on an index, all columns of the primary table can be accessed. However, if a queried column is not in the index, the primary table must be accessed additionally, which incurs significant overhead. When designing indexes, you must fully consider the actual query scenarios of your business.
- Indexes must be explicitly specified when used.
Local indexes and global indexes
The main differences between local indexes and global indexes are as follows:
- Whether the partitioning key is consistent with the primary table: If they are consistent, it is a local index; if not, it is a global index.
- Different maintenance costs: When writing a piece of data, for a local index, since the partitioning keys are the same, the primary table row to be written and the index row are on the same node, making it a single-machine transaction. For a global index, in a distributed architecture, the primary table row and the index row are likely not on the same node, so it may be a distributed transaction, which incurs greater overhead.
Note the following:
- Global indexes are not recommended because they generate distributed transactions on the server side, which significantly affect latency and throughput.
- When you plan local indexes, you must also carefully consider the index maintenance cost. For example, after you create indexes, you must maintain both the primary table and the index tables, which increases the write traffic to storage, affects latency and throughput, and increases storage usage.
Filter
Logical operators
OBKV-Table supports the following logical operators:
Operators |
Description |
Valid values |
|---|---|---|
| AND | Logical AND. Both filters must be met. | operator.AND |
| OR | Logical OR. Either of the two filters must be met. | operator.OR |
Comparison operators
OBKV-Table supports the following comparison operators:
Operator |
Description |
Supported types |
|---|---|---|
| > | Greater than | See Data types supported by OBKV-Table |
| >= | Greater than or equal to | |
| < | Less than | |
| <= | Less than or equal to | |
| != | Not equal to | |
| = | Equal to | |
| IS NULL | NULL | Only the NULL type is supported. |
| IS NOT NULL | NOT NULL |
Aggregation
The following table describes the aggregate functions supported by OBKV-Table.
Function |
Description |
Parameter type |
Result type |
|---|---|---|---|
| COUNT | Counts rows | Any type | BIGINT |
| SUM | Returns the sum | Integer type / Floating-point type | BIGINT/DOUBLE |
| MAX | Returns the maximum value | A data column of a numeric, character, date, or other comparable type | Consistent with the parameter type |
| MIN | Returns the minimum value | A data column of a numeric, character, date, or other comparable type | Consistent with the parameter type |
| AVG | Returns the average value | Integer type / Floating-point type | BIGINT/DOUBLE |
Notice
- Aggregation operations in OBKV-Table are special queries based on filter functions. They return specific results based on the semantics of the aggregate function rather than a large amount of raw data.
- Aggregation operations in OBKV-Table scan a large amount of data and consume significant I/O resources. Use them with caution.