# 配置Doris物化视图
Doris的物化视图既包含计算逻辑也包含数据实体，不同于视图仅包含计算逻辑，本身不存储数据。
Doris物化视图的使用场景主要有查询加速、轻量化ETL（数据建模）、湖仓一体。按照数据时效性分为以下两类：
- 同步物化视图：将预先计算（根据定义好的**SELECT** 语句进行计算）的数据集存储在Doris中。Doris会自动维护同步物化视图的数据，无论是新的导入或删除操作，都能保证基表和物化视图的数据同步更新、保持一致。查询时，Doris会自动匹配到最优的物化视图，并从物化视图中读取数据。
  同步物化视图的定义SQL只能使用单表，数据实时刷新，与基表的数据保持强一致性，且不支持直接查询。
  
- 异步物化视图：在创建物化视图时会同时注册一个刷新任务，刷新任务会在需要时运行，执行**INSERT OVERWRITE** 语句以将最新的数据写入物化视图中。
  异步物化视图的定义SQL可以使用单表或多表，支持全量刷新、分区增量刷新，与基表的数据保持最终一致性，可能会有一定延迟，应用于对数据时效性要求不高的场景，支持直接查询。
  
该章节仅适用于MRS 3.6.0-LTS及之后版本。
#### 适用场景介绍
- 同步物化视图适用于以下场景
  - 加速耗时的聚合运算
  
  - 查询需要匹配不同的前缀索引
  
  - 通过预先过滤减少需要扫描的数据量
  
  - 通过预先完成复杂的表达式计算来加速查询
   
- 异步物化视图适用于以下场景
  - 查询加速与并发提升 物化视图能够显著加快查询速度，同时增强系统的并发处理能力，有效减少资源消耗。
    
  
  - 简化ETL流程 在数据抽取、转换和加载（ETL）过程中，物化视图能够简化流程，提升开发效率，使数据处理更加顺畅。
    
  
  - 加速湖仓一体架构中的外表查询 在湖仓一体架构中，物化视图能够显著加快对外部数据源的查询速度，提高数据访问效率。
    
  
  - 提升写入效率 通过减少资源竞争，物化视图能够优化数据写入过程，提高写入效率，确保数据的一致性和完整性。
    
   
 
#### 约束限制
- 同步物化视图
  - 同步物化视图只支持针对单个表执行SELECT语句，支持WHERE、GROUP BY、ORDER BY等子句，但不支持 JOIN、HAVING、LIMIT子句和LATERAL VIEW。
  
  - 不能直接查询同步物化视图。
  
  - 创建同步物化视图的SELECT列表中，不能包含自增列、不能包含常量、不能有重复表达式，也不支持窗口函数。
  
  - 如果SELECT列表包含聚合函数，则聚合函数必须是根表达式（即不支持**sum(a) + 1** ，支持**s** **um(a + 1)** ），且聚合函数之后不能有其他非聚合函数表达式，例如，支持**SELECT x, sum(a)** ，不支持**SELECT sum(a), x**。
  
  - 如果删除语句的条件列在物化视图中存在，则不能进行删除操作。如果需要删除数据，则需要先将物化视图删除，再删除数据。
  
  - 单表上过多的物化视图会影响数据导入的效率。导入数据时，物化视图和Base表的数据是同步更新的。如果一张表的物化视图表过多，可能会导致导入速度变慢，类似于单次导入需要同时导入多张表的数据。
  
  - 物化视图针对Unique Key数据模型时，只能改变列的顺序，不支持聚合操作。因此，在Unique Key模型上不能通过创建物化视图的方式对数据进行粗粒度的聚合操作。
   
- 异步物化视图
  - 异步物化视图与基表的数据最终会保持一致，但无法实时同步，即无法保持数据实时一致性。
  
  - 如果查询中包含了窗口函数，暂不支持将该查询透明地改写为物化视图加速查询。
  
  - 如果创建物化视图涉及表的数量多于查询所涉及的表（例如查询仅涉及t1和t2，而物化视图包含了t1、t2、t3），则系统不支持将该查询透明地改写为物化视图加速查询。
  
  - 如果物化视图包含**UNION ALL**等集合操作，LIMIT、ORDER BY、CROSS JOIN，物化视图可以正常构建，但是不能用于透明改写。
   
 
