http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I got the following use case : 1. I...
# questions-and-troubleshooting
e
@Rocky I got the following use case : 1. I ingest data into a starrocks primary modal table called generic_data 2. the table has generic columns like: id, time, original_message (thats the whole message, id and time were extracted from it) 3. we want a MV that does the following: a. for every new row inserted into the generic_data table (by using pipe files() ) we want a row inserted into the MV that has the original_message column but also some columns that have values that we extracted from the original_message - that will allow for better ui performance b. this MV should hold the last x days of data (x is based on a column value - a column called time) c. we need near real time so the ui can see the data within a minute from ingestion into the generic_data table can starrocks help with this use case ? the tables might contain huge volumes (100 GB)
r
Yes, StarRocks is an excellent fit for this use case. You can achieve near real-time extraction and automated data retention using Asynchronous Materialized Views (MVs) on top of a partitioned Primary Key table. Here is how you can set this up: 1. The Base Table (Primary Key Table) To support the "last X days" requirement efficiently, your base table must be partitioned by the 
time
column. This allows StarRocks to manage data retention at the partition level.
Copy code
sql
CREATE TABLE generic_data (
    id BIGINT NOT NULL,
    time DATETIME NOT NULL,
    original_message JSON,
    PRIMARY KEY (id, time)
)
PARTITION BY TABLET (time) -- Or PARTITION BY (time) for manual/dynamic range partitioning
DISTRIBUTED BY HASH(id);
2. The Materialized View You can create an asynchronous MV that extracts fields from the 
original_message
JSON and automatically manages retention using 
partition_ttl
. * Extraction: Use the 
->
operator or 
get_json_string()
function. * Latency: Set the refresh interval to 1 minute. * Retention: Use the 
partition_ttl
property to automatically drop data older than your desired window.
Copy code
sql
CREATE MATERIALIZED VIEW message_extracted_mv
-- Partition the MV the same way as the base table for incremental refresh
PARTITION BY time 
REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)
PROPERTIES (
    "partition_ttl" = "30 days" -- Automatically keep only the last 30 days
)
AS 
SELECT 
    id,
    time,
    original_message,
    -- Extract values into typed columns for UI performance
    cast(original_message->'user_id' as VARCHAR) as user_id,
    cast(original_message->'status' as INT) as status_code,
    get_json_string(original_message, '$.metadata.region') as region
FROM generic_data;
Why this works for your case: * Scale (100 GB+): StarRocks handles hundreds of terabytes easily. 100 GB is well within the "small to medium" range for StarRocks, and its Primary Key model is optimized for high-frequency ingestion (like 
pipe files()
). * Near Real-Time: By setting 
REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)
, the MV will check for new data in the base table every minute. Since it is partitioned, it will only refresh the partitions that have changed (incremental partition refresh), making it very fast. * Automated TTL: The 
partition_ttl
property ensures that as new time-partitions are created, the oldest ones are automatically deleted from the MV, keeping your UI-facing data lean. * UI Performance: Extracting JSON fields into separate columns in the MV allows StarRocks to use its columnar storage and vectorized engine to scan only the necessary data, providing sub-second query responses for your UI. Important Notes: * Base Table Partitioning: The MV can only perform incremental partition-level refreshes if the base table is also partitioned. * JSON Optimization: If your 
original_message
is very large, consider enabling the Flat JSON feature (
"enable_flat_json" = "true"
in table properties) to further accelerate the extraction process-time during the extraction. References • using_starrocks/Flat_json.md • sql-reference/data-types/semi_structured/JSON.md • using_starrocks/async_mv/materialized_view_task_run_details.md
e
@Rocky a couple of questions: 1. you said: "*Near Real-Time:* By setting 
REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)
, the MV will check for new data in the base table every minute. Since it is partitioned, it will only refresh the partitions that have changed (incremental partition refresh), making it very fast." how does starrocks behind the scene know that in day X paritition which rows were added to the base table (so it only ingests those into the MV) 2. lets say tomorrow the product said that the ui should be able to filter/sort based on some new columns. can we alter the existing MV to extract also those column for the original_message message ? if yes then what happens for old row that were already in the MV 3. if tomorrow we add add a parittion or a column to the base table how does it effect the MV 4. can we add partition to the MV dynamically without having those partitions in the base table and who does that affect the partition_ttl" = "30 days" ?
r
Great questions. Here is how StarRocks manages those specific scenarios: 1. How StarRocks tracks changes (Partition-level Refresh) StarRocks uses data versioning at the partition level. * Version Tracking: Every time you ingest data into a partition of the 
generic_data
table (e.g., the partition for 
2023-10-27
), StarRocks increments a "version number" for that specific partition. * Detection: When the 1-minute refresh task triggers, StarRocks compares the version of each partition in the base table with the version stored in the Materialized View's metadata. * The Refresh: If the versions differ, StarRocks re-calculates that specific partition. It does not track individual rows; it simply re-runs the MV's query for the time range of the modified partition. This is why partitioning is critical—it ensures you only re-process one day (or hour) of data rather than 100 GB. 2. Adding new columns for extraction StarRocks does not currently support 
ALTER MATERIALIZED VIEW
to change the underlying SQL query (e.g., adding a new JSON extraction). * The Workflow: To add a new column, you must drop and recreate the MV. * Zero-Downtime Strategy: If you need to keep the UI running, the standard practice is: 1. Create a new MV (
message_extracted_v2
) with the updated columns. 2. Once it is fully refreshed and up-to-date, use the 
SWAP
command:
Copy code
sql
       ALTER MATERIALIZED VIEW message_extracted_mv SWAP WITH message_extracted_v2;
