This is an automated email from the ASF dual-hosted git repository.
luzhijing pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new ac7c3dbb8a [doc](mtmv)Supplement asynchronous materialized view
document (#706)
ac7c3dbb8a is described below
commit ac7c3dbb8a5922cc73b1624ecd0a73f35470cd1d
Author: zhangdong <493738...@qq.com>
AuthorDate: Fri Jun 28 12:47:20 2024 +0800
[doc](mtmv)Supplement asynchronous materialized view document (#706)
Co-authored-by: Luzhijing <82810928+luzhij...@users.noreply.github.com>
---
.../async-materialized-view.md | 459 ++++++++++++++++++++-
.../Create/CREATE-ASYNC-MATERIALIZED-VIEW.md | 26 +-
.../async-materialized-view.md | 451 +++++++++++++++++++-
.../Create/CREATE-ASYNC-MATERIALIZED-VIEW.md | 30 +-
.../async-materialized-view.md | 457 ++++++++++++++++++--
.../Create/CREATE-ASYNC-MATERIALIZED-VIEW.md | 68 ++-
.../async-materialized-view.md | 413 ++++++++++++++++--
.../Create/CREATE-ASYNC-MATERIALIZED-VIEW.md | 32 +-
8 files changed, 1835 insertions(+), 101 deletions(-)
diff --git a/docs/query/view-materialized-view/async-materialized-view.md
b/docs/query/view-materialized-view/async-materialized-view.md
index fd42802c6f..21bee69a22 100644
--- a/docs/query/view-materialized-view/async-materialized-view.md
+++ b/docs/query/view-materialized-view/async-materialized-view.md
@@ -131,7 +131,7 @@ REFRESH MATERIALIZED VIEW mv1
partitions(p_20231017_20231018);
Specific syntax can be viewed [REFRESH MATERIALIZED
VIEW](../../sql-manual/sql-statements/Utility-Statements/REFRESH-MATERIALIZED-VIEW.md)
-### task management
+### Task management
Each materialized view defaults to a job responsible for refreshing data,
which is used to describe the refresh strategy and other information of the
materialized view. Each time a refresh is triggered, a task is generated,
Task is used to describe specific refresh information, such as the time used
for refreshing, which partitions were refreshed, etc
@@ -154,7 +154,7 @@ Can pause the scheduled scheduling of materialized views
Specific syntax can be viewed [PAUSE MATERIALIZED VIEW
JOB](../../sql-manual/sql-statements/Utility-Statements/PAUSE-MATERIALIZED-VIEW.md)
-#### RESUME materialized view job scheduling
+#### Resume materialized view job scheduling
```sql
RESUME MATERIALIZED VIEW JOB ON mv1;
@@ -164,7 +164,7 @@ Can RESUME scheduled scheduling of materialized views
Specific syntax can be viewed [RESUME MATERIALIZED VIEW
JOB](../../sql-manual/sql-statements/Utility-Statements/RESUME-MATERIALIZED-VIEW.md)
-#### Viewing tasks in materialized views
+#### View tasks in materialized views
```sql
select * from tasks("type"="mv");
@@ -182,7 +182,7 @@ Can cancel the operation of this task
Specific syntax can be viewed [CANCEL MATERIALIZED VIEW
TASK](../../sql-manual/sql-statements/Utility-Statements/CANCEL-MATERIALIZED-VIEW-TASK.md)
-### Modifying materialized views
+### Modify materialized views
Modify the properties of materialized views
```sql
@@ -203,6 +203,457 @@ The materialized view has a dedicated deletion syntax and
cannot be deleted thro
Specific syntax can be viewed [DROP ASYNC MATERIALIZED
VIEW](../../sql-manual/sql-statements/Data-Definition-Statements/Drop/DROP-ASYNC-MATERIALIZED-VIEW.md)
+## Partition description
+There are two ways to partition materialized views:
+
+1. Custom Partitioning
+
+2. Automatically Create Partitions Based on Dependent Base Table Partitions
+
+### Custom partitioning
+When creating a materialized view without specifying partition information,
the materialized view will default to creating a single partition where all
data will be stored.
+
+### Partitioning based on dependent base tables
+A materialized view can be created by joining multiple base tables.
+
+A materialized view can be partitioned to follow one of the base tables (it is
recommended to choose the fact table).
+
+For example
+
+The table creation statement for t1 is as follows:
+
+```sql
+CREATE TABLE `t1` (
+ `user_id` LARGEINT NOT NULL,
+ `o_date` DATE NOT NULL,
+ `num` SMALLINT NOT NULL
+) ENGINE=OLAP
+COMMENT 'OLAP'
+PARTITION BY RANGE(`o_date`)
+(
+PARTITION p20170101 VALUES [('2017-01-01'), ('2017-01-02')),
+PARTITION p20170102 VALUES [('2017-01-02'), ('2017-01-03')),
+PARTITION p20170201 VALUES [('2017-02-01'), ('2017-02-02'))
+)
+DISTRIBUTED BY HASH(`user_id`) BUCKETS 2
+PROPERTIES ('replication_num' = '1') ;
+```
+
+The table creation statement for t2 is as follows:
+
+```sql
+CREATE TABLE `t2` (
+ `user_id` LARGEINT NOT NULL,
+ `age` SMALLINT NOT NULL
+) ENGINE=OLAP
+PARTITION BY LIST(`age`)
+(
+ PARTITION `p1` VALUES IN ('1'),
+ PARTITION `p2` VALUES IN ('2')
+)
+DISTRIBUTED BY HASH(`user_id`) BUCKETS 2
+PROPERTIES ('replication_num' = '1') ;
+```
+
+If the materialized view creation statement is as follows, then the
materialized view mv1 will have the same three partitions as t1:
+- [('2017-01-01'), ('2017-01-02'))
+- [('2017-01-02'), ('2017-01-03'))
+- [('2017-02-01'), ('2017-02-02'))
+
+```sql
+CREATE MATERIALIZED VIEW mv1
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`order_date`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES (
+'replication_num' = '1'
+)
+AS
+SELECT t1.o_date as order_date, t1.user_id as user_id, t1.num, t2.age FROM t1
join t2 on t1.user_id=t2.user_id;
+```
+
+If the materialized view creation statement is as follows, then the
materialized view mv2 will have the same three partitions as t2:
+- ('1')
+- ('2')
+
+```sql
+CREATE MATERIALIZED VIEW mv2
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`age`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES (
+'replication_num' = '1'
+)
+AS
+SELECT t1.o_date as order_date, t1.user_id as user_id, t1.num, t2.age FROM t1
join t2 on t1.user_id=t2.user_id;
+```
+#### The base table has multiple partition columns
+Currently, only Hive external tables support multiple partition columns.
+
+Hive external tables often have multi-level partitions. For example, the
first-level partition is by date, and the second-level partition is by region.
+
+A materialized view can choose one of the partition columns from a Hive table
as the partition column for the materialized view.
+
+For example, the Hive table creation statement is as follows:
+```sql
+CREATE TABLE hive1 (
+`k1` int)
+PARTITIONED BY (
+`year` int,
+`region` string)
+STORED AS ORC;
+
+alter table hive1 add if not exists
+partition(year=2020,region="bj")
+partition(year=2020,region="sh")
+partition(year=2021,region="bj")
+partition(year=2021,region="sh")
+partition(year=2022,region="bj")
+partition(year=2022,region="sh")
+```
+If the materialized view creation statement is as follows, then the
materialized view mv_hive will have the following three partitions:
+- ('2020')
+- ('2021')
+- ('2022')
+```sql
+CREATE MATERIALIZED VIEW mv_hive
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`year`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT k1,year,region FROM hive1;
+```
+
+If the materialized view creation statement is as follows, then the
materialized view mv_hive2 will have the following two partitions:
+- ('bj')
+- ('sh')
+```sql
+CREATE MATERIALIZED VIEW mv_hive2
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`region`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT k1,year,region FROM hive1;
+```
+
+#### Only using a subset of the base table partitions
+Note: Supported from version 2.1.1
+
+If some base tables have many partitions, but the materialized view only
focuses on the recent "hot" data, this feature can be used.
+
+If the base table creation statement is as follows:
+```sql
+CREATE TABLE t1 (
+ `k1` INT,
+ `k2` DATE NOT NULL
+) ENGINE=OLAP
+DUPLICATE KEY(`k1`)
+COMMENT 'OLAP'
+PARTITION BY range(`k2`)
+(
+PARTITION p26 VALUES [("2024-03-26"),("2024-03-27")),
+PARTITION p27 VALUES [("2024-03-27"),("2024-03-28")),
+PARTITION p28 VALUES [("2024-03-28"),("2024-03-29"))
+)
+DISTRIBUTED BY HASH(`k1`) BUCKETS 2
+PROPERTIES (
+'replication_num' = '1'
+);
+```
+If the creation statement of the materialized view is as follows, it means
that the materialized view only focuses on the data of the most recent day. If
the current time is 2024-03-28 xx: xx: xx, then the materialized view will only
have one partition [("2024-03-28"), ("2024-03-29")]
+```sql
+CREATE MATERIALIZED VIEW mv1
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`k2`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES (
+'replication_num' = '1',
+'partition_sync_limit'='1',
+'partition_sync_time_unit'='DAY'
+)
+AS
+SELECT * FROM t1;
+```
+If another day has passed and the current time is 2024-03-29 xx: xx: xx, t1
adds a partition [("2024-03-29"), ("2024-03-30")]. If the materialized view is
refreshed at this time, after the refresh is completed, the materialized view
will only have one partition [("2024-03-29"), ("2024-03-30")]
+
+If the partition field is of string type, you can set the materialized view
property 'partition_date_format', for example, '%Y-%m-%d'.
+
+#### Partition rolling up
+
+Partition rolling up can be used when the data in each partition of the base
table becomes very small after aggregation. This can reduce the number of
partitions in the materialized view.
+
+##### List partition
+Note: Hive partitions correspond to Doris list partitions.
+
+If the base table creation statement is as follows
+```sql
+CREATE TABLE `t1` (
+ `k1` INT NOT NULL,
+ `k2` DATE NOT NULL
+) ENGINE=OLAP
+DUPLICATE KEY(`k1`)
+COMMENT 'OLAP'
+PARTITION BY list(`k2`)
+(
+PARTITION p_20200101 VALUES IN ("2020-01-01"),
+PARTITION p_20200102 VALUES IN ("2020-01-02"),
+PARTITION p_20200201 VALUES IN ("2020-02-01")
+)
+DISTRIBUTED BY HASH(`k1`) BUCKETS 2
+PROPERTIES ('replication_num' = '1') ;
+```
+If the materialized view creation statement is as follows, then the
materialized view will have two partitions:
+- ("2020-01-01","2020-01-02")
+- ("2020-02-01")
+```sql
+CREATE MATERIALIZED VIEW mv1
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ partition by (date_trunc(`k2`,'month'))
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ AS
+ SELECT * FROM t1;
+```
+If the materialized view creation statement is as follows, then the
materialized view will have one partition:
+- ("2020-01-01","2020-01-02","2020-02-01")
+```sql
+CREATE MATERIALIZED VIEW mv1
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ partition by (date_trunc(`k2`,'year'))
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ AS
+ SELECT * FROM t1;
+```
+##### Range partition
+If the base table creation statement is as follows:
+```sql
+CREATE TABLE `t1` (
+ `k1` LARGEINT NOT NULL,
+ `k2` DATE NOT NULL
+) ENGINE=OLAP
+DUPLICATE KEY(`k1`)
+COMMENT 'OLAP'
+PARTITION BY range(`k2`)
+(
+PARTITION p_20200101 VALUES [("2020-01-01"),("2020-01-02")),
+PARTITION p_20200102 VALUES [("2020-01-02"),("2020-01-03")),
+PARTITION p_20200201 VALUES [("2020-02-01"),("2020-02-02"))
+)
+DISTRIBUTED BY HASH(`k1`) BUCKETS 2
+PROPERTIES ('replication_num' = '1') ;
+```
+If the materialized view creation statement is as follows, then the
materialized view will have two partitions:
+- [("2020-01-01","2020-02-01"))
+- [("2020-02-01","2020-03-01"))
+```sql
+CREATE MATERIALIZED VIEW mv1
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ partition by (date_trunc(`k2`,'month'))
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ AS
+ SELECT * FROM t1;
+```
+If the materialized view creation statement is as follows, then the
materialized view will have one partition:
+- [("2020-01-01","2021-01-01"))
+```sql
+CREATE MATERIALIZED VIEW mv1
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ partition by (date_trunc(`k2`,'year'))
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ AS
+ SELECT * FROM t1;
+```
+Note: When the partition is a string, the way it is rolled up is still being
designed. The current behavior may change, it is best not to use it
+
+## Data refreshing
+### Refresh principle
+The materialized view is refreshed on a per-partition basis. If the
materialized view does not specify partitions, then each refresh will refresh
the default partition of the materialized view, effectively refreshing all the
data in the materialized view.
+### Trigger mechanism
+There are three trigger refresh mechanisms for materialized views:
+#### Manual trigger
+Users trigger the refresh of the materialized view through SQL statements.
Currently, there are three strategies:
+1. Not concerned with which partitions are refreshed, but requires that after
the refresh is complete, the data in the materialized view is synchronized with
the base table.
+```sql
+REFRESH MATERIALIZED VIEW mvName AUTO;
+```
+2. Regardless of the existing data in the materialized view, refresh all
partitions of the materialized view.
+```sql
+REFRESH MATERIALIZED VIEW mvName COMPLETE;
+```
+3. Regardless of the existing data in the materialized view, only refresh the
specified partitions.
+```sql
+REFRESH MATERIALIZED VIEW mvName partitions(partitionName1,partitionName2);
+```
+`partitionName` can be obtained through `show partitions from mvName`.
+#### Scheduled trigger
+Specify how often to refresh the data through the creation statement of the
materialized view.
+1. If the materialized view creation statement is as follows, and it requires
a full refresh (refresh complete), then the materialized view is refreshed
every 10 hours, and all partitions of the materialized view are refreshed.
+```sql
+CREATE MATERIALIZED VIEW mv1
+REFRESH COMPLETE ON SCHEDULE EVERY 10 hour
+partition by(`xxx`)
+AS
+select ...;
+```
+2. If the materialized view creation statement is as follows, and it requires
an automatic full refresh (refresh auto), then the materialized view is
refreshed every 10 hours, and the partitions to be refreshed are automatically
calculated (starting from version 2.1.3, automatic calculation of partitions to
be refreshed is supported for Hive)
+```sql
+CREATE MATERIALIZED VIEW mv1
+REFRESH AUTO ON SCHEDULE EVERY 10 hour
+partition by(`xxx`)
+AS
+select ...;
+```
+#### Automatic trigger
+Note: Supported from version 2.1.4
+
+After changes occur in the base table data, automatically trigger the refresh
of related materialized views, with the same partition range as specified for
`scheduled trigger`.
+
+If the materialized view creation statement is as follows, then when there are
changes in the data of t1, the materialized view will be automatically
refreshed:
+```sql
+CREATE MATERIALIZED VIEW mv1
+REFRESH ON COMMIT
+partition by(`xxx`)
+AS
+select ... from t1;
+```
+
+## Problem localization
+### Localization means
+The commonly used commands for `olapTable` are also applicable to materialized
views, such as `show partitions`, `desc table`, `show data`, etc.
+
+The unique commands for materialized views mainly include the following:
+#### View materialized view metadata
+[mv_infos()](../../sql-manual/sql-functions/table-functions/mv_infos)
+
+Focus on the following fields:
+- State: If the state changes to SCHEMA_CHANGE, it means the schema of the
base table has changed. In this case, the materialized view cannot be used for
transparent rewriting (but direct querying of the materialized view is not
affected). If the next refresh task is successful, the state will be restored
to NORMAL.
+- SchemaChangeDetail: The reason for the SCHEMA_CHANGE.
+- RefreshState: The status of the last refresh task of the materialized view.
If it is FAIL, it means the execution failed, and further localization can be
done through tasks().
+- SyncWithBaseTables: Whether the materialized view is synchronized with the
base table data. If not synchronized, further determination can be made by
using show partitions to identify which partition is not synchronized.
+#### View tasks for the materialized view
+[tasks("type"="mv")](../../sql-manual/sql-functions/table-functions/tasks.md)
+
+Focus on the following fields:
+- Status: If it is FAILED, it means the task execution failed. You can check
the reason for failure through ErrorMsg. You can also search Doris logs using
LastQueryId to get more detailed error information.
+- ErrorMsg: The reason for the failure.
+- DurationMs: The duration of the task execution.
+- TaskContext: The context of the task, where you can see the trigger
information for the task.
+- RefreshMode: `complete` indicates that all partitions were refreshed,
`PARTIAL` indicates that some partitions were refreshed, and `NOT_REFRESH`
indicates that no partitions needed to be refreshed.
+
+### Common issues
+1. How does the materialized view determine which partitions need to be
refreshed?
+
+ Doris internally calculates the correspondence between the partitions of
the materialized view and the base table, and records the version of the base
table partitions used by the materialized view since the last successful
refresh. For example, if the materialized view `mv1` is created from base
tables `t1` and `t2`, and it is partitioned based on `t1`, the mapping between
the partitions of `t1` and `mv1` is maintained.
+
+ Assuming partition `p202003` of `mv1` corresponds to partitions `p20200301`
and `p20200302` of `t1`, after refreshing `p202003`, Doris will record
partitions `p20200301` and `p20200302`, as well as the current version of table
`t2`.
+
+ When refreshing next time, Doris will check if `p20200301`, `p20200302`, or
the version of `t2` has changed. If any of them has changed, it means that
`p202003` needs to be refreshed.
+
+ Of course, if it is acceptable for the business that `t2` changes do not
trigger a refresh of `mv1`, you can set this through the materialized view's
property `excluded_trigger_tables`.
+
+2. What to do if the materialized view consumes too many resources and affects
other business operations?
+
+ You can control the resources used by materialized view refresh tasks by
specifying the `workload_group` property of the materialized view.
+
+ It's important to note that if the memory is set too low and a single
partition refresh requires a significant amount of memory, the task may fail.
It's necessary to balance these considerations based on the business
requirements.
+
+3. Can new materialized views be created based on existing materialized views?
+
+ Yes, it's possible. Support for this feature started from version 2.1.3.
However, when refreshing data, each materialized view has its own separate
refresh logic.
+
+ For example, `mv2` is created based on `mv1`, and `mv1` is based on `t1`.
+
+ Then when `mv2` is refreshed, it will not consider whether `mv1` is
synchronized with `t1`.
+
+4. What external tables are supported?
+
+ All external tables supported by Doris can be used to create materialized
views. However, currently only Hive supports partitioned refresh, with support
for other types expected to be added gradually.
+
+5. The materialized view appears to be consistent with the data in Hive, but
in reality, it is not consistent.
+
+ The materialized view can only guarantee that its data is consistent with
the results queried through the catalog.
+
+ The catalog has certain metadata and data caching.
+
+ If you want the materialized view to be consistent with the data in Hive,
you need to ensure that the catalog's data is consistent with Hive's data by
using methods like `refresh catalog`.
+
+6. Does the materialized view support schema changes?
+
+ It does not support modification because the column properties of the
materialized view are derived from the SQL definition of the materialized view.
Explicit customization is not currently supported.
+
+7. Can the base table used by a materialized view allow schema changes?
+
+ Yes, it is allowed. However, after the change, the `State` of the
materialized views that use the base table will change from `NORMAL` to
`SCHEMA_CHANGE`. In this state, the materialized view cannot be used for
transparent rewriting, but direct queries to the materialized view are not
affected.
+
+ If the next refresh task of the materialized view is successful, the
`State` will change back from `SCHEMA_CHANGE` to `NORMAL`.
+
+8. Can tables with a primary key model be used to create materialized views?
+
+ Materialized views do not have specific requirements regarding the data
model of the base table. However, the materialized view itself can only have a
detailed model.
+
+9. Can indexes be created on materialized views?
+
+ Yes, you can create indexes on materialized views.
+
+10. Does the table get locked when refreshing a materialized view?
+
+ At a very small stage, the table will be locked and will not continuously
occupy the table lock (almost equivalent to the lock time of importing data)
+
+11. Is materialized views suitable for near real-time scenarios?
+
+ Not very suitable. The minimum unit for refreshing materialized views is
the partition. If the data volume is large, it will occupy more resources and
not be real-time enough. Consider synchronizing materialized views or other
means.
+
+## Usage scenarios
+### Query acceleration
+For BI report scenarios and other acceleration scenarios, queries are usually
performed by joining multiple tables and then aggregating them. Users are
sensitive to the response time of queries and generally need to return multiple
query results at the second level, which consumes a lot of computing resources
and sometimes makes it difficult to ensure the expected response time. In this
case, multi table materialized views can solve this problem.
+
+Materialized views are effective in accelerating repetitive and regular
queries. The materialized view supports both direct querying and transparent
rewriting. Transparent rewriting refers to the use of a set of materialized
views, and the optimizer automatically selects the optimal materialized view
available to respond to queries based on the rewriting algorithm and cost model.
+
+Use the pre computed results of materialized views to respond to queries.
Greatly reduces the resources used for table connections and aggregation
operations, and reduces query response time.
+
+### Data lake acceleration
+#### Background of demand
+Many users have a need for federated data queries based on Doris. Doris's
Multi Catalog feature makes this task very convenient. As long as a catalog is
created, there is no need to migrate data to Doris, and external data can be
queried through Doris
+#### Pain points
+But this can also cause some problems, as the speed of querying external data
may be affected by the network and third-party services, and may be slow. For
scenarios with high response speed requirements, it is difficult to meet the
requirements
+#### How to achieve appearance query acceleration
+Asynchronous materialized views can be created based on external catalogs, but
the data of the materialized view itself is stored within Doris, so querying
the materialized view will be fast. Therefore, for scenarios with high response
speed requirements, we can create a materialized view based on an external
catalog
+
+### Data modeling
+In some scenarios, users may use fact tables and dimension tables to create a
summary table, which can then be used for Ad hoc queries. This summary table
can also serve as a basic indicator table for subsequent modeling.
+
+At this point, the materialized view can be used to model the data of the base
table. Afterwards, the created materialized views can be used to create
higher-level materialized views (supported by 2.1.3), flexibly meeting
different needs.
+
+Different levels of materialized views can have their own refresh methods set,
for example:
+- The first layer's materialized view can be set to timed refresh, while the
second layer is set to trigger refresh. After the first layer's materialized
view refresh is completed, it will automatically trigger the refresh of the
second layer's materialized view.
+- The materialized views of each layer are set to be refreshed on a scheduled
basis. Therefore, when the materialized views of the second layer are
refreshed, it will not consider whether the materialized view data of the first
layer is synchronized with the base table. Instead, the materialized view data
of the first layer will be processed and synchronized to the second layer.
+
+## The relationship between materialized views and olap
+Note: Starting support in 2.1.4
+
+The underlying layer of the materialized view is an OLAP table of a duplicate
model.
+
+In theory, all functionalities of the Duplicate model are supported, but in
order to efficiently refresh data in a materialized view, some limitations are
placed on the functionalities:
+1. The partitioning of materialized views is automatically created and
maintained based on the base table, so partitioning operations cannot be
performed on materialized views
+2. Due to the related job processing behind the materialized view, the command
to delete or rename the table cannot be used to manipulate the materialized
view. Instead, the command of the materialized view itself needs to be used
+3. The column of the materialized view is derived from the query statement, so
it cannot be modified, otherwise it will cause the refresh task of the
materialized view to fail
+4. The materialized view has some properties that are not available in the
duplicate table, which need to be modified through the commands in the
materialized view. Other common properties need to be modified using the alter
table
+5. Currently, it is not possible to create a Rollup for asynchronous
materialized views, but indexes can be created
+6. The commands such as `desc` and `show partitions` are also applicable to
materialized views
+
## The use of materialized views
can be viewed [Query async materialized
view](./query-async-materialized-view.md)
diff --git
a/docs/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
b/docs/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
index 9121b07d6b..64036ea2d0 100644
---
a/docs/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
+++
b/docs/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
@@ -40,9 +40,9 @@ This statement is used to create an asynchronous materialized
view.
CREATE MATERIALIZED VIEW (IF NOT EXISTS)? mvName=multipartIdentifier
(LEFT_PAREN cols=simpleColumnDefs RIGHT_PAREN)? buildMode?
(REFRESH refreshMethod? refreshTrigger?)?
- (KEY keys=identifierList)?
+ ((DUPLICATE)? KEY keys=identifierList)?
(COMMENT STRING_LITERAL)?
- (PARTITION BY LEFT_PAREN partitionKey = identifier RIGHT_PAREN)?
+ (PARTITION BY LEFT_PAREN mvPartition RIGHT_PAREN)?
(DISTRIBUTED BY (HASH hashKeys=identifierList | RANDOM) (BUCKETS
(INTEGER_VALUE | AUTO))?)?
propertyClause?
AS query
@@ -130,10 +130,13 @@ MANUAL：Manual refresh
SCHEDULE：Timed refresh
+COMMIT: Trigger-based refresh. When the base table data changes, a task to
refresh the materialized view is automatically generated.
+
```sql
refreshTrigger
: ON MANUAL
| ON SCHEDULE refreshSchedule
+| ON COMMIT
;
refreshSchedule
@@ -176,8 +179,27 @@ There are two types of partitioning methods for
materialized views. If no partit
For example, if the base table is a range partition with a partition field of
`create_time` and partitioning by day, and `partition by(ct) as select
create_time as ct from t1` is specified when creating a materialized view,
then the materialized view will also be a range partition with a partition
field of 'ct' and partitioning by day
+Materialized views can also reduce the number of partitions by using partition
roll-up. Currently, the partition roll-up function supports `date_trunc`, and
the roll-up units supported are `year`, `month`, and `day`.
+
The selection of partition fields and the definition of materialized views
must meet the conditions for partition incremental updates for the materialized
view to be created successfully; otherwise, an error "Unable to find a suitable
base table for partitioning" will occur.
+```sql
+mvPartition
+ : partitionKey = identifier
+ | partitionExpr = functionCallExpression
+ ;
+```
+
+For example, if the base table is partitioned by day, the materialized view is
also partitioned by day.
+```sql
+partition by (`k2`)
+```
+
+For example, if the base table is partitioned by day, the materialized view is
partitioned by month.
+```sql
+partition by (date_trunc(`k2`,'month'))
+```
+
#### property
The materialized view can specify both the properties of the table and the
properties unique to the materialized view.
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/query/view-materialized-view/async-materialized-view.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/query/view-materialized-view/async-materialized-view.md
index d842d199c5..f83563e632 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/query/view-materialized-view/async-materialized-view.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/query/view-materialized-view/async-materialized-view.md
@@ -103,7 +103,7 @@ CREATE MATERIALIZED VIEW mv1
l_suppkey;
```
-具体的语法可查看[CREATE ASYNC MATERIALIZED
VIEW](../../sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md)
+具体的语法可查看
[CREATE-ASYNC-MATERIALIZED-VIEW](../../sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md).
### 查看物化视图元信息
@@ -111,9 +111,9 @@ CREATE MATERIALIZED VIEW mv1
select * from mv_infos("database"="tpch") where Name="mv1";
```
-物化视图独有的特性可以通过[mv_infos()](../../sql-manual/sql-functions/table-functions/mv_infos)
查看
+物化视图独有的特性可以通过
[mv_infos()](../../sql-manual/sql-functions/table-functions/mv_infos) 查看。
-和 table 相关的属性，仍通过[SHOW
TABLES](../../sql-manual/sql-statements/Show-Statements/SHOW-TABLES)来查看
+和 Table 相关的属性，仍通过 [SHOW
TABLES](../../sql-manual/sql-statements/Show-Statements/SHOW-TABLES) 查看。
### 刷新物化视图
@@ -133,10 +133,10 @@ REFRESH MATERIALIZED VIEW mv1
partitions(p_20231017_20231018);
### 任务管理
-每个物化视图都会默认有一个 job 负责刷新数据，job 用来描述物化视图的刷新策略等信息，每次触发刷新，都会产生一个 task，
-task 用来描述具体的一次刷新信息，例如刷新用的时间，刷新了哪些分区等
+每个物化视图都会默认有一个 Job 负责刷新数据，Job 用来描述物化视图的刷新策略等信息，每次触发刷新，都会产生一个 Task，
+Task 用来描述具体的一次刷新信息，例如刷新用的时间，刷新了哪些分区等
-#### 查看物化视图的 job
+#### 查看物化视图的 Job
```sql
select * from jobs("type"="mv") order by CreateTime;
@@ -152,9 +152,9 @@ PAUSE MATERIALIZED VIEW JOB ON mv1;
可以暂停物化视图的定时调度
-具体的语法可查看[PAUSE MATERIALIZED VIEW
JOB](../../sql-manual/sql-statements/Utility-Statements/PAUSE-MATERIALIZED-VIEW.md)
+具体的语法可查看[PAUSE MATERIALIZED VIEW
Job](../../sql-manual/sql-statements/Utility-Statements/PAUSE-MATERIALIZED-VIEW.md)
-#### 恢复物化视图 job 定时调度
+#### 恢复物化视图 Job 定时调度
```sql
RESUME MATERIALIZED VIEW JOB ON mv1;
@@ -164,7 +164,7 @@ RESUME MATERIALIZED VIEW JOB ON mv1;
具体的语法可查看[RESUME MATERIALIZED VIEW
JOB](../../sql-manual/sql-statements/Utility-Statements/RESUME-MATERIALIZED-VIEW.md)
-#### 查看物化视图的 task
+#### 查看物化视图的 Task
```sql
select * from tasks("type"="mv");
@@ -172,13 +172,13 @@ select * from tasks("type"="mv");
具体的语法可查看[tasks("type"="mv")](../../sql-manual/sql-functions/table-functions/tasks.md)
-#### 取消物化视图的 task
+#### 取消物化视图的 Task
```sql
CANCEL MATERIALIZED VIEW TASK realTaskId on mv1;
```
-可以取消本次 task 的运行
+可以取消本次 Task 的运行
具体的语法可查看[CANCEL MATERIALIZED VIEW
TASK](../../sql-manual/sql-statements/Utility-Statements/CANCEL-MATERIALIZED-VIEW-TASK.md)
@@ -189,7 +189,7 @@ CANCEL MATERIALIZED VIEW TASK realTaskId on mv1;
ALTER MATERIALIZED VIEW mv1 set("grace_period"="3333");
```
-修改物化视图的名字，物化视图的刷新方式及物化视图特有的 property 可通过[ALTER ASYNC MATERIALIZED
VIEW](../../sql-manual/sql-statements/Data-Definition-Statements/Alter/ALTER-ASYNC-MATERIALIZED-VIEW.md)来修改
+修改物化视图的名字，物化视图的刷新方式及物化视图特有的 Property 可通过[ALTER ASYNC MATERIALIZED
VIEW](../../sql-manual/sql-statements/Data-Definition-Statements/Alter/ALTER-ASYNC-MATERIALIZED-VIEW.md)来修改
物化视图本身也是一个 Table，所以 Table 相关的属性，例如副本数，仍通过`ALTER TABLE`相关的语法来修改。
@@ -199,13 +199,151 @@ ALTER MATERIALIZED VIEW mv1 set("grace_period"="3333");
DROP MATERIALIZED VIEW mv1;
```
-物化视图有专门的删除语法，不能通过 drop table 来删除，
+物化视图有专门的删除语法，不能通过 Drop Table 来删除，
具体的语法可查看[DROP ASYNC MATERIALIZED
VIEW](../../sql-manual/sql-statements/Data-Definition-Statements/Drop/DROP-ASYNC-MATERIALIZED-VIEW.md)
-## 最佳实践
-### 基表分区过多，物化视图只关注最近一段时间的数据
-创建基表，有三个分区
+## 分区说明
+物化视图的分区有两种方式：
+
+1. 自定义分区
+
+2. 依赖基表的分区自动创建分区
+
+### 自定义分区
+创建物化视图的时候不指定分区信息，物化视图就会默认创建一个分区，所有数据都存放在这个分区中。
+
+### 依赖基表进行分区
+物化视图可以通过多个基表 Join 关联创建。
+
+物化视图能选择追随其中一个基表进行分区（建议选择事实表）。
+
+例如
+
+t1的建表语句如下：
+
+```sql
+CREATE TABLE `t1` (
+ `user_id` LARGEINT NOT NULL,
+ `o_date` DATE NOT NULL,
+ `num` SMALLINT NOT NULL
+) ENGINE=OLAP
+COMMENT 'OLAP'
+PARTITION BY RANGE(`o_date`)
+(
+PARTITION p20170101 VALUES [('2017-01-01'), ('2017-01-02')),
+PARTITION p20170102 VALUES [('2017-01-02'), ('2017-01-03')),
+PARTITION p20170201 VALUES [('2017-02-01'), ('2017-02-02'))
+)
+DISTRIBUTED BY HASH(`user_id`) BUCKETS 2
+PROPERTIES ('replication_num' = '1') ;
+```
+
+t2的建表语句如下：
+
+```sql
+CREATE TABLE `t2` (
+ `user_id` LARGEINT NOT NULL,
+ `age` SMALLINT NOT NULL
+) ENGINE=OLAP
+PARTITION BY LIST(`age`)
+(
+ PARTITION `p1` VALUES IN ('1'),
+ PARTITION `p2` VALUES IN ('2')
+)
+DISTRIBUTED BY HASH(`user_id`) BUCKETS 2
+PROPERTIES ('replication_num' = '1') ;
+```
+
+如果物化视图的建表语句如下，那么物化视图mv1将和t1一样，有三个分区：
+- [('2017-01-01'), ('2017-01-02'))
+- [('2017-01-02'), ('2017-01-03'))
+- [('2017-02-01'), ('2017-02-02'))
+
+```sql
+CREATE MATERIALIZED VIEW mv1
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`order_date`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES (
+'replication_num' = '1'
+)
+AS
+SELECT t1.o_date as order_date, t1.user_id as user_id, t1.num, t2.age FROM t1
join t2 on t1.user_id=t2.user_id;
+```
+
+如果如果物化视图的建表语句如下，那么物化视图 mv2 将和 t2 一样，有两个分区：
+- ('1')
+- ('2')
+
+```sql
+CREATE MATERIALIZED VIEW mv2
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`age`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES (
+'replication_num' = '1'
+)
+AS
+SELECT t1.o_date as order_date, t1.user_id as user_id, t1.num, t2.age FROM t1
join t2 on t1.user_id=t2.user_id;
+```
+#### 基表有多列分区
+目前仅支持 Hive 外表有多列分区。
+
+Hive 外表有很多多级分区的情况,例如一级分区按照日期，二级分区按照区域。
+
+物化视图可以选择 Hive 的某一级分区列作为物化视图的分区列。
+
+例如hive的建表语句如下：
+```sql
+CREATE TABLE hive1 (
+`k1` int)
+PARTITIONED BY (
+`year` int,
+`region` string)
+STORED AS ORC;
+
+alter table hive1 add if not exists
+partition(year=2020,region="bj")
+partition(year=2020,region="sh")
+partition(year=2021,region="bj")
+partition(year=2021,region="sh")
+partition(year=2022,region="bj")
+partition(year=2022,region="sh")
+```
+如果物化视图的创建语句如下,那么物化视图 `mv_hive` 将有如下三个分区：
+- ('2020')
+- ('2021')
+- ('2022')
+```sql
+CREATE MATERIALIZED VIEW mv_hive
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`year`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT k1,year,region FROM hive1;
+```
+
+如果物化视图的建表语句如下,那么物化视图 `mv_hive2` 将有如下两个分区：
+- ('bj')
+- ('sh')
+```sql
+CREATE MATERIALIZED VIEW mv_hive2
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`region`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT k1,year,region FROM hive1;
+```
+
+#### 仅使用基表部分分区
+注意：2.1.1开始支持
+
+有些基表有很多分区，但是物化视图只关注最近一段时间的“热”数据，那么可以使用此功能。
+
+如果基表的建表语句如下
```sql
CREATE TABLE t1 (
`k1` INT,
@@ -224,7 +362,7 @@ PROPERTIES (
'replication_num' = '1'
);
```
-创建物化视图，只关注最近一天的数据，如果当前时间为 2024-03-28
xx:xx:xx，这样物化视图会仅有一个分区[("2024-03-28"),("2024-03-29"))
+如果物化视图的创建语句如下，代表物化视图只关注最近一天的数据，如果当前时间为 2024-03-28
xx:xx:xx，这样物化视图会仅有一个分区[("2024-03-28"),("2024-03-29"))
```sql
CREATE MATERIALIZED VIEW mv1
BUILD DEFERRED REFRESH AUTO ON MANUAL
@@ -238,8 +376,283 @@ PROPERTIES (
AS
SELECT * FROM t1;
```
-时间又过了一天，当前时间为 2024-03-29 xx:xx:xx，`t1`新增一个分区
[("2024-03-29"),("2024-03-30"))，如果此时刷新物化视图，刷新完成后，物化视图会仅有一个分区
[("2024-03-29"),("2024-03-30"))
+如果时间又过了一天，当前时间为 2024-03-29 xx:xx:xx，t1 新增一个分区
[("2024-03-29"),("2024-03-30"))，如果此时刷新物化视图，刷新完成后，物化视图会仅有一个分区
[("2024-03-29"),("2024-03-30"))
+
+如果分区字段是字符串类型，可以设置物化视图属性'partition_date_format',例如'%Y-%m-%d'
+
+#### 分区上卷
+
+基表的数据经过聚合后，每个分区的数据可能变的很少，这种情况下，可以使用分区上卷，减少物化视图的分区数量。
+
+##### List 分区
+注：Hive 分区对应 Apache Doris 的 List 分区。
+
+如果基表建表语句如下
+```sql
+CREATE TABLE `t1` (
+ `k1` INT NOT NULL,
+ `k2` DATE NOT NULL
+) ENGINE=OLAP
+DUPLICATE KEY(`k1`)
+COMMENT 'OLAP'
+PARTITION BY list(`k2`)
+(
+PARTITION p_20200101 VALUES IN ("2020-01-01"),
+PARTITION p_20200102 VALUES IN ("2020-01-02"),
+PARTITION p_20200201 VALUES IN ("2020-02-01")
+)
+DISTRIBUTED BY HASH(`k1`) BUCKETS 2
+PROPERTIES ('replication_num' = '1') ;
+```
+如果物化视图创建语句如下，那么物化视图有两个分区：
+- ("2020-01-01","2020-01-02")
+- ("2020-02-01")
+```sql
+CREATE MATERIALIZED VIEW mv1
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ partition by (date_trunc(`k2`,'month'))
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ AS
+ SELECT * FROM t1;
+```
+如果如果物化视图创建语句如下，那么物化视图有一个分区：
+- ("2020-01-01","2020-01-02","2020-02-01")
+```sql
+CREATE MATERIALIZED VIEW mv1
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ partition by (date_trunc(`k2`,'year'))
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ AS
+ SELECT * FROM t1;
+```
+##### range分区
+如果基表建表语句如下
+```sql
+CREATE TABLE `t1` (
+ `k1` LARGEINT NOT NULL,
+ `k2` DATE NOT NULL
+) ENGINE=OLAP
+DUPLICATE KEY(`k1`)
+COMMENT 'OLAP'
+PARTITION BY range(`k2`)
+(
+PARTITION p_20200101 VALUES [("2020-01-01"),("2020-01-02")),
+PARTITION p_20200102 VALUES [("2020-01-02"),("2020-01-03")),
+PARTITION p_20200201 VALUES [("2020-02-01"),("2020-02-02"))
+)
+DISTRIBUTED BY HASH(`k1`) BUCKETS 2
+PROPERTIES ('replication_num' = '1') ;
+```
+如果物化视图创建语句如下，那么物化视图有两个分区：
+- [("2020-01-01","2020-02-01"))
+- [("2020-02-01","2020-03-01"))
+```sql
+CREATE MATERIALIZED VIEW mv1
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ partition by (date_trunc(`k2`,'month'))
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ AS
+ SELECT * FROM t1;
+```
+如果物化视图创建语句如下，那么物化视图有一个分区：
+- [("2020-01-01","2021-01-01"))
+```sql
+CREATE MATERIALIZED VIEW mv1
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ partition by (date_trunc(`k2`,'year'))
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ AS
+ SELECT * FROM t1;
+```
+注意：分区是字符串时，上卷的方式还在设计中。现在的行为有可能变动，最好不要使用。
+
+## 数据刷新
+### 刷新原理
+物化视图是按照分区为单位进行刷新的，如果物化视图没有指定分区，那么每次都刷新物化视图的默认分区，相当于刷新物化视图的全部数据。
+### 触发机制
+物化视图有三种触发刷新机制
+#### 手动触发
+用户通过 SQL 语句触发物化视图的刷新，目前有三种策略：
+1. 不关心具体刷新哪些分区，要求刷新完成后，物化视图的数据和基表保持同步.
+```sql
+REFRESH MATERIALIZED VIEW mvName AUTO;
+```
+2. 不管物化视图现存哪些数据，刷新物化视图的所有分区
+```sql
+REFRESH MATERIALIZED VIEW mvName COMPLETE;
+```
+3. 不管物化视图现存哪些数据，只刷新指定的分区
+```sql
+REFRESH MATERIALIZED VIEW mvName partitions(partitionName1,partitionName2);
+```
+`partitionName` 可以通过 `show partitions from mvName` 获取。
+#### 定时触发
+通过物化视图的创建语句指定间隔多久刷新一次数据
+1. 如果物化视图的创建语句如下，要求全量刷新（refresh complete），那么物化视图每 10 小时刷新一次，并且刷新物化视图的所有分区。
+```sql
+CREATE MATERIALIZED VIEW mv1
+REFRESH COMPLETE ON SCHEDULE EVERY 10 hour
+partition by(`xxx`)
+AS
+select ...;
+```
+2. 如果物化视图的创建语句如下，要求全量刷新（refresh
auto），那么物化视图每10小时刷新一次，并且自动计算需要刷新的分区（2.1.3开始能自动计算 Hive 需要刷新的分区）。
+```sql
+CREATE MATERIALIZED VIEW mv1
+REFRESH AUTO ON SCHEDULE EVERY 10 hour
+partition by(`xxx`)
+AS
+select ...;
+```
+#### 自动触发
+注意：2.1.4开始支持
+
+基表数据发生变更后，自动触发相关物化视图刷新，刷新的分区范围同**定时触发**。
+
+如果物化视图的创建语句如下，那么当 t1 的数据发生变化，会自动触发物化视图的刷新。
+```sql
+CREATE MATERIALIZED VIEW mv1
+REFRESH ON COMMIT
+partition by(`xxx`)
+AS
+select ... from t1;
+```
+
+## 问题定位
+### 定位手段
+`olapTable` 的常用命令都适用于物化视图，例如`show partitions`，`desc table`，`show data`等。
+
+物化视图独有的命令主要有以下几个：
+#### 查看物化视图元信息
+[mv_infos()](../../sql-manual/sql-functions/table-functions/mv_infos)
+
+重点关注以下字段：
+- State：如果变为 `SCHEMA_CHANGE`，代表基表的 Schema
发生了变化，此时物化视图将不能用来透明改写(但是不影响直接查询物化视图)，下次刷新任务如果执行成功，将恢复为 `NORMAL`。
+- SchemaChangeDetail：`SCHEMA_CHANGE` 发生的原因
+- RefreshState: 物化视图最后一次任务刷新的状态，如果为`FAIL`，代表执行失败，可以通过 `tasks()` 进一步定位
+- SyncWithBaseTables: 是否和基表数据同步，如果不同步，可通过 `show partitions` 进一步判断哪个分区不同步
+#### 查看物化视图的 Task
+[tasks("type"="mv")](../../sql-manual/sql-functions/table-functions/tasks.md)
+重点关注以下字段：
+- Status：如果为 `FAILED`，代表运行失败，可通过 `ErrorMsg` 查看失败原因，也可通过 `LastQueryId` 来搜索
Apache Doris 的日志，获取更详细的错误信息
+- ErrorMsg：失败原因
+- DurationMs：Task 运行耗时
+- TaskContext：Task 上下文，可看到 Task 的触发信息
+- RefreshMode：`COMPLETE` 代表刷新了全部分区，`PARTIAL` 代表刷新了部分分区，`NOT_REFRESH`
代表不需要刷新任何分区
+
+### 常见问题
+1. 物化视图是如何判断需要刷新哪些分区的？
+
+
doris内部会计算物化视图和基表的分区对应关系，并且记录上次刷新成功之后物化视图使用的基表分区版本，例如物化视图mv1由基表t1和t2创建，并且依赖t1进行分区
+
+ 假设mv1的分区p202003
对应基表t1的分区p20200301和p20200302，那么刷新p202003之后，会记录分区p20200301，p20200302，以及表t2的当前版本
+
+ 下次刷新时，会判断p20200301，p20200302以及t2的版本是否发生变化，如果其中之一发生了变化，代表p202003需要刷新
+
+ 当然如果业务上能接受t2的变化，不触发mv1的刷新，可以通过物化视图的属性`excluded_trigger_tables`来设置
+
+2. 物化视图占用资源过多，影响其他业务怎么办？
+
+ 可以通过物化视图的属性指定`workload_group`来控制物化视图刷新任务的资源
+
+ 使用时需要注意，如果内存设置的太小，单个分区刷新又需要的内存较多，任务会刷新失败。需要根据业务情况进行权衡
+
+3. 能基于物化视图创建新的物化视图么
+
+ 能。2.1.3开始支持。但是刷新数据的时候，每个物化视图都是单独的刷新逻辑
+
+ 例如mv2基于mv1创建，mv1基于t1创建
+
+ 那么mv2刷新的时候不会考虑mv1相对于t1数据是否同步
+
+4. 都支持哪些外表？
+
+ doris支持的所有外表都能用于创建物化视图，但是目前仅有hive支持分区刷新，后续会陆续支持
+
+5. 物化视图显示和hive数据一致，但是实际上不一致
+
+ 物化视图仅能保证物化视图的数据和通过 Catalog 查询的结果一致
+
+ Catalog 有一定的元数据，数据缓存。
+
+ 如果想物化视图和 Hive 数据一致，需要通过 Refresh Catalog 等方式，确保 Catalog 的数据和 Hive 的数据一致
+
+6. 物化视图支持 Schema Change 么？
+
+ 不支持修改，因为物化视图的列属性是根据物化视图定义 SQL 推导出来的。目前不支持显示的自定义修改
+
+7. 物化视图使用的基表允许 Schema Change 么？
+
+ 允许。但是变更之后，使用到该基表的物化视图的 State 会由 `NORMAL` 变为
`SCHEMA_CHANGE`，此时物化视图将不能被用来透明改写，但是不影响直查物化视图
+
+ 如果物化视图下次刷新任务成功，那么 State 会由 `SCHEMA_CHANGE` 变回 `NORMAL`
+
+8. 主键模型的表能用来创建物化视图么？
+
+ 物化视图对基表的数据模型没有要求。但是物化视图本身只能是明细模型。
+
+9. 物化视图上还能建索引么？
+
+ 能
+
+10. 物化视图刷新的时候会锁表么？
+
+ 很小的阶段会锁表，不会持续的占用表锁（几乎等同于导入数据的锁表时间）
+
+11. 物化视图适合近实时场景么？
+
+ 不太适合。物化视图刷新的最小单位是分区，如果数据量较大会占用较多的资源，并且不够实时。可以考虑同步物化视图或其他手段。
+
+## 使用场景
+### 查询加速
+对于 BI 报表场景以及其他加速场景，查询通常是多张表进行 Join
计算，之后进行聚合计算，用户对于查询的响应时间敏感，一般需要在秒级别返回多条查询结果，会消耗大量计算资源，并且有时很难保证预期的响应时间，此时多表物化视图可以解决此问题。
+
+物化视图对于加速重复有规律的查询很有效。物化视图视图支持直查，也支持透明改写，透明改写指的是使用一组物化视图，优化器根据改写算法和代价模型自动选择可用的最优物化视图来响应查询。
+
+使用物化视图的预计算结果来响应查询。极大地降低了表连接和聚合操作使用的资源，减少查询响应时间。
+
+### 数据湖加速
+#### 需求背景
+很多用户有基于 Apache Doris 进行联邦数据查询的需求，Apache Doris
的多源数据目录（Multi-Catalog）功能让这件事变的很方便，只要创建一个 Catalog，无须把数据迁移到 Apache Doris，就可以通过
Apache Doris 对外部数据进行查询
+#### 痛点
+但是这也会造成一些问题，因为查询外部数据的速度可能会收到网络及第三方服务的影响，又可能会很慢，对于响应速度要求比较高的场景，很难满足需求
+#### 如何实现外表的查询加速
+异步物化视图可以基于外部 Catalog 来创建，但是物化视图本身的数据是存在 Apache Doris
内部的，所以查询物化视图的速度会很快。因此，对于响应速度要求比较高的场景，我们可以基于外部 Catalog 创建一个物化视图
+
+### 数据建模
+有些场景，用户会使用事实表和维度表加工成一张汇总表，之后对此汇总表进行 Ad hoc 查询，此汇总表也可作为基础指标表用于后续的建模。
+
+此时可以利用物化视图对基表的数据进行建模。之后，还可以利用创建好的物化视图创建更高层级的物化视图（2.1.3支持），灵活满足不同的需求。
+
+不同层级的物化视图都可以自己设置刷新方式，例如：
+- 第一层的物化视图可以设置为定时刷新，第二层的设置为触发刷新，那么第一层的物化视图刷新完成后，会自动触发第二层物化视图的刷新。
+- 每层的物化视图都设置为定时刷新，那么第二层物化视图刷新的时候，不会考虑第一层的物化视图数据是否和基表同步，只会把第一层物化视图的数据加工后同步到第二层。
+
+## 物化视图和 Olap 的关系
+注意： 2.1.4 版本开始支持。
+
+物化视图底层是一个 Duplicate 模型的 Olap 表。
+
+理论上支持 Duplicate 模型的所有功能，但是物化视图为了能正常高效刷新数据，所以对功能做了一些限制：
+1. 物化视图的分区是基于基表自动创建维护的，所以不能对物化视图进行分区操作
+2. 由于物化视图背后有相关的 Job 要处理，所以不能用删除 Table 或重命名 Table 的命令操作物化视图，需要使用物化视图自身的命令
+3. 物化视图的 Column是根据查询语句推导出来的，所以不能修改，否则会导致物化视图的刷新任务失败
+4. 物化视图有一些 Duplicate Table 没有的 Property，这部分 Property 需要通过物化视图的命令进行修改，其他公用的
Property 需要用 Alter Table 进行修改
+5. 目前不能对异步物化视图创建 ROLLUP，但是能创建索引
+6. `desc`，`show partitions` 等命令同样适用于物化视图
## 物化视图的使用
@@ -248,5 +661,3 @@ SELECT * FROM t1;
## 注意事项
- 异步物化视图仅支持在[Nereids 优化器](../nereids/nereids)使用
--
当前判断物化视图和基表是否同步仅支持`OlapTable`。对于其它外表，会直接认为是同步的。例如，物化视图的基表全是外表。在查询`mv_infos()`时，SyncWithBaseTables
会永远为 1（true）。在刷新物化视图时需要手动刷新指定的分区或指定`complete`刷新全部分区
-
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
index e428537267..3fcfe11e9e 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
@@ -40,11 +40,11 @@ CREATE ASYNC MATERIALIZED VIEW
CREATE MATERIALIZED VIEW (IF NOT EXISTS)? mvName=multipartIdentifier
(LEFT_PAREN cols=simpleColumnDefs RIGHT_PAREN)? buildMode?
(REFRESH refreshMethod? refreshTrigger?)?
- (KEY keys=identifierList)?
+ ((DUPLICATE)? KEY keys=identifierList)?
(COMMENT STRING_LITERAL)?
- (PARTITION BY LEFT_PAREN partitionKey = identifier RIGHT_PAREN)?
+ (PARTITION BY LEFT_PAREN mvPartition RIGHT_PAREN)?
(DISTRIBUTED BY (HASH hashKeys=identifierList | RANDOM) (BUCKETS
(INTEGER_VALUE | AUTO))?)?
- propertyClause?
+ PropertyClause?
AS query
```
@@ -131,10 +131,13 @@ MANUAL：手动刷新
SCHEDULE：定时刷新
+COMMIT：触发式刷新，基表数据变更时，自动生成刷新物化视图的任务
+
```sql
refreshTrigger
: ON MANUAL
| ON SCHEDULE refreshSchedule
+| ON COMMIT
;
refreshSchedule
@@ -176,7 +179,26 @@ KEY(k1,k2)
例如：基表是 Range 分区，分区字段为 `create_time` 并按天分区，创建物化视图时指定 `partition by(ct) as
select create_time as ct from t1`，那么物化视图也会是 Range 分区，分区字段为 `ct`，并且按天分区。
-分区字段的选择和物化视图的定义需要满足分区增量更新的条件，物化视图才可以创建成功，否则会报错 `Unable to find a suitable base
table for partitioning`。
+物化视图也可以通过分区上卷的方式减少物化视图的分区数量，目前分区上卷函数支持 `date_trunc`,上卷的单位支持 `year`, `month`,
`day`
+
+分区字段的选择和物化视图的定义需要满足分区增量更新的条件，物化视图才可以创建成功，否则会报错 `Unable to find a suitable base
table for partitioning`
+
+```sql
+mvPartition
+ : partitionKey = identifier
+ | partitionExpr = functionCallExpression
+ ;
+```
+
+例如基表按天分区，物化视图同样按天分区
+```sql
+partition by (`k2`)
+```
+
+例如基表按天分区，物化视图按月分区
+```sql
+partition by (date_trunc(`k2`,'month'))
+```
#### Property
物化视图既可以指定 Table 的 Property，也可以指定物化视图特有的 Property。
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query/view-materialized-view/async-materialized-view.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query/view-materialized-view/async-materialized-view.md
index 531f9586fb..f83563e632 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query/view-materialized-view/async-materialized-view.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query/view-materialized-view/async-materialized-view.md
@@ -103,7 +103,7 @@ CREATE MATERIALIZED VIEW mv1
l_suppkey;
```
-具体的语法可查看[CREATE ASYNC MATERIALIZED
VIEW](../../sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md)
+具体的语法可查看
[CREATE-ASYNC-MATERIALIZED-VIEW](../../sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md).
### 查看物化视图元信息
@@ -111,9 +111,9 @@ CREATE MATERIALIZED VIEW mv1
select * from mv_infos("database"="tpch") where Name="mv1";
```
-物化视图独有的特性可以通过[mv_infos()](../../sql-manual/sql-functions/table-functions/mv_infos.md)查看
+物化视图独有的特性可以通过
[mv_infos()](../../sql-manual/sql-functions/table-functions/mv_infos) 查看。
-和 table 相关的属性，仍通过[SHOW
TABLES](../../sql-manual/sql-statements/Show-Statements/SHOW-TABLES.md)来查看
+和 Table 相关的属性，仍通过 [SHOW
TABLES](../../sql-manual/sql-statements/Show-Statements/SHOW-TABLES) 查看。
### 刷新物化视图
@@ -129,20 +129,20 @@ SHOW PARTITIONS FROM mv1;
REFRESH MATERIALIZED VIEW mv1 partitions(p_20231017_20231018);
```
-具体的语法可查看[REFRESH MATERIALIZED
VIEW](../../sql-manual/sql-statements/Utility-Statements/REFRESH-MATERIALIZED-VIEW.md)
+具体的语法可查看[REFRESH MATERIALIZED
VIEW](../../sql-manual/sql-statements/Utility-Statements/REFRESH-MATERIALIZED-VIEW)
### 任务管理
-每个物化视图都会默认有一个 job 负责刷新数据，job 用来描述物化视图的刷新策略等信息，每次触发刷新，都会产生一个 task，
-task 用来描述具体的一次刷新信息，例如刷新用的时间，刷新了哪些分区等
+每个物化视图都会默认有一个 Job 负责刷新数据，Job 用来描述物化视图的刷新策略等信息，每次触发刷新，都会产生一个 Task，
+Task 用来描述具体的一次刷新信息，例如刷新用的时间，刷新了哪些分区等
-#### 查看物化视图的 job
+#### 查看物化视图的 Job
```sql
select * from jobs("type"="mv") order by CreateTime;
```
-具体的语法可查看[jobs("type"="mv")](../../sql-manual/sql-functions/table-functions/jobs.md)
+具体的语法可查看[jobs("type"="mv")](../../sql-manual/sql-functions/table-functions/jobs)
#### 暂停物化视图 job 定时调度
@@ -152,9 +152,9 @@ PAUSE MATERIALIZED VIEW JOB ON mv1;
可以暂停物化视图的定时调度
-具体的语法可查看[PAUSE MATERIALIZED VIEW
JOB](../../sql-manual/sql-statements/Utility-Statements/PAUSE-MATERIALIZED-VIEW.md)
+具体的语法可查看[PAUSE MATERIALIZED VIEW
Job](../../sql-manual/sql-statements/Utility-Statements/PAUSE-MATERIALIZED-VIEW.md)
-#### 恢复物化视图 job 定时调度
+#### 恢复物化视图 Job 定时调度
```sql
RESUME MATERIALIZED VIEW JOB ON mv1;
@@ -164,7 +164,7 @@ RESUME MATERIALIZED VIEW JOB ON mv1;
具体的语法可查看[RESUME MATERIALIZED VIEW
JOB](../../sql-manual/sql-statements/Utility-Statements/RESUME-MATERIALIZED-VIEW.md)
-#### 查看物化视图的 task
+#### 查看物化视图的 Task
```sql
select * from tasks("type"="mv");
@@ -172,13 +172,13 @@ select * from tasks("type"="mv");
具体的语法可查看[tasks("type"="mv")](../../sql-manual/sql-functions/table-functions/tasks.md)
-#### 取消物化视图的 task
+#### 取消物化视图的 Task
```sql
CANCEL MATERIALIZED VIEW TASK realTaskId on mv1;
```
-可以取消本次 task 的运行
+可以取消本次 Task 的运行
具体的语法可查看[CANCEL MATERIALIZED VIEW
TASK](../../sql-manual/sql-statements/Utility-Statements/CANCEL-MATERIALIZED-VIEW-TASK.md)
@@ -189,7 +189,7 @@ CANCEL MATERIALIZED VIEW TASK realTaskId on mv1;
ALTER MATERIALIZED VIEW mv1 set("grace_period"="3333");
```
-修改物化视图的名字，物化视图的刷新方式及物化视图特有的 property 可通过[ALTER ASYNC MATERIALIZED
VIEW](../../sql-manual/sql-statements/Data-Definition-Statements/Alter/ALTER-ASYNC-MATERIALIZED-VIEW.md)来修改
+修改物化视图的名字，物化视图的刷新方式及物化视图特有的 Property 可通过[ALTER ASYNC MATERIALIZED
VIEW](../../sql-manual/sql-statements/Data-Definition-Statements/Alter/ALTER-ASYNC-MATERIALIZED-VIEW.md)来修改
物化视图本身也是一个 Table，所以 Table 相关的属性，例如副本数，仍通过`ALTER TABLE`相关的语法来修改。
@@ -199,13 +199,151 @@ ALTER MATERIALIZED VIEW mv1 set("grace_period"="3333");
DROP MATERIALIZED VIEW mv1;
```
-物化视图有专门的删除语法，不能通过 drop table 来删除，
+物化视图有专门的删除语法，不能通过 Drop Table 来删除，
具体的语法可查看[DROP ASYNC MATERIALIZED
VIEW](../../sql-manual/sql-statements/Data-Definition-Statements/Drop/DROP-ASYNC-MATERIALIZED-VIEW.md)
-## 最佳实践
-### 基表分区过多，物化视图只关注最近一段时间的数据
-创建基表，有三个分区
+## 分区说明
+物化视图的分区有两种方式：
+
+1. 自定义分区
+
+2. 依赖基表的分区自动创建分区
+
+### 自定义分区
+创建物化视图的时候不指定分区信息，物化视图就会默认创建一个分区，所有数据都存放在这个分区中。
+
+### 依赖基表进行分区
+物化视图可以通过多个基表 Join 关联创建。
+
+物化视图能选择追随其中一个基表进行分区（建议选择事实表）。
+
+例如
+
+t1的建表语句如下：
+
+```sql
+CREATE TABLE `t1` (
+ `user_id` LARGEINT NOT NULL,
+ `o_date` DATE NOT NULL,
+ `num` SMALLINT NOT NULL
+) ENGINE=OLAP
+COMMENT 'OLAP'
+PARTITION BY RANGE(`o_date`)
+(
+PARTITION p20170101 VALUES [('2017-01-01'), ('2017-01-02')),
+PARTITION p20170102 VALUES [('2017-01-02'), ('2017-01-03')),
+PARTITION p20170201 VALUES [('2017-02-01'), ('2017-02-02'))
+)
+DISTRIBUTED BY HASH(`user_id`) BUCKETS 2
+PROPERTIES ('replication_num' = '1') ;
+```
+
+t2的建表语句如下：
+
+```sql
+CREATE TABLE `t2` (
+ `user_id` LARGEINT NOT NULL,
+ `age` SMALLINT NOT NULL
+) ENGINE=OLAP
+PARTITION BY LIST(`age`)
+(
+ PARTITION `p1` VALUES IN ('1'),
+ PARTITION `p2` VALUES IN ('2')
+)
+DISTRIBUTED BY HASH(`user_id`) BUCKETS 2
+PROPERTIES ('replication_num' = '1') ;
+```
+
+如果物化视图的建表语句如下，那么物化视图mv1将和t1一样，有三个分区：
+- [('2017-01-01'), ('2017-01-02'))
+- [('2017-01-02'), ('2017-01-03'))
+- [('2017-02-01'), ('2017-02-02'))
+
+```sql
+CREATE MATERIALIZED VIEW mv1
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`order_date`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES (
+'replication_num' = '1'
+)
+AS
+SELECT t1.o_date as order_date, t1.user_id as user_id, t1.num, t2.age FROM t1
join t2 on t1.user_id=t2.user_id;
+```
+
+如果如果物化视图的建表语句如下，那么物化视图 mv2 将和 t2 一样，有两个分区：
+- ('1')
+- ('2')
+
+```sql
+CREATE MATERIALIZED VIEW mv2
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`age`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES (
+'replication_num' = '1'
+)
+AS
+SELECT t1.o_date as order_date, t1.user_id as user_id, t1.num, t2.age FROM t1
join t2 on t1.user_id=t2.user_id;
+```
+#### 基表有多列分区
+目前仅支持 Hive 外表有多列分区。
+
+Hive 外表有很多多级分区的情况,例如一级分区按照日期，二级分区按照区域。
+
+物化视图可以选择 Hive 的某一级分区列作为物化视图的分区列。
+
+例如hive的建表语句如下：
+```sql
+CREATE TABLE hive1 (
+`k1` int)
+PARTITIONED BY (
+`year` int,
+`region` string)
+STORED AS ORC;
+
+alter table hive1 add if not exists
+partition(year=2020,region="bj")
+partition(year=2020,region="sh")
+partition(year=2021,region="bj")
+partition(year=2021,region="sh")
+partition(year=2022,region="bj")
+partition(year=2022,region="sh")
+```
+如果物化视图的创建语句如下,那么物化视图 `mv_hive` 将有如下三个分区：
+- ('2020')
+- ('2021')
+- ('2022')
+```sql
+CREATE MATERIALIZED VIEW mv_hive
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`year`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT k1,year,region FROM hive1;
+```
+
+如果物化视图的建表语句如下,那么物化视图 `mv_hive2` 将有如下两个分区：
+- ('bj')
+- ('sh')
+```sql
+CREATE MATERIALIZED VIEW mv_hive2
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`region`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT k1,year,region FROM hive1;
+```
+
+#### 仅使用基表部分分区
+注意：2.1.1开始支持
+
+有些基表有很多分区，但是物化视图只关注最近一段时间的“热”数据，那么可以使用此功能。
+
+如果基表的建表语句如下
```sql
CREATE TABLE t1 (
`k1` INT,
@@ -224,7 +362,7 @@ PROPERTIES (
'replication_num' = '1'
);
```
-创建物化视图，只关注最近一天的数据，如果当前时间为 2024-03-28
xx:xx:xx，这样物化视图会仅有一个分区[("2024-03-28"),("2024-03-29"))
+如果物化视图的创建语句如下，代表物化视图只关注最近一天的数据，如果当前时间为 2024-03-28
xx:xx:xx，这样物化视图会仅有一个分区[("2024-03-28"),("2024-03-29"))
```sql
CREATE MATERIALIZED VIEW mv1
BUILD DEFERRED REFRESH AUTO ON MANUAL
@@ -238,8 +376,283 @@ PROPERTIES (
AS
SELECT * FROM t1;
```
-时间又过了一天，当前时间为 2024-03-29 xx:xx:xx，`t1`新增一个分区
[("2024-03-29"),("2024-03-30"))，如果此时刷新物化视图，刷新完成后，物化视图会仅有一个分区
[("2024-03-29"),("2024-03-30"))
+如果时间又过了一天，当前时间为 2024-03-29 xx:xx:xx，t1 新增一个分区
[("2024-03-29"),("2024-03-30"))，如果此时刷新物化视图，刷新完成后，物化视图会仅有一个分区
[("2024-03-29"),("2024-03-30"))
+
+如果分区字段是字符串类型，可以设置物化视图属性'partition_date_format',例如'%Y-%m-%d'
+
+#### 分区上卷
+
+基表的数据经过聚合后，每个分区的数据可能变的很少，这种情况下，可以使用分区上卷，减少物化视图的分区数量。
+
+##### List 分区
+注：Hive 分区对应 Apache Doris 的 List 分区。
+
+如果基表建表语句如下
+```sql
+CREATE TABLE `t1` (
+ `k1` INT NOT NULL,
+ `k2` DATE NOT NULL
+) ENGINE=OLAP
+DUPLICATE KEY(`k1`)
+COMMENT 'OLAP'
+PARTITION BY list(`k2`)
+(
+PARTITION p_20200101 VALUES IN ("2020-01-01"),
+PARTITION p_20200102 VALUES IN ("2020-01-02"),
+PARTITION p_20200201 VALUES IN ("2020-02-01")
+)
+DISTRIBUTED BY HASH(`k1`) BUCKETS 2
+PROPERTIES ('replication_num' = '1') ;
+```
+如果物化视图创建语句如下，那么物化视图有两个分区：
+- ("2020-01-01","2020-01-02")
+- ("2020-02-01")
+```sql
+CREATE MATERIALIZED VIEW mv1
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ partition by (date_trunc(`k2`,'month'))
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ AS
+ SELECT * FROM t1;
+```
+如果如果物化视图创建语句如下，那么物化视图有一个分区：
+- ("2020-01-01","2020-01-02","2020-02-01")
+```sql
+CREATE MATERIALIZED VIEW mv1
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ partition by (date_trunc(`k2`,'year'))
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ AS
+ SELECT * FROM t1;
+```
+##### range分区
+如果基表建表语句如下
+```sql
+CREATE TABLE `t1` (
+ `k1` LARGEINT NOT NULL,
+ `k2` DATE NOT NULL
+) ENGINE=OLAP
+DUPLICATE KEY(`k1`)
+COMMENT 'OLAP'
+PARTITION BY range(`k2`)
+(
+PARTITION p_20200101 VALUES [("2020-01-01"),("2020-01-02")),
+PARTITION p_20200102 VALUES [("2020-01-02"),("2020-01-03")),
+PARTITION p_20200201 VALUES [("2020-02-01"),("2020-02-02"))
+)
+DISTRIBUTED BY HASH(`k1`) BUCKETS 2
+PROPERTIES ('replication_num' = '1') ;
+```
+如果物化视图创建语句如下，那么物化视图有两个分区：
+- [("2020-01-01","2020-02-01"))
+- [("2020-02-01","2020-03-01"))
+```sql
+CREATE MATERIALIZED VIEW mv1
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ partition by (date_trunc(`k2`,'month'))
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ AS
+ SELECT * FROM t1;
+```
+如果物化视图创建语句如下，那么物化视图有一个分区：
+- [("2020-01-01","2021-01-01"))
+```sql
+CREATE MATERIALIZED VIEW mv1
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ partition by (date_trunc(`k2`,'year'))
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES (
+ 'replication_num' = '1'
+ )
+ AS
+ SELECT * FROM t1;
+```
+注意：分区是字符串时，上卷的方式还在设计中。现在的行为有可能变动，最好不要使用。
+
+## 数据刷新
+### 刷新原理
+物化视图是按照分区为单位进行刷新的，如果物化视图没有指定分区，那么每次都刷新物化视图的默认分区，相当于刷新物化视图的全部数据。
+### 触发机制
+物化视图有三种触发刷新机制
+#### 手动触发
+用户通过 SQL 语句触发物化视图的刷新，目前有三种策略：
+1. 不关心具体刷新哪些分区，要求刷新完成后，物化视图的数据和基表保持同步.
+```sql
+REFRESH MATERIALIZED VIEW mvName AUTO;
+```
+2. 不管物化视图现存哪些数据，刷新物化视图的所有分区
+```sql
+REFRESH MATERIALIZED VIEW mvName COMPLETE;
+```
+3. 不管物化视图现存哪些数据，只刷新指定的分区
+```sql
+REFRESH MATERIALIZED VIEW mvName partitions(partitionName1,partitionName2);
+```
+`partitionName` 可以通过 `show partitions from mvName` 获取。
+#### 定时触发
+通过物化视图的创建语句指定间隔多久刷新一次数据
+1. 如果物化视图的创建语句如下，要求全量刷新（refresh complete），那么物化视图每 10 小时刷新一次，并且刷新物化视图的所有分区。
+```sql
+CREATE MATERIALIZED VIEW mv1
+REFRESH COMPLETE ON SCHEDULE EVERY 10 hour
+partition by(`xxx`)
+AS
+select ...;
+```
+2. 如果物化视图的创建语句如下，要求全量刷新（refresh
auto），那么物化视图每10小时刷新一次，并且自动计算需要刷新的分区（2.1.3开始能自动计算 Hive 需要刷新的分区）。
+```sql
+CREATE MATERIALIZED VIEW mv1
+REFRESH AUTO ON SCHEDULE EVERY 10 hour
+partition by(`xxx`)
+AS
+select ...;
+```
+#### 自动触发
+注意：2.1.4开始支持
+
+基表数据发生变更后，自动触发相关物化视图刷新，刷新的分区范围同**定时触发**。
+
+如果物化视图的创建语句如下，那么当 t1 的数据发生变化，会自动触发物化视图的刷新。
+```sql
+CREATE MATERIALIZED VIEW mv1
+REFRESH ON COMMIT
+partition by(`xxx`)
+AS
+select ... from t1;
+```
+
+## 问题定位
+### 定位手段
+`olapTable` 的常用命令都适用于物化视图，例如`show partitions`，`desc table`，`show data`等。
+
+物化视图独有的命令主要有以下几个：
+#### 查看物化视图元信息
+[mv_infos()](../../sql-manual/sql-functions/table-functions/mv_infos)
+
+重点关注以下字段：
+- State：如果变为 `SCHEMA_CHANGE`，代表基表的 Schema
发生了变化，此时物化视图将不能用来透明改写(但是不影响直接查询物化视图)，下次刷新任务如果执行成功，将恢复为 `NORMAL`。
+- SchemaChangeDetail：`SCHEMA_CHANGE` 发生的原因
+- RefreshState: 物化视图最后一次任务刷新的状态，如果为`FAIL`，代表执行失败，可以通过 `tasks()` 进一步定位
+- SyncWithBaseTables: 是否和基表数据同步，如果不同步，可通过 `show partitions` 进一步判断哪个分区不同步
+#### 查看物化视图的 Task
+[tasks("type"="mv")](../../sql-manual/sql-functions/table-functions/tasks.md)
+重点关注以下字段：
+- Status：如果为 `FAILED`，代表运行失败，可通过 `ErrorMsg` 查看失败原因，也可通过 `LastQueryId` 来搜索
Apache Doris 的日志，获取更详细的错误信息
+- ErrorMsg：失败原因
+- DurationMs：Task 运行耗时
+- TaskContext：Task 上下文，可看到 Task 的触发信息
+- RefreshMode：`COMPLETE` 代表刷新了全部分区，`PARTIAL` 代表刷新了部分分区，`NOT_REFRESH`
代表不需要刷新任何分区
+
+### 常见问题
+1. 物化视图是如何判断需要刷新哪些分区的？
+
+
doris内部会计算物化视图和基表的分区对应关系，并且记录上次刷新成功之后物化视图使用的基表分区版本，例如物化视图mv1由基表t1和t2创建，并且依赖t1进行分区
+
+ 假设mv1的分区p202003
对应基表t1的分区p20200301和p20200302，那么刷新p202003之后，会记录分区p20200301，p20200302，以及表t2的当前版本
+
+ 下次刷新时，会判断p20200301，p20200302以及t2的版本是否发生变化，如果其中之一发生了变化，代表p202003需要刷新
+
+ 当然如果业务上能接受t2的变化，不触发mv1的刷新，可以通过物化视图的属性`excluded_trigger_tables`来设置
+
+2. 物化视图占用资源过多，影响其他业务怎么办？
+
+ 可以通过物化视图的属性指定`workload_group`来控制物化视图刷新任务的资源
+
+ 使用时需要注意，如果内存设置的太小，单个分区刷新又需要的内存较多，任务会刷新失败。需要根据业务情况进行权衡
+
+3. 能基于物化视图创建新的物化视图么
+
+ 能。2.1.3开始支持。但是刷新数据的时候，每个物化视图都是单独的刷新逻辑
+
+ 例如mv2基于mv1创建，mv1基于t1创建
+
+ 那么mv2刷新的时候不会考虑mv1相对于t1数据是否同步
+
+4. 都支持哪些外表？
+
+ doris支持的所有外表都能用于创建物化视图，但是目前仅有hive支持分区刷新，后续会陆续支持
+
+5. 物化视图显示和hive数据一致，但是实际上不一致
+
+ 物化视图仅能保证物化视图的数据和通过 Catalog 查询的结果一致
+
+ Catalog 有一定的元数据，数据缓存。
+
+ 如果想物化视图和 Hive 数据一致，需要通过 Refresh Catalog 等方式，确保 Catalog 的数据和 Hive 的数据一致
+
+6. 物化视图支持 Schema Change 么？
+
+ 不支持修改，因为物化视图的列属性是根据物化视图定义 SQL 推导出来的。目前不支持显示的自定义修改
+
+7. 物化视图使用的基表允许 Schema Change 么？
+
+ 允许。但是变更之后，使用到该基表的物化视图的 State 会由 `NORMAL` 变为
`SCHEMA_CHANGE`，此时物化视图将不能被用来透明改写，但是不影响直查物化视图
+
+ 如果物化视图下次刷新任务成功，那么 State 会由 `SCHEMA_CHANGE` 变回 `NORMAL`
+
+8. 主键模型的表能用来创建物化视图么？
+
+ 物化视图对基表的数据模型没有要求。但是物化视图本身只能是明细模型。
+
+9. 物化视图上还能建索引么？
+
+ 能
+
+10. 物化视图刷新的时候会锁表么？
+
+ 很小的阶段会锁表，不会持续的占用表锁（几乎等同于导入数据的锁表时间）
+
+11. 物化视图适合近实时场景么？
+
+ 不太适合。物化视图刷新的最小单位是分区，如果数据量较大会占用较多的资源，并且不够实时。可以考虑同步物化视图或其他手段。
+
+## 使用场景
+### 查询加速
+对于 BI 报表场景以及其他加速场景，查询通常是多张表进行 Join
计算，之后进行聚合计算，用户对于查询的响应时间敏感，一般需要在秒级别返回多条查询结果，会消耗大量计算资源，并且有时很难保证预期的响应时间，此时多表物化视图可以解决此问题。
+
+物化视图对于加速重复有规律的查询很有效。物化视图视图支持直查，也支持透明改写，透明改写指的是使用一组物化视图，优化器根据改写算法和代价模型自动选择可用的最优物化视图来响应查询。
+
+使用物化视图的预计算结果来响应查询。极大地降低了表连接和聚合操作使用的资源，减少查询响应时间。
+
+### 数据湖加速
+#### 需求背景
+很多用户有基于 Apache Doris 进行联邦数据查询的需求，Apache Doris
的多源数据目录（Multi-Catalog）功能让这件事变的很方便，只要创建一个 Catalog，无须把数据迁移到 Apache Doris，就可以通过
Apache Doris 对外部数据进行查询
+#### 痛点
+但是这也会造成一些问题，因为查询外部数据的速度可能会收到网络及第三方服务的影响，又可能会很慢，对于响应速度要求比较高的场景，很难满足需求
+#### 如何实现外表的查询加速
+异步物化视图可以基于外部 Catalog 来创建，但是物化视图本身的数据是存在 Apache Doris
内部的，所以查询物化视图的速度会很快。因此，对于响应速度要求比较高的场景，我们可以基于外部 Catalog 创建一个物化视图
+
+### 数据建模
+有些场景，用户会使用事实表和维度表加工成一张汇总表，之后对此汇总表进行 Ad hoc 查询，此汇总表也可作为基础指标表用于后续的建模。
+
+此时可以利用物化视图对基表的数据进行建模。之后，还可以利用创建好的物化视图创建更高层级的物化视图（2.1.3支持），灵活满足不同的需求。
+
+不同层级的物化视图都可以自己设置刷新方式，例如：
+- 第一层的物化视图可以设置为定时刷新，第二层的设置为触发刷新，那么第一层的物化视图刷新完成后，会自动触发第二层物化视图的刷新。
+- 每层的物化视图都设置为定时刷新，那么第二层物化视图刷新的时候，不会考虑第一层的物化视图数据是否和基表同步，只会把第一层物化视图的数据加工后同步到第二层。
+
+## 物化视图和 Olap 的关系
+注意： 2.1.4 版本开始支持。
+
+物化视图底层是一个 Duplicate 模型的 Olap 表。
+
+理论上支持 Duplicate 模型的所有功能，但是物化视图为了能正常高效刷新数据，所以对功能做了一些限制：
+1. 物化视图的分区是基于基表自动创建维护的，所以不能对物化视图进行分区操作
+2. 由于物化视图背后有相关的 Job 要处理，所以不能用删除 Table 或重命名 Table 的命令操作物化视图，需要使用物化视图自身的命令
+3. 物化视图的 Column是根据查询语句推导出来的，所以不能修改，否则会导致物化视图的刷新任务失败
+4. 物化视图有一些 Duplicate Table 没有的 Property，这部分 Property 需要通过物化视图的命令进行修改，其他公用的
Property 需要用 Alter Table 进行修改
+5. 目前不能对异步物化视图创建 ROLLUP，但是能创建索引
+6. `desc`，`show partitions` 等命令同样适用于物化视图
## 物化视图的使用
@@ -247,6 +660,4 @@ SELECT * FROM t1;
## 注意事项
-- 异步物化视图仅支持在[Nereids 优化器](../nereids/nereids.md)使用
--
当前判断物化视图和基表是否同步仅支持`OlapTable`。对于其它外表，会直接认为是同步的。例如，物化视图的基表全是外表。在查询`mv_infos()`时，SyncWithBaseTables
会永远为 1（true）。在刷新物化视图时需要手动刷新指定的分区或指定`complete`刷新全部分区
-
+- 异步物化视图仅支持在[Nereids 优化器](../nereids/nereids)使用
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
index 694ba98371..7562d2c43b 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
@@ -1,7 +1,7 @@
---
{
- "title": "CREATE-ASYNC-MATERIALIZED-VIEW",
- "language": "zh-CN"
+"title": "CREATE-ASYNC-MATERIALIZED-VIEW",
+"language": "zh-CN"
}
---
@@ -40,9 +40,9 @@ CREATE ASYNC MATERIALIZED VIEW
CREATE MATERIALIZED VIEW (IF NOT EXISTS)? mvName=multipartIdentifier
(LEFT_PAREN cols=simpleColumnDefs RIGHT_PAREN)? buildMode?
(REFRESH refreshMethod? refreshTrigger?)?
- (KEY keys=identifierList)?
+ ((DUPLICATE)? KEY keys=identifierList)?
(COMMENT STRING_LITERAL)?
- (PARTITION BY LEFT_PAREN partitionKey = identifier RIGHT_PAREN)?
+ (PARTITION BY LEFT_PAREN mvPartition RIGHT_PAREN)?
(DISTRIBUTED BY (HASH hashKeys=identifierList | RANDOM) (BUCKETS
(INTEGER_VALUE | AUTO))?)?
propertyClause?
AS query
@@ -121,10 +121,33 @@ refreshMethod
MANUAL：手动刷新
- @@ -153,7 +153,7 @@ REFRESH ON SCHEDULE EVERY 2 HOUR STARTS "2023-12-13
21:07:09"
+SCHEDULE：定时刷新
+
+COMMIT：触发式刷新，基表数据变更时，自动生成刷新物化视图的任务
+
+```sql
+refreshTrigger
+: ON MANUAL
+| ON SCHEDULE refreshSchedule
+| ON COMMIT
+;
+
+refreshSchedule
+: EVERY INTEGER_VALUE mvRefreshUnit (STARTS STRING_LITERAL)?
+;
+
+mvRefreshUnit
+: MINUTE | HOUR | DAY | WEEK
+;
```
-##### key
+例如：每2小时执行一次，从2023-12-13 21:07:09开始
+```sql
+CREATE MATERIALIZED VIEW mv1
+REFRESH ON SCHEDULE EVERY 2 HOUR STARTS "2023-12-13 21:07:09"
+```
+
+##### Key
物化视图为 Duplicate Key 模型，因此指定的列为排序列
```sql
@@ -139,23 +162,42 @@ CREATE MATERIALIZED VIEW mv1
KEY(k1,k2)
```
-##### partition
+##### Partition
物化视图有两种分区方式，如果不指定分区，默认只有一个分区，如果指定分区字段，会自动推导出字段来自哪个基表并同步基表(当前支持 `OlapTable` 和
`hive`)的所有分区（限制条件：基表如果是 `OlapTable`，那么只能有一个分区字段）。
例如：基表是 Range 分区，分区字段为 `create_time` 并按天分区，创建物化视图时指定 `partition by(ct) as
select create_time as ct from t1`，那么物化视图也会是 Range 分区，分区字段为 `ct`，并且按天分区。
-分区字段的选择和物化视图的定义需要满足分区增量更新的条件，物化视图才可以创建成功，否则会报错 `Unable to find a suitable base
table for partitioning`。
+物化视图也可以通过分区上卷的方式减少物化视图的分区数量，目前分区上卷函数支持 `date_trunc`,上卷的单位支持 `year`, `month`,
`day`
+
+分区字段的选择和物化视图的定义需要满足分区增量更新的条件，物化视图才可以创建成功，否则会报错 `Unable to find a suitable base
table for partitioning`
+
+```sql
+mvPartition
+ : partitionKey = identifier
+ | partitionExpr = functionCallExpression
+ ;
+```
+
+例如基表按天分区，物化视图同样按天分区
+```sql
+partition by (`k2`)
+```
+
+例如基表按天分区，物化视图按月分区
+```sql
+partition by (date_trunc(`k2`,'month'))
+```
-#### property
-物化视图既可以指定 Table 的 property，也可以指定物化视图特有的 property。
+#### Property
+物化视图既可以指定 Table 的 Property，也可以指定物化视图特有的 Property。
-物化视图特有的 property 包括：
+物化视图特有的 Property 包括：
`grace_period`：查询改写时允许物化视图数据的最大延迟时间（单位：秒）。如果分区 A 和基表的数据不一致，物化视图的分区 A 上次刷新时间为
1，系统当前时间为 2，那么该分区不会被透明改写。但是如果 `grace_period` 大于等于1，该分区就会被用于透明改写。
`excluded_trigger_tables`：数据刷新时忽略的表名，逗号分割。例如`table1,table2`
-`refresh_partition_num`：单次 insert 语句刷新的分区数量，默认为
1。物化视图刷新时会先计算要刷新的分区列表，然后根据该配置拆分成多个 insert 语句顺序执行。遇到失败的 insert
语句，整个任务将停止执行。物化视图保证单个 insert 语句的事务性，失败的 insert 语句不会影响到已经刷新成功的分区。
+`refresh_partition_num`：单次 insert 语句刷新的分区数量，默认为
1。物化视图刷新时会先计算要刷新的分区列表，然后根据该配置拆分成多个 Insert 语句顺序执行。遇到失败的 Insert
语句，整个任务将停止执行。物化视图保证单个 Insert 语句的事务性，失败的 Insert 语句不会影响到已经刷新成功的分区。
`workload_group`：物化视图执行刷新任务时使用的 `workload_group`
名称。用来限制物化视图刷新数据使用的资源，避免影响到其它业务的运行。关于 `workload_group` 的创建及使用，可参考
[WORKLOAD-GROUP](../../../../admin-manual/workload-group.md) 文档。
@@ -171,7 +213,7 @@ SELECT random() as dd,k3 FROM user
### 示例
-1. 创建一个立即刷新，之后每周刷新一次的物化视图 mv1,数据源为 hive catalog
+1. 创建一个立即刷新，之后每周刷新一次的物化视图 mv1,数据源为 Hive Catalog
```sql
CREATE MATERIALIZED VIEW mv1 BUILD IMMEDIATE REFRESH COMPLETE ON SCHEDULE
EVERY 1 WEEK
diff --git
a/versioned_docs/version-2.1/query/view-materialized-view/async-materialized-view.md
b/versioned_docs/version-2.1/query/view-materialized-view/async-materialized-view.md
index fd42802c6f..fbbd971b02 100644
---
a/versioned_docs/version-2.1/query/view-materialized-view/async-materialized-view.md
+++
b/versioned_docs/version-2.1/query/view-materialized-view/async-materialized-view.md
@@ -1,7 +1,7 @@
---
{
- "title": "Asynchronous materialized view",
- "language": "en"
+"title": "Asynchronous materialized view",
+"language": "en"
}
---
@@ -35,9 +35,9 @@ Prepare two tables and data
use tpch;
CREATE TABLE IF NOT EXISTS orders (
- o_orderkey integer not null,
- o_custkey integer not null,
- o_orderstatus char(1) not null,
+ o_orderkey integer not null,
+ o_custkey integer not null,
+ o_orderstatus char(1) not null,
o_totalprice decimalv3(15,2) not null,
o_orderdate date not null,
o_orderpriority char(15) not null,
@@ -52,16 +52,16 @@ CREATE TABLE IF NOT EXISTS orders (
PROPERTIES ("replication_num" = "1");
insert into orders values
- (1, 1, 'ok', 99.5, '2023-10-17', 'a', 'b', 1, 'yy'),
- (2, 2, 'ok', 109.2, '2023-10-18', 'c','d',2, 'mm'),
- (3, 3, 'ok', 99.5, '2023-10-19', 'a', 'b', 1, 'yy');
+ (1, 1, 'ok', 99.5, '2023-10-17', 'a', 'b', 1, 'yy'),
+ (2, 2, 'ok', 109.2, '2023-10-18', 'c','d',2, 'mm'),
+ (3, 3, 'ok', 99.5, '2023-10-19', 'a', 'b', 1, 'yy');
CREATE TABLE IF NOT EXISTS lineitem (
- l_orderkey integer not null,
- l_partkey integer not null,
- l_suppkey integer not null,
- l_linenumber integer not null,
- l_quantity decimalv3(15,2) not null,
+ l_orderkey integer not null,
+ l_partkey integer not null,
+ l_suppkey integer not null,
+ l_linenumber integer not null,
+ l_quantity decimalv3(15,2) not null,
l_extendedprice decimalv3(15,2) not null,
l_discount decimalv3(15,2) not null,
l_tax decimalv3(15,2) not null,
@@ -76,14 +76,14 @@ CREATE TABLE IF NOT EXISTS lineitem (
)
DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber)
PARTITION BY RANGE(l_shipdate)
- (FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
+(FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3
PROPERTIES ("replication_num" = "1");
insert into lineitem values
- (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17',
'2023-10-17', 'a', 'b', 'yyyyyyyyy'),
- (2, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-18', '2023-10-18',
'2023-10-18', 'a', 'b', 'yyyyyyyyy'),
- (3, 2, 3, 6, 7.5, 8.5, 9.5, 10.5, 'k', 'o', '2023-10-19', '2023-10-19',
'2023-10-19', 'c', 'd', 'xxxxxxxxx');
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k',
'2023-10-17', '2023-10-17', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (2, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k',
'2023-10-18', '2023-10-18', '2023-10-18', 'a', 'b', 'yyyyyyyyy'),
+ (3, 2, 3, 6, 7.5, 8.5, 9.5, 10.5, 'k', 'o',
'2023-10-19', '2023-10-19', '2023-10-19', 'c', 'd', 'xxxxxxxxx');
```
Create materialized views
```sql
@@ -92,15 +92,15 @@ CREATE MATERIALIZED VIEW mv1
partition by(l_shipdate)
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES ('replication_num' = '1')
- AS
- select l_shipdate, o_orderdate, l_partkey, l_suppkey,
sum(o_totalprice) as sum_total
- from lineitem
- left join orders on lineitem.l_orderkey = orders.o_orderkey and
l_shipdate = o_orderdate
- group by
- l_shipdate,
- o_orderdate,
- l_partkey,
- l_suppkey;
+ AS
+select l_shipdate, o_orderdate, l_partkey, l_suppkey, sum(o_totalprice) as
sum_total
+from lineitem
+ left join orders on lineitem.l_orderkey = orders.o_orderkey and
l_shipdate = o_orderdate
+group by
+ l_shipdate,
+ o_orderdate,
+ l_partkey,
+ l_suppkey;
```
Specific syntax can be viewed [CREATE ASYNC MATERIALIZED
VIEW](../../sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md)
@@ -131,7 +131,7 @@ REFRESH MATERIALIZED VIEW mv1
partitions(p_20231017_20231018);
Specific syntax can be viewed [REFRESH MATERIALIZED
VIEW](../../sql-manual/sql-statements/Utility-Statements/REFRESH-MATERIALIZED-VIEW.md)
-### task management
+### Task management
Each materialized view defaults to a job responsible for refreshing data,
which is used to describe the refresh strategy and other information of the
materialized view. Each time a refresh is triggered, a task is generated,
Task is used to describe specific refresh information, such as the time used
for refreshing, which partitions were refreshed, etc
@@ -154,7 +154,7 @@ Can pause the scheduled scheduling of materialized views
Specific syntax can be viewed [PAUSE MATERIALIZED VIEW
JOB](../../sql-manual/sql-statements/Utility-Statements/PAUSE-MATERIALIZED-VIEW.md)
-#### RESUME materialized view job scheduling
+#### Resume materialized view job scheduling
```sql
RESUME MATERIALIZED VIEW JOB ON mv1;
@@ -164,7 +164,7 @@ Can RESUME scheduled scheduling of materialized views
Specific syntax can be viewed [RESUME MATERIALIZED VIEW
JOB](../../sql-manual/sql-statements/Utility-Statements/RESUME-MATERIALIZED-VIEW.md)
-#### Viewing tasks in materialized views
+#### View tasks in materialized views
```sql
select * from tasks("type"="mv");
@@ -182,7 +182,7 @@ Can cancel the operation of this task
Specific syntax can be viewed [CANCEL MATERIALIZED VIEW
TASK](../../sql-manual/sql-statements/Utility-Statements/CANCEL-MATERIALIZED-VIEW-TASK.md)
-### Modifying materialized views
+### Modify materialized views
Modify the properties of materialized views
```sql
@@ -203,6 +203,359 @@ The materialized view has a dedicated deletion syntax and
cannot be deleted thro
Specific syntax can be viewed [DROP ASYNC MATERIALIZED
VIEW](../../sql-manual/sql-statements/Data-Definition-Statements/Drop/DROP-ASYNC-MATERIALIZED-VIEW.md)
+## Partition description
+There are two ways to partition materialized views:
+
+1. Custom Partitioning
+
+2. Automatically Create Partitions Based on Dependent Base Table Partitions
+
+### Custom partitioning
+When creating a materialized view without specifying partition information,
the materialized view will default to creating a single partition where all
data will be stored.
+
+### Partitioning based on dependent base tables
+A materialized view can be created by joining multiple base tables.
+
+A materialized view can be partitioned to follow one of the base tables (it is
recommended to choose the fact table).
+
+For example
+
+The table creation statement for t1 is as follows:
+
+```sql
+CREATE TABLE `t1` (
+ `user_id` LARGEINT NOT NULL,
+ `o_date` DATE NOT NULL,
+ `num` SMALLINT NOT NULL
+) ENGINE=OLAP
+COMMENT 'OLAP'
+PARTITION BY RANGE(`o_date`)
+(
+PARTITION p20170101 VALUES [('2017-01-01'), ('2017-01-02')),
+PARTITION p20170102 VALUES [('2017-01-02'), ('2017-01-03')),
+PARTITION p20170201 VALUES [('2017-02-01'), ('2017-02-02'))
+)
+DISTRIBUTED BY HASH(`user_id`) BUCKETS 2
+PROPERTIES ('replication_num' = '1') ;
+```
+
+The table creation statement for t2 is as follows:
+
+```sql
+CREATE TABLE `t2` (
+ `user_id` LARGEINT NOT NULL,
+ `age` SMALLINT NOT NULL
+) ENGINE=OLAP
+PARTITION BY LIST(`age`)
+(
+ PARTITION `p1` VALUES IN ('1'),
+ PARTITION `p2` VALUES IN ('2')
+)
+DISTRIBUTED BY HASH(`user_id`) BUCKETS 2
+PROPERTIES ('replication_num' = '1') ;
+```
+
+If the materialized view creation statement is as follows, then the
materialized view mv1 will have the same three partitions as t1:
+- [('2017-01-01'), ('2017-01-02'))
+- [('2017-01-02'), ('2017-01-03'))
+- [('2017-02-01'), ('2017-02-02'))
+
+```sql
+CREATE MATERIALIZED VIEW mv1
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`order_date`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES (
+'replication_num' = '1'
+)
+AS
+SELECT t1.o_date as order_date, t1.user_id as user_id, t1.num, t2.age FROM t1
join t2 on t1.user_id=t2.user_id;
+```
+
+If the materialized view creation statement is as follows, then the
materialized view mv2 will have the same three partitions as t2:
+- ('1')
+- ('2')
+
+```sql
+CREATE MATERIALIZED VIEW mv2
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`age`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES (
+'replication_num' = '1'
+)
+AS
+SELECT t1.o_date as order_date, t1.user_id as user_id, t1.num, t2.age FROM t1
join t2 on t1.user_id=t2.user_id;
+```
+#### The base table has multiple partition columns
+Currently, only Hive external tables support multiple partition columns.
+
+Hive external tables often have multi-level partitions. For example, the
first-level partition is by date, and the second-level partition is by region.
+
+A materialized view can choose one of the partition columns from a Hive table
as the partition column for the materialized view.
+
+For example, the Hive table creation statement is as follows:
+```sql
+CREATE TABLE hive1 (
+`k1` int)
+PARTITIONED BY (
+`year` int,
+`region` string)
+STORED AS ORC;
+
+alter table hive1 add if not exists
+partition(year=2020,region="bj")
+partition(year=2020,region="sh")
+partition(year=2021,region="bj")
+partition(year=2021,region="sh")
+partition(year=2022,region="bj")
+partition(year=2022,region="sh")
+```
+If the materialized view creation statement is as follows, then the
materialized view mv_hive will have the following three partitions:
+- ('2020')
+- ('2021')
+- ('2022')
+```sql
+CREATE MATERIALIZED VIEW mv_hive
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`year`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT k1,year,region FROM hive1;
+```
+
+If the materialized view creation statement is as follows, then the
materialized view mv_hive2 will have the following two partitions:
+- ('bj')
+- ('sh')
+```sql
+CREATE MATERIALIZED VIEW mv_hive2
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`region`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT k1,year,region FROM hive1;
+```
+
+#### Only using a subset of the base table partitions.
+Note: Supported from version 2.1.1
+
+If some base tables have many partitions, but the materialized view only
focuses on the recent "hot" data, this feature can be used.
+
+If the base table creation statement is as follows:
+```sql
+CREATE TABLE t1 (
+ `k1` INT,
+ `k2` DATE NOT NULL
+) ENGINE=OLAP
+DUPLICATE KEY(`k1`)
+COMMENT 'OLAP'
+PARTITION BY range(`k2`)
+(
+PARTITION p26 VALUES [("2024-03-26"),("2024-03-27")),
+PARTITION p27 VALUES [("2024-03-27"),("2024-03-28")),
+PARTITION p28 VALUES [("2024-03-28"),("2024-03-29"))
+)
+DISTRIBUTED BY HASH(`k1`) BUCKETS 2
+PROPERTIES (
+'replication_num' = '1'
+);
+```
+If the creation statement of the materialized view is as follows, it means
that the materialized view only focuses on the data of the most recent day. If
the current time is 2024-03-28 xx: xx: xx, then the materialized view will only
have one partition [("2024-03-28"), ("2024-03-29")]
+```sql
+CREATE MATERIALIZED VIEW mv1
+BUILD DEFERRED REFRESH AUTO ON MANUAL
+partition by(`k2`)
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES (
+'replication_num' = '1',
+'partition_sync_limit'='1',
+'partition_sync_time_unit'='DAY'
+)
+AS
+SELECT * FROM t1;
+```
+If another day has passed and the current time is 2024-03-29 xx: xx: xx, t1
adds a partition [("2024-03-29"), ("2024-03-30")]. If the materialized view is
refreshed at this time, after the refresh is completed, the materialized view
will only have one partition [("2024-03-29"), ("2024-03-30")]
+
+If the partition field is of string type, you can set the materialized view
property 'partition_date_format', for example, '%Y-%m-%d'.
+
+## Data refreshing
+### Refresh principle
+The materialized view is refreshed on a per-partition basis. If the
materialized view does not specify partitions, then each refresh will refresh
the default partition of the materialized view, effectively refreshing all the
data in the materialized view.
+### Trigger mechanism
+There are three trigger refresh mechanisms for materialized views:
+#### Manual trigger
+Users trigger the refresh of the materialized view through SQL statements.
Currently, there are three strategies:
+1. Not concerned with which partitions are refreshed, but requires that after
the refresh is complete, the data in the materialized view is synchronized with
the base table.
+```sql
+REFRESH MATERIALIZED VIEW mvName AUTO;
+```
+2. Regardless of the existing data in the materialized view, refresh all
partitions of the materialized view.
+```sql
+REFRESH MATERIALIZED VIEW mvName COMPLETE;
+```
+3. Regardless of the existing data in the materialized view, only refresh the
specified partitions.
+```sql
+REFRESH MATERIALIZED VIEW mvName partitions(partitionName1,partitionName2);
+```
+`partitionName` can be obtained through `show partitions from mvName`.
+#### Scheduled trigger
+Specify how often to refresh the data through the creation statement of the
materialized view.
+1. If the materialized view creation statement is as follows, and it requires
a full refresh (refresh complete), then the materialized view is refreshed
every 10 hours, and all partitions of the materialized view are refreshed.
+```sql
+CREATE MATERIALIZED VIEW mv1
+REFRESH COMPLETE ON SCHEDULE EVERY 10 hour
+partition by(`xxx`)
+AS
+select ...;
+```
+2. If the materialized view creation statement is as follows, and it requires
an automatic full refresh (refresh auto), then the materialized view is
refreshed every 10 hours, and the partitions to be refreshed are automatically
calculated (starting from version 2.1.3, automatic calculation of partitions to
be refreshed is supported for Hive)
+```sql
+CREATE MATERIALIZED VIEW mv1
+REFRESH AUTO ON SCHEDULE EVERY 10 hour
+partition by(`xxx`)
+AS
+select ...;
+```
+#### Automatic trigger
+Note: Supported from version 2.1.4
+
+After changes occur in the base table data, automatically trigger the refresh
of related materialized views, with the same partition range as specified for
`scheduled trigger`.
+
+If the materialized view creation statement is as follows, then when there are
changes in the data of t1, the materialized view will be automatically
refreshed:
+```sql
+CREATE MATERIALIZED VIEW mv1
+REFRESH ON COMMIT
+partition by(`xxx`)
+AS
+select ... from t1;
+```
+
+## Problem localization
+### Localization means
+The commonly used commands for `olapTable` are also applicable to materialized
views, such as `show partitions`, `desc table`, `show data`, etc.
+
+The unique commands for materialized views mainly include the following:
+#### View materialized view metadata
+[mv_infos()](../../sql-manual/sql-functions/table-functions/mv_infos)
+
+Focus on the following fields:
+- State: If the state changes to SCHEMA_CHANGE, it means the schema of the
base table has changed. In this case, the materialized view cannot be used for
transparent rewriting (but direct querying of the materialized view is not
affected). If the next refresh task is successful, the state will be restored
to NORMAL.
+- SchemaChangeDetail: The reason for the SCHEMA_CHANGE.
+- RefreshState: The status of the last refresh task of the materialized view.
If it is FAIL, it means the execution failed, and further localization can be
done through tasks().
+- SyncWithBaseTables: Whether the materialized view is synchronized with the
base table data. If not synchronized, further determination can be made by
using show partitions to identify which partition is not synchronized.
+#### View tasks for the materialized view
+[tasks("type"="mv")](../../sql-manual/sql-functions/table-functions/tasks.md)
+
+Focus on the following fields:
+- Status: If it is FAILED, it means the task execution failed. You can check
the reason for failure through ErrorMsg. You can also search Doris logs using
LastQueryId to get more detailed error information.
+- ErrorMsg: The reason for the failure.
+- DurationMs: The duration of the task execution.
+- TaskContext: The context of the task, where you can see the trigger
information for the task.
+- RefreshMode: `complete` indicates that all partitions were refreshed,
`PARTIAL` indicates that some partitions were refreshed, and `NOT_REFRESH`
indicates that no partitions needed to be refreshed.
+
+### Common issues
+1. How does the materialized view determine which partitions need to be
refreshed?
+
+ Doris internally calculates the correspondence between the partitions of
the materialized view and the base table, and records the version of the base
table partitions used by the materialized view since the last successful
refresh. For example, if the materialized view `mv1` is created from base
tables `t1` and `t2`, and it is partitioned based on `t1`, the mapping between
the partitions of `t1` and `mv1` is maintained.
+
+ Assuming partition `p202003` of `mv1` corresponds to partitions `p20200301`
and `p20200302` of `t1`, after refreshing `p202003`, Doris will record
partitions `p20200301` and `p20200302`, as well as the current version of table
`t2`.
+
+ When refreshing next time, Doris will check if `p20200301`, `p20200302`, or
the version of `t2` has changed. If any of them has changed, it means that
`p202003` needs to be refreshed.
+
+ Of course, if it is acceptable for the business that `t2` changes do not
trigger a refresh of `mv1`, you can set this through the materialized view's
property `excluded_trigger_tables`.
+
+2. What to do if the materialized view consumes too many resources and affects
other business operations?
+
+ You can control the resources used by materialized view refresh tasks by
specifying the `workload_group` property of the materialized view.
+
+ It's important to note that if the memory is set too low and a single
partition refresh requires a significant amount of memory, the task may fail.
It's necessary to balance these considerations based on the business
requirements.
+
+3. Can new materialized views be created based on existing materialized views?
+
+ Yes, it's possible. Support for this feature started from version 2.1.3.
However, when refreshing data, each materialized view has its own separate
refresh logic.
+
+ For example, `mv2` is created based on `mv1`, and `mv1` is based on `t1`.
+
+ Then when `mv2` is refreshed, it will not consider whether `mv1` is
synchronized with `t1`.
+
+4. What external tables are supported?
+
+ All external tables supported by Doris can be used to create materialized
views. However, currently only Hive supports partitioned refresh, with support
for other types expected to be added gradually.
+
+5. The materialized view appears to be consistent with the data in Hive, but
in reality, it is not consistent.
+
+ The materialized view can only guarantee that its data is consistent with
the results queried through the catalog.
+
+ The catalog has certain metadata and data caching.
+
+ If you want the materialized view to be consistent with the data in Hive,
you need to ensure that the catalog's data is consistent with Hive's data by
using methods like `refresh catalog`.
+
+6. Does the materialized view support schema changes?
+
+ It does not support modification because the column properties of the
materialized view are derived from the SQL definition of the materialized view.
Explicit customization is not currently supported.
+
+7. Can the base table used by a materialized view allow schema changes?
+
+ Yes, it is allowed. However, after the change, the `State` of the
materialized views that use the base table will change from `NORMAL` to
`SCHEMA_CHANGE`. In this state, the materialized view cannot be used for
transparent rewriting, but direct queries to the materialized view are not
affected.
+
+ If the next refresh task of the materialized view is successful, the
`State` will change back from `SCHEMA_CHANGE` to `NORMAL`.
+
+8. Can tables with a primary key model be used to create materialized views?
+
+ Materialized views do not have specific requirements regarding the data
model of the base table. However, the materialized view itself can only have a
detailed model.
+
+9. Can indexes be created on materialized views?
+
+ Yes, you can create indexes on materialized views.
+
+10. Does the table get locked when refreshing a materialized view?
+
+ At a very small stage, the table will be locked and will not continuously
occupy the table lock (almost equivalent to the lock time of importing data)
+
+11. Is materialized views suitable for near real-time scenarios?
+
+ Not very suitable. The minimum unit for refreshing materialized views is
the partition. If the data volume is large, it will occupy more resources and
not be real-time enough. Consider synchronizing materialized views or other
means.
+
+## Usage scenarios
+### Query acceleration
+For BI report scenarios and other acceleration scenarios, queries are usually
performed by joining multiple tables and then aggregating them. Users are
sensitive to the response time of queries and generally need to return multiple
query results at the second level, which consumes a lot of computing resources
and sometimes makes it difficult to ensure the expected response time. In this
case, multi table materialized views can solve this problem.
+
+Materialized views are effective in accelerating repetitive and regular
queries. The materialized view supports both direct querying and transparent
rewriting. Transparent rewriting refers to the use of a set of materialized
views, and the optimizer automatically selects the optimal materialized view
available to respond to queries based on the rewriting algorithm and cost model.
+
+Use the pre computed results of materialized views to respond to queries.
Greatly reduces the resources used for table connections and aggregation
operations, and reduces query response time.
+
+### Data Lake Acceleration
+#### Background of demand
+Many users have a need for federated data queries based on Doris. Doris's
Multi Catalog feature makes this task very convenient. As long as a catalog is
created, there is no need to migrate data to Doris, and external data can be
queried through Doris
+#### Pain points
+But this can also cause some problems, as the speed of querying external data
may be affected by the network and third-party services, and may be slow. For
scenarios with high response speed requirements, it is difficult to meet the
requirements
+#### How to achieve appearance query acceleration
+Asynchronous materialized views can be created based on external catalogs, but
the data of the materialized view itself is stored within Doris, so querying
the materialized view will be fast. Therefore, for scenarios with high response
speed requirements, we can create a materialized view based on an external
catalog
+
+### Data modeling
+In some scenarios, users may use fact tables and dimension tables to create a
summary table, which can then be used for Ad hoc queries. This summary table
can also serve as a basic indicator table for subsequent modeling.
+
+At this point, the materialized view can be used to model the data of the base
table. Afterwards, the created materialized views can be used to create
higher-level materialized views (supported by 2.1.3), flexibly meeting
different needs.
+
+Different levels of materialized views can have their own refresh methods set,
for example:
+- The first layer's materialized view can be set to timed refresh, while the
second layer is set to trigger refresh. After the first layer's materialized
view refresh is completed, it will automatically trigger the refresh of the
second layer's materialized view.
+- The materialized views of each layer are set to be refreshed on a scheduled
basis. Therefore, when the materialized views of the second layer are
refreshed, it will not consider whether the materialized view data of the first
layer is synchronized with the base table. Instead, the materialized view data
of the first layer will be processed and synchronized to the second layer.
+
+## The relationship between materialized views and olap
+Note: Starting support in 2.1.4
+
+The underlying layer of the materialized view is an OLAP table of a duplicate
model.
+
+In theory, all functionalities of the Duplicate model are supported, but in
order to efficiently refresh data in a materialized view, some limitations are
placed on the functionalities:
+1. The partitioning of materialized views is automatically created and
maintained based on the base table, so partitioning operations cannot be
performed on materialized views
+2. Due to the related job processing behind the materialized view, the command
to delete or rename the table cannot be used to manipulate the materialized
view. Instead, the command of the materialized view itself needs to be used
+3. The column of the materialized view is derived from the query statement, so
it cannot be modified, otherwise it will cause the refresh task of the
materialized view to fail
+4. The materialized view has some properties that are not available in the
duplicate table, which need to be modified through the commands in the
materialized view. Other common properties need to be modified using the alter
table
+5. Currently, it is not possible to create a Rollup for asynchronous
materialized views, but indexes can be created
+6. The commands such as `desc` and `show partitions` are also applicable to
materialized views
+
## The use of materialized views
can be viewed [Query async materialized
view](./query-async-materialized-view.md)
diff --git
a/versioned_docs/version-2.1/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
b/versioned_docs/version-2.1/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
index fd93816e59..57088d7f8d 100644
---
a/versioned_docs/version-2.1/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
+++
b/versioned_docs/version-2.1/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
@@ -1,7 +1,7 @@
---
{
- "title": "CREATE-ASYNC-MATERIALIZED-VIEW",
- "language": "en"
+"title": "CREATE-ASYNC-MATERIALIZED-VIEW",
+"language": "en"
}
---
@@ -40,9 +40,9 @@ This statement is used to create an asynchronous materialized
view.
CREATE MATERIALIZED VIEW (IF NOT EXISTS)? mvName=multipartIdentifier
(LEFT_PAREN cols=simpleColumnDefs RIGHT_PAREN)? buildMode?
(REFRESH refreshMethod? refreshTrigger?)?
- (KEY keys=identifierList)?
+ ((DUPLICATE)? KEY keys=identifierList)?
(COMMENT STRING_LITERAL)?
- (PARTITION BY LEFT_PAREN partitionKey = identifier RIGHT_PAREN)?
+ (PARTITION BY LEFT_PAREN mvPartition RIGHT_PAREN)?
(DISTRIBUTED BY (HASH hashKeys=identifierList | RANDOM) (BUCKETS
(INTEGER_VALUE | AUTO))?)?
propertyClause?
AS query
@@ -131,10 +131,13 @@ MANUAL：Manual refresh
SCHEDULE：Timed refresh
+COMMIT: Trigger-based refresh. When the base table data changes, a task to
refresh the materialized view is automatically generated.
+
```sql
refreshTrigger
: ON MANUAL
| ON SCHEDULE refreshSchedule
+| ON COMMIT
;
refreshSchedule
@@ -174,11 +177,30 @@ KEY(k1,k2)
##### partition
There are two types of partitioning methods for materialized views. If no
partitioning is specified, there will be a default single partition. If a
partitioning field is specified, the system will automatically deduce the
source base table of that field and synchronize all partitions of the base
table (currently supporting `OlapTable` and `hive`). (Limitation: If the base
table is an `OlapTable`, it can only have one partition field)
-For example, if the base table is a range partition with a partition field of
`create_time` and partitioning by day, and `partition by(ct) as select
create_time as ct from t1` is specified when creating a materialized view,
+For example, if the base table is a range partition with a partition field of
`create_time` and partitioning by day, and `partition by(ct) as select
create_time as ct from t1` is specified when creating a materialized view,
then the materialized view will also be a range partition with a partition
field of 'ct' and partitioning by day
+Materialized views can also reduce the number of partitions by using partition
roll-up. Currently, the partition roll-up function supports `date_trunc`, and
the roll-up units supported are `year`, `month`, and `day`.
+
The selection of partition fields and the definition of materialized views
must meet the conditions for partition incremental updates for the materialized
view to be created successfully; otherwise, an error "Unable to find a suitable
base table for partitioning" will occur.
+```sql
+mvPartition
+ : partitionKey = identifier
+ | partitionExpr = functionCallExpression
+ ;
+```
+
+For example, if the base table is partitioned by day, the materialized view is
also partitioned by day.
+```sql
+partition by (`k2`)
+```
+
+For example, if the base table is partitioned by day, the materialized view is
partitioned by month.
+```sql
+partition by (date_trunc(`k2`,'month'))
+```
+
#### property
The materialized view can specify both the properties of the table and the
properties unique to the materialized view.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
