http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> how would I partition a materialize...
# questions-and-troubleshooting
t
@Rocky how would I partition a materialized view on a tables that has this as the partition expression? 
PARTITION BY (from_unixtime(timestampMillis / 1000, '%Y-%m'))
r
To partition a materialized view (MV) based on a table that uses a custom partition expression like 
PARTITION BY (from_unixtime(timestampMillis / 1000, '%Y-%m'))
, you need to ensure the MV's partition key maps to the base table's partitions. In StarRocks, when a base table uses Expression Partitioning, the partitions are effectively defined by the result of that expression (in your case, a string in the format 
YYYY-MM
). Recommended Approach To align the MV partitions with the base table, you should select the same expression (or an equivalent one) in your MV and use the 
str2date
function in the 
PARTITION BY
clause. Converting the string to a 
DATE
type in the MV is highly recommended because it enables features like partition-based TTL (
partition_ttl
) and better performance. Example SQL:
Copy code
sql
CREATE MATERIALIZED VIEW your_mv_name
-- Use str2date to convert the string result to a DATE type for the MV's partition
PARTITION BY str2date(mv_month, '%Y-%m')
DISTRIBUTED BY HASH(some_id)
REFRESH ASYNC
AS
SELECT 
    -- You must include the partition expression in the SELECT list
    from_unixtime(timestampMillis / 1000, '%Y-%m') AS mv_month,
    some_id,
    SUM(amount) AS total_amount
