Flexible partial column update

Use cases

• Real-time CDC synchronization: When you use CDC to sync data from a source system to SelectDB, source records often contain only the primary key and the changed columns rather than complete rows. Flexible partial column update handles this naturally — each row in a batch can update a different set of columns.

Prerequisites and limitations

Table model and feature limitations

1. Table model: Only Merge-on-Write tables using the unique key model are supported.

2. Column type: Tables with a variant column are not supported.

3. Materialized views: Tables with synchronous materialized views are not supported.

Import methods and data requirements

1. Import method: Only Stream Load and tools built on it (such as the Flink Doris Connector) are supported.

2. Data format: Import files must be in JSON format.

3. Primary key requirement: Every imported row must include all primary key columns. Rows missing primary key columns are filtered and counted as filtered rows. If filtered rows exceed the max_filter_ratio threshold, the entire import job fails. Filtered rows are recorded in the error log.

4. Column name matching: JSON keys that do not match a column name in the target table are silently ignored. System-internal column names such as __DORIS_VERSION_COL__ are also ignored.

Unsupported import parameters

The following Stream Load header parameters cannot be used with flexible partial column update:

• merge_type

• delete

• fuzzy_parse

• columns

• jsonpaths

• hidden_columns

• function_column.sequence_col

• sql

• memtable_on_sink_node

• group_commit

• where

Enable and use the feature

Step 1: Enable the feature

Enable for a new table

Add the following two properties to the PROPERTIES clause when creating the table. The first enables the Merge-on-Write implementation; the second adds the hidden columns required to support this feature.

"enable_unique_key_merge_on_write" = "true",
"enable_unique_key_skip_bitmap_column" = "true"

Enable for an existing table

The table must already be a Merge-on-Write table ("enable_unique_key_merge_on_write" = "true" in its PROPERTIES) and must have the light schema change feature enabled.

Run the following command:

ALTER TABLE db1.tbl1 ENABLE FEATURE "UPDATE_FLEXIBLE_COLUMNS";

To verify, run show create table db1.tbl1 and confirm that the PROPERTIES output contains "enable_unique_key_skip_bitmap_column" = "true".

Step 2: Run an import

Stream Load

Add the following parameter to the HTTP request header:

unique_key_update_mode:UPDATE_FLEXIBLE_COLUMNS

Flink Doris Connector

Add the following sink configuration:

'sink.properties.unique_key_update_mode' = 'UPDATE_FLEXIBLE_COLUMNS'

Example

1. Prepare the table and data

Create table t1 with flexible partial column update enabled:

CREATE TABLE t1 (
  `k` int(11) NULL, 
  `v1` BIGINT NULL,
  `v2` BIGINT NULL DEFAULT "9876",
  `v3` BIGINT NOT NULL DEFAULT "0",
  `v4` BIGINT NOT NULL DEFAULT "1234",
  `v5` BIGINT NULL
) UNIQUE KEY(`k`) DISTRIBUTED BY HASH(`k`) BUCKETS 1
PROPERTIES(
"replication_num" = "1",
"enable_unique_key_merge_on_write" = "true",
"enable_unique_key_skip_bitmap_column" = "true"
);

Initial data:

+---+----+----+----+----+----+
| k | v1 | v2 | v3 | v4 | v5 |
+---+----+----+----+----+----+
| 0 | 0  | 0  | 0  | 0  | 0  |
| 1 | 1  | 1  | 1  | 1  | 1  |
| 2 | 2  | 2  | 2  | 2  | 2  |
| 3 | 3  | 3  | 3  | 3  | 3  |
| 4 | 4  | 4  | 4  | 4  | 4  |
| 5 | 5  | 5  | 5  | 5  | 5  |
+---+----+----+----+----+----+

2. Import update data

Create a JSON file test1.json that mixes deletions, partial updates, and a new-row insert:

{"k": 0, "__DORIS_DELETE_SIGN__": 1}
{"k": 1, "v1": 10}
{"k": 2, "v2": 20, "v5": 25}
{"k": 3, "v3": 30}
{"k": 4, "v4": 20, "v1": 43, "v3": 99}
{"k": 5, "v5": null}
{"k": 6, "v1": 999, "v3": 777}
{"k": 2, "v4": 222}
{"k": 1, "v2": 111, "v3": 111}

Load the file with Stream Load:

curl --location-trusted -u root: \
-H "Expect: 100-continue" \
-H "strict_mode:false" \
-H "format:json" \
-H "read_json_by_line:true" \
-H "unique_key_update_mode:UPDATE_FLEXIBLE_COLUMNS" \
-T test1.json \
-XPUT http://<host>:<http_port>/api/d1/t1/_stream_load

3. Verify the results

Query the table after the import:

+---+-----+------+-----+------+--------+
| k | v1  | v2   | v3  | v4   | v5     |
+---+-----+------+-----+------+--------+
| 1 | 10  | 111  | 111 | 1    | 1      |
| 2 | 2   | 20   | 2   | 222  | 25     |
| 3 | 3   | 3    | 30  | 3    | 3      |
| 4 | 43  | 4    | 99  | 20   | 4      |
| 5 | 5   | 5    | 5   | 5    | <null> |
| 6 | 999 | 9876 | 777 | 1234 | <null> |
+---+-----+------+-----+------+--------+

Result analysis:

• k = 0: Deleted because __DORIS_DELETE_SIGN__ is set to 1.

• k = 1: Updated twice in the same import. SelectDB merges both updates: v1 = 10, v2 = 111, v3 = 111. Columns not referenced in either update retain their original values.

• k = 2: Also updated twice; SelectDB merges both updates.

• k = 3: Only v3 is updated to 30.

• k = 4: v1, v3, and v4 are updated.

• k = 5: v5 is explicitly set to NULL.

• k = 6: New row. v1 and v3 receive the specified values. v2 and v4 fall back to their DEFAULT values (9876 and 1234). v5, which has no default, is set to NULL.