http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> StarRocks 3.5.20 shared_data mode. ...
# questions-and-troubleshooting
a
@Rocky StarRocks 3.5.20 shared_data mode. External iceberg catalog is mapped as iceberg_catalog_hms. I have an iceberg table that is partitioned with create statement. I want to create an ASYNC partitioned materialized view in StarRocks that is doing SELECT * from this table. How do I do it? The table create statement is as follows: CREATE TABLE 
table_a
( 
id_1
varchar(1073741824) DEFAULT NULL, 
id_2
varchar(1073741824) DEFAULT NULL, 
attribute_1
varchar(1073741824) DEFAULT NULL, 
status_value
varchar(1073741824) DEFAULT NULL, 
status_reason
varchar(1073741824) DEFAULT NULL, 
payload_data
varchar(1073741824) DEFAULT NULL, 
timestamp_epoch
bigint(20) DEFAULT NULL, 
timestamp_utc
datetime DEFAULT NULL, 
customer_id
int(11) DEFAULT NULL, 
location_id
int(11) DEFAULT NULL ) PARTITION BY (day(timestamp_utc)) PROPERTIES ( "write.format.default" = "PARQUET", "location" = "s3a://<storage-path>/<container>/table_a", "write.parquet.compression-codec" = "zstd" ); I want to create an ASYNC partitioned materialized view in StarRocks that is doing SELECT * from this table. How do I do it?
r
To create an asynchronous partitioned materialized view (MV) in StarRocks 3.5.20 that maps to an Iceberg table partitioned by 
day(timestamp_utc)
, you should use the 
date_trunc
function in the MV's 
PARTITION BY
clause. From StarRocks v3.5.0 onwards, the mapping for Iceberg partition transforms is explicit. For an Iceberg 
day
transform, you use 
date_trunc('day', column_name)
. SQL Template Assuming your database name in the Iceberg catalog is 
your_db
, use the following syntax:
Copy code
sql
CREATE MATERIALIZED VIEW mv_table_a
PARTITION BY date_trunc('day', timestamp_utc)
DISTRIBUTED BY HASH(id_1) -- Choose a column with high cardinality for distribution
REFRESH ASYNC EVERY (INTERVAL 1 HOUR) -- Or MANUAL if you prefer manual refreshes
AS 
SELECT * FROM iceberg_catalog_hms.your_db.table_a;
Key Requirements & Notes: 1. Partition Alignment: StarRocks uses the 
date_trunc('day', ...)
expression to align its internal partitions with the Iceberg table's day-transformed partitions. This allows StarRocks to perform partition-level incremental refreshes, only updating the MV partitions where the source Iceberg data has changed. 2. Mapping Table: For StarRocks 3.5.x, the mapping for Iceberg transforms is: * 
day(<col>)
$\rightarrow$ 
PARTITION BY date_trunc('day', <col>)
* 
month(<col>)
$\rightarrow$ 
PARTITION BY date_trunc('month', <col>)
* 
year(<col>)
$\rightarrow$ 
PARTITION BY date_trunc('year', <col>)
* 
hour(<col>)
$\rightarrow$ 
PARTITION BY date_trunc('hour', <col>)
3. Distribution: You must specify a 
DISTRIBUTED BY
clause (e.g., 
HASH(id_1)
). While StarRocks 3.1+ supports random bucketing, for an MV of this scale, hash distribution on a unique/high-cardinality ID is generally recommended for performance. 4. Shared-Data Mode: In shared-data mode, the MV data is stored in your remote object storage (S3/OSS), similar to other StarRocks tables. 5. Refresh Strategy: * `REFRESH ASYNC EVERY (INTERVAL ...)`: Automatically checks for changes at the specified interval. * `REFRESH DEFERRED MANUAL`: Creates the MV without an initial refresh; you trigger it later with 
REFRESH MATERIALIZED VIEW mv_table_a
. Troubleshooting Tip: If you find that StarRocks is performing a full refresh instead of a partition refresh, ensure that the partition column (
timestamp_utc
) is present in the 
SELECT
list (which 
SELECT *
handles) and that the 
date_trunc
granularity exactly matches the Iceberg partition transform. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
a
@Rocky - starrocks returns:
Copy code
Getting analyzing error. Detail message: Do not support create materialized view when base iceberg table has partition evolution.
r
The error message 
Do not support create materialized view when base iceberg table has partition evolution
indicates that StarRocks has detected that your source Iceberg table (
table_a
) has undergone Partition Evolution. In Iceberg, partition evolution allows you to change the partition spec (e.g., changing from partitioning by 
month
to 
day
, or adding a new partition column) without rewriting existing data. However, StarRocks currently requires a stable, single partition spec to build the 1-to-1 or N-to-1 partition mapping required for a partitioned materialized view. If the table has multiple partition specs (Spec ID 0, Spec ID 1, etc.), StarRocks cannot yet align its internal partitions across these different schemas. How to verify if your table has evolved You can check the partition specs of your Iceberg table by querying the metadata via StarRocks:
Copy code
sql
SELECT * FROM iceberg_catalog_hms.your_db.`table_a$partitions`;
If you see data files associated with different partition structures or if you check the table history and see multiple partition specs, this is the cause. Workarounds 1. Create a Non-Partitioned Materialized View If the dataset is not excessively large, you can create the MV without the 
PARTITION BY
clause. Note that this means StarRocks will perform a full refresh every time instead of an incremental partition-level refresh.
Copy code
sql
CREATE MATERIALIZED VIEW mv_table_a
DISTRIBUTED BY HASH(id_1)
REFRESH ASYNC EVERY (INTERVAL 1 DAY)
AS 
SELECT * FROM iceberg_catalog_hms.your_db.table_a;
2. Rewrite the Iceberg Table If you require partition-level refreshes for performance, you must ensure the source table has a single, consistent partition spec. You can achieve this by rewriting the Iceberg table: * Create a new Iceberg table with the desired partition spec. * 
INSERT INTO
the new table from the old one (this forces all data into the new spec). * Point the Materialized View to the new table. 3. Use a Standard View with Query Acceleration StarRocks 3.x has significant optimizations for querying Iceberg directly (Data Cache, IO coalescing, etc.). If you don't need the specific pre-computation of an MV, a standard 
VIEW
might provide sufficient performance without the partition mapping limitation:
Copy code
sql
CREATE VIEW v_table_a AS 
SELECT * FROM iceberg_catalog_hms.your_db.table_a;
Future Support Support for Iceberg partition evolution in materialized views is an active area of development (tracked in StarRocks GitHub issues like #64617). While some metadata support for evolution was added in later versions (e.g., v4.0), v3.5.20 still enforces this restriction for partitioned MVs to ensure data consistency during incremental refreshes. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md
a
@Rocky How should the iceberg table look to achieve the result I need?
r
To achieve a partitioned materialized view in StarRocks 3.5.20, your Iceberg table must have a single, consistent partition spec. The "partition evolution" error occurs because Iceberg allows a table to have multiple historical partition definitions (Spec IDs), and StarRocks requires the table to have only one active spec that applies to all data files. Here is how the Iceberg table needs to look and how to fix it: 1. The "Clean" Table Requirement For StarRocks to accept the mapping, the Iceberg table's metadata should show only one partition spec (usually Spec ID 0 or the current active ID). If you create the table from scratch and never run an 
ALTER TABLE ... SET PARTITION SPEC
command, it will work. 2. How to "Fix" the Current Table If your current table has already evolved, you must rewrite it so that all data is consolidated into a single partition spec. You can do this via Spark (using Iceberg procedures) or by performing a CTAS (Create Table As Select). Option A: Create a New "Clean" Table (Recommended) The most reliable way to clear the evolution history is to create a fresh table and move the data:
Copy code
sql
-- 1. Create a new table with the EXACT same partition logic
CREATE TABLE iceberg_catalog_hms.your_db.table_a_new (
  id_1 varchar,
  ...
  timestamp_utc datetime
)
PARTITION BY (day(timestamp_utc))
PROPERTIES (
 "write.format.default" = "PARQUET"
);

-- 2. Insert data from the old evolved table into the new table
-- This forces all data into the new, single partition spec
INSERT INTO iceberg_catalog_hms.your_db.table_a_new 
SELECT * FROM iceberg_catalog_hms.your_db.table_a;

-- 3. Drop the old table and rename (if your catalog supports it) or update your MV to point to 'table_a_new'
Option B: Rewrite Data Files (Using Spark/Iceberg) If you have access to Spark, you can use the 
rewrite_data_files
procedure. Note that simply rewriting files sometimes doesn't remove the old Spec IDs from the metadata; a CTAS (Option A) is often safer for StarRocks compatibility. 3. How to Verify the Table is "Clean" Before trying to create the Materialized View again, check the metadata of your Iceberg table to ensure there is only one spec. While StarRocks doesn't have a direct "show specs" command, you can check the files metadata. If you see multiple different partition paths for the same column logic (or files with null partitions where there should be some), the evolution is still present. 4. Final Materialized View Syntax Once the table is "clean" (single partition spec), this command will succeed:
Copy code
sql
CREATE MATERIALIZED VIEW mv_table_a
PARTITION BY date_trunc('day', timestamp_utc) -- Must match Iceberg day() transform
DISTRIBUTED BY HASH(id_1)
REFRESH ASYNC
AS 
SELECT * FROM iceberg_catalog_hms.your_db.table_a_new;
Summary of Constraints * No 
ALTER TABLE ... DROP/ADD PARTITION
: In Iceberg, these actions evolve the spec. * One Spec ID: All data files in the table must belong to the same Partition Spec ID. * Direct Mapping: The 
date_trunc
granularity in StarRocks must exactly match the transform (
day
, 
month
, 
year
) used in Iceberg.
8 Views
Open in Slack
PreviousNext
Powered by