3. This atomically renames them, so your UI query remains the same and sees no downtime. 3. Adding columns/partitions to the Base Table * Adding a Partition: This is seamless. When you add a partition to the base table and ingest data into it, the MV will automatically detect the new partition during its next refresh and create a corresponding partition for itself. * Adding a Column: If you add a column to the base table that the MV doesn't use, the MV remains active. If the base table schema changes in a way that affects the MV's query (or if you use 
SELECT *
), the MV may become 
INACTIVE
. You can restore it using:
Copy code
sql
    ALTER MATERIALIZED VIEW message_extracted_mv ACTIVE;
4. Dynamic Partitions and TTL * Alignment: A partitioned MV must be based on a partitioned base table. You cannot create a partitioned MV on a flat table. * Granularity: You can have different granularities. For example, your base table could be partitioned by hour, while your MV is partitioned by day. You do this using 
PARTITION BY date_trunc("day", time)
. * Independence of TTL: The 
partition_ttl
in the MV is independent of the base table. * Scenario: You can keep 3 years of raw logs in 
generic_data
(base table) but set 
"partition_ttl" = "30 days"
in the MV. * Result: StarRocks will automatically drop partitions from the MV that are older than 30 days, even though the data still exists in the base table. This keeps the MV (and your UI performance) very lean. Summary for your 100 GB use case: StarRocks is built exactly for this "ingest raw -> extract via MV -> serve UI" pattern. The combination of Primary Key tables (for fast ingestion) and Async MVs (for extraction/transformation) handles 100 GB easily with sub-second query speeds for the UI. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md
e
@Rocky is it possible that the base table will be partitioned by day and by hour and the MV will also then be partitioned by hour and by day but the ttl_refresh will be by day ? so this would allow the 1 minute interval so check if partitioned changed by hour and not by day (less data to re ingest)
r
In StarRocks, you can achieve exactly what you described, but you don't actually need to partition by both day and hour as separate columns. The most efficient way to handle your "fast refresh + daily retention" requirement is to use Hourly Partitioning for both the table and the MV. 1. Why Hourly Partitioning is the key If you partition both the base table and the MV by hour (
date_trunc('hour', time)
), StarRocks will behave exactly as you want: * Minimal Ingestion: When you ingest data into the base table at 10:15 AM, only the 
10:00
partition version changes. * Fast MV Refresh: When the MV refreshes every minute, it sees that only the 
10:00
partition is out of sync. It will re-process only that 1 hour of data, not the whole day. * Automatic TTL: The 
partition_ttl
property is time-aware. If you set it to 
30 days
, StarRocks will automatically drop any hourly partitions that are older than 30 days. 2. Is "Day + Hour" partitioning possible? While StarRocks 3.4+ supports multi-column partitioning (e.g., partitioning by 
(date, city)
), using it for 
(day, hour)
is generally redundant and not recommended for this use case. * An hourly partition (e.g., 
2023-10-27 10:00
) inherently knows which day it belongs to. * Adding a second partition column for "Day" complicates the mapping and does not provide additional refresh benefits. 3. Advanced Option: Mixed Granularity (StarRocks 3.4+) If you are worried about having too many partitions (e.g., you want to keep 1 year of data but only need hourly precision for the last 3 days), StarRocks 3.4 introduced Mixed Granularity Partitioning. * You can have hourly partitions for recent data (for fast 1-minute refreshes). * You can then merge those into daily partitions for older data to reduce metadata overhead. _ _However*, for 100 GB and a retention of "X days" (likely a month or two), simple hourly partitioning is much simpler and highly performant. 30 days of hourly partitions is only 720 partitions, which is very small for StarRocks (it can handle tens of thousands). Summary of Recommendation: Set up both your base table and MV with hourly partitions:
Copy code
sql
-- Base Table
CREATE TABLE generic_data ( ... )
PARTITION BY date_trunc('hour', time) -- Hourly granularity
DISTRIBUTED BY HASH(id);