#### 创建Doris同步物化视图
1. 登录安装了MySQL的节点，执行以下命令，连接Doris数据库。 
   若集群已启用Kerberos认证（安全模式），需先执行以下命令再连接Doris数据库：
   **export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1**
   **mysql -u** *数据库登录用户* **-p** **-P** *FE查询连接端口* **-h** *Doris FE实例IP地址*
   执行命令后输入数据库登录用户密码。
   ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/note_3.0-zh-cn.png)
   - Doris FE的查询连接端口，可以通过登录Manager，选择"集群 \> 服务 \> Doris \> 配置"，查询Doris服务的"query_port"参数获取。
   
   - Doris FE实例IP地址可通过登录MRS集群的Manager界面，选择"集群 \> 服务 \> Doris \> 实例"，查看任一FE实例的业务IP地址。
   
   - 用户也可以使用MySQL连接软件或者在Doris WebUI界面连接数据库。
    
   
   
2. 执行以下命令创建数据库，例如名称为"test_db"： 
   **create database** *test_db* **;**
   **use** *test_db* **;**
   
   
3. 执行以下命令创建表，例如名称为"sales_records"： 
   **create table** *sales_records*
   **(**
   **record_id int,**
   **seller_id int,**
   **store_id int,**
   **sale_date date,**
   **sale_amt bigint**
   **)**
   **distributed by** **hash(record_id)**
   **properties** **("replication_num" = "2");**
   
   
4. 执行以下命令向表中插入数据： 
   **insert into** *sales_records* **values** **(1,1,1,'2020-02-02',1);**
   
   
5. 执行以下命令创建物化视图： 
   **create materialized** *view store_amt* **as** **select** **store_id,sum(sale_amt)** **from** **sales_records** **group by** **store_id;**
   
   
6. 执行以下命令查询物化视图： 
   **select** **store_id,sum(sale_amt)** **from** *sales_records* **group by** **store_id;**
   ```
   +----------+---------------+
   | store_id | sum(sale_amt) |
   +----------+---------------+
   |        1 |             1 |
   +----------+---------------+
   ```
   在查询SQL中添加**explain**可查看执行计划中的VOlapScanNode，用于判断查询是否命中物化视图，但如果表中没有数据，则可能不会命中物化视图。
   **explain select** **store_id,sum(sale_amt)** **from** *sales_records* **group by** **store_id;**
   ```
   ...
   0:VOlapScanNode(148)                                               
   TABLE: test_db.sales_records(store_amt), PREAGGREGATION: ON     
   partitions=1/1 (sales_records)
   ...
   ```
   
   
7. 执行以下命令检查物化视图： 
   **show alter table materialized view from** *test_db*;
   
   
8. 执行以下命令查看物化视图结构： 
   **desc** *sales_records* **all** **;**
   
   
9. 执行以下命令查看物化视图创建语句： 
   **show** **create materialized view** **store_amt** **on** *sales_records* **;**
   
   
10. 执行以下命令删除物化视图： 
    **d** **rop materialized view** *store_amt* **on** *sales_records* **;**
    
    
 
#### 创建异步物化视图
1. 登录安装了MySQL的节点，执行以下命令，连接Doris数据库。 
   若集群已启用Kerberos认证（安全模式），需先执行以下命令再连接Doris数据库：
   **export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1**
   **mysql -u** *数据库登录用户* **-p** **-P** *FE查询连接端口* **-h** *Doris FE实例IP地址*
   执行命令后输入数据库登录用户密码。
   ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/note_3.0-zh-cn.png)
   - Doris FE的查询连接端口，可以通过登录Manager，选择"集群 \> 服务 \> Doris \> 配置"，查询Doris服务的"query_port"参数获取。
   
   - Doris FE实例IP地址可通过登录MRS集群的Manager界面，选择"集群 \> 服务 \> Doris \> 实例"，查看任一FE实例的业务IP地址。
   
   - 用户也可以使用MySQL连接软件或者在Doris WebUI界面连接数据库。
    
   
   
