http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I am running hourly batches reading...
# questions-and-troubleshooting
c
@Rocky I am running hourly batches reading device events from Iceberg into a primary key table with quantized timestamp and bitmap fields. It is a two-step process, first merge devices into a dictionary table using auto-increment, second-step aggregate devices into bitmaps. Is there a way to accomplish similar functionality using async materialized views instead of batched tasks?
r
Yes, you can accomplish this functionality by combining Global Dictionaries (Primary Key tables with 
AUTO_INCREMENT
) and Async Materialized Views (MVs). This setup allows you to replace manual batch scripts with a declarative, StarRocks-native pipeline. Here is how you can implement this two-step process using these features: Step 1: Automate the Dictionary Mapping To handle the "Device to Integer" mapping without manual batching, use a StarRocks Task or a simple Primary Key Table with 
AUTO_INCREMENT
. 1. Dictionary Table: Create a Primary Key table to store the mapping.
Copy code
sql
    CREATE TABLE device_dict (
        device_id STRING,
        id BIGINT AUTO_INCREMENT -- Automatically generates the integer ID
    )
    PRIMARY KEY (device_id)
    DISTRIBUTED BY HASH(device_id);
2. Automated Insertion (The "Task" replacement): Instead of an external batch script, you can create a scheduled Task within StarRocks to pull new devices from Iceberg:
Copy code
sql
    CREATE TASK sync_device_ids
    SCHEDULE EVERY 1 HOUR
    AS
    INSERT INTO device_dict (device_id)
    SELECT DISTINCT device_id
    FROM iceberg_catalog.db.events
    WHERE event_time >= now() - interval 1 hour;
_Note: The 
AUTO_INCREMENT
column will automatically assign IDs to any new 
device_id
values inserted._ Step 2: Aggregate into Bitmaps using Async MVs Now, use an Async Materialized View to handle the join and bitmap aggregation. This MV will read from both your Iceberg table and your local dictionary table. 1. Define the Async MV:
Copy code
sql
    CREATE MATERIALIZED VIEW device_bitmap_mv
    -- Refresh every hour, or after the dictionary task finishes
    REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
    DISTRIBUTED BY HASH(quantized_time)
    AS
    SELECT
        date_trunc('hour', event_time) as quantized_time,
        -- Use BITMAP_AGG or BITMAP_UNION(TO_BITMAP(id))
        BITMAP_AGG(dict.id) as device_bitmaps
    FROM iceberg_catalog.db.events as events
    JOIN device_dict as dict ON events.device_id = dict.device_id
    GROUP BY quantized_time;
Optimization: Using 
dict_mapping
(v3.2.5+) If you are on StarRocks v3.2.5 or later, you can use the 
dict_mapping
function to simplify the lookup logic. This allows the system to handle the mapping more transparently:
Copy code
sql
-- You can define an MV that automatically fetches IDs from the dictionary
CREATE MATERIALIZED VIEW device_bitmap_mv
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
AS
SELECT 
    date_trunc('hour', event_time) as quantized_time,
    BITMAP_AGG(dict_mapping('device_dict', device_id)) as device_bitmaps
FROM iceberg_catalog.db.events
GROUP BY quantized_time;
Why this is better than Batched Tasks: * Dependency Management: Async MVs track data freshness. If the underlying Iceberg data or the Dictionary table changes, the MV knows it needs a refresh. * Performance: Since Async MVs are physical tables (OLAP engine), queries on the bitmaps are extremely fast. * Simplicity: You move logic from external orchestrators (like Airflow/Cron) into the database DDL. * Transparent Rewrite: Standard queries against the Iceberg table can be automatically rewritten by the optimizer to use the pre-aggregated bitmaps in the MV. Important Considerations: * Data Consistency: Ensure the Dictionary Table (Step 1) is updated before the Async MV refreshes. You can trigger the MV refresh manually at the end of your dictionary task using 
REFRESH MATERIALIZED VIEW device_bitmap_mv WITH SYNC MODE;
. * Iceberg Partitioning: If your Iceberg table is large, ensure the MV uses a 
PARTITION BY
clause that matches your time-quanta to enable incremental partition-level refreshes. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
c
@Rocky I never heard of 
REFRESH MATERIALIZED VIEW my_mv WITH SYNC MODE
. This is actually the core feature that would enable what I want. Where can I read about it? I do not see it in https://docs.starrocks.io/docs/sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW/
r
You are correct that it is not listed in the 
CREATE MATERIALIZED VIEW
DDL documentation, as it is an operational command rather than a creation parameter. You can find the detailed documentation for this syntax in the REFRESH MATERIALIZED VIEW reference page. Key Details about `WITH SYNC MODE`: * Purpose: By default, the 
REFRESH MATERIALIZED VIEW
command is asynchronous (it returns a 
TaskID
immediately and runs in the background). Using 
WITH SYNC MODE
tells StarRocks to hold the connection open and only return once the refresh has successfully completed or failed. * Version Support: This was introduced in v2.5.8 and v3.1.0 onwards. * Use Case: This is specifically designed for the scenario you described—*task chaining*. It allows you to use a script or a StarRocks Task to ensure Step A (Dictionary update) finishes before Step B (MV Refresh) begins. Example in a StarRocks Task If you want to automate your two-step process entirely within StarRocks without an external orchestrator, you can wrap both steps in a single scheduled Task:
Copy code
sql
CREATE TASK weekly_event_aggregation
SCHEDULE EVERY 1 HOUR
AS 
BEGIN
    -- Step 1: Update the dictionary (Synchronous)
    INSERT INTO device_dict (device_id)
    SELECT DISTINCT device_id 
    FROM iceberg_catalog.db.events 
    WHERE event_time >= now() - interval 1 hour;

    -- Step 2: Trigger the MV refresh and wait for it to finish
    REFRESH MATERIALIZED VIEW device_bitmap_mv WITH SYNC MODE;
