http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> would an older batch that has been ...
# questions-and-troubleshooting
d
@Rocky would an older batch that has been materialised ever be rematerialised ahead of a newer batch?
r
Yes, in StarRocks, an older batch (partition) will typically be rematerialized ahead of a newer batch if both are stale. StarRocks manages asynchronous materialized views at the partition level. When a refresh task is triggered (either automatically or manually) and multiple partitions need to be updated, the scheduling logic follows these rules: 1. Chronological Refresh Order By default, StarRocks refreshes partitions in chronological order from the least recent to the most recent (based on the partition key, such as a date). * If you have stale data in an older partition (e.g., due to late-arriving data in the base table) and also have stale data in a newer partition, StarRocks will prioritize processing the older one first. * This ensures that data is built up sequentially, which is often necessary for maintaining correct logic in downstream dependencies. 2. Batching with 
partition_refresh_number
 The property 
partition_refresh_number
(defaulting to 
1
in versions 3.3+) controls how many partitions are refreshed in a single internal batch. * If this value is set (e.g., 
10
), and 20 partitions are stale, StarRocks will take the 10 oldest stale partitions, refresh them, and then move on to the next 10. * The goal of this sequential "old-to-new" processing is to ensure that the materialized view reaches a consistent state across the timeline, starting from the past. 3. Manual Overrides You can change this behavior by manually specifying which partitions to refresh. For example:
Copy code
sql
REFRESH MATERIALIZED VIEW my_mv 
PARTITION START ("2023-01-01") END ("2023-01-02");
By manually targeting a specific range, you can force a newer batch to refresh regardless of the state of older batches. However, in the absence of manual intervention, the system's automated scheduler favors the older data first. Summary If an older batch has already been materialized once but becomes stale again (due to base table changes), it will be queued for rematerialization ahead of newer stale batches because of StarRocks' "least recent to most recent" scheduling logic." most-stale-first / chronological-ascending" scheduling strategy. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/REFRESH_MATERIALIZED_VIEW.md
d
@Rocky let's say we have raw ingest (JSON records) that are materialised through to normalised gold tables (PK'd). If we receive an event indicating a record with a given ID has been deleted, what's the best approach to marking that latter record as soft deleted?
r
The best approach for handling soft deletes in a StarRocks pipeline involving JSON records and normalized Primary Key (PK) gold tables is to use the Partial Update feature of the Primary Key table. In this architecture, you don't need to manually run 
UPDATE
statements on the gold table. Instead, you treat the "delete event" as a stream of data that updates only the 
is_deleted
flag. 1. Table Design Ensure your gold table is a Primary Key table and includes a soft-delete flag (e.g., 
is_deleted
).
Copy code
sql
CREATE TABLE gold_table (
    record_id BIGINT NOT NULL,
    data_field_1 VARCHAR(100),
    data_field_2 INT,
    is_deleted BOOLEAN DEFAULT FALSE,
    updated_at DATETIME
) 
ENGINE=OLAP
PRIMARY KEY(record_id)
DISTRIBUTED BY HASH(record_id);
2. The Soft Delete Strategy: Partial Update When a "delete" event arrives in your JSON stream (e.g., 
{"id": 123, "event_type": "DELETE"}
), you can perform a Partial Update via 
Stream Load
or 
Routine Load
. This allows you to update the 
is_deleted
column without providing values for all the other columns (
data_field_1
, etc.). How to implement it: * Property: Set 
"partial_update" = "true"
. * Column Mapping: Map the ID from the JSON to your Primary Key and set 
is_deleted = true
. Example using Routine Load (Kafka): If your Kafka JSON looks like 
{"id": 123, "op": "del"}
, you can create a routine load that specifically targets the soft-delete flag:
Copy code
sql
CREATE ROUTINE LOAD soft_delete_job ON gold_table
COLUMNS(record_id, is_deleted = 1) -- Hardcode is_deleted to 1
PROPERTIES (
    "format" = "json",
    "jsonpaths" = "[\"$.id\"]", -- Only extract the ID
    "partial_update" = "true"  -- Crucial: tells StarRocks not to overwrite other columns with NULL
)
FROM KAFKA ( ... );
3. Handling Different Event Types in One Stream If your JSON stream contains both 
INSERT
and 
DELETE
events, you can use conditional column mapping to set the 
is_deleted
flag dynamically:
Copy code
sql
-- In Stream Load or Routine Load headers/properties
"columns": "tmp_id, tmp_op, record_id = tmp_id, is_deleted = if(tmp_op = 'del', 1, 0)"
Why this is the "Best Approach": 1. Performance: StarRocks PK tables use a "Delete+Insert" strategy internally. A partial update is highly optimized and avoids the heavy overhead of standard SQL 
UPDATE
commands. 2. Simplicity: You don't need a separate "reaper" process or complex logic to find and update rows. The ingestion pipeline handles it as standard data flow. 3. Data Integrity: By using PK tables, you ensure that even if a "Delete" event arrives slightly out of order with an "Update" event, you can use a 
version
column (via the 
__version
property) to ensure the final state is correct. Querying Since the records still exist in the table, you should create a view on top of your gold table to simplify downstream consumption:
Copy code
sql
CREATE VIEW active_gold_table AS 
SELECT * FROM gold_table WHERE is_deleted = FALSE;
*Note: If you ever need to perform a hard delete instead, you can include the 
__op
column in your load and set it to 
1
(for Delete), which will physically remove the record from the PK table's index.* References • loading/Load_to_Primary_Key_tables.md • loading/StreamLoad.md • loading/Flink-connector-starrocks.md
d
@Rocky would this still work even if not coming from kafka? We'd push into another bronze-level table all of our delete events from our ingestor. I believe we post over HTTP
r
Yes, this approach works perfectly for HTTP-based ingestion and inter-table movements (e.g., Bronze to Gold), provided you are on a recent version of StarRocks. Here is how you handle both scenarios: 1. Direct HTTP Push (Stream Load) If your ingestor pushes delete events directly to the Gold table via HTTP, you use the Stream Load interface. You only need to include the Primary Key and the 
is_deleted
flag in your payload and headers. * Header: Set 
partial_update: true
. * Columns: Specify only the columns you are providing.
Copy code
bash
curl --location-trusted -u root: \
    -H "partial_update: true" \
    -H "columns: record_id, is_deleted=1" \
    -T delete_events.json -XPUT \
    http://<fe_host>:<fe_http_port>/api/db/gold_table/_stream_load
