http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I need to implement the following: ...
# questions-and-troubleshooting
e
@Rocky I need to implement the following: 1. data that is older than 7 days need to be moved from starrocks native tables to iceberg format in s3 2. once the data is moved to s3, it needs to be removed from the starrocks native table. The idea is to save storage money. a couple of things that can go wrong and we need to protect ourself from: 1. we moved half of the data to s3/we moved all of the data to s3 and the process that that does this failed -> if we try again to run this process, we dont want to have duplicates in s3 2. data can arrive late, meaning we deleted the data for the last 7 days and 2 days later data that was 10 days old arrived -> this means we cant just do replace partition , drop partition 3. late data might arrive also the moment we just finished copying the data of a certain partition to s3 note: by partition I mean the data is partitioned by date (day) 4. we want to do this process for each customer and if possible know which customers got their data moved to s3 and removed from starrocks and which did not do we need to write some orchestrator service to do this ? does starrocks provide this capability ? can you give us some advice on the best practice starrocks recommends for this use case ?
r
StarRocks does not currently provide a native, "one-click" background service to automatically archive data to Iceberg and delete it from local storage. To implement this safely and handle your specific requirements (late-arriving data, idempotency, and per-customer tracking), you will need to use an orchestrator (such as Airflow, Dagster, or a custom service) to execute a series of SQL commands. Here is the recommended best practice for this workflow: 1. Recommended Workflow The safest way to move data while protecting against failures and late data is to use partition-level operations. 1. Define a Tracking Table: Create a table in StarRocks (e.g., 
archival_log
) to track the status of each customer's partition (e.g., 
customer_id
, 
date
, 
status
, 
last_updated
). 2. Move Data (Idempotent): Use 
INSERT OVERWRITE
into the Iceberg table.
Copy code
sql
    INSERT OVERWRITE iceberg_catalog.target_db.table_name
    PARTITION (date_col = '2023-10-01')
    SELECT * FROM native_catalog.source_db.table_name
    WHERE date_col = '2023-10-01' AND customer_id = 'XYZ';
3. Verify: Check the row counts between the native table and Iceberg for that specific partition/customer. 4. Remove Data: Use 
DROP PARTITION
or 
DELETE
. * Best for Cost: 
ALTER TABLE native_table DROP PARTITION (p20231001);
(Note: This removes the entire partition for all customers in that partition). * Best for Per-Customer: 
DELETE FROM native_table WHERE date_col = '2023-10-01' AND customer_id = 'XYZ';
────────── 2. Addressing Your Specific Risks Risk 1: Process Failure & Duplicates * Solution: Use 
INSERT OVERWRITE
 instead of 
INSERT INTO
. * If the process fails mid-way, the next attempt will overwrite the partially written data in S3 for that partition, ensuring you never have duplicates in Iceberg regardless of how many times the job retries. Risks 2 & 3: Late Data & Race Conditions If you use 
DROP PARTITION
, you risk losing data that arrived between the 
INSERT
and the 
DROP
. * Solution: Ingestion Timestamps. Add a hidden or explicit 
ingestion_time
column (defaulting to 
now()
) to your native table. 1. Get the current max timestamp: 
SET @job_start = now();
2. Move data: 
INSERT OVERWRITE ... SELECT * FROM source WHERE date_col = '...' AND ingestion_time < @job_start;
3. Delete data: 
DELETE FROM source WHERE date_col = '...' AND ingestion_time < @job_start;
_ This ensures that any data arriving _during* the migration (with an 
ingestion_time
> 
@job_start
) remains in the native table and will be picked up by the next archival run. Risk 4: Per-Customer Tracking * Solution: Since StarRocks does not provide built-in per-customer migration metadata, you should maintain a State Table in your 
default_catalog
.
Copy code
sql
    CREATE TABLE archival_status (
        customer_id VARCHAR(100),
        partition_date DATE,
        status VARCHAR(20), -- 'PENDING', 'COPIED', 'DELETED'
        rows_moved BIGINT,
        updated_at DATETIME
    ) DISTRIBUTED BY HASH(customer_id);
* Your orchestrator should update this table after each successful step. This allows you to easily query which customers have been successfully moved and which failed. ────────── 3. Summary of Capability * Orchestrator needed? Yes. Use Airflow or a similar tool to manage the loops (for each customer) and the retries. * StarRocks Capability: StarRocks provides the "engine" (Iceberg Catalog, 
INSERT OVERWRITE
, 
DELETE
, 
DROP PARTITION
), but not the "logic" (scheduling and state tracking). * Storage Savings: This is a very common pattern for "Cold Data Archiving." Moving data to Iceberg in S3 significantly reduces costs while keeping the data queryable via StarRocks' External Catalogs. Final Advice
If your data volume is massive and performance is a priority, try to archive entire partitions (all customers for a single day) rather than customer-by-customer. Deleting data by 
customer_id
(
DELETE WHERE customer_id = ...
) is much more resource-intensive for the cluster than dropping an entire partition. Only use the per-customer approach if your business logic strictly requires customers to be on different archival schedules that differ significantly. References • quick_start/iceberg.md • data_source/datalake_faq.md • data_source/catalog/iceberg/iceberg_catalog.md • data_source/feature-support-data-lake-analytics.md • data_source/catalog/iceberg/iceberg_timetravel.md
e
@Rocky explain why: "*Risks 2 & 3: Late Data & Race Conditions* If you use 
DROP PARTITION
, you risk losing data that arrived between the 
INSERT
and the 
DROP
. * Solution: Ingestion Timestamps. Add a hidden or explicit 
ingestion_time
column (defaulting to 
now()
) to your native table. 1. Get the current max timestamp: 
SET @job_start = now();
2. Move data: 
INSERT OVERWRITE ... SELECT * FROM source WHERE date_col = '...' AND ingestion_time < @job_start;
3. Delete data: 
DELETE FROM source WHERE date_col = '...' AND ingestion_time < @job_start;
_ This ensures that any data arriving _during* the migration (with an 
ingestion_time
> 
@job_start
) remains in the native table and will be picked up by the next archival run." solves the issue of data arriving late ? also doesnt INSERT OVERWRITE means overwrite all the data in that parititon in s3 ? so if we would to run it for data that arrived late, then the old data in that s3 partition would be deleted ? also whats this hidden or explicit 
ingestion_time
column ?
r
This is a common challenge when building a "Cold Data Archival" pipeline. To handle late data and race conditions safely, you need to move from a "partition-based" logic to a "time-range based" logic. 1. What is the 
ingestion_time
column? There is no "magic" hidden column; you should add an explicit column to your StarRocks native table to track when a row actually hit the database, regardless of what the business 
event_date
says.
Copy code
sql
ALTER TABLE your_native_table ADD COLUMN ingestion_time DATETIME DEFAULT CURRENT_TIMESTAMP;
* Why? The 
event_date
(e.g., 
2023-10-01
) tells you when the user did something. The 
ingestion_time
tells you when the data reached StarRocks. * The Late Data Fix: If a record from 10 days ago arrives today, its 
event_date
is 10 days old, but its 
ingestion_time
is now. This allows you to distinguish between "data I've already archived" and "new data that just arrived for an old date." ────────── 2. Why the "Line in the Sand" solves Late Data The "Line in the Sand" (@job_start) prevents race conditions where data arrives while your script is running. 1. Job Starts: You capture the current time: 
SET @job_start = now();
2. Move Data: You only move data where 
ingestion_time < @job_start
. 3. Late Arrival: If new data (late-arriving) hits the table 1 second after your job started, its 
ingestion_time
will be 
> @job_start
. 4. Delete Data: When you run the 
DELETE
command, you use the same 
@job_start
filter. * The 
DELETE
will ignore the new record that just arrived. * That new record stays in StarRocks and will be picked up by the next run of your archival job. ────────── 3. Does 
INSERT OVERWRITE
delete old data in S3? Yes, it does, and that is a risk if you handle late data. If you move data for 
2023-10-01
on Monday, and then more data for 
2023-10-01
arrives on Tuesday: * If you use 
INSERT OVERWRITE ... PARTITION(date='2023-10-01')
, S3 will delete Monday's data and replace it with only Tuesday's data. This causes data loss. The Correct Approach for Iceberg: Use 
INSERT INTO
 (Append) for archiving. Since Iceberg is a transactional table format, StarRocks' 
INSERT INTO
is atomic. If the job fails halfway, Iceberg will not commit the files, and you won't see partial data. To make it Idempotent (avoiding duplicates on retry): Your orchestrator should track the last successfully processed 
ingestion_time
in a metadata table. 1. Check metadata: 
SELECT max_ingestion_time FROM archival_log WHERE customer_id = 'A'
. 2. 
INSERT INTO iceberg SELECT ... FROM native WHERE ingestion_time > @last_max AND ingestion_time <= @job_start
. 3. If the job fails and restarts, 
@last_max
remains the same, and you simply re-run the same range. ────────── 4. Best Practice Implementation Summary Do you need an orchestrator (Airflow, Python script, etc.)? Yes. StarRocks executes the data movement, but the orchestrator manages the "state" (which customer is done). Recommended Workflow per Customer: 1. Define a state: Store 
last_archived_ingestion_time
in a StarRocks table. 2. Step 1 (Move):
Copy code
sql
    -- Use INSERT INTO to append late data to existing S3 partitions
    INSERT INTO iceberg_catalog.db.table
    SELECT * FROM native_catalog.db.table
    WHERE customer_id = 'XYZ'
      AND ingestion_time > @last_archived_ingestion_time
      AND ingestion_time <= @job_start;
3. Step 2 (Delete):
Copy code
sql
    DELETE FROM native_catalog.db.table
    WHERE customer_id = 'XYZ'
      AND ingestion_time > @last_archived_ingestion_time
      AND ingestion_time <= @job_start;
4. Step 3 (Update State): Update your log table with the new 
@job_start
.
Note on Storage Cost: 
DELETE
in StarRocks is metadata-fast, but to actually reclaim disk space, StarRocks needs to perform "compaction" in the background. Dropping a partition (
DROP PARTITION
) is the only way to reclaim space instantly, but as you noted, it is too "blunt" a tool if you have multiple customers or late data in the same partition. Use 
DELETE
for precision; the space will be reclaimed shortly after. References • data_source/catalog/iceberg/DML.md • loading/InsertInto.md • sql-reference/sql-functions/date-time-functions/current_timestamp.md References • data_source/catalog/iceberg/DML.md • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/DDL.md
e
@Rocky a couple of tihngs -> the moving the data failed right after we finished moving it to s3 then maybe we didnt manage to save "`last_archived_ingestion_time`" so we cant relay on that, no ? once you said somethings about a anti left join, maybe that can help ? also do you recommend a external orchestrator service or maybe to use tasks of starrocks ?
r
To handle the failure where data is moved to S3 but the "state" (the 
last_archived_ingestion_time
) isn't updated, you need an idempotent process. You are correct that an "anti-join" (or 
NOT EXISTS
) is the best way to achieve this. 1. The Anti-Join Strategy (Deduplication) If your script fails after copying data to S3 but before deleting it from StarRocks, the next run will try to copy the same data again. To prevent duplicates in S3, use an anti-join to only move data that is not already in S3. The Logic:
Copy code
sql
INSERT INTO iceberg_catalog.db.table
SELECT src.* 
FROM native_catalog.db.table src
LEFT JOIN iceberg_catalog.db.table dest 
  ON src.unique_id = dest.unique_id -- Requires a unique ID per row
WHERE dest.unique_id IS NULL       -- Only rows NOT already in S3
  AND src.customer_id = 'XYZ'
  AND src.ingestion_time < @job_start;