FROM your_base_table
GROUP BY mv_month, some_id;
Key Requirements & Versioning * Partition Alignment: For the MV to support partition-level incremental refresh, the partition column in the MV must align one-to-one with the base table's partitions. * String vs. Date: * Since v3.1.4+: You can partition an MV directly on a 
STRING
type. You could simply use 
PARTITION BY mv_month
without 
str2date
. * Older versions: You must use 
str2date
to convert string partition keys to a 
DATE
or 
DATETIME
type. * SELECT List: The column used in the MV's 
PARTITION BY
clause must be present in the 
SELECT
list of the MV definition. Why use 
str2date
? Even if your version supports string partitioning, using 
str2date
to turn the 
YYYY-MM
string into a 
DATE
is preferred because: 1. TTL Support: You can use 
PROPERTIES ("partition_ttl" = "12 month")
to automatically drop old partitions. 2. Query Pruning: Standard date-based filters in queries will more reliably prune partitions in the MV. If your base table is an External Table (like Hive or Iceberg) and the partition column is already a string, the same logic applies: use 
str2date
in the MV's 
PARTITION BY
to map it. ` clause to map that string column to a formal partitions. 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/from_unixtime.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md
t
Copy code
CREATE MATERIALIZED VIEW connected_sessions
DISTRIBUTED BY HASH(`clientId`)
REFRESH ASYNC
-- Pair every connected event with the very next disconnected event
AS WITH connected_pairs AS (
    SELECT c.clientId, c.timestampMillis AS connectedAtMs, c.disconnectedAtMs
    -- this subquery pairs all rows with the next row where connected=0
    FROM (SELECT *,
   			MIN(CASE WHEN connected = 0 THEN timestampMillis END) OVER (PARTITION BY clientId ORDER BY timestampMillis DESC) AS disconnectedAtMs
    	FROM connected_client
    	ORDER BY timestampMillis DESC) c
    -- filter out just the connected=1 events
    WHERE c.connected = 1
    ORDER BY timestampMillis DESC
)
-- Now we need to filter out duplicate connected events, so we only care about the first connected event
-- for each matching disconnect event. So we group by client and disconnected time and then select the
-- earliest connection time.
SELECT
	clientId,
	MIN(connectedAtMs) AS connectedAtMs,
	disconnectedAtMs,
	(disconnectedAtMs - MIN(connectedAtMs)) AS sessionDuration
FROM connected_pairs
GROUP BY clientId, disconnectedAtMs;
I have a complex MV @Rocky
r
To partition this complex Materialized View (MV) so that it aligns with your base table's expression partitioning, you need to follow these steps: 1. Include the Partition Expression: The column or expression used for partitioning the base table must be present in the MV's 
SELECT
list. 2. Align the Types: Since your base table expression 
from_unixtime(..., '%Y-%m')
produces a 
STRING
, you should ideally use 
str2date
in the MV's 
PARTITION BY
clause to treat it as a 
DATE
. This enables TTL and better partition management. 3. Group By the Partition Key: Because your MV uses aggregations (
GROUP BY
), the partition column must be included in the 
GROUP BY
clause. Recommended MV Definition
Copy code
sql
CREATE MATERIALIZED VIEW connected_sessions
-- Align the MV partition with the base table's month-level partitions
PARTITION BY str2date(session_month, '%Y-%m')
DISTRIBUTED BY HASH(`clientId`)
REFRESH ASYNC
AS 
WITH connected_pairs AS (
    SELECT 
        c.clientId, 
        c.timestampMillis AS connectedAtMs, 
        c.disconnectedAtMs,
        -- Derive the month string to match the base table's partition logic
        from_unixtime(c.timestampMillis / 1000, '%Y-%m') AS session_month
    FROM (
        SELECT *,
               MIN(CASE WHEN connected = 0 THEN timestampMillis END) 
               OVER (PARTITION BY clientId ORDER BY timestampMillis DESC) AS disconnectedAtMs
        FROM connected_client
    ) c
    WHERE c.connected = 1
)
SELECT
    clientId,
    MIN(connectedAtMs) AS connectedAtMs,
    disconnectedAtMs,
    (disconnectedAtMs - MIN(connectedAtMs)) AS sessionDuration,
    session_month -- Must be in SELECT list for partitioning
FROM connected_pairs
GROUP BY 
    clientId, 
    disconnectedAtMs, 
    session_month; -- Must be in GROUP BY for aggregated MVs
Important Considerations for Your Specific MV * Window Functions and Query Rewrite: Materialized views containing window functions (like your 
MIN(...) OVER(...)
) generally do not support transparent query rewrite. You will need to query the MV directly (e.g., 
SELECT * FROM connected_sessions
) rather than querying the base table and expecting StarRocks to redirect to the MV. * The "Cross-Partition" Challenge: Your logic pairs a 
connected
event with a 
disconnected
event. * If a session starts in January (
connectedAtMs
) but ends in February (
disconnectedAtMs
), the record will be placed in the January partition of the MV (based on our 
session_month
derived from 
connectedAtMs
). * Refresh Dependency: If a new "disconnect" event arrives in the February partition of the base table, StarRocks might not automatically know it needs to refresh the January partition of the MV to complete the pair. * Solution: For sessionization logic that spans partitions, it is often safer to set the property 
"partition_refresh_number" = "-1"
(to refresh all) or use a manual refresh strategy if you find that late-arriving disconnects aren't updating older session records in the MV. * Version Note: Partitioning an MV on a 
STRING
column (the result of 
from_unixtime
) is supported in StarRocks v3.1.4+. If you are on an older version, you must use the 
str2date
conversion shown above. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References
t
@Rocky Detail message: Please check the partition expression str2date(session_month, '%Y-%m'), it should refer base table's partition column directly.
r
To partition a materialized view (MV) that is based on a table using an Expression Partitioning (like 
from_unixtime(timestampMillis / 1000, '%Y-%m')
), you must ensure that the MV's partition key maps directly and one-to-one to the base table's partitioning. The error message 
it should refer base table's partition column directly
typically occurs for two reasons: 1. CTE/Alias Tracking: The partition key in the MV's 
PARTITION BY
clause is an alias (
session_month
) that StarRocks cannot trace back to the base table's partition column through the CTE or complex subquery. 2. Partition Alignment Barrier: Your query uses a window function (
OVER (PARTITION BY clientId ...)
) that is partitioned by a different key than the base table (which is partitioned by time). This creates a "cross-partition dependency" that makes the MV ineligible for partition-based incremental refresh. Why your current MV is failing In StarRocks, to align a partitioned MV: * The MV's 
PARTITION BY
expression must be a direct function of the base table's partition column. * The logic must ensure that a change in one partition of the base table only affects one partition of the MV. * The Issue: Your window function 
MIN(...) OVER (PARTITION BY clientId ...)
looks at all data for a specific client across all time. If a new "disconnected" event arrives in June, it might change the 
disconnectedAtMs
for a "connected" event that occurred in May. This means a change in the June partition of the base table affects the May partition of the MV. StarRocks cannot safely partition an MV when such cross-partition dependencies exist. How to fix it 1. If you must use 
PARTITION BY
(Partitioned Refresh) To make this work as a partitioned MV, you must simplify the query so StarRocks can guarantee partition alignment. You should use the same expression as the base table and ensure the partition column is in the 
GROUP BY
. Note: You may need to move the partition expression out of the CTE to the top-level 
SELECT
or ensure the alias name is extremely clear.
Copy code
sql
CREATE MATERIALIZED VIEW connected_sessions
-- Use the alias directly (Supported in v3.1.4+)
PARTITION BY session_month 
DISTRIBUTED BY HASH(`clientId`)
REFRESH ASYNC
AS 
SELECT
    clientId,
    MIN(connectedAtMs) AS connectedAtMs,
    disconnectedAtMs,
    (disconnectedAtMs - MIN(connectedAtMs)) AS sessionDuration,
    -- Reference the expression directly in the final SELECT
    from_unixtime(connectedAtMs / 1000, '%Y-%m') AS session_month
FROM (
    -- Innermost query to pair events
    SELECT 
        clientId, 
        timestampMillis AS connectedAtMs, 
        connected,
        MIN(CASE WHEN connected = 0 THEN timestampMillis END) 
            OVER (PARTITION BY clientId ORDER BY timestampMillis DESC) AS disconnectedAtMs
    FROM connected_client
) t
WHERE connected = 1
GROUP BY clientId, disconnectedAtMs, session_month;
2. Dealing with the "Direct Reference" Error If you still get the "refer directly" error, it means StarRocks's optimizer is losing the connection between 
session_month
and the base table's 
timestampMillis
because of the subquery. Try these adjustments: * Check Version: Ensure you are on v3.1.4 or higher if you are partitioning by a 
STRING
result. * Match Expression: Use the exact same expression in the MV as the base table: 
PARTITION BY from_unixtime(timestampMillis / 1000, '%Y-%m')
. * Remove the CTE: Try rewriting the query as a nested subquery rather than a CTE, as some StarRocks versions handle partition tracking better in nested queries. Important Limitation Because your window function uses 
PARTITION BY clientId
(and not time), StarRocks might ultimately reject the partitioned MV creation even with correct syntax because the refresh logic is not "partition-safe." If you cannot resolve the alignment, you may have to create a non-partitioned MV and refresh the whole table, or use a shorter 
partition_ttl
.
Recommended Debug Step: Try to create a very simple partitioned MV on the table first:
Copy code
sql
CREATE MATERIALIZED VIEW test_partition 
PARTITION BY sm
REFRESH ASYNC
AS SELECT clientId, from_unixtime(timestampMillis / 1000, '%Y-%m') as sm FROM connected_client;
If this works, then the complexity (window function/aggregation) in your original query is what's breaking "partition alignment. is what's preventing the partition. is what is breaking the "direct mapping. is what is breaking the "direct" connection. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md
t
is there any other incremental refresh strategy I could use? @Rocky
r
Besides Partitioned Refresh (which is what you are trying to configure), StarRocks recently introduced a new log-based incremental refresh mode, but it has strict limitations that may impact your specific query. Here are the three incremental strategies available for a complex MV like yours: 1. Partitioned Refresh (Recommended) This is the strategy you are currently attempting. It is "incremental" at the partition level—only the partitions of the MV that correspond to changed partitions in the base table are updated. To fix the error "it should refer base table's partition column directly", you must ensure StarRocks can trace the MV partition key back to the base table's partition key through all subqueries. How to fix your SQL: * You must include the exact raw column (
timestampMillis
) in your subqueries. * The 
PARTITION BY
clause in the MV must match the partitioning logic of the base table.
Copy code
sql
CREATE MATERIALIZED VIEW connected_sessions
-- Use the exact same expression as the base table for alignment
PARTITION BY from_unixtime(timestampMillis / 1000, '%Y-%m')
DISTRIBUTED BY HASH(`clientId`)
REFRESH ASYNC
AS 
SELECT
    clientId,
    MIN(connectedAtMs) AS connectedAtMs,
    disconnectedAtMs,
    (disconnectedAtMs - MIN(connectedAtMs)) AS sessionDuration,
    -- Keep the raw column or the exact partition expression in the final SELECT
    timestampMillis 
FROM (
    SELECT 
        clientId, 
        timestampMillis, -- Pass this up from the base table
        timestampMillis AS connectedAtMs, 
        MIN(CASE WHEN connected = 0 THEN timestampMillis END) 
            OVER (PARTITION BY clientId ORDER BY timestampMillis DESC) AS disconnectedAtMs,
        connected
    FROM connected_client
) t
WHERE connected = 1
GROUP BY clientId, disconnectedAtMs, timestampMillis;
2. "INCREMENTAL" Refresh Mode (v3.3+) StarRocks v3.3 introduced a 
refresh_mode
property. Setting 
"refresh_mode" = "INCREMENTAL"
enables a row-level incremental refresh (processing only the "delta" rows). * Pros: Only processes new rows, not whole partitions. * Cons: Does not currently support window functions (
OVER(...)
). Since your query relies on a window function to pair connect/disconnect events, this mode will likely return an "Unsupported" error during creation. 3. Multi-Layer Materialized Views If the partitioning logic is too complex for one MV, you can break it into two layers: 1. Layer 1: A partitioned MV that simply filters and organizes the base data. 2. Layer 2: An MV built on top of Layer 1 that performs the complex window functions and sessionization. This helps because Layer 1 can handle the "partition alignment" with the base table, and Layer 2 can then refresh incrementally based on the changes in Layer 1. Summary of Differences Strategy | Granularity | Window Function Support | Version Partitioned Refresh | Partition level | Supported | v2.4+ Incremental Mode | Row/Delta level | Not yet supported | v3.3+ Full Refresh | Entire table | Supported | All Why your specific query is hard to partition: Your window function 
PARTITION BY clientId
creates a "cross-partition" dependency. A disconnect event in June might update a session that started in May. For Partitioned Refresh to be truly accurate, StarRocks would need to refresh both the May and June partitions when June data arrives. To handle this, you can use the property 
"partition_refresh_number" = "-1"
to ensure all affected partitions are caught partitions--out-of-sync are updated. date are updated. References • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
t
@Rocky option 1 does not work "Unsupported expr 'from_unixtime(timestampMillis / 1000, '%Y-%m')' in PARTITION BY clause"
r
The error "Unsupported expr" occurs because the 
PARTITION BY
clause in a Materialized View (MV) is more restrictive than in a base table. While base tables support many functions for expression partitioning, MVs typically only support 
date_trunc
or 
str2date
 (and direct column references) for partition alignment. To align your MV with a base table that is partitioned via 
from_unixtime(timestampMillis / 1000, '%Y-%m')
, you should use the following approach: The Solution: Use an Alias for a String Partition Since StarRocks v3.1.4+, you can partition an MV by a 
STRING
column. The trick to avoiding the "refer directly" error is to ensure the partition expression is selected in the MV and that the 
PARTITION BY
clause references that alias. However, because your query uses a window function and a CTE, StarRocks can lose the "direct reference" to the base table's partitioning. To fix this, you must: 1. Include the expression in your 
SELECT
list. 2. Reference the alias directly in the 
PARTITION BY
clause. 3. Ensure the alias is passed through all layers of your query.
Copy code
sql
CREATE MATERIALIZED VIEW connected_sessions
-- Reference the alias directly.
-- In v3.1.4+, this allows one-to-one alignment with a STRING partition.
PARTITION BY (session_month) 
DISTRIBUTED BY HASH(`clientId`)
REFRESH ASYNC
AS 
SELECT
    clientId,
    MIN(connectedAtMs) AS connectedAtMs,
    disconnectedAtMs,
    (disconnectedAtMs - MIN(connectedAtMs)) AS sessionDuration,
    -- This expression must match the base table's partition logic exactly
    from_unixtime(timestampMillis / 1000, '%Y-%m') AS session_month
FROM (
    -- Innermost subquery must include the source for the partition key
    SELECT 
        clientId, 
        timestampMillis,
        timestampMillis AS connectedAtMs, 
        MIN(CASE WHEN connected = 0 THEN timestampMillis END) 
            OVER (PARTITION BY clientId ORDER BY timestampMillis DESC) AS disconnectedAtMs,
        connected
    FROM connected_client
) t
WHERE connected = 1
GROUP BY 
    clientId, 
    disconnectedAtMs, 
    session_month;
Why this works: * Direct Reference: By using 
PARTITION BY (session_month)
where 
session_month
is an alias for the expression used in the base table, StarRocks attempts to map the MV partitions to the base table partitions. * Version Note: If you are on a version older than v3.1.4, StarRocks does not support partitioning MVs by 
STRING
. In that case, you would need to convert the logic to use 
DATE
(via 
str2date
or 
date_trunc
) which would require the base table to also be partitioned by a 
DATE/DATETIME
type for alignment. If you still get the "Direct Reference" error: If the error persists, it is likely due to the window function. StarRocks' optimizer sometimes fails to track partition alignment through window functions because a single "session" (defined by your window) could theoretically span multiple partitions (e.g., connected in Jan, disconnected in Feb). Recommended Alternative if Alignment Fails: If you cannot get the partitions to align perfectly due to the complexity of the sessionization: 1. Remove the 
PARTITION BY
from the MV: This will result in a "Full Refresh" every time. 2. Use a shorter refresh interval: Set 
"refresh_interval" = "..."
to manage the load. 3. Multi-step approach: Create a simple partitioned MV that just cleans the data, then create a second (potentially non-partitioned) MV on top of that for the session logic. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md
t
@Rocky Detail message: Please check the partition expression session_month, it should refer base table's partition column directly.
r
The error message 
it should refer base table's partition column directly
occurs because StarRocks' Materialized View (MV) optimizer cannot establish a "lineage" between your alias (
session_month
) and the actual partitioning column of the base table. This is a common limitation when working with Expression Partitioning on 
BIGINT
(timestamp) columns. Here is the breakdown of why this is happening and how to fix it. 1. Why your current approach is failing * Alias Restriction: In an MV's 
PARTITION BY
clause, you generally cannot use an alias that is a result of a complex function. The optimizer expects the partitioning expression to be a direct function of the base table's partition column. * Unsupported Functions: While base tables support 
from_unixtime
for partitioning, Materialized Views do not currently support 
from_unixtime
 in their 
PARTITION BY
clause for partition alignment. Only 
date_trunc
, 
str2date
, 
time_slice
, and 
date_slice
are supported. * Type Mismatch: 
str2date
requires a 
STRING
input. Your base column 
timestampMillis
is a 
BIGINT
. Even if you alias it, the optimizer sees the underlying 
BIGINT
and rejects 
str2date
. 2. How to fix it To successfully create a partitioned MV on a table with millisecond timestamps, you have two main options: Option A: The "Direct Column" approach (Recommended) The most robust way to align partitions is to have a 
DATE
or 
DATETIME
column in your base table. StarRocks can then easily align the MV partitions to it. 1. Modify the Base Table: Add a 
DATETIME
column (e.g., 
event_date
). You can populate this at load time using 
from_unixtime(timestampMillis / 1000)
. 2. Partition the Base Table: 
PARTITION BY date_trunc('month', event_date)
. 3. Create the MV:
Copy code
sql
    CREATE MATERIALIZED VIEW connected_sessions
    PARTITION BY date_trunc('month', event_date) -- Direct reference to base column
    REFRESH ASYNC
    AS
    SELECT
        clientId,
        event_date,
        ...
    FROM connected_client;
Option B: The Two-Layer MV (Workaround) If you cannot change your base table schema, you can create a "bridge" to convert the data types. 1. MV Layer 1 (Non-partitioned): Create a simple MV that converts the 
BIGINT
to a 
DATETIME
.
Copy code
sql
    CREATE MATERIALIZED VIEW client_events_converted
    REFRESH ASYNC
    AS SELECT *, from_unixtime(timestampMillis / 1000) as event_dt FROM connected_client;
2. MV Layer 2 (Partitioned): Create your sessionization MV on top of Layer 1. Since Layer 1 now has a 
DATETIME
column, you can use 
date_trunc
for partitioning.
Copy code
sql
    CREATE MATERIALIZED VIEW connected_sessions
    PARTITION BY date_trunc('month', event_dt)
    REFRESH ASYNC
    AS SELECT ... FROM client_events_converted ...;
Summary of Requirements for Partition Alignment To avoid the "refer directly" error, your MV definition must meet these criteria: * The 
PARTITION BY
expression in the MV must use one of: 
date_trunc
, 
str2date
, 
time_slice
, or 
date_slice
. * The column passed into these functions must be a raw column from the base table's 
SELECT
list. * The base table must be partitioned by that same column (or a compatible expression). If you cannot satisfy these (e.g., because you are partitioning a 
BIGINT
by month), you will have to use a non-partitioned MV and refresh the whole table at once, or use the Two-Layer approach above. References • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md
t
@Rocky fully explain option B
r
The "Two-Layer MV" strategy is a design pattern used to bypass StarRocks' strict rules regarding partition alignment. The core problem you are facing is that StarRocks allows base tables to be partitioned by almost any expression (like 
from_unixtime
), but it only allows Materialized Views to be incrementally refreshed if their partition key uses a very specific set of functions (
date_trunc
, 
time_slice
, 
str2date
). By creating two layers, you create a "translation" layer that satisfies both sides. Layer 1: The "Translation" MV The goal of this layer is to take the "incompatible" 
BIGINT
column from your base table and expose it as a 
DATETIME
column. This makes it compatible with the functions StarRocks requires for MV partitioning. * Partitioning: This MV should be partitioned exactly like your base table so that it can refresh incrementally. * Transformation: It converts your 
timestampMillis
into a 
DATETIME
object.
Copy code
sql
CREATE MATERIALIZED VIEW mv_layer1_prepared_data
PARTITION BY (event_date) -- We create a DATE/DATETIME partition here
DISTRIBUTED BY HASH(clientId)
REFRESH ASYNC
AS
SELECT 
    *,
    -- Convert BIGINT to DATETIME so Layer 2 can use date_trunc
    FROM_UNIXTIME(timestampMillis / 1000) AS event_date 
FROM connected_client;
Layer 2: The "Logic" MV Now that 
mv_layer1_prepared_data
exists, it looks like a "base table" to the next layer. Crucially, it now has a raw DATETIME column (
event_date
). StarRocks can now "refer directly" to 
event_date
and apply 
date_trunc
to it, which is a supported operation for MV partition alignment. * Source: It reads from 
mv_layer1_prepared_data
instead of the raw table. * Partitioning: It uses 
date_trunc
on the 
event_date
column.
Copy code
sql
CREATE MATERIALIZED VIEW connected_sessions
PARTITION BY date_trunc('month', event_date) -- This is now "Direct Reference"
DISTRIBUTED BY HASH(clientId)
REFRESH ASYNC
AS
SELECT
    clientId,
    MIN(connectedAtMs) AS connectedAtMs,
    disconnectedAtMs,
    (disconnectedAtMs - MIN(connectedAtMs)) AS sessionDuration,
    event_date -- Must be in the SELECT list
FROM (
    SELECT 
        clientId, 
        event_date,
        timestampMillis AS connectedAtMs, 
        MIN(CASE WHEN connected = 0 THEN timestampMillis END) 
            OVER (PARTITION BY clientId ORDER BY timestampMillis DESC) AS disconnectedAtMs,
        connected
    FROM mv_layer1_prepared_data -- Use Layer 1 as source
) t
WHERE connected = 1
GROUP BY clientId, disconnectedAtMs, event_date;
Why this works 1. Lineage: Layer 2 sees 
event_date
as a direct column from its source (Layer 1). Because Layer 2 uses 
date_trunc('month', event_date)
, StarRocks can map one month of data in Layer 2 to the corresponding days/months of data in Layer 1. 2. Validation: You avoid the "Unsupported expr" error because you are no longer using 
from_unixtime
in the 
PARTITION BY
clause; you are using 
date_trunc
, which StarRocks' MV optimizer is programmed to understand. 3. Automation: When new data arrives in the base table, StarRocks tracks the dependencies. It will first refresh the affected partitions in Layer 1, and then automatically trigger a refresh for the affected partitions in Layer 2. Important Performance Note Since your sessionization logic uses a Window Function (
OVER (PARTITION BY clientId)
), a session could technically start at 11:59 PM on the last day of the month and end the next month. When using partitioned MVs with window functions, you should add this property to Layer 2 to ensure that if data in a previous month is updated, the MV catches it:
Copy code
sql
PROPERTIES (
    "partition_refresh_number" = "1" -- Refreshes 1 partition at a time
);
_(If your data is late-arriving, you may need to increase the number of partitions checked during refresh using the 
partition_ttl_number
or by manually triggering refreshes for specific time ranges.)_
6 Views
Open in Slack
PreviousNext
Powered by