2. 执行以下命令创建异步物化视图： 
   **CREATE MATERIALIZED VIEW**
   **\[ IF NOT EXISTS \]** *\<materialized_view_name\>*
   **\[** *(\<columns_definition\>)* **\]**
   **\[ BUILD** *\<build_mode\>* **\]**
   **\[ REFRESH** *\<refresh_method\>* **\[refresh_trigger\]\]**
   **\[ \[DUPLICATE\] KEY** *(\<key_cols\>)* **\]**
   **\[ COMMENT '** *\<table_comment\>* **' \]**
   **\[ PARTITION BY (**
   **{** *\<partition_col\>* 
   **\| DATE_TRUNC(** *\<partition_col\>, \<partition_unit\>* **) }**
   **)\]**
   **\[ DISTRIBUTED BY { HASH (** *\<distribute_cols\>* **) \| RANDOM }**
   **\[ BUCKETS {** *\<bucket_count\>* **\| AUTO } \]**
   **\]**
   **\[ PROPERTIES (**
   **-- Table property**
   *\<table_property\>*
   **-- Additional table properties**
   **\[ , ... \])**
   **\]**
   **AS** *\<query\>* **;**
   ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/note_3.0-zh-cn.png)
   - build_mode：表示刷新时机，即物化视图创建完成后是否立即刷新。
     - IMMEDIATE：立即刷新，默认值。
     
     - DEFERRED：延迟刷新。
      
   
   - refresh_method：刷新方式。刷新物化视图时，目前Doris只能感知内表和Hive数据源表数据的变化。
     - COMPLETE：刷新所有分区。
     
     - AUTO：尽量增量刷新，只刷新自上次物化刷新后数据变化的分区，如果不能感知数据变化的分区，会自动转换为全量刷新，刷新所有分区。
      
   
   - refresh_trigger：触发方式。
     - ON MANUAL：手动触发。例如： **REFRESH MATERIALIZED** *VIEW* **mvName \[** **refresh_method** **\];**
       **REFRESH MATERIALIZED** *VIEW* **mvName partitions(partitionName1,partitionName2);**
       
     
     - ON SCHEDULE：定时触发，用于指定间隔多久刷新一次数据。
     
     - ON COMMIT：自动触发。
      
    
   
   
3. 创建基表并导入数据。 
   1. 创建基表。例如"lineitem"： **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_extendedprice decimalv3(15,2) not null,**
      **l_discount decimalv3(15,2) not null,**
      **l_tax decimalv3(15,2) not null,**
      **l_returnflag char(1) not null,**
      **l_linestatus char(1) not null,**
      **l_shipdate date not null,**
      **l_commitdate date not null,**
      **l_receiptdate date not null,**
      **l_shipinstruct char(25) not null,**
      **l_shipmode char(10) not null,**
      **l_comment varchar(44) not null**
      **)**
      **DUPLICATE KEY** **(l_orderkey, l_partkey, l_suppkey, l_linenumber)**
      **PARTITION BY RANGE** **(** **l_shipdate** **)**
      **(** **FROM** **('2023-10-17')** **TO** **('2023-11-01')** **INTERVAL** **1 DAY)**
      **DISTRIBUTED BY HASH(** **l_orderkey** **) BUCKETS** **3;**
      
   
   
   
   2. 向表中插入数据： **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, 4, 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, 4, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-19', '2023-10-19', '2023-10-19', 'a', 'b', 'yyyyyyyyy');**
      
   
   3. 创建基表，例如"orders"： **CREATE TABLE IF NOT EXISTS** *orders* **(**
      **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,**
      **o_clerk char(15) not null,**
      **o_shippriority integer not null,**
      **o_comment varchar(79) not null**
      **)**
      **DUPLICATE KEY** **(o_orderkey, o_custkey)**
      **PARTITION BY RANGE** **(o_orderdate)(**
      **FROM** **('2023-10-17')** **TO** **('2023-11-01')** **INTERVAL** **1 DAY)**
      **DISTRIBUTED BY HASH** **(o_orderkey)** **BUCKETS** **3;**
      
   
   4. 向表中插入数据： **INSERT** **INTO** *orders* **VALUES**
      **(1, 1, 'o', 9.5, '2023-10-17', 'a', 'b', 1, 'yy'),**
      **(1, 1, 'o', 10.5, '2023-10-18', 'a', 'b', 1, 'yy'),**
      **(2, 1, 'o', 11.5, '2023-10-19', 'a', 'b', 1, 'yy'),**
      **(3, 1, 'o', 12.5, '2023-10-19', 'a', 'b', 1, 'yy');**
      
   
   
   
   