* Why this works: If the previous job succeeded in writing to S3 but failed to delete from StarRocks, the 
LEFT JOIN ... WHERE NULL
will find those rows in S3 and skip them. * Performance Note: Doing a join between a StarRocks native table and an Iceberg table is very efficient in StarRocks, as it can push down filters to the data lake. ────────── 2. Orchestrator: External (Airflow) vs. Internal (StarRocks Tasks) Feature | ***External Orchestrator (Airflow)*** | ***StarRocks Tasks (
SUBMIT TASK
)*** Per-Customer Loops | Excellent. You can use "Dynamic Task Mapping" to run 100 customers in parallel. | Difficult. You have to write a complex stored procedure or generate 100 separate task objects. Failure Recovery | Excellent. Built-in retries, alerting (Slack/Email), and clear UI to see where it failed. | Basic. You have to check 
information_schema.task_runs
to see errors. State Management | Easy. Can use XComs or external DBs to track "Last Archived Time." | Manual. You must maintain a StarRocks table for state tracking. Recommendation: Use an external orchestrator (Airflow/Dagster/Python). While StarRocks Tasks are great for simple internal SQL refreshes, your use case involves state management and conditional logic (checking if S3 move finished before deleting) across different customers. An external tool will be much easier to maintain and monitor. ────────── 3. Summary of the Robust Workflow To protect against all the risks you mentioned, your orchestrator should do the following for each customer: 1. Snapshot the time: 
SET @job_start = now();
2. Move to S3 (Idempotent): * Use 
INSERT INTO
(Append) with the Anti-Join logic mentioned above. _ _Why not 
INSERT OVERWRITE
?* Because 
OVERWRITE
replaces the whole partition. If customer A's data for Oct 1st is in S3, and you run an 
OVERWRITE
for customer B for Oct 1st, customer A's data might be deleted. 
INSERT INTO
+ 
Anti-Join
is safer for multi-tenant tables. 3. Delete from StarRocks: * 
DELETE FROM native_table WHERE customer_id = 'XYZ' AND ingestion_time < @job_start;
* Crucial: Use the exact same 
@job_start
time from step 1. This ensures you don't delete "late data" that arrived while the 
INSERT
was running. 4. Update State: Update your 
archival_log
table with the success status and the 
@job_start
timestamp. 4. What is the "hidden" 
ingestion_time
? In StarRocks, there isn't a secret system-managed column for this. You should add it yourself:
Copy code
sql
ALTER TABLE your_table ADD COLUMN ingestion_time DATETIME DEFAULT CURRENT_TIMESTAMP;
* Explicit is better: By making it an explicit column, you can use it in your 
WHERE
clauses and 
JOIN
conditions, and it is easily visible for debugging. References • integrations/airflow.md • loading/InsertInto.md
e
@Rocky if I was to say that we just moved all the customers data to s3 and delete it from starRocks (not per customer) -> would you then recommend using starrocks tasks or still not ?
r
e
@Rocky if I was to say that we just moved all the customers data to s3 and delete it from starRocks (not per customer) -> would you then recommend using starrocks tasks or still not ?
r
If you move data for all customers at once, the logic is much simpler, but I still recommend an external orchestrator (like Airflow or a Python script) over StarRocks Tasks. Here is why, and how you should implement it: 1. Why NOT StarRocks Tasks? StarRocks 
SUBMIT TASK
is powerful, but it has a major limitation for this specific workflow: A Task is limited to a single ETL statement (e.g., one 
INSERT
or one 
CTAS
). To safely archive data without losing late-arriving records, you need a multi-step sequence that uses the exact same timestamp: 1. Capture Time: 
SET @job_start = now();
2. Move Data: 
INSERT INTO iceberg ... WHERE ingestion_time < @job_start
3. Delete Data: 
DELETE FROM native ... WHERE ingestion_time < @job_start
If you use StarRocks Tasks, you would have to create two separate tasks. Since each task runs in its own session, you cannot share the 
@job_start
variable between them. If Task B starts 5 seconds after Task A, their 
now()
values will be different, creating a risk where you delete data that was never moved to S3. 2. Recommended Approach: Stored Procedure + External Trigger If you want to keep the logic "inside" StarRocks but need a reliable way to run it, use a Stored Procedure (available since v3.1) and trigger it from a simple external cron job or Airflow. A Stored Procedure allows you to use variables and multiple statements in one atomic-like block:
Copy code
sql
CREATE PROCEDURE archive_all_customers()
BEGIN
    -- 1. Capture the "Line in the Sand"
    SET @job_start = NOW();

    -- 2. Move data to S3 (Idempotent with Anti-Join)
    INSERT INTO iceberg_catalog.db.table
    SELECT src.* 
    FROM native_catalog.db.table src
    LEFT JOIN iceberg_catalog.db.table dest 
      ON src.unique_id = dest.unique_id 
    WHERE dest.unique_id IS NULL       
      AND src.ingestion_time < @job_start;

    -- 3. Delete the exact same range from Native Table
    DELETE FROM native_catalog.db.table 
    WHERE ingestion_time < @job_start;
END;
Why this is better: * Consistency: The 
DELETE
uses the same timestamp as the 
INSERT
. Late data is safe. * Simplicity: Your orchestrator just has to run one command: 
CALL archive_all_customers();
. * Idempotency: The 
LEFT JOIN ... WHERE NULL
ensures that if the procedure fails halfway and you run it again, it won't create duplicate rows in S3. 3. Comparison Summary Feature | StarRocks Tasks | Stored Procedure + Airflow/Cron Multi-statement support | No (Single SQL only) | Yes Shared Variables | No | Yes Visibility/Alerts | Basic (check system tables) | Excellent (Airflow UI/Slack) Complexity | Low | Medium Final Recommendation: Use Airflow (or a simple Python script) to call a StarRocks Stored Procedure. This gives you the best of both worlds: the logic stays close to the data in SQL, but you get the reliability, logging, and error alerting of a proper orchestrator. References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • sql-reference/sql-statements/stored-procedures/stored_procedure.md • integrations/airflow.md References • sql-reference/sql-functions/table-functions/files.md • unloading/arrow_flight.md • administration/management/Backup_and_restore.md • sql-reference/http_sql_api.md
e
@Rocky in this Stored Procedure approach can we pass it the customer id we want to to archive ? in case we do want to move/delete data per customer -> so monitoring and handling issues is easier also why didnt you suggest this Stored Procedure before ? is having the sql code to do this in the external trigger service better ?
r
Yes, you can absolutely pass parameters to a StarRocks Stored Procedure. If you want to move data per customer to make monitoring and handling failures easier, that is a very common and recommended pattern. 1. Stored Procedure with Parameters You can define a procedure that accepts a 
customer_id
(and even a time threshold) to make it reusable. Example Syntax:
Copy code
sql
CREATE PROCEDURE archive_customer_data(IN cid INT)
BEGIN
    -- 1. Capture the exact timestamp for this specific run
    SET @job_start = NOW();

    -- 2. Move data to S3 (Idempotent using Anti-Join)
    INSERT INTO iceberg_catalog.db.table
    SELECT src.* 
    FROM native_catalog.db.table src
    LEFT JOIN iceberg_catalog.db.table dest 
      ON src.unique_id = dest.unique_id 
    WHERE src.customer_id = cid              -- Parameter used here
      AND src.ingestion_time < @job_start
      AND dest.unique_id IS NULL;            -- Deduplication check

    -- 3. Delete from StarRocks
    DELETE FROM native_catalog.db.table 
    WHERE customer_id = cid                  -- Parameter used here
      AND ingestion_time < @job_start;
END;
To run it for a specific customer: 
CALL archive_customer_data(101);
────────── 2. Why didn't I suggest this before? Stored Procedures (introduced in StarRocks v3.1) are great, but there are three reasons why an external orchestrator (like Airflow) is usually suggested first for complex data movement: 1. Visibility & Error Handling: * If a loop inside a Stored Procedure fails at the 50th customer, it is hard to see exactly where it stopped without looking at internal logs. * An orchestrator like Airflow gives you a "green/red" UI for every single customer. If customer #50 fails, you can "retry" just that one task with a single click. 2. Resource Pressure: * The code in a Stored Procedure runs on the FE (Leader) node. If you run a massive loop with complex logic for thousands of customers, it can put unnecessary load on the FE. * An external orchestrator offloads the "loop logic" and "decision making" to a separate machine, only sending the final SQL to StarRocks. 3. Parallelism: * A Stored Procedure loop is typically serial (one customer after another). * An orchestrator can run 10 customers in parallel by opening 10 different database connections, making the total archival window much shorter. 3. The "Sweet Spot" Recommendation The best architecture is often a hybrid: * Logic inside StarRocks: Keep the 
INSERT
and 
DELETE
logic inside a Stored Procedure (like the one above). This ensures the SQL is version-controlled and tested within the DB. * Orchestration outside: Use Airflow/Python to loop through your customers and 
CALL
that procedure. Why this is the best of both worlds: * You get the shared variables (the 
@job_start
trick) and multi-step safety of the procedure. * You get the retries, parallel execution, and monitoring of Airflow. Summary If you are moving data for all customers in one giant query, a StarRocks Stored Procedure is perfect. If you are doing it per customer, use an external tool to 
CALL
that procedure for each customer. References • sql-reference/sql-statements/stored-procedures/stored_procedure.md • integrations/airflow.md • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md References • sql-reference/sql-functions/JAVA_UDF.md • sql-reference/sql-functions/Python_UDF.md
e
@Rocky im confused a bit -> you said that Stored Procedure put resource pressure in the FE and you said that it makes monitoring harder. so why you recommend Stored Procedure and external trigger if we want to archive per customer why not have the Stored Procedure sql logic also in the external trigger ? also are the sql steps Stored Procedure atomic ? meaning if we moved to s3 but not deleted from starRcoks then everything rolls back ? sounds like that cant be true since its 2 different systems (starrocks and s3)
r
You're asking great questions that get to the heart of "Database logic vs. Application logic." Let’s clear up the confusion. 1. Why use a Stored Procedure (SP) at all? The main reason to use an SP isn't performance—it’s logic encapsulation and session safety. * The "API" Pattern: By using an SP, the database provides a clear "Archive" interface. Your Airflow developer doesn't need to know the complex 
INSERT INTO ... LEFT JOIN ... WHERE
logic; they just call 
archive(customer_id)
. * Variable Consistency: Inside an SP, you can set a variable like 
@run_time = NOW()
. Because the 
INSERT
and the 
DELETE
both happen in the same session using that exact same variable, you are guaranteed to delete exactly what you just moved. If you do this in Python with two separate calls, you have to be very careful to pass the exact same timestamp string to both, or you might accidentally delete data that wasn't moved. However, if you prefer having the SQL in your external script, go for it. There is no performance penalty for doing so. If your team finds it easier to version-control SQL inside a Python/Airflow repo, that is a perfectly valid and common architectural choice. 2. Is it Atomic? (The "Rollback" Question) No, it is not atomic across StarRocks and S3. You are exactly right—because they are two different systems, StarRocks cannot "roll back" a file it already wrote to S3/Iceberg if the subsequent 
DELETE
fails. * If the 
INSERT
fails: The 
DELETE
won't run. This is safe. * If the 
INSERT
succeeds but the 
DELETE
fails: You will have "duplicate" data (the data exists in both StarRocks and S3). * If the 
DELETE
succeeds but then the script crashes: You are safe (the data is in S3). How we handle this "Lack of Atomicity": Since we can't have a true transaction, we use Idempotency. In the SQL I provided, there is a 
LEFT JOIN ... WHERE dest.id IS NULL
. This means if the job fails halfway and you run it again, StarRocks will see the data is already in S3 and won't move it again. It will then proceed to attempt the 
DELETE
again. 3. FE Pressure & Monitoring * FE Pressure: This is only a concern if you write a loop inside the Stored Procedure (e.g., 
FOR customer IN all_customers DO ...
). This makes the FE node do a lot of "thinking" and "waiting." * Monitoring: If you put a loop inside the SP, Airflow only sees one task: "Running." It won't know if you are on customer 5 or customer 500. The Winner: If you want to archive per customer, the best practice is: 1. Orchestrator (Airflow): Handles the loop and the list of customers. 2. SQL (either in SP or Python): Performs the "Move then Delete" for one customer at a time. This gives you a "Green/Red" status for every customer in your Airflow UI, makes retries easy, and keeps the StarRocks FE node happy. In summary: If you are comfortable managing the SQL strings in your external service, skip the Stored Procedure. The only thing you lose is the "clean API" feeling and some session-variable convenience. References • sql-reference/sql-statements/stored-procedures/stored_procedure.md • loading/SQL_transaction.md • data_source/catalog/iceberg/iceberg_catalog.md • integrations/airflow.md References • data_source/catalog/iceberg/DML.md • administration/management/FE_configuration.md • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md
e
@Rocky question, when we move the data to iceberg is it done by positions or by column names ?
r
By default, moving data to Iceberg via 
INSERT INTO
is done by position, but starting from StarRocks v3.4.0, you can choose to do it by name. 1. Default Behavior: By Position In all versions (v3.1+), StarRocks maps the columns in your 
SELECT
statement to the columns in the Iceberg table based on their order, regardless of their names. * Example: If your Iceberg table has columns 
(id, name, city)
and you run:
Copy code
sql
    INSERT INTO iceberg_table SELECT city_name, user_id, user_name FROM native_table;
