http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> ```[RBO] [PHASE1] MV rewrite fail f...
# questions-and-troubleshooting
k
@Rocky
Copy code
[RBO] [PHASE1] MV rewrite fail for my_mv: query table size: 1, mv table size: 3, can not try view delta rewrite because no FK constraints
what causes this
r
This message indicates that StarRocks attempted a View Delta Join rewrite but failed because it couldn't guarantee the correctness of the result without specific constraints. What is happening? * Query table size: 1 vs MV table size: 3: Your query only involves 1 table, but your Materialized View (MV) is built on a join of 3 tables. * View Delta Rewrite: For the MV to "rewrite" your query, StarRocks must "prune" (ignore) the 2 extra tables in the MV. * The Conflict: StarRocks can only remove those extra tables if it is certain that joining them does not change the number of rows (i.e., they are cardinality-preserving joins). If the joins are 
INNER JOINs
, the optimizer needs to know that every row in the primary table has exactly one matching row in the joined tables. The Cause The rewrite failed because the optimizer does not have enough information to prove that the extra tables in the MV are "safe" to remove. Without Foreign Key (FK) and Unique Key constraints, StarRocks assumes that joining those tables might filter out rows or duplicate them, which would make the MV result different from the base table query. How to fix it You need to define Unique Constraints and Foreign Key Constraints on your base tables to act as hints for the optimizer. Note that StarRocks does not enforce these constraints during loading; you must ensure the data integrity yourself. 1. Define Unique/Primary Keys: Ensure the join columns in the "dimension" tables have unique constraints. 2. Define Foreign Keys: Define the relationship on the "fact" table pointing to the dimension table. Example: If your MV joins 
sales
(fact) with 
product
(dimension) on 
product_id
, you should set the constraints like this:
Copy code
sql
-- 1. Ensure the dimension table has a unique/primary key
ALTER TABLE product SET ("unique_constraints" = "product_id");

-- 2. Define the foreign key on the fact table
ALTER TABLE sales SET (
    "foreign_key_constraints" = "(product_id) REFERENCES product(product_id)"
);
Once these constraints are added, the optimizer will understand the 1:N relationship and allow the View Delta rewrite to proceed. Summary of Requirements * For Inner Joins: Both Unique Key and Foreign Key constraints are required. * For Left Joins: Only a Unique Key constraint on the right table is typically required to prune the join. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • using_starrocks/accelerate-cardinality-preserving-joins.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
k
@Rocky create materialized view hash bucket partition on iceberg hash bucket table
r
You can create an asynchronous Materialized View (MV) on an Iceberg table that is both partitioned and hash-bucketed. However, there is a critical distinction regarding how StarRocks handles Iceberg's "bucket" transform. 1. Can you align partitions? * Supported: If your Iceberg table is partitioned using 
Identity
, 
hour
, 
day
, 
month
, or 
year
transforms, you can align the MV's partitions with the Iceberg partitions for incremental refreshes. * NOT Supported: If the Iceberg table uses the 
bucket(<col>, <n>)
 partition transform, StarRocks cannot currently use that transform as the partition expression for the MV's partition-level refresh. 2. General Syntax Even if you cannot align with an Iceberg 
bucket
partition, you can still create an MV on that table. The MV itself can have its own partitioning (based on a different column) and its own hash distribution (bucketing).
Copy code
sql
CREATE MATERIALIZED VIEW my_iceberg_mv
-- 1. Partitioning (Must map to a supported Iceberg column/transform)
PARTITION BY (iceberg_date_col) 
-- 2. Bucketing/Distribution (Standard StarRocks internal distribution)
DISTRIBUTED BY HASH(id) BUCKETS 16
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
AS 
SELECT id, name, iceberg_date_col 
FROM iceberg_catalog.db.iceberg_table;
3. Key Constraints & Rules * Partition Mapping: To enable partition-level incremental refresh, the MV's 
PARTITION BY
expression must map to the base table's partition column. As noted, 
bucket
and 
truncate
transforms from Iceberg are not supported for this mapping. * Internal Distribution: The 
DISTRIBUTED BY HASH
clause in the MV definition controls how StarRocks stores the data across its own BE nodes. This is independent of how the data is stored in the Iceberg data lake. * Iceberg Partition Transforms: * 
Identity
→ Map directly: 
PARTITION BY (col)
* 
day(col)
→ Map using: 
PARTITION BY (date_trunc('day', col))
* 
bucket(col, n)
→ Unsupported for partition alignment. Recommendation If your Iceberg table is partitioned via 
bucket(col, n)
, you should create the MV without a 
PARTITION BY
clause (making it a non-partitioned MV that refreshes fully) OR partition the MV by a different supported column (like a 
DATE
or 
STRING
column) if you need partial refreshes. You can always use 
DISTRIBUTED BY HASH
to optimize query performance on the MV itself. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
k
@Rocky how to make this mv complete successfully: Cancelled: Cancelled by pipeline engine, reason: Memory limit exceeded: Memory of process exceed limit. read and decompress page Backend: starrocks-etl-cn-5.starrocks-etl-cn-search.starrocks-etl.svc.cluster.local, Used: 50448882144, Limit: 50444390890. Mem usage has exceed the limit of BE:
r
This error occurs because the Materialized View (MV) refresh task is consuming more memory than the Backend (BE) process is allowed to use. In your case, it hit a limit of ~47 GB. To fix this and make the MV complete successfully, apply these strategies in order: 1. Enable "Spill to Disk" (Easiest Fix) From v3.1 onwards, StarRocks can spill intermediate calculation results (like large joins or aggregations) to disk instead of failing when memory is full.
Copy code
sql
ALTER MATERIALIZED VIEW my_mv SET ('session.enable_spill' = 'true');
2. Limit Refresh Scope (Most Effective for Large Data) If your MV is partitioned, you can prevent it from trying to refresh too many partitions at once. This reduces the peak memory footprint by processing data in smaller "batches." * 
partition_refresh_number
: Limits how many partitions are refreshed in a single job. * 
session.query_mem_limit
: Specifically increases the memory allowed for this refresh task.
Copy code
sql
ALTER MATERIALIZED VIEW my_mv SET (
    "partition_refresh_number" = "1", -- Refresh 1 partition at a time
    "session.query_mem_limit" = "64424509440" -- Increase memory limit for this MV (e.g., to 60GB)
);
3. Adjust Resource Group Memory StarRocks handles MV refreshes in a default resource group called 
default_mv_wg
. By default, it is limited to 80% of the BE memory. If your BE is under heavy load, you can check or adjust this limit in `be.conf`: * Parameter: 
default_mv_resource_group_memory_limit
(Default is 
0.8
). * If you are on a managed environment or cannot change 
be.conf
, prioritize the 
session.query_mem_limit
mentioned in Step 2. 4. Optimize the MV Design If the error persists even with spilling, the query itself might be too complex for the current BE hardware: * Add Partitioning: If your MV is not partitioned, StarRocks must refresh the entire table every time. Adding 
PARTITION BY
is the best long-term fix for memory issues. * Check Join Types: Ensure you aren't accidentally creating a Cartesian product (large many-to-many joins) which causes memory to explode during the "read and decompress" phase. How to verify the fix After making changes, you can monitor the real-time memory usage of the refresh task:
Copy code
sql
-- Check current running tasks
SHOW PROC '/current_queries'; 
-- Check the specific error and query ID of the last failure
SELECT * FROM information_schema.task_runs WHERE task_name = 'mv_name' ORDER BY create_time DESC;
References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/BE_configuration.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md References • faq/Sql_faq.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/BE_configuration.md • administration/Meta_recovery.md
k
@Rocky this is a large dimensional data view that is not partitioned
r
Because this is a non-partitioned view, StarRocks must perform a "Full Refresh" every time. This means it loads and processes the entire dataset in a single transaction, which is highly memory-intensive for large dimensional joins. Since you cannot rely on partition-level refreshes, use these specific techniques to manage the memory footprint: 1. Enable Spill to Disk (Required) For a large, non-partitioned MV, this is the most critical setting. It allows StarRocks to move intermediate data (like large join build tables or sort buffers) from memory to disk if it hits the limit.
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET (
    "session.enable_spill" = "true"
);
2. Increase the Query Memory Limit The error shows your BE/CN is capped at ~47GB. If your hardware has more RAM available, you can explicitly tell this MV to allow more memory consumption for its refresh task.
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET (
    "session.query_mem_limit" = "107374182400" -- e.g., Set to 100GB (in bytes)
);
3. Reduce Parallelism to Save Memory If the refresh is still OOMing, it may be because StarRocks is trying to process too many things in parallel, each consuming its own memory buffer. You can trade speed for memory stability by lowering the Degree of Parallelism (
pipeline_dop
).
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET (
    "session.pipeline_dop" = "4" -- Try a low number like 2 or 4
);
4. Use a Dedicated Resource Group By default, MVs run in 
default_mv_wg
, which is often restricted to 80% of BE memory. You can create a dedicated resource group for this specific "heavy" MV to ensure it doesn't compete with other queries and has a higher memory ceiling.
Copy code
sql
-- 1. Create a group
CREATE RESOURCE GROUP heavy_mv_group
SAMPLERS = (cpu_core_limit = 8, mem_limit = 0.9); -- Allow up to 90% memory

-- 2. Assign the MV to it
ALTER MATERIALIZED VIEW your_mv_name SET ("resource_group" = "heavy_mv_group");
5. Architectural Recommendation If this view continues to fail, you should reconsider the unpartitioned design. * Even if the data is "dimensional" (e.g., a flat table of products or users), if it grows into the hundreds of millions of rows, you should add a "dummy" partition (like a 
last_updated_date
or even a 
hash(id) % 10
bucket column). * Partitioning allows you to use 
partition_refresh_number = 1
, which forces StarRocks to process the data in smaller, manageable chunks instead of one massive, memory-exploding job. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/resource_group.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • faq/Sql_faq.md • knowledge/usage/best-practice.md
k
@Rocky does starrocks support hash(id) partitioning?
r
StarRocks does not support 
hash(id)
as a partitioning expression (
PARTITION BY
). In StarRocks, "Partitioning" and "Bucketing" are two distinct levels of data distribution, and 
hash
is used exclusively for the second level (Bucketing). 1. The Difference in StarRocks * Partitioning (Level 1): Used for data lifecycle (TTL) and coarse-grained pruning. Supports 
Range
, 
List
, and 
Expression
(mostly time-based or direct column values). * Bucketing (Level 2): Used for parallelizing queries and evening out data distribution across the cluster. This is where you use 
DISTRIBUTED BY HASH(id)
. 2. Can you use it for your MV? Since you are trying to solve an OOM (Out of Memory) issue on a large dimensional view: * 
DISTRIBUTED BY HASH(id)
 will help balance the data on disk, but it will not help with the MV refresh memory issue because it doesn't allow "partition-level" incremental refreshes. * To solve the OOM, the MV must be Partitioned (Level 1). If your data doesn't have a natural date column, you cannot currently use a 
hash()
or 
modulo
function to create "artificial" partitions. 3. Workarounds for Large Dimensional Tables If your dimensional data is too large for a single-shot refresh and has no date column, consider these options: * Partition by a Category/ID Column: If you have a column with relatively low cardinality (e.g., 
tenant_id
, 
region_id
, or 
category_code
), you can use Column Expression Partitioning:
Copy code
sql
    CREATE MATERIALIZED VIEW my_mv
    PARTITION BY (category_column) -- Creates one partition per unique value
    DISTRIBUTED BY HASH(id)
    AS SELECT ... FROM table;
Then set 
ALTER MATERIALIZED VIEW my_mv SET ("partition_refresh_number" = "1");
to refresh one category at a time. * Add a "Batch ID" in Source: If possible, add a column in your source table like 
batch_id = id % 10
. You can then 
PARTITION BY (batch_id)
in the MV to break the refresh into 10 smaller, memory-safe jobs. * Scale the Backend (CN/BE): If the data is truly "flat" and cannot be partitioned, you must rely on Spill to Disk and higher memory limits (as discussed in the previous step) or increase the physical RAM of your nodes. References • table_design/data_distribution/Data_distribution.md • table_design/data_distribution/expression_partitioning.md • best_practices/partitioning.md References • table_design/data_distribution/Data_distribution.md • table_design/data_distribution/expression_partitioning.md • best_practices/partitioning.md • table_design/data_distribution/Random_distribution.md • table_design/data_distribution/Hash_distribution.md References • table_design/data_distribution/Data_distribution.md • best_practices/partitioning.md
k
@Rocky how do i manage making materialized views that take ~2 hours with spot instance interruption
r
Managing a 2-hour refresh on spot instances is risky because StarRocks treats a single Materialized View (MV) refresh as an atomic transaction. If the node executing the task (BE/CN) is interrupted, the entire process fails and rolls back; there is no built-in "checkpoint" or "resume" for a single SQL query. To make this succeed, you must move away from the "one big 2-hour task" model. Here are the strategies to handle spot instance interruptions: 1. Artificial Partitioning (The Best Solution) Since your data isn't naturally partitioned, you should create an artificial partition. This is the only way to get "checkpoints." If you have 10 partitions, and a spot instance dies during the 8th partition, StarRocks (or your retry logic) only needs to finish the remaining ones. * How to do it: Add a column in your source table (or use an existing one) like 
hash(id) % 10
and name it 
refresh_bucket
. * Create the MV with partitioning:
Copy code
sql
    CREATE MATERIALIZED VIEW my_mv
    PARTITION BY (refresh_bucket)
    PROPERTIES (
        "partition_refresh_number" = "1" -- Refresh only one bucket at a time
    )
    AS SELECT ..., hash(id) % 10 as refresh_bucket FROM ...;
* Result: Instead of one 120-minute job, you have ten 12-minute jobs. If a node is reclaimed, you only lose ~12 minutes of work. 2. Increase Automatic Retries (v3.3+) If you are on StarRocks v3.3 or later, you can configure the Frontend (FE) to automatically retry failed MV refreshes. This won't save progress, but it will restart the job automatically when a new node is available. * FE Configuration:
Copy code
sql
    -- Check current settings
    ADMIN SHOW FRONTEND CONFIG LIKE '%max_mv_refresh_failure_retry_times%';

    -- Set to retry 3 times (Default is 1)
    ADMIN SET FRONTEND CONFIG ("max_mv_refresh_failure_retry_times" = "3");
3. Ensure FE Stability In a spot instance environment, your FE nodes must be on On-Demand (Stable) instances. * The FE is the "scheduler." If the FE dies, it loses the state of the running task. * If the BE/CN (compute) nodes are spot instances, the FE can detect the failure and trigger the retry mentioned above. 4. Adjust the Timeout A 2-hour refresh might exceed the default timeout, causing it to fail even without a spot interruption. Ensure the timeout is longer than your expected execution time:
Copy code
sql
ALTER MATERIALIZED VIEW my_mv SET ('session.insert_timeout' = '10800'); -- 3 hours
5. External Orchestration (Airflow/dbt) If the internal scheduler is too limited for your spot instance volatility, consider: 1. Setting the MV to 
REFRESH MANUAL
. 2. Using an external tool like Airflow to trigger the refresh. 3. Airflow can handle complex retry logic, backoffs, and alerts much better than the internal StarRocks scheduler when infrastructure is unstable. Summary Without partitioning, a 2-hour task is a "all-or-nothing" gamble. Option 1 (Artificial Partitioning) is the industry-standard way to handle long-running ETL on preemptible hardware because it effectively turns your partitions into "save points." References • administration/management/FE_configuration.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • 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 References
k
@Rocky is there a way to add the column to the mv then partition it by that instead of adding to source table
r
Directly answering your question: No, you cannot partition a Materialized View (MV) if the base table is not partitioned. In StarRocks, the partitioning of an MV is strictly tied to the partitioning of its base tables. This "Partition Alignment" is how StarRocks knows which specific parts of the MV to refresh when data changes. Why this limitation exists StarRocks tracks "Data Versions" at the partition level. * If the base table is partitioned, StarRocks knows "Partition A changed, so I only need to refresh Partition A in the MV." * If the base table is not partitioned, StarRocks only sees the table as one single entity. It has no way to map a "calculated column" in your MV back to a specific segment of the base table. Therefore, it cannot perform the "one-partition-at-a-time" refresh that you need to avoid OOMs and spot interruptions. The "Least Painful" Workaround If you absolutely cannot change the schema of your source table, you have one primary architectural option to get the "checkpointing" effect for spot instances: The "Intermediate Partitioned Table" Pattern: 1. Create a new internal StarRocks table that is partitioned (e.g., 
PARTITION BY (batch_id)
where 
batch_id
is 
id % 10
). 2. Use an 
INSERT INTO ... SELECT
 to pull data from your large non-partitioned view into this new table. _ _Note:* You can run this in 10 small manual batches (e.g., 
WHERE id % 10 = 0
, then 
WHERE id % 10 = 1
) to avoid OOM and survive spot kills. 3. Build your MV on top of this new partitioned table. Summary of what won't work: * Adding the column in the MV SELECT: You can add the column (e.g., 
SELECT id % 10 as bucket
), but the 
PARTITION BY (bucket)
clause will fail during creation because the base table isn't partitioned. * Views: Creating a view that adds the column and then an MV on top of that view also won't work, because the view itself is not partitioned. Recommendation: If you want to survive 2-hour spot instance windows, you must find a way to land that data into a partitioned physical table first. This breaks the "all-or-nothing" refresh cycle into smaller, resumable chunks. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/Materialized_view.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md
k
@Rocky how to check percent completion of mv refresh
r
To check the progress of a Materialized View (MV) refresh in StarRocks, you can query the system metadata tables or use process monitoring commands. 1. Check the 
PROGRESS
Column (Recommended) The most direct way to see a percentage is by querying the 
information_schema.task_runs
table. First, find the 
task_name
for your MV (usually in the format 
mv-<id>
):
Copy code
sql
SHOW MATERIALIZED VIEWS LIKE 'your_mv_name';
Then, query the task history to see the progress:
Copy code
sql
SELECT 
    TASK_NAME, 
    CREATE_TIME, 
    STATE, 
    PROGRESS 
FROM information_schema.task_runs 
WHERE TASK_NAME = 'mv-12345' -- Replace with your task_name
ORDER BY CREATE_TIME DESC 
LIMIT 1;
Note on "Percent" Accuracy: * Partitioned MVs: If your MV is partitioned and refreshes multiple partitions, the 
PROGRESS
column will update as each partition completes (e.g., if 5 out of 10 partitions are done, it may show 50%). * Unpartitioned MVs: Since a single refresh is one atomic transaction, the progress will often stay at 0% and then jump immediately to 100% once the transaction commits. 2. Monitor Real-Time Progress (Rows Scanned) If the 
PROGRESS
column is stuck at 0% because you have a single large unpartitioned refresh, you can monitor the actual data being processed using the process list:
Copy code
sql
SHOW PROC '/current_queries';
Look for the query that starts with 
INSERT OVERWRITE <your_mv_name>
. You can track: * ScanRows / ScanBytes: How much data has been read from the source table so far. * CPUTime / MemoryUsage: Current resource consumption. * ExecTime: How long the query has been running. 3. Check Task Run Details (v3.1+) For deeper insight into which partitions are pending or completed, you can parse the 
EXTRA_MESSAGE
field in `task_runs`:
Copy code
sql
SELECT 
    TASK_NAME,
    get_json_string(EXTRA_MESSAGE, '$.mvPartitionsToRefresh') AS partitions_to_do,
    get_json_string(EXTRA_MESSAGE, '$.basePartitionsToRefreshMap') AS source_data_status
FROM information_schema.task_runs
WHERE STATE = 'RUNNING';
References • using_starrocks/async_mv/Materialized_view.md • sql-reference/information_schema/task_runs.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md References • sql-reference/sql-statements/materialized_view/SHOW_ALTER_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
2 Views
Open in Slack
PreviousNext
Powered by