_In this case, only the 
is_deleted
column is updated; all other existing data for that 
record_id
remains untouched._ ────────── 2. Bronze-to-Gold Movement (
INSERT INTO ... SELECT
) If you first land your delete events into a "Bronze" table and then want to move them to the "Gold" Primary Key table, you can use a standard 
INSERT
statement. Requirement: This requires StarRocks v3.3.1 or newer. Starting from v3.3.1, if you specify a column list that is a subset of the table's columns, StarRocks automatically performs a Partial Update instead of a full overwrite.
Copy code
sql
-- Assuming bronze_deletes contains the IDs of records to be soft-deleted
INSERT INTO gold_table (record_id, is_deleted)
SELECT record_id, 1 FROM bronze_deletes;
Key configuration for this to work: * Target Table: Must be a Primary Key table. * Session Variable: Ensure 
enable_insert_partial_update
is set to 
true
(it is 
true
by default in supported versions). * Column List: You must explicitly list the columns in the 
INSERT INTO
clause. If you omit the column list, StarRocks will assume a "Full Upsert" and fill missing columns with 
NULL
or default values. Summary of Versions * Stream Load (HTTP): Supported for many versions (2.5+). * INSERT INTO SELECT: Supported for partial updates since v3.3.1. If you are on a version earlier than 3.3.1 and need to move data between tables for partial updates, you would typically use the 
UPDATE
statement, but 
INSERT INTO ... SELECT
is significantly more performant for batch updates. References • loading/InsertInto.md • loading/StreamLoad.md • sql-reference/System_variable.md (see 
enable_insert_partial_update
) References • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md • release_notes/release-3.1.md
d
@Rocky not quite. Our ingestor pushes records into a raw_ingest table. A number of views then pick records up from this table based on the value of a couple of columns, and a materialiser then UNIONS all of the relevant views together to populate our dim/fct tables at the gold layer. Does that still work with Insert Into?
r
Yes, this architecture still works with 
INSERT INTO
, but there is a key distinction depending on whether your "Materializer" is a scheduled script/task or a StarRocks Asynchronous Materialized View (MV). 1. If your "Materializer" is a Task (e.g., Airflow, Cron) This is the most flexible approach for soft deletes. You can run an 
INSERT INTO ... SELECT
that specifically targets only the primary key and the 
is_deleted
flag. * Version Requirement: StarRocks v3.3.1+. * How it works: When you provide a column list that is a subset of the target Primary Key table's columns, StarRocks performs a Partial Update. It does not matter if the source is a 
UNION
of multiple views; StarRocks simply takes the resulting rows and applies them. Example Materializer Query:
Copy code
sql
-- This query only updates 'is_deleted'. 
-- Existing values in 'data_field_1', etc., are preserved.
INSERT INTO gold_dim_table (record_id, is_deleted)
SELECT record_id, 1 
FROM unioned_ingest_view
WHERE event_type = 'DELETE';
2. If your "Materializer" is an Asynchronous MV If your Gold table is actually defined as a 
CREATE MATERIALIZED VIEW
, the behavior is different. * MVs do not support Partial Updates: A Materialized View is designed to reflect the full state of its source query. When an MV refreshes, it re-calculates the rows and overwrites the existing data (usually at the partition level). * The Problem: If your MV source query only selects 
record_id
and 
is_deleted
, the MV will treat the other columns as 
NULL
or default values and overwrite your existing "Gold" data. * The Solution: If you use an MV for the Gold layer, the 
is_deleted
logic must be part of the main MV definition that includes all columns. The MV would then "materialize" the soft-delete status alongside the data. Which should you use? * Use 
INSERT INTO ... SELECT
(Partial Update): If your ingest stream only contains the "Delete Event" (the ID) and you want to flip a switch on a record that already exists in Gold without re-processing all its original fields. This is highly efficient for "late-arriving" deletes. * Use Asynchronous MV: If your Gold table is intended to be a fully managed, declarative representation of your raw data. In this case, your 
unioned_ingest_view
should probably use a 
JOIN
or a 
Window Function
(like 
row_number()
) to determine the final state (active vs. deleted) for every record, and the MV will materialise that entire state. Recommendation for your setup: Given you are using a 
UNION
of views to "materialize" into Gold, you are likely treating Gold as a persistent table. In this case, manually triggered 
INSERT INTO ... SELECT
 (or using a 
Task
object in StarRocks 3.4+) is the best way to utilize the Partial Update feature to mark records as soft-deleted. Pro-tip: Ensure the session variable 
enable_insert_partial_update
is set to 
true
(it is the default in v3.3.20+, v3.4.9+, and v3.5.8+). References • sql-reference/System_variable.md (Search for 
enable_insert_partial_update
) • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md
2 Views
Open in Slack
PreviousNext
Powered by