Semi-structured encoding

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

OceanBase Database allows you to enable semi-structured encoding when you create a table in a MySQL tenant. The SEMISTRUCT_PROPERTIES parameter controls this feature. Additionally, you must set the ROW_FORMAT=COMPRESSED parameter for the table. Otherwise, an error will occur.

Considerations

• When SEMISTRUCT_PROPERTIES=(encoding_type=encoding), the table is considered a semi-structured table. This means 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.
• You can also set the freq_threshold parameter to specify a frequency threshold. 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 called frequent columns. For example, if you have a user table where the JSON field stores user information, and 90% of the 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 data, thus 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 stored as structured columns in a specific format. The columns derived from the JSON column are called subcolumns. Subcolumns can be further divided into sparse columns and frequent columns.

• Sparse columns: Subcolumns that exist in some JSON data but not in others. These columns have a frequency lower than the threshold specified by the freq_threshold parameter.
• Frequent columns: Subcolumns that exist in a high frequency in the JSON data. These columns are stored as independent columns to improve the performance of filter queries.

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, the id and name fields are present in every JSON data, with a frequency of 100%. The nickname field is present in only 4 JSON data, with a frequency of 80%.

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

Examples

1. Enable semi-structured encoding

   Notice

   Before you enable semi-structured encoding, make sure that the cluster 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 subconfiguration item does not affect the other subconfiguration items.

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 and only affects subsequent writes. Here is an example of disabling semi-structured encoding:

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

3. Query the semi-structured encoding configuration

   Use the SHOW CREATE TABLE statement to query the semi-structured encoding configuration. Here is an 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 is displayed, indicating that semi-structured encoding is enabled.

   Using semi-structured encoding can improve the performance of JSON_VALUE() function for filter queries. Based on the JSON semi-structured encoding technology, OceanBase Database optimizes the performance of JSON_VALUE expressions for filter 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.

   Here is an 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 character set 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';