@Rocky how to create MV based on table with partition evolution: Query 1 ERROR at Line 3: : Getting analyzing error. Detail message: Do not support create materialized view when base iceberg table core_opportunity has done partition evolution.

Based on the error message and recent StarRocks development tracking (e.g., GitHub issue #64617), StarRocks does not currently support creating Materialized Views (MVs) on Apache Iceberg tables that have undergone partition evolution. Why this happens In Apache Iceberg, partition evolution allows a table to change its partitioning scheme (e.g., changing from

days(timestamp)

to

identity(category)

) without rewriting existing data. This results in the table having multiple "partition specs" simultaneously. StarRocks' analyzer currently blocks MV creation in this scenario because the mapping between the base table's multiple partition specs and the MV's partition logic is too complex to ensure consistent incremental refreshes and query rewrites. Recommended Workarounds 1. Unify the Iceberg Table's Partitioning The most direct way to resolve this is to rewrite the Iceberg table so that all data conforms to a single (the latest) partition spec. You can do this using Spark or Flink procedures: * Rewrite Data Files: Run a maintenance job (like

rewrite_data_files

in Spark) to re-organize the data according to the current partition spec. * Once the table metadata no longer references multiple active partition specs, StarRocks will allow the creation of the MV. 2. Manual Materialization (Local Table + INSERT INTO) If you cannot rewrite the base table, you can simulate the behavior of an asynchronous MV by creating a native StarRocks table and a scheduled task: 1. Create a local StarRocks table with the desired schema and partitioning. 2. Use a scheduled task (or a simple script/airflow job) to run:

sql
    INSERT INTO starrocks_table SELECT * FROM iceberg_catalog.db.core_opportunity;

This approach bypasses the analyzer check for MVs but requires you to manage the refresh logic (like manual partition overwrites) yourself. 3. Materialize through a standard View If your goal is just to have a simplified representation, you can create a standard View. However, this won't provide the performance benefits of a Materialized View since it won't pre-compute or store the data. Status and Versioning This is a known limitation that has been tracked as an enhancement request in the StarRocks community. As of mid-2026, there is no official version that supports MVs on evolved Iceberg tables without first unifying the partitioning on the Iceberg side. If you need this feature for production workloads, I recommend monitoring StarRocks GitHub Issue #64617 for updates or reaching out to the community Slack for the latest status on development. 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/feature-support-asynchronous-materialized-views.md

@Rocky ive done rewrite data files but starrocks will still not create the mv

The reason StarRocks still blocks the Materialized View creation after

rewrite_data_files

is that StarRocks' analyzer checks the metadata history of the Iceberg table, not just the physical layout of the current data. Why

rewrite_data_files

isn't enough In Apache Iceberg, when you evolve a partition (e.g., from

days(ts)

to

identity(ts)

), a new "Partition Spec" is created. Even if you rewrite all data files to conform to the latest spec, the table's metadata still contains a list of all historical partition specs. StarRocks currently uses a strict check: if the Iceberg table has more than one partition spec in its history (

table.specs().size() > 1

), it returns the error you're seeing. This is because the logic for incremental partition-level refresh cannot yet safely guarantee correctness when a table's partitioning rules have changed over time. How to resolve this 1. The "Reset" Method (Recreate the Table) Since Iceberg does not allow you to easily "delete" old partition specs from a table's metadata, the most reliable way to create an MV is to move the data to a fresh table with a single partition spec: 1. Create a new table with the final desired partition scheme. 2. Migrate the data:

INSERT INTO new_table SELECT * FROM core_opportunity

. 3. Create the MV based on

new_table

. 4. (Optional) Swap the table names. 2. Create the MV without Partition Alignment If you don't strictly need the Materialized View to be partitioned in the same way as the base table (i.e., you are okay with a full refresh or the MV doesn't need to be partitioned), you can try creating the MV without the

PARTITION BY

clause. However, if the analyzer still blocks it, the "Reset" method above is required. 3. Verify the Specs You can check how many partition specs your table has by querying the Iceberg metadata (if using Spark or a compatible engine):

sql
-- In Spark
SELECT * FROM catalog.db.table.snapshots;
-- Check the 'summary' column for 'spark.app.id' or look at the metadata.json file 
-- to see the "partition-specs" array.

If that array has more than one entry, StarRocks will trigger the error. Status of the fix This is a known limitation tracked in StarRocks GitHub Issue #64617. The community is working on allowing MVs on evolved tables by verifying if all active data actually belongs to a single spec, but until that logic is released, recreatng the table is the only path forward for using MVs. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • data_source/catalog/iceberg/procedures.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References • data_source/catalog/iceberg/procedures.md • 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