StarRocks will attempt to put 
city_name
into the 
id
column, 
user_id
into 
name
, etc. This will likely cause a type error or data corruption. 2. New Behavior: By Name (v3.4.0+) If you are on version 3.4.0 or later, you can use the 
BY NAME
clause to ensure columns are matched correctly even if they are in a different order. * Example:
Copy code
sql
    INSERT INTO iceberg_table BY NAME
    SELECT city, name, id FROM native_table;
Even though the 
SELECT
order is different, StarRocks will automatically match the 
city
column in the source to the 
city
column in the Iceberg destination. Important Notes: * Partitions: If you are inserting into a partitioned Iceberg table, the destination columns must include the partition columns. * Missing Columns: If you don't use 
BY NAME
and your 
SELECT
statement has fewer columns than the destination table, the remaining columns will be filled with 
NULL
. * Explicit Column List: You can also manually control the mapping by providing a column list: 
INSERT INTO iceberg_table (id, city, name) SELECT id, city, name FROM ...
Recommendation: If you are on v3.4.0+, always use 
BY NAME
to make your archival scripts more robust against schema changes. References • loading/InsertInto.md • data_source/catalog/iceberg/DML.md • sql-reference/sql-statements/loading_unloading/INSERT.md References • data_source/catalog/iceberg/DML.md • loading/InsertInto.md • data_source/catalog/iceberg/iceberg_catalog.md
e
@Rocky I remember you said by NAME has a bug in the Files() pipe when loading data from s3 into starrocks it strips it away and does select *, does that happen also here ? also is it possible to do select * BY NAME - since we copy the entire rows to s3 and not just some columns in a row
r
You are exactly right to be cautious. Let’s clarify the 
BY NAME
behavior, the bug you remember, and the correct syntax for your archival process. *1. Is 
SELECT * BY NAME
possible?* There is no such syntax as 
SELECT * BY NAME
in a standalone query. The 
BY NAME
clause is a modifier for the 
INSERT
 statement, not the 
SELECT
statement. The correct syntax for your archive is:
Copy code
sql
INSERT INTO iceberg_catalog.db.table BY NAME
SELECT * FROM native_catalog.db.table
WHERE customer_id = 101;
In this case, 
BY NAME
tells StarRocks: "Look at the column names in the Iceberg table, and find columns with the same names in the results of the SELECT statement." 2. The 
FILES()
Bug vs. Your Archive The bug you are remembering specifically concerned the 
FILES()
table function (used for reading raw files like CSV/Parquet from S3 into StarRocks). * The Bug: In some versions, when using 
INSERT INTO ... BY NAME SELECT * FROM FILES(...)
, the schema inference of 
FILES()
and the 
BY NAME
mapping didn't always play nice. Specifically, if the 
FILES()
function inferred a schema slightly differently than the target table, the "push-down" of column names could fail or be ignored, falling back to positional mapping. * Does it happen here? No. When you are moving data from a StarRocks native table to an Iceberg table, both tables have explicit, pre-defined schemas in the StarRocks metadata. StarRocks doesn't have to "guess" or infer the schema from a raw file. The mapping is much more stable. *3. How 
SELECT *
works with `BY NAME`* When you use 
BY NAME
with 
SELECT *
, StarRocks follows a specific expansion rule (introduced/refined in v3.4): 1. Expansion: The 
*
is expanded based on the target table's column names. 2. Matching: It searches the source table for those specific names. 3. Result: * If a column exists in both: Data is moved. * If a column is in the Source but not the Target: It is ignored (this is what you likely meant by "stripping it away"). * If a column is in the Target but not the Source: It is filled with NULL. Recommendation for your Archive If you are copying the "entire row," 
SELECT * BY NAME
is the cleanest way to do it, provided your StarRocks table and Iceberg table were created with the same column names. One final safety tip: If you want to be 100% immune to mapping bugs, explicitly list the columns:
Copy code
sql
INSERT INTO iceberg_catalog.db.table (id, customer_id, data, ts) -- Target columns
SELECT id, customer_id, data, ts                                -- Source columns
FROM native_catalog.db.table;
This bypasses the 
BY NAME
logic entirely and uses explicit position-based mapping, which is the most "bulletproof" method across all StarRocks versions. Summary * Syntax: Use 
INSERT INTO ... BY NAME SELECT * ...
. * Stability: The "bug" was mostly a 
FILES()
inference issue; table-to-table movement is safe. * Behavior: It will automatically "strip away" columns from the source that don't exist in the target Iceberg table. References • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md • data_source/catalog/iceberg/DML.md • sql-reference/sql-functions/table-functions/files.md References • data_source/catalog/iceberg/DML.md • data_source/catalog/iceberg/iceberg_catalog.md
You are very observant. Let's clarify that specific 
FILES()
behavior and how it differs from your Iceberg archival use case. 1. The 
FILES()
"Bug" vs. Your Archive The issue you’re remembering with the 
FILES()
function (used for loading raw S3 files) is usually related to Schema Inference. * How it happens with `FILES()`: When you load from a raw file (like a CSV or Parquet), StarRocks has to "guess" or infer the column names. If that inference is slightly off, or if you are using a format like CSV without clear headers, the 
BY NAME
mapping can fail or fall back to positional matching because the names don't match exactly. * Why it's NOT an issue here: You are moving data from a StarRocks Native Table to an Iceberg Table. Both of these have explicit, fixed schemas defined in the database. StarRocks doesn't have to "infer" anything; it knows exactly what the column names are on both sides. The 
BY NAME
logic is extremely stable for table-to-table transfers. *2. Is 
SELECT * BY NAME
possible?* There is no syntax like 
SELECT * BY NAME
. The 
BY NAME
clause belongs to the 
INSERT
statement, not the 
SELECT
. The correct syntax for your archive is:
Copy code
sql
INSERT INTO iceberg_catalog.db.archive_table BY NAME
SELECT * FROM starrocks_db.original_table
WHERE customer_id = 101;
What this does: 1. StarRocks looks at all the column names in your 
original_table
. 2. It looks at all the column names in your 
archive_table
. 3. It matches them up. If 
original_table
has a column 
user_email
and 
archive_table
also has 
user_email
, it moves the data there, regardless of where that column is positioned (1st, 5th, or 10th). 3. Copying "Entire Rows" safely If you want to ensure you copy everything, 
INSERT ... BY NAME SELECT *
is the way to go, but keep these two details in mind: * Identical Schemas: If you want a perfect 1:1 copy, your Iceberg table should have been created using the same schema as the StarRocks table (e.g., via 
CREATE TABLE ... AS SELECT * FROM ... LIMIT 0
). * Extra Columns: If your StarRocks table has 10 columns but your Iceberg archive only has 8, the 
BY NAME
logic will automatically ignore the 2 extra columns. This is likely the "stripping away" you were thinking of—it’s actually a feature that prevents the query from failing if your source table grows over time but your archive doesn't need the new columns. Summary Recommendation For your archival process: 1. *Use `INSERT INTO ... BY NAME SELECT `: This is the safest way to handle row movement if you are on StarRocks v3.4.0+. 2. Explicit Columns (Optional): If you are on an older version or want to be "bulletproof," explicitly list the columns: 
INSERT INTO archive (col1, col2) SELECT col1, col2 FROM ...
. References • loading/InsertInto.md • data_source/catalog/iceberg/DML.md • sql-reference/sql-statements/loading_unloading/INSERT.md • sql-reference/sql-functions/table-functions/files.md References • loading/InsertInto.md • data_source/catalog/iceberg/DML.md • sql-reference/sql-statements/loading_unloading/INSERT.md References • sql-reference/sql-functions/table-functions/files.md
e
@Rocky what you said before about moving in parallel customers data, isnt that not possible since iceberg would not be able to handle that -all customers data sit in the same table -> each iceberg commit would cause the other to fail
r
You are absolutely correct that, by default, Apache Iceberg’s Optimistic Concurrency Control (OCC) can cause failures when multiple processes try to commit to the same table at once. However, StarRocks has introduced specific features to solve this exact problem. Here is how StarRocks handles concurrent writes to the same Iceberg table: 1. The Solution: Iceberg Commit Queue (v4.1.0+) Starting in StarRocks v4.1.0, a new feature called the Iceberg Commit Queue was introduced to solve this "race to commit" problem. * How it works: When enabled (
enable_iceberg_commit_queue = true
, which is the default), StarRocks creates a single-threaded executor for each Iceberg table. * Serialization: If you run 10 parallel 
INSERT
statements for different customers, StarRocks will write the data files in parallel (which is fast), but it will queue and serialize the metadata commits. * Benefit: This prevents the 
CommitFailedException
because only one commit is processed at a time for that specific table, ensuring every "customer" insert eventually succeeds without manual retries. 2. For Older Versions (v3.1 to v4.0) If you are on an older version of StarRocks, you do not have the automatic commit queue, and you are right—parallel commits can conflict. You have three ways to handle this: * A. Catalog Retries: You can increase the number of commit retries directly in your Iceberg Catalog configuration using Iceberg's native table properties. You can set these when creating the catalog or table:
Copy code
sql
    -- Example of setting retries in the catalog properties
    CREATE EXTERNAL CATALOG iceberg_catalog
    PROPERTIES (
        "type" = "iceberg",
        "iceberg.catalog.type" = "glue",
        "commit.retry.num-retries" = "10",
        "commit.retry.min-wait-ms" = "100",
        "commit.retry.max-wait-ms" = "2000"
    );
* B. Limit Concurrency: Instead of moving 100 customers at once, use a task orchestrator (like Airflow or a simple script) to limit the number of concurrent 
INSERT
statements to a small number (e.g., 3–5). * C. Write to Different Partitions: If your table is partitioned by 
customer_id
, Iceberg (depending on the catalog and isolation level) can sometimes handle concurrent appends to different partitions more gracefully, but the metadata commit at the end is still the bottleneck. 3. "Writing" vs. "Committing" It is important to distinguish between the two: * Data Writing (Parallel): Even if you have 10 
INSERT
statements, the actual writing of Parquet files to S3 happens in parallel across your CN (Compute Nodes). This is efficient and doesn't conflict. * Metadata Committing (The Bottleneck): The conflict only happens at the very end when StarRocks tells the Iceberg Catalog (Glue/Hive) to "swap" the metadata file. This is the part the Commit Queue serializes. Recommendation * If you are on v4.1+: Go ahead and move data in parallel. The system handles the queueing for you. * If you are on v3.x: Do not run too many parallel 
INSERT
statements at once. Set 
commit.retry.num-retries
to a higher value (like 10 or 20) in your catalog properties to give the optimistic locking a better chance to succeed. References • administration/management/FE_configuration.md • data_source/catalog/iceberg/iceberg_catalog.md • release_notes/release-4.0.md References • administration/management/FE_configuration.md • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/DML.md
e
@Rocky you said:
Copy code
-- 1. Capture the exact timestamp for this specific run
    SET @job_start = NOW();

    -- 2. Move data to S3 (Idempotent using Anti-Join)
    INSERT INTO iceberg_catalog.db.table
    SELECT src.* 
    FROM native_catalog.db.table src
    LEFT JOIN iceberg_catalog.db.table dest 
      ON src.unique_id = dest.unique_id 
    WHERE src.customer_id = cid              -- Parameter used here
      AND src.ingestion_time < @job_start
      AND dest.unique_id IS NULL;            -- Deduplication check

    -- 3. Delete from StarRocks
    DELETE FROM native_catalog.db.table 
    WHERE customer_id = cid                  -- Parameter used here
      AND ingestion_time < @job_start;
