http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Starrocks, shared-data mode 3.5.16...
# questions-and-troubleshooting
a
@Rocky Starrocks, shared-data mode 3.5.16, having iceberg catalog connected externally. I have a table in iceberg defined as follows: CREATE TABLE 
test_notes_partitioned
( 
id
int(11) DEFAULT NULL, 
notetype
varchar(1073741824) DEFAULT NULL, 
enteredts
datetime DEFAULT NULL ) PARTITION BY (month(enteredts)) I want to create an async materialized view on top of it that correctly leverages the underlying partitioning. How do I do it?
r
To create an asynchronous materialized view (MV) on a partitioned Iceberg table in StarRocks 3.5.x, you must align the MV's partitioning logic with the Iceberg transform. For an Iceberg table partitioned by 
month(enteredts)
, StarRocks maps this to the 
date_trunc('month', <col>)
function. Recommended SQL Construction In StarRocks 3.5.16, you can create the MV as follows:
Copy code
sql
CREATE MATERIALIZED VIEW test_notes_mv
PARTITION BY date_trunc('month', enteredts) -- Matches Iceberg's month(enteredts)
DISTRIBUTED BY RANDOM -- Recommended for shared-data mode if no specific key is needed
REFRESH DEFERRED MANUAL -- Use MANUAL or ASYNC EVERY for external catalogs
AS 
SELECT 
    id, 
    notetype, 
    enteredts
FROM iceberg_catalog.your_db.test_notes_partitioned;
Key Requirements & Details * Partition Alignment: StarRocks 3.3+ automatically recognizes the relationship between Iceberg partition transforms and StarRocks date functions. To leverage the underlying partitioning (enabling incremental refresh and partition pruning), use the mapping: * Iceberg 
month(col)
$\rightarrow$ StarRocks 
date_trunc('month', col)
* Iceberg 
day(col)
$\rightarrow$ StarRocks 
date_trunc('day', col)
* Iceberg 
hour(col)
$\rightarrow$ StarRocks 
date_trunc('hour', col)
* Shared-Data Mode: Since you are in shared-data mode, using 
DISTRIBUTED BY RANDOM
is often the simplest approach, though you can specify a hash key if you frequently join on a specific column. * Refresh Scheme: External catalog MVs do not support automatic refresh triggered by data changes in the base table. You must either: * Trigger it manually: 
REFRESH MATERIALIZED VIEW test_notes_mv;
* Set a scheduled interval: 
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
. * Batch Refresh Control: For large Iceberg tables, use the property 
"partition_refresh_number" = "1"
(default in 3.3+) to refresh partitions one by one, reducing memory pressure on the compute nodes. Verification Once created, you can verify if the partition mapping was successfully established by running:
Copy code
sql
SHOW CREATE MATERIALIZED VIEW test_notes_mv;
Check if the 
partition_expression
correctly maps to the base table. If aligned, StarRocks will only refresh partitions where the underlying Iceberg data has changed. References • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
a
@Rocky - I am getting
Copy code
Getting analyzing error. Detail message: Failed to build mv partition expr from base table: null.
r
The error 
Failed to build mv partition expr from base table: null
typically occurs in StarRocks when the Materialized View (MV) partition expression cannot be successfully mapped to the partition metadata of the underlying Iceberg table. In StarRocks 3.5.x, there are three common reasons for this failure when dealing with Iceberg transforms like `month()`: 1. Correct Syntax for Partition Mapping While earlier versions were more restrictive, StarRocks 3.5.x supports multi-column and expression-based partitioning for Iceberg. For an Iceberg table partitioned by 
month(enteredts)
, you should use the 
date_trunc
function. Crucial: In 3.5, ensure you use parentheses around the partition expression if it is a function, and ensure the column name case matches exactly what is shown in 
DESC iceberg_catalog.db.table
. Try the following refined DDL:
Copy code
sql
CREATE MATERIALIZED VIEW test_notes_mv
-- Wrap the expression in parentheses
PARTITION BY (date_trunc('month', enteredts)) 
DISTRIBUTED BY RANDOM
REFRESH DEFERRED MANUAL
AS 
SELECT 
    id, 
    notetype, 
    enteredts
