This topic describes the data model of OBKV-Table. OBKV-Table provides a table model API that is conceptually similar to SQL and also involves concepts such as rows and columns.
Table organization
Table organization is the underlying storage structure of OBKV-Table and determines how data is stored on disk. OBKV-Table supports two table organizations:
- Index-organized table
- Heap-organized table. Heap-organized tables require OBKV Client V2.1.1 or later.
To specify the table organization when you create a table, add the ORGANIZATION option to the CREATE TABLE statement:
CREATE TABLE table_name
(column_definition)
ORGANIZATION [=] {INDEX | HEAP};
The ORGANIZATION option specifies the storage order of data rows in the table. Valid values are as follows:
INDEX: Specifies that the table is an index-organized table.HEAP: Specifies that the table is a heap-organized table.
If the ORGANIZATION option is not specified, its value is the same as the value of the default_table_organization parameter.
Index-organized table
An index-organized table is the default table organization for OBKV-Table and is suitable for most scenarios. The index of an index-organized table is based on the primary key, allowing for quick data location.
For example, create an index-organized table:
CREATE TABLE IF NOT EXISTS `employees` (
`employeeId` int NOT NULL,
`name` varchar(100) NOT NULL,
`department` varchar(50),
`position` varchar(50),
`workYear` int,
PRIMARY KEY (`employeeId`)
);
Heap-organized table
A heap-organized table is another table organization for OBKV-Table, mainly suitable for scenarios with high requirements for write performance and storage efficiency, especially when partitioned storage is needed. During writes, a heap-organized table does not require primary key conflict verification, which helps improve write performance and reduce additional I/O consumption. Additionally, because it does not enforce data sorting, a heap-organized table can significantly reduce I/O resource usage during minor compactions. Therefore, it is particularly suitable for scenarios sensitive to write throughput and storage cost, such as LOB applications.
For example, create a heap-organized table:
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 table using the ORGANIZATION parameter
Usage limitations are as follows:
- The local unique key or the primary key of a heap-organized table must include all partitioning keys.
- When creating an index on a heap-organized table, the index name cannot be the same as the name of the first primary key column.
- The following DDL operations are not supported for heap-organized tables in OceanBase AI Database:
- Primary key constraint operations
- Modifying a column to be the primary key
- Changing the data type length of a primary key column from larger to smaller
- Adding a primary key column
- Modifying a primary key to an auto-increment column
- Dropping a primary key column
- Partition splitting
- For details on the interfaces not supported by heap-organized tables, see OBKV-Table operations.
Table identification
Notice
We strongly recommend adding the kv_attributes setting when creating a table so that the system can accurately identify the OBKV table type.
When creating an OBKV-Table, we recommend adding the kv_attributes='{"Table": {}}' setting to ensure it is correctly identified as an OBKV-Table. Example:
CREATE TABLE `t1` (
`c1` bigint NOT NULL,
`c1sk` varchar(20) NOT NULL,
`c2` varbinary(1024) DEFAULT NULL,
`c3` varchar(20) DEFAULT NULL,
`c4` bigint DEFAULT NULL,
PRIMARY KEY(`c1`, `c1sk`))
-- '{"Table": {}}' This format cannot be changed, and Table is case-insensitive.
kv_attributes = '{"Table": {}}'
partition by range columns (`c1`) (
PARTITION p0 VALUES LESS THAN (300),
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN MAXVALUE);
Table structure
The table structure determines how data is stored within the table. The structure of an OBKV-Table mainly consists of the following parts:
- ColumnValue
- Row
ColumnValue
A ColumnValue represents a key-value pair, that is, the combination of a column name and the value of the column. It is mainly used to assign an initial value and to obtain the value of a ColumnValue object.
ColumnValue empCol = new ColumnValue("department", "oceanbase_product");
Row
A Row contains one or more ColumnValue pairs and stores the values of a row.
The following examples show how to construct a row. To simplify coding, OBKV-Table provides multiple encoding methods.
Method 1:
Row emp = row().add(colVal("employeeId", 12345))
.add(colVal("name", "zhangsan"))
.add(colVal("department", "oceanbase_dev"))
.add(colVal("position", "engineer"))
.add(colVal("workYear", 5));
Method 2:
ColumnValue id = colVal("employeeId", 12345);
ColumnValue name = colVal("name", "zhangsan");
ColumnValue dep = colVal("department", "oceanbase_dev");
ColumnValue pos = colVal("position", "engineer");
ColumnValue workYear = colVal("workYear", 5);
Row emp = new Row();
emp.add(id, name, dep, pos, workYear);
Note
OBKV-Table provides some syntactic sugar at the API level. For example, the MutationFactory class provides static methods such as ColumnValue colVal(String columnName, Object value) and Row row(ColumnValue... columnValue) to create objects. This reduces the coding workload when you build complex objects such as Row.
Rowkey:
- The primary key is very important in OBKV-Table, as it ensures the uniqueness of each record.
- If the primary key consists of multiple fields, a pure KV database requires users to encode these fields together, whereas OBKV-Table allows users to specify the rowkey directly based on the fields.
Queries
Queries in OBKV-Table mainly consist of the following parts:
- ScanRange
- Filter
ScanRange
A ScanRange is typically the primary key or a prefix of the primary key. The definition interface is as follows:
TableQuery addScanRange(Object[] starts, Object[] ends);
ScanRange
A ScanRange is typically the primary key or a prefix of the primary key. The API is defined as follows:
TableQuery addScanRange(Object[] starts, Object[] ends);
The parameters are described as follows:
- The parameters are two arrays, and the number of dimensions of the arrays is the number of primary key columns.
<starts[m], ends[m]>represents the value range of the m-th primary key column.
The sample schema is as follows:
CREATE TABLE test(c1 INT, c2 INT, c3 INT, c4 INT, PRIMARY KEY(c1, c2));
The sample query is as follows:
//SELECT c1, c2 FROM test WHERE c1 = 0 AND c2 >=2 AND c2 <= 40;
QueryResultSet resultSet = obTableClient.query("test")
.select("c1","c2")
.setScanRangeColumn("c1", "c2")
.addScanRange(new Object[]{0, 2}, new Object[]{0, 40})
.execute();
Differences between Filter and ScanRange
SQL and OBKV-Table handle query conditions differently:
SQL:
- In the
WHEREclause, any column can be used as a filter condition. - There is no need to distinguish between primary and non-primary keys.
- The SQL optimizer automatically converts primary key-related conditions into ScanRanges and non-primary key conditions into Filters.
- In the
OBKV-Table:
- Mainly used for simple transaction processing scenarios.
- Does not have a built-in optimizer.
- Users must set the ScanRange and Filter through APIs.
Why distinguish between ScanRange and Filter?
ScanRange (scan range):
- Used for the primary key or its prefix.
- Data in the database is sorted by primary key.
- Allows quick location of the data range that meets the conditions.
Filter (filter):
- Used for non-primary key columns.
- Cannot locate data directly; requires scanning.
- Used to determine whether data meets conditions during the scan.
When both are present, the query process is as follows:
- First, use the ScanRange to quickly identify the possible data range that meets the conditions.
- Then, within this range, use the Filter to further filter out the data that meets all conditions.
Note that:
- The ScanRange must be included in the query plan; otherwise, even if a Filter is present, the full table scan path will be taken, consuming a large amount of the database's I/O resources.
- The OBKV-Table server does not provide optimizer capabilities and will not convert primary key conditions written in the Filter into a ScanRange.