-- Materialized View
CREATE MATERIALIZED VIEW message_extracted_mv
PARTITION BY time -- This automatically aligns with the base table's hour partitions
REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)
PROPERTIES (
    "partition_ttl" = "30 days" -- Drops hourly partitions older than 30 days
)
AS SELECT ... FROM generic_data;
Result: Your 1-minute refresh will only touch the current hour's data, and your 30-day retention will be handled automatically. References • best_practices/partitioning.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • table_design/data_distribution/expression_partitioning.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md References • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
e
@Rocky please explain this "*Adding a Column:* If you add a column to the base table that the MV doesn't use, the MV remains active. If the base table schema changes in a way that affects the MV's query (or if you use 
SELECT *
), the MV may become 
INACTIVE
. You can restore it using:" what do you mean by inactive ? we would query the MV not the base table
r
In StarRocks, "INACTIVE" is a safety state. When you query the MV directly (instead of the base table), an inactive state has two major impacts: 1. What happens when it is "INACTIVE"? * Refresh is paused: The 1-minute automatic refresh will stop. Even if you try to trigger a manual refresh, it will fail. * Stale Data: Since it isn't refreshing, the data in the MV will become increasingly out-of-date compared to your 
generic_data
table. * Direct Queries still work: You can still run 
SELECT * FROM message_extracted_mv
. It won't return an error, but it will return "stale" data (the data that was there just before it became inactive). 2. Why does it happen? StarRocks marks an MV as inactive to prevent metadata mismatches. * Safe change: If you add a column 
new_col
to the base table and your MV only selects 
id
and 
time
, the MV remains ACTIVE and continues refreshing. * Unsafe change: If you drop or rename a column that the MV is using for its extraction, the MV becomes INACTIVE because its SQL definition is now broken. * *The 
SELECT *
trap:* If you defined your MV using 
SELECT _ FROM generic_data
, and then you add _any* column to the base table, the MV becomes INACTIVE. This is because the "schema" of 
*
has changed, and StarRocks needs you to confirm how to handle the new structure. 3. How to fix it? If your MV becomes inactive (e.g., after you added a column to the base table), you can reactivate it with one command:
Copy code
sql
ALTER MATERIALIZED VIEW message_extracted_mv ACTIVE;
Once active, it will immediately resume its 1-minute refresh schedule and catch up on the missing data. Pro-Tip for your UI use case: To avoid the MV becoming inactive unexpectedly: 1. *Avoid `SELECT ` in the MV definition. Explicitly list the columns you want to extract. 2. If you add a column to the base table that you don't need in the MV, the MV will usually stay active and keep serving your UI without interruption. You can check the status of your MV anytime with:
Copy code
sql
SHOW MATERIALIZED VIEWS LIKE 'message_extracted_mv'\G
-- Look at the 'is_active' and 'inactive_reason' fields
References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • sql-reference/information_schema/materialized_views.md References • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/Materialized_view-single_table.md
e
@Rocky if the MV tries to extract from the original_message json some key in order to put them as columns in the MV -> if for some reason the base table got a original_message that did not have those key or has null for them as values -> would this fail the MV ingestion ?
r
No, missing keys or null values will not fail the Materialized View ingestion. StarRocks is designed to be "null-tolerant" when it comes to semi-structured data extraction. Here is exactly what happens: 1. Missing Keys = NULL If your MV query looks like 
get_json_string(original_message, '$.user_id')
and a row arrives where the 
original_message
JSON does not contain the 
user_id
key: * The function will simply return NULL. * The MV refresh will succeed. * The row will be stored in the MV with a 
NULL
in that specific column. 2. Actual Null Values = NULL If the key exists but its value is explicitly 
null
(e.g., 
{"user_id": null}
), the extraction function will also return a SQL 
NULL
. 3. Malformed JSON Even if the 
original_message
is not a valid JSON string (e.g., it got truncated to 
{"user_id": 123
), the standard extraction functions (
get_json_string
, 
get_json_int
, etc.) are designed to return 
NULL
rather than throwing an exception that stops the whole refresh process. How to handle this (if you want to): If you want to ensure that your MV only contains rows where certain keys exist, you can add a filter to your MV definition:
Copy code
sql
CREATE MATERIALIZED VIEW message_extracted_mv ...
AS 
SELECT 
    get_json_string(original_message, '$.user_id') as user_id,
    ...
FROM generic_data
WHERE get_json_string(original_message, '$.user_id') IS NOT NULL; -- This filters out rows with missing keys
Pro-Tip: The 
VARIANT
Type If you are using StarRocks 3.2+, consider using the 
VARIANT
 data type for your 
original_message
column. * It is much faster than 
get_json_string
. * You can query it using sub-column notation: 
original_message:user_id
. * It handles missing keys automatically by returning 
NULL
, just like the functions, but with significantly better performance because it stores the JSON in a columnar format under the hood. Summary: Your MV is safe. Missing keys in the source data will simply result in empty (NULL) cells in the view, not a system failure. References • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/get_json_string.md • sql-reference/sql-functions/variant-functions/get_variant.md • table_design/data_types/variant.md • using_starrocks/Flat_json.md References • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/get_json_string.md • sql-reference/sql-functions/variant-functions/get_variant.md • using_starrocks/async_mv/materialized_view_task_run_details.md • using_starrocks/Flat_json.md • administration/management/BE_configuration.md • sql-reference/sql-functions/meta-functions/inspect_mv_relationships.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
e
@Rocky does starrocks support VARAINT type in its native tables ?
r
Currently, the answer is no for the specific 
VARIANT
data type, but yes for the functionality through the 
JSON
type. Here is the distinction: 1. The 
VARIANT
Type (External Only) As of StarRocks v4.1, the specific 
VARIANT
data type is only supported for Iceberg tables (stored in Parquet format). It is not currently a valid data type for StarRocks native (OLAP) tables. 2. The "Native" Solution: 
JSON
+ Flat JSON For native StarRocks tables, you should use the 
JSON
 data type. To get the performance benefits usually associated with a 
VARIANT
type (like columnar storage and fast sub-field access), StarRocks uses a feature called Flat JSON. When you enable Flat JSON, StarRocks automatically "shreds" the most frequently used keys from your JSON blob and stores them as hidden sub-columns. This gives you the performance of a flat table with the flexibility of a JSON blob. Example of a native table with "Variant-like" performance:
Copy code
sql
CREATE TABLE generic_data (
    id BIGINT,
    original_message JSON -- Use JSON here
) 
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id)
PROPERTIES (
    "flat_json.enable" = "true" -- This enables the "Variant" style optimization
);
Which should you use? * If your data is in Iceberg: Use 
VARIANT
. * If you are using StarRocks native tables: Use 
JSON
and ensure 
"flat_json.enable" = "true"
is set in the table properties. Why use Flat JSON? * Automatic: You don't need to define the schema of the JSON. StarRocks learns which keys are common. * Performance: Queries like 
SELECT original_message->'user_id'
will read only the specific sub-column from disk, making it nearly as fast as a standard 
VARCHAR
column. References • sql-reference/data-types/semi_structured/VARIANT.md • using_starrocks/Flat_json.md • table_design/data_types/variant.md • table_design/data_types/JSON.md References • sql-reference/data-types/semi_structured/VARIANT.md • using_starrocks/Flat_json.md • table_design/StarRocks_table_design.md • table_design/data_types/variant.md • sql-reference/data-types/data-type-usage-notes.md • administration/management/BE_configuration.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md -8868) • administration/management/FE_configuration.md • using_starrocks/async_mv/materialized_view_task_run_details.md • sql-reference/sql-functions/meta-functions/inspect_mv_relationships.md • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/get_json_string.md • sql-reference/sql-functions/variant-functions/get_variant.md
e
@Rocky regarding the MV, can it be based both on native starrocks tables and iceberg s3 tables and the results to be in the same MV?
r
Yes, you can absolutely do this. In fact, this is one of StarRocks' strongest features—acting as a "bridge" between your data lake (Iceberg/S3) and your real-time warehouse (Native Tables). How it works When you create an Asynchronous Materialized View that joins a native table and an external Iceberg table, StarRocks: 1. Executes the Cross-Catalog Join: It pulls the data from S3 (Iceberg) and joins it with the data in your native StarRocks table. 2. Stores the Result Locally: The joined result is saved into StarRocks' own high-performance storage. 3. Serves Queries Instantly: When you query the MV, StarRocks doesn't need to go to S3 or join the tables again; it just reads the pre-computed, local result. Example Syntax You simply use the full catalog path for the external table and the local path for the native table:
Copy code
sql
CREATE MATERIALIZED VIEW mixed_source_mv
REFRESH ASYNC EVERY(INTERVAL 1 HOUR)
AS
SELECT 
    l.id,
    l.status,            -- From native table
    e.user_region,       -- From Iceberg table
    e.signup_date        -- From Iceberg table