FROM iceberg_catalog.your_db.test_notes_partitioned;
2. Check for "Partition Evolution" This is the most common cause of the 
null
error. If the Iceberg table's partition strategy was changed (e.g., it was originally unpartitioned and then changed to 
month(enteredts)
, or the column type was changed), Iceberg creates multiple "Partition Specs." * Limitation: StarRocks currently does not support creating partitioned MVs on Iceberg tables that have undergone Partition Evolution (multiple partition specs). * How to check: Run 
DESC EXTENDED iceberg_catalog.your_db.test_notes_partitioned
. If you see multiple specs or the 
partition_spec_id
is not 
0
, the MV creation will likely fail with this error. * Workaround: You would need to rewrite the Iceberg table into a new table with a single, clean partition spec. 3. Column Data Type & Nullability Ensure that 
enteredts
is recognized as a 
DATETIME
or 
DATE
type in StarRocks. * If Iceberg has it as 
timestamptz
(Timestamp with local timezone), StarRocks might struggle to align it with 
date_trunc
unless the session timezones are perfectly aligned. * The error message 
null
sometimes implies the analyzer found the column but could not extract the partition transform logic from the Iceberg metadata. 4. Troubleshooting Steps If the above DDL still fails: 1. Simplify: Try creating the MV without partitioning first: 
CREATE MATERIALIZED VIEW test_notes_mv REFRESH DEFERRED MANUAL AS SELECT ...
If this works, the issue is strictly the partition mapping. 2. Verify Base Partitioning: Run 
SHOW CREATE TABLE iceberg_catalog.your_db.test_notes_partitioned
inside StarRocks. Confirm that StarRocks actually "sees" the table as partitioned by 
month(enteredts)
. If the 
PARTITION BY
clause is missing in the output, StarRocks cannot build a partitioned MV on it. 3. Alias approach: Occasionally, defining the expression in the 
SELECT
and partitioning by the alias helps the analyzer:
Copy code
sql
    PARTITION BY (dt)
    AS SELECT ..., date_trunc('month', enteredts) as dt FROM ...
Recommendation: Check for Partition Evolution first, as it is the most frequent silent killer for Iceberg MV partitioning. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md
a
@Rocky - OK, I got the view created, but refresh fails with the following error: com.starrocks.sql.common.DmlException: Insert has filtered data, txn_id = 1664223, tracking sql = select tracking_log from information_schema.load_tracking_logs where job_id=5070590
r
The error 
Insert has filtered data
means that StarRocks' strict mode is rejecting one or more rows during the refresh process because they don't comply with the destination table's schema or partition rules. To resolve this, follow these steps: 1. Identify the exact cause Run the SQL command provided in your error message to see the specific reason why rows are being filtered:
Copy code
sql
SELECT tracking_log 
FROM information_schema.load_tracking_logs 
WHERE job_id = 5070590;
Common reasons found in this log include: * Column Value Too Long: Your Iceberg table defines 
notetype
as 
varchar(1073741824)
(1GB). StarRocks' 
VARCHAR
has a maximum limit of 1,048,576 bytes (1MB). If any row in Iceberg exceeds 1MB, the refresh will fail. * Invalid Datetime: If 
enteredts
contains "zero dates" (like 
0000-00-00
) or values outside the StarRocks range (
1970-01-01
to 
9999-12-31
), they will be filtered. * Partition Range: If the MV logic cannot map a specific row to a partition (e.g., a 
NULL
value in 
enteredts
while the MV partitioning is strict), it may be rejected. 2. Solutions A. Truncate long strings (Recommended) If the issue is the 1MB 
VARCHAR
limit, use 
SUBSTR
in your MV definition to ensure the data fits:
Copy code
sql
CREATE MATERIALIZED VIEW test_notes_mv
PARTITION BY (date_trunc('month', enteredts))
AS 
SELECT 
    id, 
    SUBSTR(notetype, 1, 1048576) as notetype, -- Truncate to StarRocks max limit
    enteredts
