Table model
Data in a table, such as an SQL table, is highly constrained by the schema.
You can execute a DDL statement to create a table. All types of partitioned tables of OceanBase Database are supported. The table must contain a primary key and several strongly-typed columns. You can also define secondary indexes and a UNIQUE constraint for the table.
OceanBase TableAPI provides table model-based API operations that can be seamlessly integrated or used with SQL statements in OceanBase Database. In addition to the query operations, table model-based API operations support data reads and writes by using the primary key.
Secondary index
You can create indexes for a table. Table indexes will be updated in all TableAPI operations.
The Query operation enables you to query and scan data based on secondary indexes. However, the regular KV model allows you to query data only by the primary key, which is not enough in many business circumstances. As a result, you must maintain an index table at the business layer if your applications use a database of the simple KV model that supports only primary key-based queries. This complicates the processing of business data and makes it difficult to ensure data consistency between the indexes and the primary table. In addition, reading and writing the index table require multiple interactions between the application and database, which results in high latency.
OceanBase TableAPI solves the preceding problems by providing native support for secondary indexes on the server.
Security authentication
TableAPI operations are authenticated in the same way as that in SQL mode. TableAPI allows you to execute SQL statements to manage tenants, users, and passwords.
Comparison with the SQL mode
OceanBase TableAPI provides table model-based API operations that can be seamlessly integrated or used with SQL statements in OceanBase Database. This section describes the advantages and disadvantages of TableAPI in comparison with the SQL mode.
Advantages
- Designed on the distributed storage layer, TableAPI features a short data access path and shows better performance for the same function. Because of its simple semantics, TableAPI is easier to optimize and is more predictable.
- The semantics of batch operations are more flexible and efficient than those in SQL mode. For example, you can call a multi-get operation to select columns of different rows.
- TableAPI is easy to use and provides entity semantics without complex ER mapping.
- Unlike the JDBC or ODBC API, TableAPI works on a simple RPC protocol and does not have any reconnections. The number of connections can hardly affect the client. However, it is currently limited by the RPC framework.
Disadvantages
- TableAPI supports limited query operations such as get, scan, and limit. TableAPI does not support the aggregation or sorting feature in SQL mode.
- TableAPI does not support complex transactions, such as interactive transactions. Unlike conventional relational databases, OceanBase Database is a NewSQL relational database that provides complete SQL functions. TableAPI is not designed for high scalability and availability.
- TableAPI does not support global indexes and therefore does not support operations on tables with global indexes.
KV model
The KV model is essentially a simplified relational table model. When a table of the KV model is created, it has only two columns: K and V. This table is partitioned based on the K or the prefix of K. For example:
create table kv (
K varbinary(1024),
V varbinary(1024),
primary key(K)) partition by key(K) partitions 16;