Semi-structured encoding

This topic describes the semi-structured encoding feature of OceanBase Database.

OceanBase Database allows you to enable semi-structured encoding for a table in a MySQL-compatible tenant. This feature is controlled by the table-level parameter SEMISTRUCT_PROPERTIES. Additionally, you must set the ROW_FORMAT to COMPRESSED. Otherwise, an error will occur.

Considerations

• When SEMISTRUCT_PROPERTIES=(encoding_type=encoding), the table is considered a semi-structured table, meaning that all JSON columns in the table will have semi-structured encoding enabled.
• When SEMISTRUCT_PROPERTIES=(encoding_type=none), the table is considered a structured table.
• Additionally, you can set the frequency threshold using the freq_threshold parameter. When semi-structured encoding is enabled, the system analyzes the frequency of each path in the JSON data. Paths with a frequency higher than the specified threshold are stored as independent subcolumns, which are referred to as frequent columns. For example, if you have a user table with a JSON field storing user information, and 90% of users have the name and age fields, the system will automatically extract these fields as frequent columns. During queries, the system can directly access these columns without parsing the entire JSON, thereby improving query performance. For more information about the syntax and parameters, see CREATE TABLE.
• The encoding_type and freq_threshold parameters are part of the Online DDL syntax and are not supported in Offline DDL.

Data format

JSON data is split into structured columns based on a specific format. The columns derived from the JSON column are called subcolumns. Subcolumns can be further categorized into sparse columns and frequent columns.

• Sparse columns: Subcolumns that are present in some JSON documents but not in others, with an occurrence frequency below the threshold specified by the freq_threshold parameter.
• Frequent columns: Subcolumns that appear in JSON documents with a frequency higher than the threshold specified by the freq_threshold parameter. These subcolumns are stored as independent columns to enhance filtering query performance.

Example:

{"id": 1001, "name": "n1", "nickname": "nn1"}
{"id": 1002, "name": "n2", "nickname": "nn2"}
{"id": 1003, "name": "n3", "nickname": "nn3"}
{"id": 1004, "name": "n4", "nickname": "nn4"}
{"id": 1005, "name": "n5"}

In this example, id and name are fields present in every JSON document with an occurrence frequency of 100%. The nickname field is present in only 4 JSON documents, with an occurrence frequency of 80%.

If freq_threshold is set to 100%, then nickname will be considered a sparse column, while id and name will be considered frequent columns. If freq_threshold is set to 80%, then nickname, id, and name will all be considered frequent columns.

Examples

1. Enable semi-structured encoding

   Notice

   Before enabling semi-structured encoding, make sure that the cluster configuration parameter micro_block_merge_verify_level is set to the default value 2. Do not disable micro-block merge verification.

   Example of enabling semi-structured encoding when creating a table

   Example of enabling semi-structured encoding for an existing table

   CREATE TABLE t1(  j json) 
   ROW_FORMAT=COMPRESSED 
   SEMISTRUCT_PROPERTIES=(encoding_type=encoding, freq_threshold=50);
   

   For more information about the syntax, see CREATE TABLE.

   CREATE TABLE t1(j json);
   ALTER TABLE t1 SET ROW_FORMAT=COMPRESSED SEMISTRUCT_PROPERTIES = (encoding_type=encoding, freq_threshold=50);
   

   For more information about the syntax, see ALTER TABLE.

   Some modification limitations:

   • Modifying the frequent column threshold does not take effect if semi-structured encoding is not enabled.
   • The freq_threshold parameter cannot be modified during a direct load or when the table is locked.
   • Modifying one sub-configuration item does not affect the others.

2. Disable semi-structured encoding

   When SEMISTRUCT_PROPERTIES is set to (encoding_type=none), semi-structured encoding is disabled. This operation does not affect existing data but only future writes. Here is an example of disabling semi-structured encoding:

   ALTER TABLE t1 SET ROW_FORMAT=COMPRESSED SEMISTRUCT_PROPERTIES = (encoding_type=none);
   

3. Query semi-structured encoding settings

   Use the SHOW CREATE TABLE statement to query semi-structured encoding settings. Example statement:

   SHOW CREATE TABLE t1;
   

   The result is as follows:

   +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Table | Create Table                                                                                                                                                                                                                                                                                                                                            |
   +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | t1    | CREATE TABLE `t1` (
     `j` json DEFAULT NULL
   ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPRESSED COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 SEMISTRUCT_PROPERTIES=(ENCODING_TYPE=ENCODING, FREQ_THRESHOLD=50) |
   +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

   When SEMISTRUCT_PROPERTIES=(encoding_type=encoding), the configuration item information will be displayed, indicating that semi-structured encoding is enabled.

   Using semi-structured encoding can improve the performance of JSON_VALUE() function for condition-based filtering queries. Based on the JSON semi-structured encoding technology, OceanBase Database optimizes the performance of JSON_VALUE expressions for condition-based filtering queries. Since the JSON data has been split into subcolumns, the system can directly filter based on the encoded subcolumn data without reconstructing the entire JSON structure, significantly improving query efficiency.

   Example query:

   -- Query rows where the value of the name field is 'Devin'
   SELECT * FROM t WHERE JSON_VALUE(j_doc, '$.name' RETURNING CHAR) = 'Devin';
   

   Character set considerations:

   • OceanBase Database uses the utf8_bin encoding for JSON data.

   • To ensure that string white-box filtering works correctly, we recommend that you set:

   SET @@collation_server = 'utf8mb4_bin';
   SET @@collation_connection='utf8mb4_bin';