@Rocky tell me more about INSERT OVERWRITE and its usage

INSERT OVERWRITE

in StarRocks is used to replace existing data in a table or specific partitions with new data. It ensures atomicity by using temporary partitions: new data is loaded into temporary storage first, and only after a successful load are the original partitions swapped with the new ones. 1. Basic Usage You can overwrite data using either a query result (

SELECT

) or specific values (

VALUES

). * Overwrite with query results (Most common):

sql
    INSERT OVERWRITE target_table
    SELECT * FROM source_table;

* Overwrite specific partitions:

sql
    INSERT OVERWRITE target_table PARTITION (p202301, p202302)
    SELECT * FROM source_table;

* Overwrite with values (Testing/Demo only):

sql
    INSERT OVERWRITE target_table
    VALUES (1, 'data1'), (2, 'data2');

2. Default Behavior (Pre-v3.4.0 or

dynamic_overwrite=false

) By default,

INSERT OVERWRITE

behaves as follows: * Whole Table: If you do not specify a partition, StarRocks overwrites the partitions that receive new data. Crucially, any partitions that do not receive data will be truncated (emptied). * Manual Partitioning: If you specify

PARTITION (p1)

, only that partition is affected. If the incoming data doesn't match

p1

, the load will fail (in strict mode) or filter the data. 3. Dynamic Overwrite (v3.4.0+) Starting from v3.4.0, you can enable a more flexible "Dynamic Overwrite" mode by setting

SET dynamic_overwrite = true;

. How it differs: * Targeted Overwrite: When overwriting a whole table, it only replaces data in partitions that are actually present in the source data. Partitions that are not involved in the source data are left untouched (not truncated). * Auto-Creation: If the source data contains values for partitions that don't exist yet, StarRocks will automatically create those partitions (for expression-partitioned tables). Example using a hint:

sql
INSERT /*+set_var(dynamic_overwrite = true)*/ OVERWRITE target_table
SELECT * FROM source_table;

4. Key Limitations & Tips * Internal Tables only:

INSERT OVERWRITE

is primarily designed for StarRocks internal tables (Duplicate, Aggregate, Unique, or Primary Key models). * Transaction Safety: The operation is atomic. If the Leader FE node crashes during the process, the transaction fails and no data is swapped, leaving your original data intact. * Labels: You can use

WITH LABEL <label_name>

to ensure idempotency and track the load task. * Performance: For massive datasets,

INSERT OVERWRITE SELECT

is preferred over

VALUES

. For external data, use

INSERT OVERWRITE ... SELECT * FROM FILES(...)

for high performance. References • table_design/table_types/primary_key_table.md • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md

@Игорь Т

INSERT OVERWRITE is primarily designed for StarRocks internal tables

Works also for Iceberg tables in 4.x versions