I assume in step 3 you meant AND eventstarttime >= '{dayStart}' AND eventstarttime < '{dayEnd}' AND ingestion_time < '{jobStart}'; right ? (you did not write the eventstarttime partition day boundaries ) another thing is this insert statement:
Copy code
INSERT INTO iceberg_catalog.db.table
    SELECT src.* 
    FROM native_catalog.db.table src
    LEFT JOIN iceberg_catalog.db.table dest 
      ON src.unique_id = dest.unique_id 
    WHERE src.customer_id = cid              -- Parameter used here
      AND src.ingestion_time < @job_start
      AND dest.unique_id IS NULL;            -- Deduplication check
how expensive is this left join, I mean if we got millions of rows in that partition would is cause starrocks memory usage to grow unbounded or maybe get a out of memory exception ? is it ok performance wise ? is there a trick like we did in the delete of using the partition boundaries but also the job_start boundary we can do here ? another question is if the left join is ok performance wise then why not in the delete instead of the job_start trick we just use the partition boundaries with a anti anti left join ?
r
You are correct on all points. Let's break down the logic and refine the archival script to be more performant and "production-ready." 1. Correcting the DELETE Step You are 100% right. In StarRocks, Partition Pruning is the single most important factor for performance. If you don't include the partition column (
eventstarttime
) in the 
DELETE
statement, StarRocks has to scan the metadata (and potentially the data) for every single partition in the table. The correct DELETE statement should be:
Copy code
sql
DELETE FROM native_catalog.db.table 
WHERE customer_id = cid
  AND eventstarttime >= '{dayStart}' 
  AND eventstarttime < '{dayEnd}'
  AND ingestion_time < @job_start;
────────── 2. Is the 
LEFT JOIN
expensive? (Performance & OOM) Joining millions of rows is what StarRocks was built for—it is an MPP (Massively Parallel Processing) engine. However, "millions" becomes "billions" over time in an archive. To prevent it from becoming expensive or hitting memory limits, you use the Partition Pruning Trick on the JOIN itself. The Performance Trick: Apply the same date filters to both the source and the target table. This ensures the 
LEFT JOIN
only compares today's data in StarRocks against today's data in Iceberg, rather than the entire history of the Iceberg table.
Copy code
sql
INSERT INTO iceberg_catalog.db.table
SELECT src.* 
FROM native_catalog.db.table src
LEFT JOIN iceberg_catalog.db.table dest 
  ON src.unique_id = dest.unique_id 
  -- VITAL: Prune the Iceberg side so the join is small
  AND dest.eventstarttime >= '{dayStart}' 
  AND dest.eventstarttime < '{dayEnd}'
WHERE src.customer_id = cid
  AND src.eventstarttime >= '{dayStart}'
  AND src.eventstarttime < '{dayEnd}'
  AND src.ingestion_time < @job_start
  AND dest.unique_id IS NULL;
* Memory: StarRocks uses a Hash Join. It will build a hash table of the `unique_id`s from the Iceberg side. Because you filtered by 
dayStart/End
, that hash table will only contain a few million IDs, which fits easily in memory (typically ~100MB per million IDs). ────────── 3. Why not use a JOIN in the DELETE? There are two reasons why we use the 
job_start
timestamp trick instead of an anti-join for the `DELETE`: A. Technical Limitation (Syntax) In StarRocks, the 
DELETE
statement is restricted based on the table model: * Duplicate/Unique/Aggregate Tables: Do not support 
JOIN
or 
EXISTS
subqueries in a 
DELETE
statement. They only support simple 
WHERE
conditions. * Primary Key Tables: These do support 
JOIN
in a 
DELETE
, but most archival tables (logs/events) are Duplicate Key tables for better write performance. B. Performance & Reliability Even if your table model supported it, a 
DELETE
with an anti-join against an external Iceberg table is risky: 1. Complexity: It requires StarRocks to query S3/Iceberg metadata just to perform a local delete. 2. Speed: Range-based deletes (using timestamps) are nearly instantaneous in StarRocks because it just marks a range of data as deleted in the metadata. A join-based delete requires a row-by-row comparison. 3. The "Safety Net": The 
job_start
trick is atomic for that run. It says: "I processed everything up to Time X. Now I am deleting everything up to Time X." It doesn't care what happened in Iceberg; it only cares about the state of the StarRocks table at the moment the job started. Summary of the Best Practice 1. Always use partition boundaries (
eventstarttime
) in every step (INSERT and DELETE). 2. Always apply those same boundaries to the 
LEFT JOIN
target to keep memory usage low. 3. The Timestamp Trick is used because it's the fastest way to achieve "At Least Once" delivery with a "Clean Up" step without needing complex, cross-catalog joins during a delete. References • sql-reference/sql-statements/table_bucket_part_index/DELETE.md
e
@Rocky question your saying: "Even if your table model supported it, a 
DELETE
with an anti-join against an external Iceberg table is risky: 1. Complexity: It requires StarRocks to query S3/Iceberg metadata just to perform a local delete. 2. Speed: Range-based deletes (using timestamps) are nearly instantaneous in StarRocks because it just marks a range of data as deleted in the metadata. A join-based delete requires a row-by-row comparison. 3. The "Safety Net": The 
job_start
trick is atomic for that run. It says: "I processed everything up to Time X. Now I am deleting everything up to Time X." It doesn't care what happened in Iceberg; it only cares about the state of the StarRocks table at the moment the job started." but for the insert we do that anti left join with iceberg thats external isnt that risky ? what do you mean by risky ?
r
This is a great architectural question. You are asking why a Join is "safe" for an 
INSERT
but "risky" for a 
DELETE
when both touch an external Iceberg table. The short answer is: One is a data movement operation (ETL), while the other is a data consistency operation. Here is the breakdown of why the "Risk" profile is different: 1. The "Risk" of Memory (OOM) You asked if millions of rows would cause an OOM. * For `INSERT`: This is "safe" because StarRocks is an MPP engine designed for massive joins. When you do the 
LEFT JOIN
during an 
INSERT
, StarRocks uses Distributed Hash Joins. If the Iceberg side is too large for memory, StarRocks uses Spill-to-Disk (v3.0.1+), which moves intermediate join data to disk to prevent OOM. * For `DELETE`: In many versions and table models (like Duplicate Key), StarRocks simply doesn't allow joins in a 
DELETE
statement. It was never optimized to handle a massive cross-catalog join inside a delete transaction. Even where allowed (Primary Key tables), a join-based delete is significantly heavier on the system than a range-based delete. 2. The "Risk" of Consistency (The Watermark Problem) This is the most important part. When you archive data, you are dealing with two different systems (StarRocks and S3/Iceberg) that don't share a single transaction manager. * The 
INSERT
Risk: The 
LEFT JOIN
in the 
INSERT
is your Deduplication Logic. Its only job is to make the archive "idempotent" (so you don't double-count if the job restarts). If S3 is slow or the join is heavy, the worst that happens is the 
INSERT
takes longer. * The 
DELETE
Risk: If your 
DELETE
relies on a join against Iceberg, you are creating a Circular Dependency. * Scenario: If the Iceberg 
INSERT
succeeded but the Iceberg metadata is lagging (S3 consistency or Catalog caching), the 
DELETE
join might "miss" rows that were actually written. * Result: You fail to delete the data from StarRocks, and it stays there forever. * The "Safety Net": By using the 
job_start
timestamp, the 
DELETE
becomes Self-Contained. It says: "I don't care what Iceberg thinks; I know I just sent everything up to 10:00 AM to Iceberg, so I am deleting everything in StarRocks up to 10:00 AM." This is much more robust than asking a different system "what do you have?" every time you want to clean up. 3. Structural Risks (Table Models) Most "Event/Log" tables in StarRocks are Duplicate Key tables because they have the fastest write speeds. * Duplicate Key Tables: These tables cannot perform a 
DELETE
with a join or subquery. They only accept simple 
WHERE
clauses (e.g., 
WHERE time < X
). * If you tried to use the "Join" method here, your code would simply crash with a syntax error. The timestamp trick is the only way to perform a precise "clean-up" on these high-performance tables. Summary: Why we do it this way Feature | 
INSERT
with Join | 
DELETE
with Join Purpose | Deduplication (Best effort) | Data Removal (Must be precise) Engine Support | Supported on all table types | Only on Primary Key tables Performance | High (uses MPP + Spilling) | Low (row-by-row metadata marking) Reliability | Depends on S3 speed | Risky (depends on S3 + Metadata sync) Better Way | 
LEFT JOIN
for safety | Timestamp Watermark for speed & logic My recommendation stands: Use the 
LEFT JOIN
in the 
INSERT
to prevent duplicates in your archive, but use the 
eventstarttime
+ 
ingestion_time < @job_start
range in the 
DELETE
to ensure your StarRocks table stays clean, fast, and consistent. References stays clean and fast. References • sql-reference/sql-statements/table_bucket_part_index/DELETE.md
e
@Rocky my table is a primary key, and you said "Even where allowed (Primary Key tables), a join-based delete is significantly heavier on the system than a range-based delete." -> what do you mean ? also the risk of "S3 consistency or Catalog caching" but after insert I can just do a catalog refresh no ?
r
Since you are using a Primary Key table, you have more flexibility than users on other table models. However, the trade-off between a "Join-based Delete" and a "Range-based Delete" (the timestamp trick) comes down to Engine Overhead and Logic Simplicity. Here is why the join is "heavier" and why the 
REFRESH
isn't always the perfect solution. 1. Why a Join-based Delete is "Heavier" In StarRocks, when you perform a 
DELETE
on a Primary Key table, the system must ultimately find the specific Primary Keys and mark them as deleted in the Primary Key Index (which is stored in memory). * Range-based Delete (The Timestamp Trick): * Execution: StarRocks scans the local metadata/data for the specified range (
eventstarttime
and 
ingestion_time
). * Workflow: It's a straight scan of the local StarRocks table to identify the keys to delete. No data from S3 is involved. It is a "local" operation. * Join-based Delete (
DELETE FROM src USING iceberg_table
): * Execution: StarRocks must launch a full MPP (Massively Parallel Processing) Job. * Steps: 1. It must reach out to the Iceberg Catalog (Glue/Hive). 2. It must read the Iceberg metadata files from S3. 3. It must potentially read data from S3 or at least a large list of IDs. 4. It must Shuffle (transfer data over the network between nodes) to perform the Join. 5. Once the join finds a match, it then proceeds to update the Primary Key Index. * Result: You are turning a simple internal cleanup into a cross-platform distributed join. For millions of rows, this consumes significantly more CPU, Memory, and Network I/O. 2. Can 
REFRESH EXTERNAL TABLE
solve the risk? Yes, running 
REFRESH EXTERNAL TABLE iceberg_catalog.db.table
after your 
INSERT
but before your 
DELETE
will solve the "metadata lag" or "caching" risk. It ensures StarRocks sees exactly what was just written to S3. However, this introduces new "Micro-Risks": 1. Latency: 
REFRESH
is a synchronous call that forces the Frontend (FE) to re-parse Iceberg manifests. If your Iceberg table has thousands of snapshots or files, this can take several seconds. 2. API Costs/Limits: If you are running this archival job frequently (e.g., every 5 minutes for many customers), constantly refreshing the catalog can put pressure on your Metadata Store (like AWS Glue) and increase S3 HEAD/LIST request costs. 3. Redundancy: If the 
INSERT
statement finished successfully, you already know the data is there. Asking the system to "Join with Iceberg to prove the data is there before I delete it" is logically redundant. 3. The "Purest" Workflow for Primary Key Tables If you want to use the Primary Key table features while keeping performance high, the recommended pattern is: 1. INSERT (The "Check"): Use the 
LEFT JOIN
here. This is where you verify if the data exists in Iceberg to prevent duplicates. 2. COMMIT: Ensure the 
INSERT
is fully committed. 3. DELETE (The "Cleanup"): Use the timestamp range. _ _Reasoning:* Since the 
INSERT
(with its anti-join) just finished, you have a "Logical Guarantee" that everything in StarRocks with 
ingestion_time < job_start
has been safely moved. * You don't need to "double-check" by joining again during the delete. Summary * Is a Join-based Delete possible? Yes, because you have a Primary Key table. * Is it recommended? Usually no, because it is an expensive way to achieve the same result as a range delete. * The Refresh: It makes the join "safe," but it doesn't make it "fast." My advice: Stick to the Timestamp Watermark for the 
DELETE
. It is the most "efficient" path for the StarRocks engine because it treats the deletion as a local maintenance task rather than a global data integration task. References
• sql-reference/sql-statements/table_bucket_part_index/DELETE.md • table_design/table_types/primary_key_table.md • data_source/catalog/iceberg/iceberg_catalog.md References • sql-reference/sql-statements/table_bucket_part_index/DELETE.md • table_design/table_types/primary_key_table.md • data_source/catalog/iceberg/iceberg_catalog.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • loading/InsertInto.md References • sql-reference/sql-statements/table_bucket_part_index/DELETE.md • table_design/table_types/primary_key_table.md • data_source/catalog/iceberg/iceberg_catalog.md • sql-reference/sql-statements/data-definition/REFRESH_EXTERNAL_TABLE.md • administration/management/resource_management/spill_to_disk.md • faq/operation_maintenance_faq.md • best_practices/partitioning.md • loading/InsertInto.md
e
@Rocky ok and how does it handle the insert left join ? if the join is huge it spills it to disk how does it avoid out of memory exception ? also how does this insert query handle a case where there are millions of rows to move to s3 -> it tries to do it in one call ? it streams it in one call ? it does it in batches ?
r
StarRocks handles large 
INSERT INTO ... SELECT
operations using its distributed MPP (Massively Parallel Processing) architecture. Here is how it manages memory and data movement for millions of rows. 1. How it avoids OOM during the Join StarRocks is designed for in-memory speed, but it has a built-in "safety valve" called Spill-to-Disk to prevent Out-of-Memory (OOM) exceptions when joins are too large for RAM. * Hash Join Mechanism: When you do a 
LEFT JOIN
, StarRocks builds a hash table from the "right" table (the Iceberg table). If you follow the partition pruning trick I mentioned earlier, this hash table only contains IDs for a specific day/partition, making it much smaller. * Spill-to-Disk (v3.0.1+): If the join is still too large and memory usage hits a threshold, StarRocks will automatically "spill" intermediate join data from memory to local disk (or S3/MinIO in v3.3+). This allows the query to complete even if the data is 10x larger than your RAM. * How to enable it:
Copy code
sql
    SET enable_spill = true;
    SET spill_mode = 'auto'; -- Triggers only when memory is low