4. 创建物化视图。 
   - 示例一 **CREATE MATERIALIZED VIEW** *mv_1_0*
     ***BUILD*** **IMMEDIATE**
     **REFRESH** **AUTO**
     **ON** **MANUAL**
     **DISTRIBUTED BY** **RANDOM** **BUCKETS** **2**
     **AS**
     **SELECT**
     **l_linestatus,**
     **to_date(o_orderdate) as date_alias,**
     **o_shippriority**
     **FROM** 
     **orders**
     **LEFT JOIN** **lineitem** **ON** **l_orderkey = o_orderkey;**
     
   
   - 示例二 **CREATE MATERIALIZED VIEW** *mv_1_1*
     **BUILD** **DEFERRED**
     **REFRESH** **COMPLETE**
     **ON** **SCHEDULE EVERY 1 DAY STARTS '2025-12-01 20:30:00'**
     **DISTRIBUTED BY** **RANDOM**
     **AS**
     **SELECT**
     **l_linestatus,**
     **to_date(o_orderdate) as date_alias,**
     **o_shippriority**
     **FRO** **M**
     **orders**
     **LEFT JOIN** **lineitem** **ON** **l_orderkey = o_orderkey;**
     
   
   - 示例三 **CREATE MATERIALIZED VIEW** *mv_1_2*
     **BUILD** **IMMEDIATE**
     **REFRESH** **AUTO**
     **ON** **SCHEDULE EVERY 1 hour**
     **DISTRIBUTED BY** **RANDOM** **BUCKETS** **3**
     **AS**
     **SELECT** **t1.l_linenumber,**
     **o_custkey,**
     **o_orderdate**
     **FROM** **(** **SELECT** **\*** **FROM** **lineitem** **WHERE** **l_linenumber \> 1) t1**
     **LEFT** **OUTER** **JOIN** **orders**
     **ON** **l_orderkey = o_orderkey;**
     
   
   
   
   
5. 查询物化视图（直查物化视图）。 
   查询基表：
   **SELECT** *t1.l_linenumber*,
   **o_custkey,**
   **o_orderdate**
   **FROM** **(** **SELECT** **\*** **FROM** **lineitem** **WHERE** **l_linenumber \> 1) t1**
   **LEFT OUTER JOIN** **orders**
   **ON** **l_orderkey = o_orderkey**
   **WHERE** **o_orderdate = '2023-10-18';**
   SQL执行后，Doris会通过透明改写等价直查物化视图：
   **SELECT** **l_linenumber,o_custkey,o_orderdate** **FROM** *mv_1_2 WHERE* **l_linenumber \> 1** **and** **o_orderdate = '2023-10-18';**
   ```
   +--------------+-----------+-------------+
   | l_linenumber | o_custkey | o_orderdate |
   +--------------+-----------+-------------+
   |            4 |         1 | 2023-10-18  |
   +--------------+-----------+-------------+
   ```
   
   
6. 执行以下命令修改物化视图： 
   **ALTER MATERIALIZED VIEW** *mv_1*
   **SET** **(**
   **"grace_period" = "10"**
   **);**
   
   
7. 执行以下命令查看物化视图创建语句： 
   **SHOW CREATE MATERIALIZED VIEW** *mv_1* **;**
   
   
8. 执行以下命令删除物化视图： 
   **DROP MATERIALIZED VIEW** *mv_1* **;**
   
   
9. 执行以下命令暂停物化视图刷新任务： 
   **PAUSE MATERIALIZED VIEW** **JOB** **ON** *\<mv_name\>*
   
   
10. 执行以下命令启用物化视图： 
    **RESUME MATERIALIZED VIEW** **JOB** **ON** *\<mv_name\>*
    
    
11. 执行以下命令取消物化视图刷新任务： 
    **CANCEL MATERIALIZED VIEW TASK** \<task_id\> **ON** *\<mv_name\>*
    
    
12. Doris支持透明改写，详细操作请参见[查询透明改写](https://doris.apache.org/zh-CN/docs/query-acceleration/materialized-view/async-materialized-view/functions-and-demands/#查询透明改写)。
    
    透明改写指在处理查询时，用户无需手动修改查询，系统会自动优化并改写查询。 Doris提供了丰富且全面的透明改写能力。
    - 条件补偿：查询和物化视图的条件不必完全相同，通过在物化视图上补偿条件来表达查询，可以最大限度地复用物化视图，不用重复构建物化视图。
    
    - JOIN改写：查询和物化使用的表相同，可以在物化视图和查询的JOIN输入或者JOIN的外层写where，优化器对此模式的查询会尝试进行透明改写。
    
    - JOIN衍生：当查询和物化视图的JOIN类型不一致时，如果物化视图能够提供查询所需的所有数据，则通过在JOIN的外部补偿谓词，也可以进行透明改写。
    
    - 聚合改写：当查询和物化视图定义中的group维度一致时，如果物化视图使用的group by维度和查询的group by维度相同，并且查询使用的聚合函数可以使用物化视图的聚合函数来表示，则可以进行透明改写。
    
    - 分区补偿：当分区物化视图不足以提供查询的所有数据时，可以使用union all的方式，将查询原表和物化视图的数据进行union all操作作为最终返回结果。