@Rocky if I have a table that is partitioned by hour, can I have a rollup (sync MV) on it by minute?

In StarRocks, the answer depends on whether you are using a Synchronous Materialized View (Sync MV) or an Asynchronous Materialized View (Async MV). 1. Synchronous Materialized View (Sync MV / Rollup) Short Answer: No. Sync MVs (historically called "Rollups") are highly restrictive. While StarRocks v3.1+ introduced support for some expressions in the

SELECT

list (like

abs(col)

or

col + 1

), the

GROUP BY

clause in a Sync MV typically requires raw column names from the base table. * Limitation: You cannot use time-transformation functions like

date_trunc('minute', ...)

or

time_slice()

in the

GROUP BY

clause of a Sync MV. * Workaround: If you absolutely need a Sync MV with minute granularity, you must include a "minute" column (e.g.,

DATETIME

truncated to the minute) as a physical column in your base table schema and group by that column. 2. Asynchronous Materialized View (Async MV) Short Answer: Yes. This is the recommended way to achieve your goal. Async MVs are designed specifically for this type of "time granularity rollup." * Granularity: You can create an Async MV that groups data by minute (

GROUP BY date_trunc('minute', ts)

) even if the base table is partitioned by hour. * Partition Alignment: To ensure efficient refreshes, you can align the MV's partitions with the base table. For example, if your base table is partitioned by hour, you can also partition the MV by hour using the

PARTITION BY

clause. This allows StarRocks to refresh only the specific "hour" in the MV when data in the corresponding "hour" of the base table changes. * Automatic Refresh: By setting the refresh strategy to

REFRESH ASYNC

, the MV will automatically update as data is loaded into the base table, providing a similar (though slightly delayed) experience to a Sync MV. Example (Async MV) If your base table

orders

is partitioned by the hour on column `order_time`:

sql
CREATE MATERIALIZED VIEW minute_rollup
REFRESH ASYNC
-- Align MV partitions with base table hour partitions
PARTITION BY date_trunc('hour', mv_minute)
AS
SELECT 
    date_trunc('minute', order_time) AS mv_minute,
    count(order_id) AS order_count,
    sum(price) AS total_revenue
FROM orders
GROUP BY mv_minute;

Summary Comparison Feature | Synchronous MV (Sync) | Asynchronous MV (Async) Minute grouping on Hour partition | ❌ Not supported | ✅ Supported Expression in GROUP BY | ❌ Limited/No | ✅ Full support Refresh Logic | Real-time (during load) | Asynchronous (triggered by load) Transparent Query Rewrite | ✅ Yes | ✅ Yes For most modern StarRocks use cases involving time-series rollups, Asynchronous Materialized Views are the standard choice. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md

@Rocky I ask because this SYNC MV is causing my routine loads to stop working . Note that that all are plain table fields that are pulled from json in routine loads. I have multiple routine load tasks for the table.

CREATE MATERIALIZED VIEW mv_trader_delivery_by_minute_sync
AS SELECT
    event_minute,
    event_type,
    flight_id,
    COUNT(event_minute)
FROM trader_delivery_raw
WHERE message_json IS NOT NULL
GROUP BY
    event_minute,
    event_type,
    flight_id;

The issues you are experiencing with your Routine Loads are almost certainly caused by the limitations of Synchronous Materialized Views (Sync MVs/Rollups) in StarRocks, specifically regarding JSON columns and the WHERE clause. Why your Routine Load is stopping In StarRocks, Sync MVs are updated synchronously during the data ingestion process. If a Sync MV fails to process a row (due to an unsupported data type or expression), the entire load task fails. After a certain number of failures, StarRocks will PAUSE the Routine Load. Your Sync MV has two "red flags" that are likely causing this: 1. JSON in WHERE clause: Sync MVs are highly optimized for standard numeric and string types. They generally do not support JSON-type columns in the

WHERE

clause or

GROUP BY

logic. When Routine Load tries to evaluate

message_json IS NOT NULL

during the ingestion "on-the-fly" rollup, it likely encounters an internal error. 2. Complex Predicates: While some recent StarRocks versions (v3.1+) added limited

WHERE

clause support to Sync MVs, it is still very restrictive. Complex logic during ingestion is much better handled by Async MVs. Recommended Solution: Switch to an Async MV To fix this, you should drop the Sync MV and use an Asynchronous Materialized View instead. Async MVs fully support JSON types,

