Creating a Partitioned Materialized View
DWS allows you to create partitioned materialized views in the following scenarios:
- Partitions are aligned proportionally. The partitioning conditions of materialized views are the same as those of base tables.
- Partitions are aligned based on time granularity, for example, daily partitioning for the base table and monthly partitioning for the materialized view.
- Partitions are aligned for multiple base tables with identical partition conditions, automatically mapping the base tables to the materialized view.
- The base table has multiple partition keys and the first key is chosen as the partition key for creating a materialized view.
Syntax
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] materialized_view_name [ ( column_name [, ...] ) ] [ BUILD { DEFERRED | IMMEDIATE } ] [ REFRESH [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ] [ COMPLETE ] [ ON DEMAND ] [ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ] ] [ { ENABLE | DISABLE } QUERY REWRITE ] [ WITH ( {storage_parameter = value} [, ... ] ) ] [ TABLESPACE tablespace_name ] [ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } } ] [ PARTITION BY {(column_name) | date_trunc('time_unit', column_name)} ] AS query;
Constraints
- You can only use range partitioning.
- The materialized view only supports a single partition key, which must be the first partition key of the base table. The base table can have a combined partition key.
- Rollup mode is supported only for time type, using date_trunc for rollup calculation. Supported rollup intervals include day, month, quarter, and year.
- Columns that generate null values in outer joins cannot be used as partition keys.
- disable full group by in MySQL compatibility mode is not supported.
- Pre-aggregated materialized views cannot be used to create partitioned materialized views.
- Query rewriting for a specified partition is not allowed.
- The base table of the partitioned materialized view does not support SPLIT and MERGE operations.
- When both sides of a union meet the partition constraint check of the materialized view, a materialized view bound to multiple tables is created.
- If JOIN is used to join base tables and the partition key is set to a public column, a materialized view bound to multiple tables is created if the joined base tables meet the partition constraint detection of the materialized view.
- Query statements cannot contain sublinks.
- Query statements cannot contain subplans.
- Data modification using DML and DDL statements is not allowed. Data can only be updated by refreshing.
Examples
Create fact tables fact_table and fact_table1.
1 2 3 4 5 6 7 |
CREATE TABLE fact_table (start_time TIMESTAMPTZ NOT NULL, id int)PARTITION BY RANGE(start_time)( PARTITION p1 VALUES LESS THAN ('2024-01-01'), PARTITION p2 VALUES LESS THAN ('2024-01-02'), PARTITION p3 VALUES LESS THAN ('2024-02-03'), PARTITION p4 VALUES LESS THAN ('2024-03-01'), PARTITION p5 VALUES LESS THAN ('2024-03-21'), PARTITION p6 VALUES LESS THAN ('2024-04-02')); |
1 2 3 4 5 6 7 |
CREATE TABLE fact_table1 (start_time TIMESTAMPTZ NOT NULL, id int)PARTITION BY RANGE(start_time)( PARTITION p1 VALUES LESS THAN ('2024-01-01'), PARTITION p2 VALUES LESS THAN ('2024-01-02'), PARTITION p3 VALUES LESS THAN ('2024-02-03'), PARTITION p4 VALUES LESS THAN ('2024-03-01'), PARTITION p5 VALUES LESS THAN ('2024-03-21'), PARTITION p6 VALUES LESS THAN ('2024-04-02')); |
Create a dimension table dimen_table.
1
|
CREATE TABLE dimen_table (start_time TIMESTAMPTZ NOT NULL, id INT); |
Scenario 1: Align partitions proportionally.
Create the materialized view level_normal. Ensure that the partition of the materialized view matches that of the fact_table table, with identical partition conditions and no specific data type requirements.
1 2 3 4 |
CREATE MATERIALIZED VIEW level_normal enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY start_time AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id; |
Scenario 2: Adjust the partitioning time granularity.
Use the date_trunc parameter on the partition key to create a materialized view with coarser partitioning time granularity compared to the base table. DWS updates the corresponding roll-up partition in the materialized view after detecting data changes in the base table partition.
You can use the date_trunc('time_unit', column_name) parameter to specify the roll-up targets. The materialized view partition covers a wider time range compared to the base table partition. For instance, if the base table is partitioned by day, the materialized view can be partitioned by month. The date_trunc parameter adjusts the time granularity in the order of day, quarter, year, or month. Supported data types include time, timestamp, and timestamptz.
1 2 3 4 |
CREATE MATERIALIZED VIEW level_month enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY date_trunc('month', start_time) AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id; |
Scenario 3: Align partitions for multiple base tables.
If multiple base tables use the same partition key type and partition range, you can create a materialized view bound to multiple base tables.
1 2 3 4 |
CREATE MATERIALIZED VIEW level_normal enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY start_time AS SELECT start_time, id FROM fact_table union SELECT start_time, id FROM fact_table1; |
1 2 3 4 |
CREATE MATERIALIZED VIEW level_normal enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY start_time AS SELECT start_time, id FROM fact_table inner join fact_table1 on fact_table.start_time=dimen_table.start_time; |
Scenario 4: When the base table has multiple partition keys, use the first partition key of the base table to create a materialized view.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE IF NOT EXISTS multi_time_base ( new_start_time timestamp with time zone NOT NULL, id int, v1 int ) PARTITION BY RANGE(new_start_time,id) ( PARTITION p1 VALUES LESS THAN ('2022-01-01',400), PARTITION p2 VALUES LESS THAN ('2022-01-05',400), PARTITION p3 VALUES LESS THAN ('2022-01-05',500), PARTITION p4 VALUES LESS THAN ('2022-02-15',500), PARTITION p5 VALUES LESS THAN ('2022-02-16',500), PARTITION p6 VALUES LESS THAN ('2022-03-10',500) ); CREATE MATERIALIZED VIEW mult_time_mv PARTITION BY (date_trunc('month', new_start_time)) AS SELECT new_start_time,id FROM multi_time_base; |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.