@Rocky StarRocks 4.1.1, primary key table loaded by Routine Load from Kafka JSON with "merge_condition" = "updated_at". Can the same job also apply deletes via __op? The docs say "DELETE operations do not support conditional updates": is the job rejected, is the delete skipped, or is the delete applied ignoring the condition? Also, for JSON loads without a COLUMNS clause, is a JSON key named __op picked up automatically?

In StarRocks (up to version 4.1.1), the behavior for your specific Primary Key table configuration is as follows: 1. Conditional Updates vs. Deletes via

__op

Yes, the same job can apply deletes using the

__op

field, but the logic differs between updates and deletes: * Behavior for DELETE (

__op

= 1): The delete is applied ignoring the condition. When the documentation states "DELETE operations do not support conditional updates," it means the

merge_condition

check is bypassed for delete records. If a record in your Kafka stream has

__op=1

, StarRocks will delete the corresponding row in the table based on the primary key immediately, without checking if the source

updated_at

is greater than the existing value in the table. * Behavior for UPSERT (

__op

= 0 or missing): The

merge_condition

=

updated_at

is strictly enforced. The update only takes effect if the source

updated_at

is greater than or equal to the existing value. Summary of the "Notice": The job is not rejected, and the delete is *not skipped*; it is simply applied without a version check. 2. Automatic

__op

Mapping in JSON For Routine Load (and most other load methods), a JSON key named

__op

is not picked up automatically if you omit the

COLUMNS

clause. * Routine Load Simple Mode: If you do not provide a

COLUMNS

clause, StarRocks maps JSON keys directly to physical table columns by name. Since

__op

is a virtual system field and not a column you define in

CREATE TABLE

, the loader will not look for it in the JSON. All rows will be processed as the default

UPSERT

(0). * Required Configuration: To enable deletes, you must include a

COLUMNS

clause in your

CREATE ROUTINE LOAD

statement to explicitly map the JSON key to the system's

__op

field. Example for your setup:

sql
CREATE ROUTINE LOAD your_job ON your_table
COLUMNS (
    pk_col, 
    val_col, 
    updated_at, 
    __op -- Maps a JSON key named "__op" to the operation type
)
PROPERTIES (
    "format" = "json",
    "merge_condition" = "updated_at"
)
FROM KAFKA (...);

_Note: If your JSON key is named something else (e.g.,

op_type

), you would use

COLUMNS(..., op_type, __op = op_type)

._ References • sql-reference/sql-statements/table_bucket_part_index/DELETE.md • loading/Load_to_Primary_Key_tables.md • data_source/catalog/iceberg/DML.md