http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> - starrocks 4.1.1 - deployed in sha...
# questions-and-troubleshooting
a
@Rocky - starrocks 4.1.1 - deployed in shared-data mode on kubernetes using kubernetes-operator. I have a materialized view doing SELECT * FROM iceberg_catalog_hms.test.test_table - how do I do an auto refresh version of this materialized view?
r
In StarRocks 4.1.1, materialized views (MVs) based on external catalogs (like Iceberg) do not support event-triggered auto-refresh (where StarRocks is notified immediately of a commit in Iceberg). Instead, you must configure a scheduled asynchronous refresh. Since you are in shared-data mode on Kubernetes, the syntax and behavior are consistent with standard StarRocks asynchronous MVs. 1. Recommended Syntax To set up an auto-refreshing version of your query, use the 
REFRESH ASYNC EVERY
clause.
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_mv
-- Optional: Partitioning the MV by the same column as the base table 
-- allows StarRocks to refresh only changed partitions (Incremental Refresh).
PARTITION BY (dt) 
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 5 MINUTE) -- Set your desired refresh frequency
AS 
SELECT * FROM iceberg_catalog_hms.test.test_table;
2. Key Considerations for Iceberg MVs * Partition-Level Refresh (Incremental): Since StarRocks v3.1.4+, the engine can detect data changes in Iceberg at the partition level. If your Iceberg table is partitioned (e.g., by 
dt
) and your MV is also partitioned by that same column, StarRocks will only refresh the partitions that have changed since the last refresh, significantly saving resources. * Metadata Polling: For the MV to "see" that the Iceberg table has changed, StarRocks needs to refresh its metadata cache. Starting from v3.3.3, StarRocks supports a periodic metadata refresh strategy for Iceberg. * Ensure the FE configuration 
enable_iceberg_metadata_disk_cache
is enabled (default) or that you haven't disabled background metadata refreshing. * Query Rewrite Staleness: If you want your queries to be automatically rewritten to use this MV even if the MV is a few seconds/minutes behind the base Iceberg table, you can set the staleness property:
Copy code
sql
    ALTER MATERIALIZED VIEW iceberg_mv SET ("mv_rewrite_staleness_second" = "300");
3. Monitoring the Refresh Because these refreshes happen in the background, you can monitor the status and history of the "auto" tasks using:
Copy code
sql
-- Check the status of the MV refresh tasks
SELECT * FROM information_schema.task_runs 
WHERE task_name LIKE 'mv-%' 
ORDER BY create_time DESC;

-- Manually trigger a refresh if needed
REFRESH MATERIALIZED VIEW iceberg_mv;
Summary for your environment * Shared-data mode: Refresh tasks will run on your default warehouse. Ensure your compute nodes have sufficient resources for the background 
INSERT OVERWRITE
operations. * Kubernetes-operator: Ensure your FE has network access to the Hive Metastore (HMS) and the underlying Iceberg storage (S3/HDFS) to perform the metadata checks and data pulls. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • data_source/catalog/iceberg/iceberg_catalog.md
a
@Rocky - starrocks 4.1.1 - deployed in shared-data mode on kubernetes using kubernetes-operator. I have an async materialized view doing SELECT * FROM iceberg_catalog_hms.test.test_table and I know that 4.1.1 introduced incremental refresh in addition to pct as well as auto mode to automatically switch between the two. What syntax should I use to leverage the newly added auto mode for the materialized view?
r
In StarRocks 4.1.1, the new 
auto
 refresh mode is controlled by the 
