OBKV-Table provides a table model API that is conceptually close to SQL and therefore also involves data types. This topic describes the data types supported by OBKV-Table.
Types supported by OBKV-Table (schema definition)
String types
The following string types are supported in OBKV-Table:
VARCHARandCHARTINYTEXT,TEXT,MEDIUMTEXT, andLONGTEXT
Note that the OBKV client does not provide an API for setting the character set. Client data is converted to the utf8mb4_general_ci character set by default. If the character set defined in the schema is different, extra character set conversion overhead is incurred during storage.
Binary types
Binary types are similar to string types, except that they store binary data instead of strings.
The following binary types are supported in OBKV-Table:
VARBINARYandBINARYTINYBLOB,BLOB,MEDIUMBLOB, andLONGBLOB
Numeric types
The following numeric types are supported in OBKV-Table:
- Integer types:
BOOL,BOOLEAN,TINYINT,SMALLINT,MEDIUMINT,INT,INTEGER, andBIGINT - Fixed-point types:
DECIMALandNUMERIC - Floating-point types:
FLOATandDOUBLE
Note the following:
- The primary client of OBKV-Table is the Java client. Java does not support unsigned types, so unsigned attributes cannot be used in schema definitions.
- The precision and scale of fixed-point and floating-point numbers are defined by the schema. If the data does not match the schema, an error is returned.
Date and time types
The following date and time types are supported in OBKV-Table: DATE, TIME, DATETIME, TIMESTAMP, and YEAR.
Note that when you use DATETIME or TIMESTAMP, the default precision is 0, which means that the precision reaches only seconds. For example, some Java functions return time values in milliseconds, which may cause precision validation to fail. Therefore, you can set the precision of time columns to 6, which is the maximum precision.
JSON type
The JSON data type is a special data type for storing and processing JSON (JavaScript Object Notation) data in a database. A JSON value can be an object, an array, a string, a number, a boolean value (true/false), or null. Note that true, false, and null must be in lowercase.
You can create a JSON column in a table and use the OBKV-Table Java client API to read and write JSON data. When you write JSON data from the client, the data must be passed as a specific Java type. Currently, the following Java types are supported:
Stringbyte[]
You can create multiple columns of the JSON data type in each table. However, note the following:
- Columns of the JSON data type cannot be used as
PRIMARY KEY,FOREIGN KEY, orUNIQUE KEY, but you can add theNOT NULLorCHECKconstraint. - Columns of the JSON data type cannot have a default value.
- Columns of the JSON data type cannot be used as a partitioning key.
- The length of JSON data cannot exceed that of
LONGTEXT. The maximum depth of a JSON object or array is100by default, which can be set using the parameter json_document_max_depth. - When you write JSON data from the client, the internal keys may be reordered. For example, if you write
{"b": 1, "a": 2}, the data read back may be{"a":2, "b":1}. Although the positions of "a" and "b" are swapped, they are still the same JSON object.
The limitations of using the JSON data type in OBKV-Table are as follows:
- Only JSON data access is supported. JSON-related functions (such as
JSON_EXTRACT) are not supported. - The client cannot use the
putAPI to write JSON data. We recommend that you use theinsertOrUpdateAPI. - If you write JSON data to a column by using an unsupported Java type (such as Integer or Boolean), an
OB_KV_COLUMN_TYPE_NOT_MATCHerror is returned.
The following example shows how to create a JSON column:
CREATE TABLE t1_json(
k BIGINT,
v JSON,
PRIMARY KEY(k))
kv_attributes = '{"Table": {}}'
PARTITION BY KEY(k) PARTITIONS 97;
The following example shows how to write JSON data from the client:
Row rowKey = row(colVal("k", 1));
String jsonText = "{ \"greeting\" : \"Hello!\", \"farewell\" : \"bye-bye!\", "
+ "\"json_text\" : \"test!\" }";
client.insertOrUpdate("test")
.setRowKey(rowKey)
.addMutateColVal(colVal("v", jsonText)).execute();
The following example shows how to read JSON data from the client:
Map<String, Object> res = client.get(tableName)
.setRowKey(row(colVal("k", 1)))
.select("k", "v")
.execute();
System.out.println(res.get("v"));
Mapping between Java data types and OBKV-Table data types
The following table describes the mapping between Java data types and OBKV-Table data types:
Java types |
OBKV-Table type |
|---|---|
| Boolean | TinyInt |
| Byte | Varbinary |
| Short | SmallInt |
| Integer | Int |
| Long | BigInt |
| String | Varchar |
| Byte[] | Varchar |
| Double | Double |
| Float | Float |
| Timestamp | Timestamp |
| java.sql.Date | DateTime |
| Date | DateTime |
| String (JSON format) | JSON |
Notice
- When you use the
StringorByte[]data type in Java, the data length must not exceed the limit of theVARCHARcolumn in the table. - For Java time types (
Timestamp,java.sql.Date, andDate), the precision must not exceed the limit of theTIMESTAMPorDATETIMEcolumn in the table. The default precision of aTIMESTAMPorDATETIMEcolumn is 0.