@Rocky if we prune snapshots to include just the 1 partition will this work

Unfortunately, pruning snapshots will not work. Even if you prune all but the latest snapshot and reduce the data to a single partition, the Iceberg table metadata still maintains a historical list of all partition specs that have ever been defined for that table. Why pruning fails When StarRocks encounters an Iceberg table, it checks the

specs

array in the Iceberg

metadata.json

. * Partition Evolution in Iceberg creates a new

spec-id

rather than replacing the old one. * StarRocks' current analyzer check for Materialized Views is often hardcoded to block MV creation if

table.specs().size() > 1

. *

expire_snapshots

and

rewrite_data_files

clean up data files and snapshot entries, but they do not remove historical partition specs from the metadata. The only effective workarounds Since the metadata history is the blocker, you have two primary options: 1. Recreate the Base Table (Cleanest) To remove the multiple partition specs, you must create a "fresh" table that has only ever known one partition scheme: 1. Create a new Iceberg table:

CREATE TABLE core_opportunity_new AS SELECT * FROM core_opportunity

. 2. Delete the old table (or rename it). 3. Create your Materialized View based on the new table. Because this new table only has a single spec (

spec-id: 0

), the StarRocks check will pass. 2. Create the MV without Partitioning If you do not require the Materialized View itself to be partitioned (i.e., you are okay with a full refresh rather than a partition-aligned incremental refresh), try creating the MV without a

PARTITION BY

clause. However, in many versions, the analyzer may still block this if it sees the base table has evolution. 3. Check StarRocks Version A fix for this specific limitation was recently addressed in the community (GitHub Issue #64617). * If you are on an older version of StarRocks (e.g., 3.1 or 3.2), you will likely remain blocked. * Check if you can upgrade to a more recent version (like v3.3.5+ or v3.4+) where support for MVs on evolved Iceberg tables has been improved. Summary:

rewrite_data_files

and

expire_snapshots

optimize the data layout but do not "reset" the metadata. To unblock the MV creation on your current version, you must recreate the table to wipe the partition evolution history from the metadata. References • data_source/catalog/iceberg/procedures.md • 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

@Rocky how to cancel scheduled recurring task

To cancel or stop a scheduled recurring task in StarRocks, the command depends on what type of "task" you are running. 1. For Generic Tasks (

SUBMIT TASK

) If you created a recurring task using the

SUBMIT TASK

command (often used for scheduled ETL or

INSERT INTO

jobs), use the following: * To delete the task entirely:

sql
    DROP TASK <task_name>;

* To pause/suspend the task (StarRocks v4.1+):

sql
    ALTER TASK <task_name> SUSPEND;

_Note: To resume it later, use

ALTER TASK <task_name> RESUME;

._ To find your task name, you can query:

SELECT * FROM INFORMATION_SCHEMA.tasks;

────────── 2. For Materialized View Refresh Schedules If you have an asynchronous Materialized View with a

SCHEDULE

(e.g.,

REFRESH ASYNC EVERY(INTERVAL 1 DAY)

), you can change it to a manual refresh mode to "cancel" the schedule:

sql
ALTER MATERIALIZED VIEW <mv_name> REFRESH ASYNC;

*This removes the recurring interval while keeping the MV. If you want to stop a currently running refresh, use:*

sql
CANCEL REFRESH MATERIALIZED VIEW <mv_name>;

────────── 3. For Routine Load Jobs If you are referring to a data loading job from Kafka:

sql
STOP ROUTINE LOAD FOR <job_name>;

Use

PAUSE ROUTINE LOAD

if you only want to stop it temporarily. ────────── 4. For Statistics Collection Jobs If you created a custom automatic statistics collection job using `CREATE ANALYZE`:

sql
DROP ANALYZE <id>;

To find the ID, run

SHOW ANALYZE JOB;

. Summary Table Task Type | Command to Stop/Remove ETL / General Task |

DROP TASK <name>

MV Schedule |

ALTER MATERIALIZED VIEW <name> REFRESH ASYNC

Kafka Load |

STOP ROUTINE LOAD FOR <name>

Stats Collection |

DROP ANALYZE <id>

References • sql-reference/sql-statements/loading_unloading/ETL/DROP_TASK.md • sql-reference/sql-statements/loading_unloading/ETL/ALTER_TASK.md • sql-reference/sql-statements/materialized_view/CANCEL_REFRESH_MATERIALIZED_VIEW.md • sql-reference/sql-statements/cbo_stats/CREATE_ANALYZE.md References • sql-reference/sql-statements/cbo_stats/CREATE_ANALYZE.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/pipe/CREATE_PIPE.md