FROM iceberg_catalog.your_db.test_notes_partitioned;
B. Handle NULLs in partitioning If 
enteredts
contains 
NULL
values, the 
date_trunc
function will return 
NULL
, and the row might not have a valid partition to land in. You can handle this with `COALESCE`:
Copy code
sql
AS SELECT ... COALESCE(enteredts, '1970-01-01 00:00:00') as enteredts ...
C. Relax Strict Mode (For testing) You can allow the refresh to proceed by filtering out the bad rows instead of failing the job. Note that this will result in missing data in the MV:
Copy code
sql
-- Set this before triggering a manual refresh in your session
SET enable_insert_strict = false;
REFRESH MATERIALIZED VIEW test_notes_mv;
For a permanent fix on the MV itself, you can add this property to the DDL:
Copy code
sql
PROPERTIES (
    "session.enable_insert_strict" = "false"
)
Check the 
load_tracking_logs
first; it will tell you exactly which column and which row is the culprit is causing is. and which value is causing the failure.
Copy code
column
is failing and it which
Copy code
value
caused the failure.** row is.** References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/data-types/VARCHAR.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/information_schema.md
a
@Rocky Error: The row is out of partition ranges. Please add a new partition.. Row: [123456789, 'UnheatedAbcd, 'AbcDEF', 12345678, 23456789, 12345689, 654321, 1774546644000, NULL, NULL, NULL, 'A', 'AbcDEF', NULL, 0, NULL, NULL, {op:'I',ts:2026-03-26 173724.998000,offset117669315,source'Mydbabc.abc_Defgh',target: 'Mydbabc.abc_ Defgh,key{NoteID 123456789}}, 2026-03-26 133724, 2026-03-01 000000, 2026-02-28 200000]
r
This error occurs because of a timezone mismatch between how Iceberg stores its partitions and how StarRocks calculates them during the MV refresh. The Cause 1. Iceberg Partitioning: Iceberg's 
month()
transform always operates on UTC. 2. StarRocks Session Timezone: If your StarRocks session or global timezone is set to a local timezone (e.g., 
America/New_York
), StarRocks converts the Iceberg timestamp to that local time when reading it. 3. The Conflict: In your error log, the value 
2026-03-01 00:00:00
(UTC) was shifted by 4 hours to 
2026-02-28 20:00:00
. * Iceberg says this row belongs in the March partition. * The MV (using your local timezone) calculates 
date_trunc('month', ...)
as February. * Since the refresh task is currently only processing the "March" partition, it rejects the "February" row with the error: 
The row is out of partition ranges
. The Solution: Force UTC in the Materialized View You must ensure the Materialized View refresh session uses UTC so that its date calculations align perfectly with Iceberg's internal metadata. Recreate the Materialized View with the 
session.time_zone
property:
Copy code
sql
CREATE MATERIALIZED VIEW test_notes_mv
PARTITION BY (date_trunc('month', enteredts))
DISTRIBUTED BY RANDOM
REFRESH DEFERRED MANUAL
PROPERTIES (
    "session.time_zone" = "UTC" -- Crucial: Aligns MV logic with Iceberg UTC partitions
)
AS 
SELECT 
    id, 
    notetype, 
    enteredts