2. How it moves data to S3 (One call or Batches?) It is neither a single API call nor a series of small SQL batches. It is a Distributed Parallel Stream. * Parallel Writing: Every BE (Backend) or CN (Compute Node) in your cluster participates. If you have 10 nodes, all 10 nodes will simultaneously open streams to S3. * Streaming to Files: StarRocks does not send the whole result set in one "blob." Instead, each node streams the data it is processing and writes it into Parquet or ORC files (the native Iceberg formats) directly on S3. * File Sizes: As it streams, StarRocks buffers data in memory until it reaches a target file size (e.g., 128MB). It then flushes that file to S3 and starts a new one. * Final Commit: Only after all nodes have finished writing their files does StarRocks send a final "commit" to the Iceberg Catalog (Glue/Hive). This commit records all the new file paths into the Iceberg metadata. 3. Performance & Scaling * Millions of rows: This is the "sweet spot" for StarRocks. Moving 10 million rows to Iceberg typically takes seconds to a minute, depending on your S3 bandwidth and node count. * Concurrency: Because it is distributed, if you find it's too slow, you can add more StarRocks nodes, and the throughput to S3 will scale linearly. * Memory Safety: The "Sink" (the part that writes to S3) also has memory limits. If the write speed to S3 is slower than the data being read, StarRocks will "backpressure" the query, slowing down the read to ensure the write buffer doesn't overflow memory. Summary: You don't need to manually batch the data. Just run the 
INSERT
once. StarRocks will handle the heavy lifting by splitting the work across the cluster and using its disk-spilling mechanism to protect your memory. References • administration/management/resource_management/spill_to_disk.md • data_source/catalog/iceberg/iceberg_catalog.md • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md References • faq/loading/Insert_into_faq.md • data_source/External_table.md • loading/InsertInto.md • loading/loading_introduction/troubleshooting_loading.md
StarRocks handles large-scale data movement (like moving millions of rows to Iceberg) through its MPP (Massively Parallel Processing) architecture. It is designed specifically to handle "millions of rows" without the manual batching that traditional databases might require. 1. How the Left Join avoids OOM (Spill-to-Disk) When you perform a 
LEFT JOIN
against a massive external Iceberg table, StarRocks uses a Hash Join. * The Challenge: Normally, a Hash Join builds a hash table in memory from the "right" table (the Iceberg table). If that table is 50GB and you only have 32GB of RAM, the node would crash (OOM). * The Solution (Spill-to-Disk): Since v3.0.1, StarRocks has a "Spill-to-Disk" feature for joins. * Partitioning: The engine splits the join data into smaller "partitions" (mathematically hashed buckets). * Spilling: If memory usage hits a threshold (e.g., 60-80% of the query limit), StarRocks "spills" some of these partitions to local disk or S3. * Processing: It keeps only the partitions it's currently processing in memory. Once one partition is finished, it swaps in the next one from disk. * How to enable it:
Copy code
sql
    SET enable_spill = true;
    SET spill_mode = 'auto'; -- Spills only when RAM is tight