FROM default_catalog.my_db.local_orders AS l
JOIN iceberg_catalog.remote_db.user_profiles AS e 
  ON l.user_id = e.user_id;
Important Considerations: 1. Catalog Names: Ensure you use the full 3-part name (
catalog.database.table
) for the external Iceberg table. 2. Refresh Strategy: Because the data involves an external S3 source, the MV cannot "automatically" refresh the moment an S3 file changes (S3 doesn't send a signal to StarRocks). You must set a schedule (e.g., 
EVERY(INTERVAL 10 MINUTE)
) or refresh it manually. 3. Partitioning: You can even partition the MV. If the Iceberg table is massive and partitioned by 
date
, you can partition the MV by that same 
date
column. StarRocks is smart enough to only refresh the specific partitions that changed in Iceberg (starting from v3.1+ for Iceberg). 4. Query Rewrite: If you have this MV, you can query the original base tables directly. StarRocks' optimizer can automatically "rewrite" the query to use the MV instead, even for cross-catalog joins, significantly speeding up your data lake queries. Why do this? This is a classic "Data Enrichment" pattern. You keep your huge historical logs in the S3 Data Lake (cheaper) and your frequently changing "hot" state in StarRocks native tables, then join them into an MV for high-speed dashboards. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
e
@Rocky can the MV be based only on a external s3 iceberg table ?
r
Yes, you can create a Materialized View (MV) based only on an external S3 Iceberg table. This is a very common use case for Data Lake Acceleration. By doing this, you keep your source data in its original open format (Iceberg/Parquet) while getting the high-speed query performance of StarRocks' local storage. How it works The syntax is almost identical to creating a standard MV, just pointing to the Iceberg catalog:
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_accelerator_mv
-- Refresh every 30 minutes
REFRESH ASYNC EVERY(INTERVAL 30 MINUTE)
AS 
SELECT 
    customer_id,
    sum(order_amount) as total_spent,
    count(order_id) as order_count