END;
Why it’s useful for your workflow: 1. Atomicity: If the dictionary update fails, the MV won't attempt to refresh with missing IDs. 2. Orchestration: It allows you to treat an Async MV as a step in a traditional ELT pipeline while still benefiting from the MV's automatic partition management and query rewrite capabilities. 3. Error Handling: If the refresh fails in 
SYNC MODE
, the task/script will receive the actual error code immediately. For more details on monitoring these tasks, you can also check the 
information_schema.task_runs
table, where 
SYNC
tasks will show up with an 
isSync: true
flag in the 
EXTRA_MESSAGE
column. References • sql-reference/sql-statements/materialized_view/REFRESH_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md
c
@Rocky the reason I want to use a materialized view is because it will manage aligning partitions with Iceberg snapshot deltas without overlap (double writes). can the same functionality be managed with a native table in a task?
r
The short answer is yes, you can use a native table and a Task to manage partition alignment, but you will lose the automatic incremental delta tracking that makes Materialized Views (MVs) so powerful for Iceberg. To achieve similar functionality with a native table in a Task, you would use the 
INSERT OVERWRITE
statement. 1. How to manage "Alignment" in a Task (
INSERT OVERWRITE
) The core tool for partition alignment in a task is Dynamic Partition Overwrite. When you run an 
INSERT OVERWRITE
against a partitioned native table, StarRocks will automatically identify which partitions are present in the 
SELECT
result and replace only those partitions in the target table.
Copy code
sql
-- Step in a StarRocks Task
INSERT OVERWRITE bitmap_table
SELECT 
    date_trunc('hour', event_time) as quantized_time,
    BITMAP_AGG(dict.id)
FROM iceberg_catalog.db.events as events
JOIN device_dict as dict ON events.device_id = dict.device_id
WHERE events.event_time >= date_trunc('hour', now() - interval 1 hour)
GROUP BY quantized_time;
* Alignment: If the query returns data for 
2023-10-01 10:00:00
, StarRocks will lock and replace only that specific hourly partition. * No Overlap: Because it's an 
OVERWRITE
, it is idempotent. Even if the task runs twice for the same hour, it simply replaces the old data with the new, preventing double-counting. 2. The Difference: "Snapshot Delta" vs "Time Window" This is where the Async MV is superior to a manual Task: Feature | Async Materialized View | Native Table in a Task Partition Alignment | Automatic. It matches Iceberg partitions to SR partitions. | Manual. Handled via 
INSERT OVERWRITE
. Delta Tracking | Metadata-based. It tracks the Iceberg 
snapshot_id
. If a job fails or data is late, it knows exactly what is missing. | Time-based. Usually relies on 
WHERE time > ...
. If data arrives in Iceberg with an old timestamp after your task runs, the task will miss it. Atomicity | Handled by the refresh engine. | Handled by the 
INSERT
transaction. 3. Can a Task track "Snapshot Deltas"? Technically, you can query Iceberg metadata tables (starting in v3.4.1) to find snapshots:
Copy code
sql
SELECT * FROM iceberg_catalog.db.events$snapshots;
However, writing a SQL script that says "find the last snapshot I processed and only read data from then until now" is extremely complex to maintain manually. Recommended Hybrid Approach Since you need the two-step process (Dictionary then Bitmap), the most "StarRocks-native" way to do this is to keep the Dictionary as a Task and use the MV for the Bitmaps: 1. Task (Step 1): Update your 
device_dict
(Primary Key table). _ _Why:* You need the 
AUTO_INCREMENT
side effect to assign IDs to new devices. 2. Async MV (Step 2): Use an MV for the bitmap aggregation. _ _Why:* The MV will automatically watch the Iceberg catalog. When a new Iceberg snapshot is committed, the MV will see the change, identify the modified partitions, and refresh only those, ensuring your bitmaps are always "aligned" with the source of truth without you writing delta-logic. Pro Tip: To make this seamless, use the 
REFRESH MATERIALIZED VIEW ... WITH SYNC MODE
command at the end of your Step 1 Task. This ensures Step 2 only runs after the dictionary is ready, but lets the MV handle the heavy lifting of Iceberg partition management. Summary of Docs • Iceberg Metadata Tables (v3.4+) • INSERT OVERWRITE behavior • Async MV Partition Refresh References • data_source/catalog/iceberg/DML.md • loading/InsertInto.md
Open in Slack
PreviousNext
Powered by