2. How it moves data to S3: One Call vs. Streaming It is technically one single SQL call, but internally it is a Distributed Parallel Stream. * Parallel Writing: Every BE (Backend) or CN (Compute Node) in your cluster participates. If you have 20 nodes, all 20 nodes will simultaneously open connections to S3 and start writing. * Streaming, not Buffering: StarRocks does not wait for the entire 
SELECT
to finish before it starts writing to S3. As soon as a node gets its first batch of results from the Join, it starts streaming that data into a Parquet file on S3. * Buffered "Batches" (The File Level): * Each node buffers data in a small memory buffer (e.g., 64MB or 128MB). * When the buffer is full, it "flushes" it to S3 as a file. * Therefore, moving 100 million rows results in hundreds of files being written to S3 in parallel across your entire cluster. * Atomic Commit: Even though thousands of files are written, they are not "visible" to other Iceberg users until the very end. Once all nodes finish writing, the StarRocks FE (Frontend) sends a final "Commit" to the Iceberg Catalog (Glue/Hive/Rest) to add all those files to a new Snapshot. 3. Why this is safer than manual batching If you tried to move 10 million rows in 10 separate batches of 1 million: 1. Metadata Overhead: You would create 10 separate Iceberg Snapshots. 2. Small File Problem: You would likely end up with thousands of tiny files on S3, which makes future queries very slow. 3. Complexity: You would have to track which rows moved and which didn't if one batch failed. StarRocks' approach is "Set and Forget": You run one command, the MPP engine handles the memory safety (via spilling) and the throughput (via parallel S3 streams), and the transaction ensures that either all 10 million rows move or none do. Summary * OOM Prevention: Managed by 
enable_spill = true
. * Data Movement: A distributed stream across all nodes. * Scalability: If it’s too slow, add more Compute Nodes; the write speed to S3 scales linearly because each node writes its own files independently. References • administration/management/resource_management/spill_to_disk.md • data_source/catalog/iceberg/DML.md • loading/InsertInto.md • knowledge/usage/best-practice.md References
e
@Rocky we are in shared-nothing mode -> so we got be nodes, can also add compute nodes so the moving of data will faster ? or its not possible to have both be and compute nodes in shared-nothing mode ?
r
In StarRocks, the architectural choice between Shared-Nothing and Shared-Data determines how you should scale your cluster. 1. Can you have both BE and CN nodes in Shared-Nothing mode? Short Answer: Technically it is possible to start them, but it is not recommended and generally not supported for production shared-nothing clusters. * Shared-Nothing (Standard): Uses Backend (BE) nodes. These nodes handle both data storage (on local disks) and computation. * Shared-Data (Cloud-Native): Uses Compute Nodes (CN). These are stateless nodes that only handle computation and caching, while the data lives permanently on S3 or HDFS. The official documentation states: "Adding CN nodes to shared-nothing clusters is not recommended and may lead to unknown behaviors." If you are in shared-nothing mode, your cluster is designed to scale by adding more BE nodes. 2. Will adding nodes make the data movement faster? Yes. Because StarRocks uses an MPP (Massively Parallel Processing) architecture, the archival task (
INSERT INTO iceberg_table SELECT ...
) is distributed across all available nodes. If you add more BE nodes to your shared-nothing cluster: * Increased Join Throughput: The 
LEFT JOIN
used to check for existing data will be partitioned across more CPUs and RAM. * Faster S3 Writing: Each BE node opens its own parallel streams to S3. If you have 3 BEs, you have 3 nodes writing to S3; if you add 3 more, you have 6 nodes writing in parallel, effectively doubling your potential write bandwidth. * Memory Safety: More nodes mean the "Hash Table" for your join is spread across more total cluster RAM, reducing the need for the "Spill-to-Disk" mechanism. Recommendation for Your Setup Since you are already in Shared-Nothing mode: 1. Do not add CN nodes. Stick to the BE architecture to keep your metadata and data management stable. 2. Scale with BE nodes: If your archival job is taking too long or hitting memory limits, adding more BE nodes is the correct way to increase speed. 3. Data Rebalancing: Note that when you add BEs in shared-nothing mode, StarRocks will automatically start rebalancing your local "Primary Key" data replicas to the new nodes in the background. This ensures that future archival jobs are perfectly balanced across the new hardware. Summary: To speed up the "move to S3" task, add more BE nodes. This provides the extra "compute juice" you need while staying within the supported architecture of your cluster. References • deployment/deploy_manually.md • introduction/Architecture.md • administration/management/BE_management.md • loading/InsertInto.md References • deployment/deploy_manually.md • introduction/Architecture.md • administration/management/BE_management.md • loading/InsertInto.md • quick_start/shared-nothing.md • deployment/plan_cluster.md References • introduction/Architecture.md • quick_start/shared-nothing.md • deployment/deploy_manually.md • quick_start/shared-data.md
e
@Rocky question regarding this approach of having a ingest_time column when using Files pipe to load data from s3 parquets, you said the BY NAME, select specific columns has a bug that the engine strips away that and puts just select * so if the parquet does not have the ingest_time column - since starrocks should give that the value how do I handle this ?
r
If you are concerned about the column selection being stripped (which can happen in some early versions of the 
PIPE
or when using specific schema-inference settings), you can handle the 
ingest_time
column safely using one of the following three methods. 1. Best Approach: Use Table Defaults This is the most "bug-proof" method because it doesn't depend on the SQL selection list in the 
INSERT
or 
PIPE
statement. When you define your StarRocks table, set a default value for the 
ingest_time
column.
Copy code
sql
CREATE TABLE my_table (
    id INT,
    data_value STRING,
    ingest_time DATETIME DEFAULT CURRENT_TIMESTAMP  -- <--- Set default
) 
ENGINE=OLAP
DISTRIBUTED BY HASH(id);
Now, when you load data, you only need to select the columns that actually exist in the Parquet file. StarRocks will automatically populate 
ingest_time
with the current system time during the write.
Copy code
sql
-- Even if the engine forces "SELECT *", ingest_time gets the default value
INSERT INTO my_table (id, data_value)
SELECT id, data_value FROM FILES(
    "path" = "<s3://bucket/data.parquet>",
    "format" = "parquet",
    ...
);
2. Using 
columns_from_path
(If the time is in the URL) If your S3 files are organized by date (e.g., `s3://bucket/2024-07-11/data.parquet`), you can use the 
columns_from_path
parameter. This is a native feature of the 
FILES()
function and is not stripped by the engine.
Copy code
sql
SELECT *, ingest_date FROM FILES(
    "path" = "<s3://bucket/dt=*/data.parquet>",
    "format" = "parquet",
    "columns_from_path" = "dt", -- Extracts '2024-07-11' into the column 'dt'
    ...
);
3. Explicit Column Listing (Standard SQL) If your StarRocks version is 3.1 or higher, the engine is generally stable with explicit column lists. You can bypass the 
*
entirely by listing exactly what you want.
Copy code
sql
INSERT INTO my_table (col1, col2, ingest_time)
SELECT col1, col2, now() 
FROM FILES(
    "path" = "<s3://bucket/data.parquet>",
    "format" = "parquet",
    ...
);
Regarding the "Bug": If you encountered an issue where 
SELECT col1, col2
was turned into 
SELECT *
, it was likely due to Schema Inference trying to "correct" the query to match the file's physical structure. * To prevent this, ensure you are using StarRocks v3.2.4+ or v3.3+, where the 
FILES()
function's interaction with the SQL planner was significantly improved. * If you are on an older version, the Method 1 (Table Defaults) is your safest bet because the default value is handled at the storage layer, not the query planning layer. Summary of "How to Handle Missing Columns" * Target column exists in StarRocks but NOT in Parquet: Use 
DEFAULT CURRENT_TIMESTAMP
in the table DDL. * Parquet has columns NOT in StarRocks: Use 
SELECT col1, col2
to pick only what you need. * Want to ensure strict matching: Set the session variable 
SET enable_insert_strict = true;
to catch any schema mismatches early. References • sql-reference/sql-functions/table-functions/files.md • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/PIPE.md • loading/Etl_in_loading.md References • sql-reference/sql-functions/table-functions/files.md • loading/InsertInto.md
e
@Rocky when I do this, move the data -> the data has a column called state that the ui user can change (open/close) -> he can do that for hot data/cold data. when moving the data from starrocks to iceberg s3, how do I make sure the ui does not know this and all works as normal with no down time
r
To ensure a seamless UI experience where users can toggle the 
state
of both "hot" and "cold" data without downtime or performance lag, you should use a Unified View Architecture with a State Override Pattern. Since updating data directly on S3 (Iceberg) is slower and StarRocks currently supports 
DELETE
and 
INSERT
but not high-concurrency 
UPDATE
on external tables, the following pattern is the industry standard for StarRocks users. 1. The Architecture: "State Override" Instead of trying to "reach into" S3 to change a single byte in a Parquet file every time a user clicks a button, you keep the bulk data on S3 and the "latest state" in a small, fast internal StarRocks table. * Table A (Cold Data): Your Iceberg table on S3. Contains millions/billions of rows. The 
state
here is the "last known state" when it was archived. * Table B (State Overrides): A small StarRocks Primary Key table stored locally. It only contains two columns: 
id
and 
current_state
. * The View (The UI's Target): A SQL view that joins them. 2. Implementation Steps Step 1: Create the Override Table This table lives on your BE nodes and handles the high-concurrency UI updates.
Copy code
sql
CREATE TABLE state_overrides (
    id BIGINT,
    current_state STRING,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP
) 
PRIMARY KEY (id)
DISTRIBUTED BY HASH(id);
Step 2: Create the Unified View This is what your UI will query. It uses a 
LEFT JOIN
to check if there is a "new" state in the local table; if not, it falls back to the archived state on S3.
Copy code
sql
CREATE VIEW unified_data_view AS
SELECT 
    cold.id,
    cold.other_columns,
    -- If override exists, use it; else use the state from S3
    COALESCE(overrides.current_state, cold.state) AS state
FROM iceberg_catalog.db.cold_table AS cold
LEFT JOIN default_catalog.db.state_overrides AS overrides 
  ON cold.id = overrides.id;
3. How it handles the UI actions * Reading: When the UI does 
SELECT * FROM unified_data_view
, StarRocks pulls the bulk data from S3 (accelerated by Data Cache) and joins it with the memory-resident Primary Key index of the override table. This is extremely fast. * Updating: When a user clicks "Close", the UI runs:
Copy code
sql
    INSERT INTO state_overrides (id, current_state) VALUES (123, 'closed');
This is a local metadata update in StarRocks. It takes milliseconds. The next time the view is queried, the 
COALESCE
will immediately show the new state. 4. Benefits of this approach * No UI Downtime: The UI doesn't care where the data is. It just talks to the 
VIEW
. * Zero S3 Writes on Toggle: You don't have to rewrite Parquet files on S3 just to change one 
state
column. * Consistency: Because 
state_overrides
is a Primary Key table, it handles upserts natively. If a user changes the state 10 times, only the latest one exists. 5. Maintenance (Baking the data back) Every month (or whenever your override table gets too large), you can "bake" the new states back into S3 and clear the override table: 1. Run 
INSERT OVERWRITE iceberg_table SELECT ... FROM unified_data_view
. 2. 
TRUNCATE TABLE state_overrides
. Summary of Feature Support * StarRocks v3.1+: Supports 
INSERT INTO/OVERWRITE
for Iceberg. * StarRocks v4.1+: Supports 
DELETE
for Iceberg. * Primary Key Tables: Essential for the "Override" table to ensure sub-second UI responsiveness. References • data_source/catalog/iceberg/DML.md • table_design/table_types/primary_key_table.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References
e
@Rocky already tried this, the join would be too long since both tables can have million of rows (and this is something the user can also sort by), the decision for performance was to have the state in the data table
r
If the join is too slow for your scale (especially when sorting by the 
state
column), and you need to keep the 
state
column directly in the data table for performance, here is how you can handle updates on Iceberg without downtime. As of mid-2026 (StarRocks v3.4/v4.0), StarRocks has introduced more advanced DML for Iceberg, but row-level 
UPDATE
on S3 still carries more latency than local tables. Here are the two most effective ways to achieve your goal: 1. Use the "Partition Overwrite" Pattern If your data is partitioned (e.g., by 
event_date
or 
region
), you can update the state of rows within a specific partition very efficiently without rewriting the entire table. * How it works: When a user changes a state, you perform an 
INSERT OVERWRITE
for just that partition. * Performance: StarRocks will only rewrite the Parquet files for that specific partition on S3. * Zero Downtime: Because Iceberg is transactional, users will see the "old" state until the very millisecond the "new" partition is committed. There is no point where the data is missing.
Copy code
sql
-- Example: Update all 'open' to 'closed' for a specific day
INSERT OVERWRITE iceberg_catalog.db.orders 
PARTITION (dt = '2024-07-11')
SELECT id, data, 
       CASE WHEN id = 123 THEN 'closed' ELSE state END as state, 
       dt
FROM iceberg_catalog.db.orders 
WHERE dt = '2024-07-11';
2. The "Update-on-Column" (UoC) Feature (New) In newer versions (v3.4+), StarRocks has begun supporting Update-on-Column for Iceberg. This is a game-changer for your specific case because it avoids rewriting the "heavy" data columns. * How it works: When you update the 
state
column, StarRocks doesn't rewrite the entire row. Instead, it writes a small "companion file" (ColumnFile) that contains only the new 
state
values and their row positions. * Why it's fast: If your table has 100 columns but you only change 
state
, the I/O is reduced by ~99%. * UI Experience: The UI user sees a standard 
UPDATE
speed, and the sorting performance remains high because the engine merges these columns in memory during the scan. 3. Alternative: Move to StarRocks "Shared-Data" Mode If your "cold" data still requires frequent updates and high-speed sorting, you might be outgrowing the standard Iceberg External Table approach. Consider using StarRocks Shared-Data (Cloud-Native) Tables instead of Iceberg: * Storage: Data lives on S3 (just like Iceberg), keeping costs low. * Performance: It uses StarRocks' native format, which supports Primary Key updates natively on S3. * No Joins: The 
state
column is part of the main table. Updates are sub-second. * Sorting: You can define 
state
as a sort key, making your UI's "sort by state" extremely fast. Summary Recommendation 1. Check your version: If you are on v3.4+, check if 
UPDATE
is enabled for your Iceberg catalog. 2. If you are on an older version: Use the Partition Overwrite method to update data in chunks. 3. For maximum performance: If the "state toggle" happens frequently across the whole 100M+ row dataset, the Shared-Data native table is the only way to get "local-disk" performance while keeping the data on S3. References • data_source/catalog/iceberg/DML.md • administration/management/resource_management/Data_Cache.md • table_design/table_types/primary_key_table.md • loading/InsertInto.md References • data_source/catalog/iceberg/DML.md • loading/InsertInto.md • introduction/Architecture.md
To achieve high performance for sorting and filtering by the 
state
column across millions of rows stored on S3 (Iceberg), the "View + Join" approach often fails because the 
COALESCE
function prevents the engine from using the sort-key indexes, and S3 latency makes the join expensive. To keep the 
state
column "inside" the data table for performance while ensuring the UI works without downtime, you should use an Asynchronous Materialized View (MV) as a high-performance cache. The "Accelerated Lake" Pattern This pattern gives you the speed of a local StarRocks table while keeping the primary data in Iceberg. 1. Create the Local Override Table Keep the small Primary Key table to handle the high-concurrency UI updates.
Copy code
sql
CREATE TABLE state_overrides (
    id BIGINT,
    current_state STRING
) 
PRIMARY KEY (id)
DISTRIBUTED BY HASH(id);
2. Create an Asynchronous Materialized View Instead of a simple SQL view, create an MV. This physically materializes the joined data onto your BE nodes' local disks. Crucially, you can define a Sort Key on the MV.
Copy code
sql
CREATE MATERIALIZED VIEW unified_data_mv
-- This makes the 'state' column physically sortable on local disk
ORDER BY (state, id) 
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (1 MINUTE) -- Adjust frequency as needed
AS
SELECT 
    cold.id,
    cold.other_columns,
    COALESCE(overrides.current_state, cold.state) AS state
FROM iceberg_catalog.db.cold_table AS cold
LEFT JOIN default_catalog.db.state_overrides AS overrides 
  ON cold.id = overrides.id;
3. Why this solves your performance issue: * Local Sort Speed: When the user sorts by 
state
in the UI, StarRocks queries the MV. Since the MV is stored on local BE disks and is 
ORDER BY state
, the query is near-instant (milliseconds), even for millions of rows. * No UI Changes: Your UI continues to query a single object (
unified_data_mv
). It doesn't need to know about the S3/Local split. * Automatic Refresh: StarRocks handles the background sync between S3 and the local MV. ────────── If you are on StarRocks v4.1+ (Native Iceberg DML) Starting with v4.1, StarRocks supports native 
DELETE
on Iceberg. If your performance bottleneck was purely the join, and you want to avoid an MV, you can do this: 1. Enable Data Cache: Ensure 
enable_scan_datacache = true
so the Iceberg data is cached on BE disks. 2. Update via Delete+Insert: When a user toggles a state, your backend runs:
Copy code
sql
    -- Atomic update equivalent in Iceberg v2
    DELETE FROM iceberg_catalog.db.data_table WHERE id = 123;
    INSERT INTO iceberg_catalog.db.data_table VALUES (123, ..., 'closed');
This keeps the state directly in the Iceberg table. However, sorting on Iceberg data will still be slower than the MV approach above because Iceberg doesn't support the same level of local primary-key indexing that an internal StarRocks MV does. Recommendation For the best UI performance (sorting/filtering), use the Asynchronous Materialized View. It bridges the gap by providing "Hot" performance for "Cold" data while keeping the UI logic simple. References • data_source/catalog/iceberg/DML.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • table_design/table_types/primary_key_table.md • using_starrocks/caching/data_cache.md References • data_source/catalog/iceberg/iceberg_catalog.md • using_starrocks/caching/query_cache.md
e
@Rocky if i have the data table with the state column and move data to iceberg s3 as we agreed in this thread, so how to make the ui experience good ? im using starrocks 4.1.1
r
In StarRocks v4.1.1, you have access to several new features that solve the "Iceberg sorting performance" and "DML" problems that were roadblocking you in earlier versions. To ensure the UI continues to work seamlessly with millions of rows across S3 (Iceberg) and local storage, you should use the Unified View with Sort-Key Alignment pattern. 1. The v4.1.1 Strategy: Direct Iceberg DML Since StarRocks v4.1 supports native 
DELETE
on Iceberg, you no longer need the complex "Override Table" join if you want the state to live inside the data table. You can perform updates directly on the Iceberg table. * Requirement: Ensure your Iceberg table is v2 or v3 (v4.1.1 adds support for Iceberg v3 and default values). * The Update Workflow: Since 
UPDATE
is typically implemented via a delete and insert in the lakehouse, you can now run:
Copy code
sql
    -- Transactional delete in 4.1.1
    DELETE FROM iceberg_catalog.db.data_table WHERE id = 123;
    -- Re-insert with the new state
    INSERT INTO iceberg_catalog.db.data_table SELECT ..., 'closed' FROM source;
Note: StarRocks 4.1.1 handles the snapshot commit, so the UI sees the change atomically (no downtime). 2. Solving the "Sorting is too slow" Problem The reason your previous join/view approach felt slow for sorting was likely because the external table didn't have a Sort Key defined. In v4.1.1, you can define a physical sort order for Iceberg tables. When you move data to S3, create the Iceberg table with an 
ORDER BY
clause:
Copy code
sql
CREATE TABLE iceberg_catalog.db.data_table (
    id BIGINT,
    state STRING,
    ...
) 
ORDER BY (state, id) -- This physically sorts the Parquet files on S3
PROPERTIES ("format-version" = "2");
When the user sorts by 
state
in the UI, StarRocks uses the Iceberg metadata to skip files and performs a much faster sorted scan. 3. Making it Transparent for the UI To keep the transition from Hot (Internal) to Cold (Iceberg) invisible to the UI, use a Simple View with Partition Pruning. 1. Partition by Age: Both your local table and your Iceberg table should use the same partition key (e.g., 
event_date
). 2. The View:
Copy code
sql
    CREATE VIEW unified_table AS
    SELECT * FROM local_hot_table WHERE event_date >= '2024-07-01'
    UNION ALL
    SELECT * FROM iceberg_catalog.db.cold_table WHERE event_date < '2024-07-01';
3. Why this is fast in 4.1.1: * Partition Pruning: If the UI filters by date, StarRocks only hits one of the tables. * Sort Key Alignment: If both tables are sorted by 
state
, the 
UNION ALL
can often be optimized. * Data Cache: Ensure you have 
enable_scan_datacache = true
. This will keep the S3 data on the BE's local SSDs, making the Iceberg table perform nearly as fast as a local table. Summary for v4.1.1 * Use Iceberg v2/v3 to enable the 
DELETE
command. * Define 
ORDER BY (state)
 on the Iceberg table to fix the UI sorting performance. * Enable Data Cache to eliminate S3 latency for "cold" data. * Use 
DELETE
+ 
INSERT
 to update the 
state
column. Even though it's on S3, v4.1.1's optimized sink and global shuffle make this significantly faster than in older versions. References • data_source/catalog/iceberg/DML.md • release_notes/release-4.0.md (Core 4.x features) • data_source/catalog/iceberg/iceberg_catalog.md References • release_notes/release-4.0.md • data_source/feature-support-data-lake-analytics.md • release_notes/release-2.1.md • release_notes/release-3.5.md
In StarRocks v4.1.1, you can achieve a "local-table" experience for data stored on S3 (Iceberg) by leveraging three specific features: Native Iceberg DML, Physical Sort Keys, and the Data Cache. Here is the architecture to ensure your UI remains fast (especially for sorting) and supports "state" toggles with zero downtime. 1. The Storage Strategy Since you want the UI to sort by 
state
efficiently, you must ensure the data on S3 is physically ordered. In StarRocks 4.x, you can define a sort key for Iceberg tables. * Hot Data: Use a StarRocks Primary Key table (Internal). This allows for sub-second updates when the user toggles the UI button for recent data. * Cold Data: Move data to an Iceberg v2 Table on S3. When creating this table, use the 
ORDER BY
clause to align with your UI's sorting requirements.
Copy code
sql
-- Create the Cold (Iceberg) table with a Sort Key
CREATE TABLE iceberg_catalog.db.cold_data (
    id BIGINT,
    state STRING,
    event_time DATETIME,
    ...
) 
PARTITION BY (day(event_time))
ORDER BY (state, event_time) -- Crucial for UI sorting performance
PROPERTIES ("format-version" = "2");
2. The Unified UI Layer (No Downtime) Create a View that combines both tables. The UI only queries this view. Because StarRocks supports Partition Pruning across views, if the UI filters by time, it will only hit the necessary storage.
Copy code
sql
CREATE VIEW unified_records AS
SELECT * FROM default_catalog.db.hot_data
UNION ALL
SELECT * FROM iceberg_catalog.db.cold_data;
3. Toggling "State" in v4.1.1 StarRocks v4.1 introduces Native DELETE for Iceberg. If a user changes the state of "Cold" data, your backend can now perform this change directly in the lake. * For Hot Data: Run a standard 
UPDATE hot_data SET state = 'closed' WHERE id = 123;
(Instant). * For Cold Data (Iceberg): Since v4.1.1 supports transactional deletes, you can perform the state change by deleting the old record and inserting the new one. This is atomic—the UI will not see "missing" data during the process.
Copy code
sql
-- Atomic update for Cold Data in v4.1.1
BEGIN;
DELETE FROM iceberg_catalog.db.cold_data WHERE id = 456;
INSERT INTO iceberg_catalog.db.cold_data VALUES (456, 'closed', ...);
COMMIT;
4. Performance Tuning for the UI To make the Iceberg table feel as fast as a local table for the UI user: 1. Enable Data Cache: This is the most important step. It caches S3 Parquet blocks on your BE nodes' local SSDs.
Copy code
sql
    SET GLOBAL enable_scan_datacache = true;
2. Iceberg Metadata Caching: Ensure the FE is caching Iceberg metadata so that "scanning" the lake doesn't require constant S3 requests.
Copy code
sql
    -- In fe.conf
    enable_iceberg_metadata_disk_cache = true
3. Sort Key Pushdown: Because you created the Iceberg table with 
ORDER BY (state)
, StarRocks will use the Iceberg min/max statistics to skip files entirely when the UI filters or sorts by state. Why this works: * Sorting: Unlike your previous attempt with a 
JOIN
(where the join broke the index), the 
UNION ALL
view allows StarRocks to use the Internal Sort Key for hot data and the Iceberg Sort Key for cold data simultaneously. * State Changes: The UI doesn't need to know the data moved. It sends an update request; your backend determines if it's "Hot" or "Cold" based on the ID/Date and executes the appropriate DML. * No Latency: With the Data Cache enabled, once a user views a page of data, the subsequent sorts and filters will happen at local SSD speeds, or near, local disk speeds. References • data_source/catalog/iceberg/DML.md • data_source/catalog/iceberg/DDL.md • data_source/data_cache.md • release_notes/release-4.0.md
e
@Rocky if I was to use shared-data mode and the state column is in the data table , how would updating a state of an exisitng data work ? I mean it sits in s3 and starrocks table right ? so if it updates in one place but fails in the other ?
r
In StarRocks v4.1.1 shared-data mode, there is a common misunderstanding about where data "sits." Here is how it works and how to handle the update consistency you're worried about. 1. Shared-Data Mode: "One Table, One S3" In shared-data mode, your StarRocks internal table is already on S3. It doesn't live in two separate places (like a "local" database and a "remote" S3). * The Reality: The data files (in StarRocks' internal format) are stored on S3. The Compute Nodes (CN) only keep a temporary cache on their local SSDs for speed. * The Update Mechanism: When you run 
UPDATE table SET state = 'closed' WHERE id = 123
, StarRocks performs an atomic transaction. It writes the new data to S3 and updates its metadata in the FE (Frontend). * Atomicity: It cannot "update in one place but fail in the other" because there is only one source of truth: the StarRocks metadata on S3. If the write to S3 fails, the transaction rolls back, and the UI continues to see the old state. ────────── 2. The Conflict: Internal Table vs. Iceberg Table If you are using the "Hot/Cold" architecture we discussed earlier (Internal Table for recent data + Iceberg for old data), you are dealing with two different catalogs. * Consistency Risk: In v4.1.1, a single SQL transaction (
BEGIN...COMMIT
) cannot span multiple catalogs. You cannot atomically update a record in an internal StarRocks table and a record in an Iceberg table at the exact same time. * Failure Scenario: If your application tries to update "state" in both tables and the second one fails, you will have inconsistent data. ────────── 3. The Recommended Solution for v4.1.1 Since you are using v4.1.1, you should consider moving away from the Iceberg split if state updates and sorting performance are your top priorities. Option A: Use a single "Cloud-Native" Internal Table (Best for consistency) Instead of moving older data to Iceberg, keep it in a StarRocks Primary Key table in shared-data mode. * Benefit: All data (hot and cold) is physically on S3 (low cost), but StarRocks manages it as one table. * Updates: Updates are 100% atomic. * Sorting: You get the full performance of StarRocks' internal indexing/sorting, which is significantly faster than Iceberg. * Downtime: Zero. Option B: If you MUST use Iceberg (for external tools) If other tools (like Spark or Presto) need to read the "cold" data, you have to accept that StarRocks cannot provide a cross-catalog transaction. To handle this: 1. Status-Based Updates: Your backend should update the Internal table first. Only if that succeeds, it should call the update on the Iceberg table. 2. View Logic: Use a 
COALESCE
or 
UNION
view that prioritizes the "Hot" table so that even if the Iceberg update is slightly delayed, the UI shows the correct state from the Hot table. Summary In shared-data mode, the internal StarRocks table is your safest bet. It gives you the S3 cost savings you want, the atomic updates you need, and the UI sorting performance that Iceberg often struggles with. References • deployment/shared_data/s3.md • loading/SQL_transaction.md • table_design/table_types/primary_key_table.md • data_source/catalog/iceberg/DML.md References • release_notes/release-2.1.md • data_source/catalog/iceberg/DML.md • release_notes/release-4.0.md • loading/SQL_transaction.md
e
@Rocky but in shared-data we read last x days from cache right ? so if it was updated in s3 when reading from cache we will see wrong state
r
In shared-data (cloud-native) mode, your concern about seeing "wrong state" due to caching is a common one, but StarRocks is designed to prevent this. Here is why you will always see the latest state, even with local caching enabled: 1. Data is Immutable (No "In-Place" Updates) StarRocks does not update existing files on S3. When you update a "state" column in a Primary Key table: * The Write: StarRocks writes the new version of the row to a brand-new segment file on S3. * The Delete: It creates a new Delete Vector (DelVector) that marks the old row in the old file as "deleted." * The Metadata: The FE (Frontend) increments the table's version number. 2. How the Cache Stays Consistent The Data Cache (on your BE/CN local disks) uses a cache key that includes the File ID and Modification Time. * New Data: Since the updated row is in a new file, it has a new File ID. The cache won't have it yet, so StarRocks fetches it from S3 and adds it to the cache. * Old Data: The old row might still be in your local cache, but StarRocks' query engine is version-aware. Before reading any data, it checks the latest Delete Vector. Because the DelVector tells the engine that the old row is now invalid, the engine simply skips that cached block. 3. The Role of the FE (Source of Truth) The FE acts as the "traffic cop." Every query first asks the FE: "What are the current valid files for this table?" The FE responds with the latest set of file IDs and the latest Delete Vectors. Even if your local cache is full of "stale" data from 5 minutes ago, the query engine will ignore those blocks because they are no longer part of the "latest version" defined by the FE. 4. What about the "Last X Days" setting? You mentioned "reading last x days from cache." This usually refers to the 
datacache.partition_duration
property. * This setting only tells StarRocks which data is allowed to be cached (e.g., "only cache data from the last 30 days"). * It does not mean the cache is a static snapshot. If a row from 2 days ago is updated, it's still within the 30-day window, so the new version will be cached and the old version will be ignored by the query engine immediately. Summary: Why it's safe * Consistency: StarRocks uses ACID transactions. A state update is only "committed" once the metadata is updated in the FE. * Visibility: Once the FE commits, every subsequent query (even a millisecond later) is forced to use the new metadata, which points away from the old cached data and toward the new S3 data. * Performance: You get the speed of the local SSD cache without the risk of "stale" data. Recommendation for v4.1.1: In shared-data mode, use the Primary Key table model. It is specifically optimized for this "Update-on-S3" workflow and provides the strongest consistency guarantees for your UI. References • deployment/shared_data/feature-support-shared-data.md • table_design/table_types/primary_key_table.md • data_source/data_cache.md • loading/SQL_transaction.md References • deployment/shared_data/feature-support-shared-data.md • data_source/data_cache.md • using_starrocks/caching/block_cache.md
e
@Rocky if i was to impment writing to s3 in iceberg format, and pipe Files() to load the data to starrocks. then when updating a state, can I also implement this mechanism you said youl did in shared-mode ?
r
In StarRocks v4.1.1, you can indeed achieve the "Delete Vector" update mechanism you're looking for, but it depends on which table type you load that data into. Here is how you can implement this and why it solves the consistency problem: 1. The Key Distinction: "Loading" vs. "Catalog" The "Delete Vector" mechanism is a feature of the StarRocks Primary Key (PK) Table. It is not a property of the files on S3 or the 
FILES()
function itself. * If you load into an Internal PK Table (Shared-Data): You get the exact mechanism I described. Even though the data physically sits on S3, StarRocks manages it using its internal primary key engine. When you update a "state," StarRocks handles the delete-and-insert logic (Delete Vectors) automatically. * If you query the Iceberg files via a Catalog: You are using the Iceberg engine. In v4.1.1, StarRocks supports 
DELETE
on Iceberg v2 tables, but a native, high-performance 
UPDATE
(like the one in internal tables) is still more limited for Iceberg than for internal tables. 2. How to implement the "Pipe" workflow To get the best UI experience (fast updates + S3 storage), you should use 
FILES()
to ingest your S3 data into a StarRocks Primary Key table (which, in shared-data mode, stores its data on S3 anyway). The Workflow: 1. Write your data to S3: (e.g., as Parquet or Iceberg files). 2. Load into StarRocks: Use the 
INSERT INTO ... SELECT * FROM FILES()
pattern to pipe the data.
Copy code
sql
    -- Load from S3 into an internal Primary Key table
    INSERT INTO internal_catalog.db.my_pk_table
    SELECT * FROM FILES(
        "path" = "<s3://my-bucket/incoming-data/*.parquet>",
        "format" = "parquet",
        ...
    );
3. Update the State: Now, when your UI needs to update a "state," you run a standard SQL 
UPDATE
.
Copy code
sql
    UPDATE my_pk_table SET state = 'processed' WHERE id = 123;
3. Does it solve the "fail in one place" problem? Yes. In this setup, there is no "other place." * The S3 files you originally "piped" in are just the source. Once they are loaded into the StarRocks table, the StarRocks table becomes the source of truth. * Because StarRocks is a database, the 
UPDATE
is a single transaction. If the update to the data/metadata on S3 fails, the whole transaction fails. You will never have a situation where the "state" is updated but the data is old. 4. Can you do this inside an Iceberg Table? If you strictly want the data to stay in Iceberg format (so other tools can read it), v4.1.1 allows you to perform DML on Iceberg v2 tables. * Update Mechanism: When you 
DELETE
or 
INSERT
in an Iceberg v2 table in v4.1.1, StarRocks uses Iceberg’s Merge-on-Read (MoR) mechanism. * Similarity: This is very similar to the "Delete Vector" mechanism—it writes a small file marking the old row as deleted and appends a new file with the new row. * Performance: While similar in concept, StarRocks' Internal Primary Key table is significantly faster for UI-style "point updates" and sorting than the Iceberg-native DML. Summary Recommendation If your goal is a fast UI with S3 cost-efficiency: 1. Use Shared-Data Mode. 2. Use a Primary Key Table. 3. Use 
FILES()
or a Pipe to ingest your S3 data into that table. This gives you the Delete Vector mechanism "out of the box," ensures atomic updates (no partial failures), and keeps your storage costs low by living on S3. References • table_design/table_types/primary_key_table.md • loading/InsertInto.md • sql-reference/sql-functions/table-functions/files.md • data_source/catalog/iceberg/DML.md References
e
@Rocky question, if we are using shred-nothing mode, and use pipe Files() in order to load iceberg data from s3 into a primary key table in starRocks and then the user updates some rows (the state column). this means we update only in s3 then will the pipe Files know to update our primary table in starRocks ? how does this work ? im using iceberg version 2 and strrocks version 4.1.1
r
This scenario involves a few layers of StarRocks logic. To give you a clear answer, we need to distinguish between file-level ingestion (what 
PIPE
does) and metadata-level synchronization (what Iceberg requires). 1. Does 
PIPE FILES()
know about Iceberg updates? Short Answer: No. The 
PIPE
feature (and the 
FILES()
function) in StarRocks v3.2+ is designed to monitor a file path for new files. It treats the storage as a simple file system, not as a structured table format like Iceberg. * The Problem: When you update an Iceberg v2 table, Iceberg writes a new snapshot. This might involve writing new data files, delete files, or manifest files. * The Inconsistency: 
PIPE
only sees "a new file appeared in the S3 bucket." It does not read the Iceberg 
metadata.json
to understand which rows are current or which rows were deleted. If it sees a new data file, it will load it. If it sees a "delete file," it won't know how to process it and will likely fail or ignore it. 2. How 
UPSERT
works if you use 
PIPE
anyway If you pointed your 
PIPE
at the Iceberg data directory and an update happened that resulted in a new data file (Copy-on-Write): 1. 
PIPE
detects the new 
.parquet
file in S3. 2. It runs the 
INSERT INTO ... SELECT FROM FILES()
command. 3. Because your StarRocks table is a Primary Key table, it will perform an UPSERT. If the 
id
already exists, StarRocks will use its internal "Delete Vector" to mark the old row as deleted and insert the new row. 4. The Result: For updates, this might look like it works. But for deletes, it will fail because the row will stay in StarRocks while being "gone" in Iceberg. 3. The Recommended Way: Iceberg Catalog + Materialized View If you want StarRocks to "know" when the Iceberg table changes and update its own internal Primary Key table automatically, you should not use 
PIPE
. Instead, use an Asynchronous Materialized View (MV). The Workflow: 1. Create an Iceberg Catalog: Point StarRocks to your Iceberg warehouse.
Copy code
sql
    CREATE EXTERNAL CATALOG ice_catalog PROPERTIES ("type"="iceberg", ...);
2. Create a Materialized View: This acts as your "Primary Key table" that stays in sync.
Copy code
sql
    CREATE MATERIALIZED VIEW sync_table
    DISTRIBUTED BY HASH(id)
    REFRESH ASYNC EVERY (INTERVAL 5 MINUTE) -- Or manual refresh
    AS SELECT * FROM ice_catalog.db.iceberg_table;
3. How it works: * StarRocks v3.1+ can detect partition-level changes in Iceberg. * When the MV refreshes, StarRocks checks the Iceberg metadata, finds exactly what changed (including deletes and updates), and updates the internal StarRocks table. * This ensures 100% consistency with the Iceberg source of truth. Summary Feature | 
PIPE FILES()
 | Materialized View (Iceberg Catalog) Source Tracking | Monitors new files only | Monitors Iceberg Metadata/Snapshots Updates | Works via PK Upsert (if new file is written) | Fully supported Deletes | Not supported (stale data remains) | Fully supported Reliability | Risky; might load manifest/junk files | Recommended for table-to-table sync Note on Version: You mentioned v4.1.1. As of now, StarRocks is on v3.3/v3.4. If you are using a specific internal version or future roadmap build, the core logic remains: 
PIPE
is for raw files; 
Catalogs/MVs
are for table formats like Iceberg. References • sql-reference/sql-statements/loading_unloading/pipe/CREATE_PIPE.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • table_design/table_types/primary_key_table.md
e
@Rocky what about synchronous MV ?
r
In StarRocks, a *Synchronous Materialized View (Sync MV)*—often called a "Rollup"—is not the right solution for syncing data from S3/Iceberg into a Primary Key table. Here is why it won't work for your specific use case, and what the real-world equivalent is: 1. Synchronous MVs are for "Internal" tables only Synchronous MVs in StarRocks have very strict limitations: * No External Sources: They can only be built on tables inside the StarRocks Default Catalog. They cannot "listen" to an Iceberg table on S3. * No Primary Key Support: Synchronous MVs are primarily designed for Duplicate Key or Aggregate Key tables to create pre-aggregated "rollups." You generally cannot create a Sync MV on top of a Primary Key table. * Simple Logic: They only support simple aggregations (like 
SUM
, 
MIN
, 
MAX
) or column reordering. They don't support the complex logic needed to "pipe" and transform data from an external source. 2. Why you likely want an "Asynchronous MV" instead If your goal is to have a table in StarRocks that automatically reflects changes made to an Iceberg table on S3, you should use an Asynchronous Materialized View. Even though it is called "Asynchronous," it provides the functionality you are looking for: * Iceberg Awareness: It can point directly to your Iceberg Catalog. * Incremental Refresh: In StarRocks v3.1.4+, it can detect partition-level changes in your Iceberg table. If you update 10 rows in a "Monday" partition on S3, StarRocks only refreshes that specific partition in the MV. * Primary Key Behavior: While the MV itself isn't a "Primary Key table" in the traditional sense, it stores the data on S3/local disks and updates it via a "Delete and Insert" strategy during refresh, ensuring you see the latest "state." 3. How to get "Near-Real-Time" (The "Sync" feel) If you need the data to feel "Synchronous" for your UI, you can tune the Asynchronous MV: 1. High-Frequency Refresh: Set the refresh interval to a very short time (e.g., 
REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)
). 2. Manual Trigger: If your backend process knows exactly when the Iceberg update finishes, it can trigger a refresh immediately: 
REFRESH MATERIALIZED VIEW my_mv;
. 3. Query Rewrite: If you query the base Iceberg table directly, StarRocks can automatically "rewrite" the query to use the MV's pre-computed data if the MV is up-to-date. Summary: The Verdict * Sync MV: ❌ Cannot read from Iceberg/S3. Only for internal rollups. * Async MV: ✅ Designed for exactly this. It maps to Iceberg, detects updates, and keeps an optimized copy of the data inside StarRocks for your UI. Note on Version 4.1.1: StarRocks' current stable release series is v3.3/v3.4. If you are using a bleeding-edge or custom build, the architecture of Sync MVs remains focused on internal data structures, while Async MVs remain the bridge to the Data Lake (Iceberg). References • using_starrocks/Materialized_view-single_table.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • data_source/catalog/iceberg/iceberg_catalog.md References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/Materialized_view-single_table.md
4 Views
Open in Slack
PreviousNext
Powered by