FROM iceberg_catalog.sales_db.orders -- Your external table
GROUP BY customer_id;
Key Behaviors to Note: 1. Refresh Mechanism: * Manual or Interval Only: Unlike native StarRocks tables, StarRocks cannot "watch" S3 to see if a file was added. Therefore, MVs on external tables do not support 
REFRESH ASYNC
(triggered by data changes). You must set a fixed interval (e.g., 
EVERY(INTERVAL 1 HOUR)
) or refresh it manually. 2. Partition-Level Refresh (Efficient Updates): * If your Iceberg table is partitioned (e.g., by 
day
), and your MV is also partitioned by that same column, StarRocks is smart enough to perform incremental refreshes. * Starting from v3.1.4, StarRocks can detect which specific partitions in the Iceberg table have changed and will only re-calculate those specific partitions in the MV, rather than refreshing the entire dataset. 3. Query Rewrite: * Even if your users continue to query the original Iceberg table directly, StarRocks can automatically rewrite those queries to hit the MV instead. This means your existing dashboards or reports get a massive speed boost without changing any SQL code. 4. Consistency: * Because the source is external, there is a risk of "staleness" between refreshes. You can control this using the 
mv_rewrite_staleness_second
property, which tells StarRocks whether it's okay to use an MV that is, for example, 5 minutes behind the source data. Summary of Benefits * Cost: Keep the bulk of your data in S3 (cheap). * Speed: Queries hit the MV (fast, local StarRocks storage). * Simplicity: No need for a complex ETL pipeline; the MV handles the data movement and transformation automatically. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/Materialized_view.md References • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
e
@Rocky if s3 iceberg partitioned by time(day), but we want the MV by time(hour)
r
The short answer is no, you cannot have a Materialized View (MV) with a finer partition granularity (hour) than its base Iceberg table (day) if you want to use StarRocks' partition-level incremental refresh. StarRocks requires that the partitioning of an MV and its base table follow an N:1 relationship. This means: * Supported (Rollup): Base table is partitioned by 
hour
$\rightarrow$ MV is partitioned by 
day
(N hours mapped to 1 day). * Not Supported (Refinement): Base table is partitioned by 
day
$\rightarrow$ MV is partitioned by 
hour
(1 day mapped to 24 hours). Why is this restricted? StarRocks tracks data changes at the partition level. When data in an external Iceberg table changes, StarRocks detects which partitions (e.g., 
2023-10-01
) have a new version. * If your MV is partitioned by hour, and the base table only reports that the day partition changed, StarRocks has no metadata to know which specific hour within that day was updated. * Because it can't identify the specific "hour" partitions to refresh, it cannot perform an incremental update. What happens if you try? If you attempt to define an MV partitioned by hour based on a daily-partitioned Iceberg table: 1. Alignment Failure: You will likely see an error during creation stating that the partition column must align with the base table's partition column. 2. Full Refresh: Even if created (depending on the version and specific syntax), the MV would likely trigger a full refresh of the entire table every time, rather than just updating the specific hours that changed, because the "partition-to-partition" mapping is broken. Recommended Workarounds 1. Align Granularity: Partition the MV by day to match the Iceberg source. You can still include an 
hour
column in the MV's 
SELECT
list for your queries, but the physical storage (partitioning) would be daily. 2. Change Source Granularity: If hourly partitions in the MV are a hard requirement for performance/TTL, you should consider re-partitioning the source Iceberg table to 
hour
. 3. Manual Refresh: If you only need to update specific hours occasionally, you can use 
REFRESH MATERIALIZED VIEW ... PARTITION START (...) END (...)
to manually force a refresh on a specific time range, though this bypasses the automatic change tracking. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.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 References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md
e
@Rocky is the following possible, we ingest data to base table in starrocks and after an hour move it to iceberg. the MV is based on the starrocks table by parittion hour , but when the MV is created it takes the initial data to populate from iceberg
r
This is a very effective architecture often called a Unified "Hot/Cold" View. Yes, it is possible and actually a recommended pattern for managing data lifecycles in StarRocks. To achieve this, you define the Materialized View using a 
UNION ALL
between your native StarRocks table (Hot) and your Iceberg table (Cold). How it works 1. The Setup: * Native Table: Used for high-speed ingestion and "Hot" data (e.g., the last hour). * Iceberg Table: Used for long-term storage on S3 (e.g., data older than an hour). * The MV: Defined as a 
UNION ALL
of both. 2. Initial Population: When you first create the MV, StarRocks will execute the query. It will pull all the historical data from the Iceberg table and any current data from the StarRocks table and store them together in the MV's local storage. 3. The "Move" (Handoff): When you move data from StarRocks to Iceberg (usually via 
INSERT INTO iceberg_table SELECT ... FROM sr_table
followed by a 
DELETE
from the native table): * StarRocks detects that a partition was added/updated in Iceberg. * StarRocks detects that a partition was deleted in the native table. * The MV triggers a partition-level refresh for that specific hour. * Because the query is a 
UNION ALL
, the "new" data in Iceberg and the "missing" data in the native table cancel each other out, leaving the MV's data for that hour consistent and stable. Example Implementation
Copy code
sql
CREATE MATERIALIZED VIEW unified_hourly_data
PARTITION BY hourly_column  -- MV is partitioned by hour
REFRESH ASYNC EVERY (INTERVAL 5 MINUTE) -- Check for moves/new data regularly
AS
-- The Hot Data (Native)
SELECT id, data, timestamp_col AS hourly_column 
FROM default_catalog.prod_db.hot_ingestion_table

