配置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，支持sum(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同步物化视图

登录安装了MySQL的节点，执行以下命令，连接Doris数据库。

若集群已启用Kerberos认证（安全模式），需先执行以下命令再连接Doris数据库：

export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1

mysql -u数据库登录用户 -p -PFE查询连接端口 -hDoris FE实例IP地址

执行命令后输入数据库登录用户密码。
- Doris FE的查询连接端口，可以通过登录Manager，单击“集群 > 服务 > Doris > 配置”，查询Doris服务的“query_port”参数获取。
- Doris DBalancer的TCP访问端口，可以通过登录Manager，单击“集群 > 服务 > Doris > 配置”，查询Doris服务的“balancer_tcp_port”参数获取。
- Doris FE或DBalancer实例IP地址可通过登录MRS集群的Manager界面，单击“集群 > 服务 > Doris > 实例”，查看任一FE或DBalancer实例的业务IP地址。
- 用户也可以使用MySQL连接软件或者在Doris WebUI界面连接数据库。
执行以下命令创建数据库，例如名称为“test_db”：

create database test_db;

use test_db;
执行以下命令创建表，例如名称为“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");
执行以下命令向表中插入数据：

insert into sales_records values(1,1,1,'2020-02-02',1);
执行以下命令创建物化视图：

create materialized view store_amt as select store_id,sum(sale_amt) from sales_records group by store_id;
执行以下命令查询物化视图：

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)
...
```
执行以下命令检查物化视图：

show alter table materialized view from test_db;
执行以下命令查看物化视图结构：

desc sales_records all;
执行以下命令查看物化视图创建语句：

show create materialized view store_amt on sales_records;
执行以下命令删除物化视图：

drop materialized view store_amt on sales_records;

创建异步物化视图

登录安装了MySQL的节点，执行以下命令，连接Doris数据库。

若集群已启用Kerberos认证（安全模式），需先执行以下命令再连接Doris数据库：

export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1

mysql -u数据库登录用户 -p -PFE查询连接端口 -hDoris FE实例IP地址

执行命令后输入数据库登录用户密码。
- Doris FE的查询连接端口，可以通过登录Manager，单击“集群 > 服务 > Doris > 配置”，查询Doris服务的“query_port”参数获取。
- Doris DBalancer的TCP访问端口，可以通过登录Manager，单击“集群 > 服务 > Doris > 配置”，查询Doris服务的“balancer_tcp_port”参数获取。
- Doris FE或DBalancer实例IP地址可通过登录MRS集群的Manager界面，单击“集群 > 服务 > Doris > 实例”，查看任一FE或DBalancer实例的业务IP地址。
- 用户也可以使用MySQL连接软件或者在Doris WebUI界面连接数据库。
执行以下命令创建异步物化视图：

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>;
- 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：自动触发。
创建基表并导入数据。
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;
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, 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');
2. 创建基表，例如“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;
3. 向表中插入数据：
  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');
创建物化视图。
- 示例一
   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
  
  FROM
  
  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;
查询物化视图（直查物化视图）。

查询基表：

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  |
+--------------+-----------+-------------+
```
执行以下命令修改物化视图：

ALTER MATERIALIZED VIEW mv_1

SET(

"grace_period" = "10"

);
执行以下命令查看物化视图创建语句：

SHOW CREATE MATERIALIZED VIEW mv_1;
执行以下命令删除物化视图：

DROP MATERIALIZED VIEW mv_1;
执行以下命令暂停物化视图刷新任务：

PAUSE MATERIALIZED VIEW JOB ON <mv_name>
执行以下命令启用物化视图：

RESUME MATERIALIZED VIEW JOB ON <mv_name>
执行以下命令取消物化视图刷新任务：

CANCEL MATERIALIZED VIEW TASK <task_id> ON <mv_name>
Doris支持透明改写，详细操作请参见查询透明改写。

透明改写指在处理查询时，用户无需手动修改查询，系统会自动优化并改写查询。 Doris提供了丰富且全面的透明改写能力。
- 条件补偿：查询和物化视图的条件不必完全相同，通过在物化视图上补偿条件来表达查询，可以最大限度地复用物化视图，不用重复构建物化视图。
- JOIN改写：查询和物化使用的表相同，可以在物化视图和查询的JOIN输入或者JOIN的外层写where，优化器对此模式的查询会尝试进行透明改写。
- JOIN衍生：当查询和物化视图的JOIN类型不一致时，如果物化视图能够提供查询所需的所有数据，则通过在JOIN的外部补偿谓词，也可以进行透明改写。
- 聚合改写：当查询和物化视图定义中的group维度一致时，如果物化视图使用的group by维度和查询的group by维度相同，并且查询使用的聚合函数可以使用物化视图的聚合函数来表示，则可以进行透明改写。
- 分区补偿：当分区物化视图不足以提供查询的所有数据时，可以使用union all的方式，将查询原表和物化视图的数据进行union all操作作为最终返回结果。