FROM iceberg_catalog.your_db.test_notes_partitioned;
Verification After recreating it with the UTC property, trigger the refresh again:
Copy code
sql
REFRESH MATERIALIZED VIEW test_notes_mv;
Why this works By setting 
"session.time_zone" = "UTC"
, StarRocks will not apply any offset to the 
enteredts
column when reading from Iceberg. 
2026-03-01 00:00:00
remains 
2026-03-01 00:00:00
, and 
date_trunc('month', ...)
correctly yields the "March" partition, matching the Iceberg source partition exactly. Note on Data Types: If the Iceberg column is 
timestamptz
, StarRocks always converts it to the session timezone. If it is a naive 
timestamp
, StarRocks treats it as UTC by default. In both cases, forcing the MV's session to 
UTC
is the safest way to ensure partition alignment for external catalogs. References • administration/management/timezone.md • 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/troubleshooting_asynchronous_materialized_views.md ized_views) ) References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • faq/loading/Broker_load_faq.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • administration/management/timezone.md
a
@Rocky - After applying "session.time_zone" = "UTC" still getting Error: The row is out of partition ranges. Please add a new partition.. Row: [123456789, 'BEF', '- XX xxxxxxx xx xxxx xxxxxxxx xx xx xxxxxxx xxx xxxx xxxxxxxx. - Xxxxxxxx xxx xxxx xxxxxx xx xxx Xxxxxxxx xx xxxx xxxxxxxx. - Xxxxxx xxxx xxx xxxx xxxxxxxx xxxxxx xxxxxxxxxxxxxxxxx. Xx xxxxxxx xxxxxx xxx xxxx xxxx xxx xxxxx xxxxxxxxx xxx, xxxxx xxxxx xxx Xxxxxxxx & xxxxxx. Xxxx xxxxx xxxxx xxx xxxxxxxx xxxxxxxxx xx xxxxxx. Xxx xx xxxx xxxx xxxx xxx xxxx xxxxxxxxx., 99999999, NULL, NULL, 999999, 1784672989597, NULL, NULL, NULL, 'X', NULL, NULL, 9, NULL, NULL, {op:'I',ts:2026-07-22 022955.302000,offset123263810,source'Mydbabc.abc_Defgh',target:'Mydbabc.abc_Defgh',key{NoteID123456789}}, 2026-07-21 222949.597000, 2026-07-01 000000, 2026-06-30 200000]
r
The 4-hour difference between the timestamps in your error message (
2026-07-01 00:00:00
vs 
2026-06-30 20:00:00
) confirms a timezone mismatch between the StarRocks Frontend (FE) and the refresh task environment. The row 
2026-07-21
is failing because StarRocks calculates its partition destination using a -4h offset (likely 
America/New_York
time), which for rows near the start of the month (like July 1st at midnight) would push them into June. Even for rows in the middle of the month, this "drift" causes the analyzer to reject the row because it doesn't align with the UTC-based partition metadata StarRocks read from Iceberg. 1. The Root Cause: Global vs. Session Timezone While you added 
"session.time_zone" = "UTC"
to the MV properties, some internal StarRocks processes (especially partition boundary calculation in the FE) may still be using the Global timezone or the System timezone of the machine running the StarRocks FE. 2. Recommended Solution: Align Global Timezone The most reliable way to fix this for Iceberg Materialized Views is to ensure the entire StarRocks cluster and the underlying OS are synchronized to UTC. 1. Check your current Global Timezone:
Copy code
sql
    SHOW VARIABLES LIKE 'time_zone';
    SHOW VARIABLES LIKE 'system_time_zone';
2. Set the Global Timezone to UTC:
Copy code
sql
    SET GLOBAL time_zone = 'UTC';
Note: After running this, you must close your current connection and open a new one for it to take effect. 3. Ensure the FE server OS time is UTC: StarRocks documentation explicitly recommends that the FE machine's system time should be UTC to avoid metadata mapping errors with external catalogs like Iceberg. 3. Alternative Workaround (DDL Change) If you cannot change the global timezone, try to make the partition expression "immune" to time-of-day shifts by casting to a 
DATE
first. A 
DATE
does not have a time component, so a 4-hour shift is less likely to move it across a month boundary (unless the data is at midnight). Try recreating the MV with this specific casting:
Copy code
sql
CREATE MATERIALIZED VIEW test_notes_mv
-- Cast to DATE first to strip time-of-day sensitivity
PARTITION BY (date_trunc('month', mv_date)) 
DISTRIBUTED BY RANDOM
REFRESH DEFERRED MANUAL
PROPERTIES (
    "session.time_zone" = "UTC"
)
AS 
SELECT 
    id, 
    notetype, 
    enteredts,
    CAST(enteredts AS DATE) as mv_date -- Materialize as a DATE
FROM iceberg_catalog.your_db.test_notes_partitioned;
4. Check for Partition Evolution If the error persists, verify if the Iceberg table has undergone Partition Evolution (e.g., the partitioning was changed from 
days
to 
months
, or added later). * Run: 
DESC EXTENDED iceberg_catalog.your_db.test_notes_partitioned;
* If you see 
partition_spec_id
values other than 
0
or multiple specs, StarRocks may struggle to map the partitions correctly even with the right timezone. In this case, the only solution is to rewrite the Iceberg table into a fresh table with a single partition spec. Summary: Your data is "leaking" between partitions because the FE calculates the range in one timezone and the refresh task evaluates the data in another. Setting 
GLOBAL time_zone = 'UTC'
is the standard fix. References • administration/management/timezone.md • 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/troubleshooting_asynchronous_materialized_views.md ed_views)
a
@Rocky - tried DDL change, getting:
Copy code
Getting analyzing error from line 38, column 4 to line 38, column 47. Detail message: Materialized view partition function date_trunc check failed: date_trunc('month', CAST(`enteredts` AS DATE)).
r
The error 
Materialized view partition function date_trunc check failed
occurs because StarRocks requires the 
PARTITION BY
expression to be a simple, direct application of 
date_trunc
on a column. Nested expressions like 
date_trunc(..., CAST(...))
are not supported. Regarding the persistent 
row is out of partition ranges
error, the 4-hour offset (
2026-07-01
vs 
2026-06-30 20:00:00
) indicates that your StarRocks Frontend (FE) is still calculating partition boundaries in a local timezone (likely 
America/New_York
), while Iceberg data is strictly UTC. To fix this, you must align the StarRocks cluster's global environment to UTC. Follow these steps exactly: 1. Set the Global Timezone to UTC Setting the timezone in the MV properties affects the refresh task, but the metadata for partition ranges (the boundaries of the MV's internal table) is often determined by the global session or FE system time. Run this command in your SQL client:
Copy code
sql
SET GLOBAL time_zone = 'UTC';
Note: You must reconnect your session for this to take effect. 2. Verify FE System Time StarRocks recommends that the physical servers running the FE nodes have their OS timezone set to UTC. If your server is set to a local time, the FE might interpret "July 1st" as "July 1st Local," which conflicts with Iceberg's "July 1st UTC." Check the system variables:
Copy code
sql
SHOW VARIABLES LIKE '%time_zone%';
If 
system_time_zone
is not UTC, you should ideally change the OS timezone of the FE server and restart the FE. 3. Recreate the Materialized View After setting the global timezone to UTC and reconnecting, recreate the MV using the standard syntax (without the 
CAST
):
Copy code
sql
CREATE MATERIALIZED VIEW test_notes_mv
PARTITION BY (date_trunc('month', enteredts))
DISTRIBUTED BY RANDOM
REFRESH DEFERRED MANUAL
PROPERTIES (
    "session.time_zone" = "UTC"
)
AS 
SELECT 
    id, 
    notetype, 
    enteredts