UNION ALL

-- The Cold Data (Iceberg)
SELECT id, data, timestamp_col AS hourly_column 
FROM iceberg_catalog.archive_db.cold_storage_table;
Key Requirements for this to work: * Partition Alignment: Both the native table and the Iceberg table should ideally be partitioned by the same hourly column. This allows StarRocks to use Partition-Level Incremental Refresh. If only one partition changes (the one you are moving), StarRocks will only refresh that specific hour, not the whole history. * StarRocks Version: Ensure you are on v3.1.4 or later for the best support for Iceberg partition-level change detection. * Atomicity: When "moving" data, try to ensure the data is visible in Iceberg before it is deleted from the native table. If the MV refreshes in the split second between the delete and the insert, you might see a temporary dip in data for that hour. (StarRocks MVs are eventually consistent by design). Why use this approach? * Query Performance: Users query the MV and get local StarRocks speed for both hot and cold data. * Transparency: Your BI tools don't need to know where the data is; they just query one "table" (the MV). * Cost Efficiency: You keep 99% of your data on cheap S3 storage while keeping the "active" ingestion in the high-performance native engine. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
e
@Rocky can a MV be based on another MV
r
Yes, in StarRocks you can build a nested Materialized View, which is a Materialized View (MV) based on one or more existing MVs. This feature was introduced in v2.5 for asynchronous MVs. Key Details: * No Hard Limit on Levels: StarRocks does not strictly limit how many levels deep you can go, but the official recommendation is to not exceed three levels in production environments to avoid excessive complexity and refresh dependencies. * Independent Refreshing: Each MV in the chain has its own refresh strategy. Updating the "base" MV does not automatically trigger an update in the "dependent" MV unless they are both scheduled or manually triggered. * Query Rewrite: StarRocks can transparently rewrite queries to use nested MVs. For example, if you have 
MV_A
based on a table, and 
MV_B
based on 
MV_A
, a query against the original table can be optimized to hit 
MV_B
directly if it matches the logic. Example Syntax:
Copy code
sql
-- First level MV
CREATE MATERIALIZED VIEW mv_level_1
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
AS 
SELECT id, sum(price) as daily_revenue 
FROM sales_table 
GROUP BY id;