refresh_mode
property. This mode allows StarRocks to automatically choose the most efficient refresh strategy (Incremental vs. Full/PCT) for each refresh task. To leverage this, you include 
"refresh_mode" = "auto"
in the 
PROPERTIES
clause of your 
CREATE MATERIALIZED VIEW
statement. Recommended Syntax For an Iceberg table in an HMS catalog, the definition would look like this:
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_auto_mv
-- 1. Partitioning is required for Incremental Refresh to work
PARTITION BY (dt) 
DISTRIBUTED BY HASH(id)
-- 2. Set your refresh schedule
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    -- 3. The new v4.1 property
    "refresh_mode" = "auto"
)
AS 
SELECT * FROM iceberg_catalog_hms.test.test_table;
How 
refresh_mode
Works * 
auto
(New in v4.1): StarRocks attempts an incremental refresh (refreshing only the changed partitions in Iceberg). If an incremental refresh is not possible (e.g., due to a non-partitioned change, a complex join, or metadata issues), it automatically falls back to a full refresh (
PCT
mode). This provides the best balance of performance and reliability. * 
incremental
: Forces StarRocks to perform only incremental refreshes. If the refresh cannot be done incrementally for any reason, the task will fail rather than falling back to a full refresh. * 
pct
(Legacy/Default): Always performs a full refresh of the materialized view partitions. Important Notes for v4.1.1 1. Rebuilding Required: You cannot 
ALTER
an existing materialized view created with the legacy 
PCT
mode to use 
auto
. You must drop and recreate the MV to enable the new refresh engine logic. 2. Iceberg Partition Transforms: If your Iceberg table uses partition transforms (like 
day(ts)
or 
month(ts)
), ensure your MV's 
PARTITION BY
expression matches the corresponding StarRocks function (e.g., 
date_trunc('day', ts)
) to ensure the 
auto
mode can successfully map partitions for incremental updates. 3. Monitoring: You can verify which mode was actually used for a specific run by checking the 
EXTRA_MESSAGE
column in the 
information_schema.task_runs
table:
Copy code
sql
    SELECT task_name, create_time, state, extra_message
    FROM information_schema.task_runs
    ORDER BY create_time DESC LIMIT 5;
The 
extra_message
JSON will contain a 
"refreshMode"
field indicating whether that specific run was 
INCREMENTAL
or 
PCT
. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References • release_notes/release-3.3.md • release_notes/release-2.5.md
a
@Rocky - I am getting Error Code: 1064. Getting analyzing error. Detail message: Invalid refresh_mode: auto. Only INCREMENTAL, PCT are supported..
r
The error message you received—`Invalid refresh_mode: auto. Only INCREMENTAL, PCT are supported.`—indicates that while the 
refresh_mode
property exists in StarRocks 4.1.1, the value 
auto
is not currently an allowed option in the DDL for that specific version. In StarRocks 4.1.x, the "AUTO" mode was introduced to automatically switch between the new row-level incremental engine (IVM) and the classic partition-level engine (PCT). However, in version 4.1.1, several behaviors regarding this feature were restricted or disabled (as noted in the 4.1.1 release notes and PR #71355). How to Fix the Error To achieve an "auto-refreshing" materialized view (MV) on Iceberg in StarRocks 4.1.1, you should choose one of the two supported modes: Option 1: 
refresh_mode
= 
"INCREMENTAL"
(Recommended for Iceberg) This uses the new Incremental Refresh (IVM) engine introduced in v4.1. It is designed specifically for Iceberg append-only tables to process only the delta changes (rows) rather than entire partitions.
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_mv
PARTITION BY (dt) 
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 5 MINUTE)
PROPERTIES (
    "refresh_mode" = "INCREMENTAL"
)
AS 
SELECT * FROM iceberg_catalog_hms.test.test_table;
Option 2: 
refresh_mode
= 
"PCT"
(The Default) This uses the classic Partition-level Incremental Refresh. If your Iceberg table is partitioned, StarRocks will detect which partitions have changed and refresh only those partitions.
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_mv
PARTITION BY (dt) 
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 5 MINUTE)
PROPERTIES (
    "refresh_mode" = "PCT"
)
AS 
SELECT * FROM iceberg_catalog_hms.test.test_table;
Key Differences in 4.1.1 * Query Rewrite: Note that in version 4.1.1, query rewrite is currently disabled for MVs using 
refresh_mode = "INCREMENTAL"
. If you need your queries to be automatically routed to the MV, you should use 
refresh_mode = "PCT"
for now. * Automatic Triggering: For Iceberg (and all external catalogs), StarRocks does not yet support "event-triggered" refresh (where the MV updates the instant a commit happens in Iceberg). You must use the 
REFRESH ASYNC EVERY
clause to set a polling interval. Summary If your goal is to have the MV update itself without manual intervention, the "auto" part comes from the 
REFRESH ASYNC
schedule, not the 
refresh_mode
. Use 
INCREMENTAL
for the most efficient resource usage on Iceberg, or 
PCT
if you require query rewrite support for automatic query rewriting. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • release_notes/release-3.3.md
2 Views
Open in Slack
PreviousNext
Powered by