FROM iceberg_catalog.your_db.test_notes_partitioned;
Why the previous 
CAST
failed The error 
Materialized view partition function date_trunc check failed
happened because StarRocks' analyzer is very strict about the partition expression. It expects: * 
PARTITION BY (col)
* 
PARTITION BY (date_trunc('unit', col))
* 
PARTITION BY (date_trunc('unit', alias))
— only if 
alias
is a simple column or a 
str2date
transformation for string columns. By setting the Global Timezone to UTC, you ensure that when StarRocks creates the partition range 
[2026-07-01, 2026-08-01)
, it treats those dates as UTC. The incoming Iceberg data will then fall perfectly into those ranges without being shifted by 4 hours. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • administration/management/timezone.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/sql-functions/date-time-functions/date_trunc.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md 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
a
@Rocky - how should I correctly use partitioning on 
enteredmonth
varchar(1073741824) NOT NULL column from iceberg?
r
To correctly partition a Materialized View (MV) using a 
VARCHAR
column like 
enteredmonth
from Iceberg, you must follow a specific aliasing pattern. StarRocks does not allow nested functions (like 
date_trunc(..., str2date(...))
) directly in the 
PARTITION BY
clause. Instead, you must transform the string in the SELECT list and then reference that alias in the partition clause. Step-by-Step Implementation 1. Identify your string format Determine what the strings in 
enteredmonth
look like (e.g., 
'2026-03-01'
or 
'2026-03'
). You will use this in the 
str2date
function. 2. The Correct MV Syntax Use the following pattern to avoid the "partition function check failed" error:
Copy code
sql
CREATE MATERIALIZED VIEW test_notes_mv
-- Use the ALIAS from the SELECT list here
PARTITION BY date_trunc('month', mv_partition_date)
DISTRIBUTED BY RANDOM
REFRESH DEFERRED MANUAL
PROPERTIES (
    "session.time_zone" = "UTC"
)
AS 
SELECT 
    id, 
    notetype, 
    enteredmonth,
    -- Transform the string to a DATE type and ALIAS it
    str2date(enteredmonth, '%Y-%m-%d') as mv_partition_date 
FROM iceberg_catalog.your_db.test_notes_partitioned;
Note: If your 
enteredmonth
string is only a month (e.g., 
'2026-03'
), use 
str2date(concat(enteredmonth, '-01'), '%Y-%m-%d')
. Why this works: 1. Strict Partition Syntax: StarRocks requires the 
PARTITION BY
clause to contain only a direct column name or a single function call on a column (or alias). By moving the 
str2date
logic into the 
SELECT
list, you satisfy this requirement. 2. Type Alignment: Partitioning in StarRocks works best with 
DATE
or 
DATETIME
types. Using 
str2date
converts your 
VARCHAR
into a type StarRocks can use for range-based partition management. 3. Timezone Stability: Since you are now partitioning based on a hardcoded string (
'2026-03-01'
) rather than a dynamic 
timestamp
value, the 4-hour offset issue you encountered earlier is naturally avoided. The string remains 
'2026-03-01'
regardless of the session timezone. Key Rules for Partitioning by Strings: * Aliasing is mandatory: You cannot put the conversion logic inside 
PARTITION BY (...)
. * One-to-one vs Rollup: * If you want 1:1 mapping with the string: 
PARTITION BY str2date(enteredmonth, '%Y-%m-%d')
. * If you want to ensure monthly rollup (standard practice): 
PARTITION BY date_trunc('month', alias)
. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-functions/date-time-functions/str2date.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md
3 Views
Open in Slack
PreviousNext
Powered by