-- Second level MV (Nested)
CREATE MATERIALIZED VIEW mv_level_2
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
AS 
SELECT id, daily_revenue * 0.1 as tax 
FROM mv_level_1; -- Based on the first MV
Best Practices: 1. Monitor Refresh Chains: Since refreshes are independent, you should ensure that the base MV completes its refresh before the dependent MV starts. If 
MV_B
refreshes while 
MV_A
is still stale, 
MV_B
will be built using the old data from 
MV_A
. 2. Use Views for Logic, MVs for Performance: Sometimes it is better to build an MV on top of a standard logical 
VIEW
. StarRocks also supports this and can often "see through" the view to the base tables for optimization. References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
e
@Rocky
Copy code
sql
-- First level MV
CREATE MATERIALIZED VIEW mv_level_1
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
AS 
SELECT id, sum(price) as daily_revenue 
FROM sales_table 
GROUP BY id;

-- Second level MV (Nested)
CREATE MATERIALIZED VIEW mv_level_2
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
AS 
SELECT id, daily_revenue * 0.1 as tax 
FROM mv_level_1; -- Based on the first MV
lets say that first level MV has interval of 1 day and is based on iceberg tables partitioned daily can the 2 level be based on that and on the hot table ? (partitoned differned)
r
Yes, you can create a second-level (nested) Materialized View that combines an existing MV with a native "hot" table. However, to maintain partition-level incremental refresh, you must follow the "coarsest granularity" rule. 1. The Granularity Rule For incremental refresh to work across multiple sources, the Materialized View's partition size must be equal to or larger than the partition size of its base tables/MVs. * Source 1 (mv_level_1): Partitioned by Day. * Source 2 (hot_table): Partitioned by Hour. * Target (mv_level_2): Must be partitioned by Day. 2. Why this works (and why Hourly doesn't) * If 
mv_level_2
is partitioned by Day: * When mv_level_1 (Day) updates, StarRocks knows exactly which Day in 
mv_level_2
to refresh (1:1 mapping). * When hot_table (Hour) updates, StarRocks knows which Day that hour belongs to and refreshes that Day (N:1 rollup). This is supported. * If 
mv_level_2
is partitioned by Hour: * When hot_table (Hour) updates, it's a 1:1 mapping. * The Problem: When mv_level_1 (Day) updates, StarRocks doesn't know which of the 24 hours within that day changed. Because it cannot "refine" the partition, it would likely fail to refresh incrementally and require a full refresh of the entire MV. 3. Requirements (StarRocks v3.3+) To use multiple sources (a 
UNION ALL
of an MV and a Table) as "reference tables" for partition tracking, you should be on StarRocks v3.3 or later. Prior to v3.3, an MV could typically only align its partitions with a single base table. Recommended Pattern Define your second-level MV to align with the daily partitions of your long-term storage (
mv_level_1
).
Copy code
sql
CREATE MATERIALIZED VIEW mv_level_2
-- Align with the COARSER granularity (Day)
PARTITION BY date_trunc('day', partition_col)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
AS
-- Cold data from the first MV
SELECT id, revenue, partition_col FROM mv_level_1

