E-MapReduce:Use materialized views for data modeling

Auto-refresh​

When creating an asynchronous materialized view, you can use the REFRESH clause to specify the refresh strategy. Currently, StarRocks asynchronous materialized views support the following refresh strategies:

• Auto-refresh (REFRESH ASYNC): A refresh task is triggered whenever data in the base table changes. Data dependencies are automatically managed by the materialized view.

• Scheduled refresh (REFRESH ASYNC EVERY (INTERVAL <refresh_interval>)): Refresh tasks are triggered periodically, such as every minute, day, or month. If there are no data changes in the base table, no refresh task will be triggered.

• Manual refresh (REFRESH MANUAL): You can only trigger refresh tasks by manually executing REFRESH MATERIALIZED VIEW. You can use this refresh strategy if you trigger refresh tasks through an external scheduling framework.

The syntax is as follows:

CREATE MATERIALIZED VIEW <db_name>.<mv_name>
REFRESH 
    [ ASYNC | 
      ASYNC [START <time>] EVERY(<interval>) | 
      MANUAL
    ]
AS <query>

Partition refresh​

When creating an asynchronous materialized view, you can use the PARTITION BY clause to associate the partitions of the base table with the partitions of the materialized view, thereby achieving refresh at the partition granularity.

• PARTITION BY <column>: You can reference the same partition column as the base table for the materialized view, so that the base table and materialized view use the same partition granularity.

• PARTITION BY date_trunc(<column>): You can use the date_trunc function to roll up based on the base table partition column, thereby creating different granularity partition strategies for the materialized view.

• PARTITION BY { time_slice | date_slice }(<column>): Compared to date_trunc, time_slice and date_slice provide more flexible time granularity adjustment, allowing for more fine-grained control of time partition granularity.

The syntax is as follows.

CREATE MATERIALIZED VIEW <db_name>.<mv_name>
REFRESH ASYNC
PARTITION BY 
    [
        <base_table_column> | 
        date_trunc(<granularity>, <base_table_column>) |
        time_slice(<base_table_column>, <granularity>) | 
        date_slice(<base_table_column>, <granularity>)
    ]
AS <query>

Collaborative use with views​

• You can create materialized views based on views. In this case, the materialized view can be automatically refreshed when data changes in the base tables referenced by the view.

• You can create materialized views based on other materialized views, enabling multi-level cascading refresh.

• You can create views based on materialized views, equivalent to creating based on regular tables.

Schema change​

• Asynchronous materialized views can be atomically replaced using the ALTER MATERIALIZED VIEW SWAP statement. You can create a new materialized view, add new columns or change column types, and then replace the old materialized view with the new one.

• Views can be directly modified using the ALTER VIEW statement.

• Regular tables in StarRocks can be modified using SWAP or ALTER operations.

• When base tables (which can be materialized views, views, or regular tables) change, cascading changes will be triggered in the corresponding materialized views.

Differences between views and materialized views

Modify views and base tables

You can use the following statements to modify your views, materialized views, and base tables.

-- Modify base table.
ALTER TABLE <db_name>.<table_name> ADD COLUMN <column_desc>;

-- Atomically replace base table.
ALTER TABLE <db_name>.<table1> SWAP WITH <table2>;

-- Modify view definition.
ALTER VIEW <db_name>.<view_name> AS <query>;

-- Atomically replace materialized view (swaps the names of two materialized views without modifying their data).
ALTER MATERIALIZED VIEW <db_name>.<mv1> SWAP WITH <mv2>;

-- Re-enable materialized view.
ALTER MATERIALIZED VIEW <db_name>.<mv_name> ACTIVE;

Schema Change follows these principles:

• Table renaming and atomic replacement operations will cause dependent materialized views to become Inactive. For Schema Change operations, materialized views will only become Inactive when the base table columns they depend on undergo Schema Change.

• Changes to view definitions will cause dependent materialized views to become Inactive.

• Atomic replacement of materialized views will cause dependent nested materialized views to become Inactive.

• The Inactive state propagates upward in a cascading manner until there are no more materialized view dependencies.

• Inactive materialized views cannot be refreshed or used for automatic query rewriting.

• Inactive materialized views can still be directly queried, but data consistency cannot be guaranteed until they are made Active.

Because data consistency cannot be guaranteed for Inactive materialized views, you can use the following methods to fix them:

• Manual repair: You can manually repair Inactive materialized views by executing ALTER MATERIALIZED VIEW <mv_name> ACTIVE. This statement will attempt to rebuild based on the original SQL definition of the materialized view. Note that for rebuilding to succeed, the SQL definition must still be valid after the underlying Schema Change, otherwise the operation will fail.

• Repair during refresh: StarRocks will automatically attempt to execute the above repair command when refreshing materialized views, rebuilding the materialized view before refreshing.

• Automatic repair: StarRocks will attempt to automatically repair Inactive materialized views. You can disable this feature by running ADMIN SET FRONTEND CONFIG('enable_mv_automatic_active_check'='false').

Use cases

Partition association can support various business scenarios:

• Fact table updates: You can partition fact tables to fine granularity levels, such as by day or by hour. After the fact table is updated, the corresponding partitions in the materialized view will be automatically refreshed.

• Dimension table updates: Typically, data updates in dimension tables will cause all associated results to be refreshed, which is costly. You can choose to ignore data updates in certain dimension tables to avoid refreshing the entire materialized view, or you can specify a time range so that only partitions within that time range are refreshed.

• Automatic refresh of external tables: In external data sources such as Apache Hive or Apache Iceberg, data often changes at the partition granularity. StarRocks materialized views can subscribe to partition-level data updates in external tables and only refresh the corresponding partitions of the materialized view.

• TTL: When setting partition strategies for materialized views, you can set the number of recent partitions to retain, thereby only keeping the latest data. The corresponding business scenario has high requirements for data timeliness, for example, analysts only need to query the latest data within a certain time window without needing to retain all historical data.

You can use multiple parameters to control refresh behavior:

• partition_refresh_number: The number of partitions to refresh in each refresh operation.

• partition_ttl_number: The number of recent partitions to retain.

• excluded_trigger_tables: Tables to ignore to avoid triggering automatic refresh.

• auto_refresh_partitions_limit: The number of partitions to refresh in each automatic refresh operation.

Limitations

Partitioned materialized views have the following limitations:

• Only supports creating partitioned materialized views based on partitioned tables.

• Only supports DATE and DATETIME partition column types, not STRING type.

• Only supports using date_trunc, time_slice, and date_slice for partition roll-up.

• Only supports a single column as the partition column, not multiple partition columns.