WHERE

clauses, and complex expressions without affecting the stability of your Routine Load. 1. Drop the problematic Sync MV

sql
DROP MATERIALIZED VIEW mv_trader_delivery_by_minute_sync;

2. Create an Async MV This version will support your JSON predicate and will refresh independently of the load process:

sql
CREATE MATERIALIZED VIEW mv_trader_delivery_by_minute_async
REFRESH ASYNC
DISTRIBUTED BY HASH(flight_id) -- Choose a good distribution key
AS 
SELECT
    event_minute,
    event_type,
    flight_id,
    COUNT(event_minute)
FROM trader_delivery_raw
WHERE message_json IS NOT NULL
GROUP BY
    event_minute,
    event_type,
    flight_id;

How to verify the error To confirm exactly why your loads are pausing, run:

sql
SHOW ROUTINE LOAD FOR <job_name> \G

Look at the

ReasonOfStateChanged

and

ErrorLogUrls

fields. You will likely see an error related to "materialized view" or "unsupported column type" in the log. Summary * Sync MVs are for simple rollups on standard types (INT, VARCHAR, etc.). They block loads if they fail. * Async MVs are for complex logic, filtering (

WHERE

), and semi-structured data (

JSON

). They are decoupled from the ingestion process, ensuring your Routine Loads remain stable. References • loading/RoutineLoad.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/Materialized_view-single_table.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md

@Rocky I created generated fields in my table to support sync MV I know they would also help with ASYNC MV but since they put pressure on routine loads I am wondering if I should drop them

The decision to drop generated columns depends on where you want the "computational cost" to occur: during data ingestion (Routine Load) or during the background refresh (Async MV). Since you are experiencing stability issues with Routine Load, here is a breakdown of why dropping them might be the right move: 1. Why you should consider dropping them * Ingestion Stability: Generated columns are computed synchronously for every row as it arrives. If you have many columns or complex JSON expressions, this puts significant CPU pressure on the BE nodes during ingestion. If a specific JSON structure causes an expression error, it can (in some cases) fail the load. * Reduced Load Overhead: By dropping the generated columns, your Routine Load becomes a "simple dump" of raw data. This increases throughput and significantly reduces the chance of the load task being paused. * Decoupled Processing: If you use an Async MV, the logic to parse the JSON and calculate those fields moves to the MV's refresh cycle. If the refresh fails (e.g., due to a bad JSON format), your Routine Load keeps running, and your base table data remains safe. 2. When to keep them * Direct Base Table Queries: If your users frequently run queries directly against the

trader_delivery_raw

table (instead of the MV) and filter by those fields, generated columns are 10x–100x faster than parsing JSON on-the-fly. * Storage vs. Compute: Generated columns store the result on disk. If you drop them, you save disk space but will use more CPU later when the Async MV refreshes or when you query the raw table. Recommended Path Given that your Routine Loads are currently stopping, I recommend the following transition: 1. Drop the Sync MV first. This is the most likely cause of the "Hard Stop." 2. Monitor the Routine Load. If the Routine Load is stable and the CPU/Latency is acceptable with just the generated columns, you can keep them to make your Async MV refresh faster (since the MV will just read the pre-computed columns). 3. Drop the Generated Columns IF the Routine Load is still slow or if you want to maximize ingestion speed. You can then define the logic (e.g.,

get_json_string(...)

) directly inside the

SELECT

statement of your Async MV. Summary of "Pressure" Component | Pressure on Routine Load | Pressure on Background Generated Columns | High (CPU per row) | None Sync MV | Very High (Aggregation per row) | None Async MV | None | Medium (Refresh cycle) Rocky's Advice: Drop the Sync MV immediately and switch to an Async MV. Only drop the generated columns if you still see high CPU or "backpressure" on your Routine Loads after the Sync MV is gone. References • sql-reference/sql-statements/generated_columns.md • loading/loading_introduction/troubleshooting_loading.md • using_starrocks/async_mv/Materialized_view.md References • knowledge/usage/best-practice.md • sql-reference/sql-statements/generated_columns.md • knowledge/usage/load/shared_data_batch_ingestion.md • loading/loading_introduction/troubleshooting_loading.md