UNION ALL

-- Hot data from the native table
SELECT id, revenue, partition_col FROM hot_table;
Summary * Possible? Yes. * Partitioning? Use Day for 
mv_level_2
. * Incremental Refresh? Works perfectly for both sources as long as you rollup the "hot" hourly data into the "cold" daily partition. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md References
e
@Rocky we want the following behavior: our MV_level_1 once a day checks our iceberg table thats partitioned by day and does refresh once a hour our MV_level_2 once a minute does a refresh based on the hot table and the level_1 MV is it possible ?
r
Yes, this is possible. StarRocks allows you to set independent refresh intervals for nested Materialized Views. Here is how your proposed architecture would function: 1. 
MV_level_1
(The "Cold" Layer) * Source: Iceberg Table (Daily partitions). * Schedule: 
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
. * Behavior: Every hour, StarRocks checks the Iceberg metadata. If a new daily partition is detected (or an existing one is updated), 
MV_level_1
will refresh that specific partition locally. 2. 
MV_level_2
(The "Unified" Layer) * Source: 
UNION ALL
of 
MV_level_1
and the native Hot Table. * Schedule: 
REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)
. * Behavior: Every minute, StarRocks checks all its base dependencies. * Hot Table changes: When you ingest data into the native table (every minute), 
MV_level_2
detects the change and refreshes the affected partition (e.g., "today's" partition). * MV_level_1 changes: When 
MV_level_1
finally completes its hourly refresh, 
MV_level_2
will detect that its base MV has new data and will refresh that partition again to include the newly archived Iceberg data. Important Configuration & Rules To make this work reliably, keep the following in mind: * Partition Alignment: 
MV_level_2
must be partitioned at the same or coarser granularity as 
MV_level_1
. Since 
MV_level_1
is partitioned by Day, 
MV_level_2
must also be partitioned by Day. This allows the 1-minute refresh to only re-calculate "today" rather than the whole history. * Stale Base MVs: By default, StarRocks allows a dependent MV to refresh even if its base MV is "stale" (meaning the base MV hasn't pulled the absolute latest data from its own sources yet). This is exactly what you want: 
MV_level_2
will combine the latest "Hot" data with whatever is currently available in 
MV_level_1
. * Data "Gaps" during Move: If you move data from the Hot Table to Iceberg, there may be a window (up to 1 hour, based on your settings) where the data has been deleted from the Hot Table but not yet pulled into 
MV_level_1
. During this window, 
MV_level_2
might not show those specific rows. If this is an issue, consider: * Increasing the refresh frequency of 
MV_level_1
. * Setting 
MV_level_1
to 
REFRESH ASYNC
(without interval) so it triggers as soon as Iceberg changes (if your Iceberg catalog supports notification). Example SQL for 
MV_level_2
Copy code
sql
CREATE MATERIALIZED VIEW mv_unified
PARTITION BY date_trunc('day', event_time)
REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)
AS
SELECT * FROM mv_level_1 -- The historical Iceberg cache
UNION ALL
SELECT * FROM hot_table; -- The real-time native table
References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/Materialized_view-single_table.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md
3 Views
Open in Slack
PreviousNext
Powered by