【解决方案】物化视图常用命令 & 常见问题解答，建议收藏

Viewed 331
mtmv content faq

基础文档与命令

异步物化视图 - 构建基础命令

https://doris.apache.org/zh-CN/docs/query/view-materialized-view/async-materialized-view

异步物化视图 - 数据查询

https://doris.apache.org/zh-CN/docs/query/view-materialized-view/query-async-materialized-view/

异步物化视图相关开关

开关 说明
SET enable_nereids_planner = true; 异步物化视图只有在新优化器下才支持，所以需要开启新优化器
SET enable_materialized_view_rewrite = true; 开启或者关闭查询透明改写，默认关闭
SET materialized_view_rewrite_enable_contain_external_table = true; 参与透明改写的物化视图是否允许包含外表，默认不允许
SET materialized_view_rewrite_success_candidate_num = 3; 透明改写成功的结果集合，允许参与到CBO候选的最大数量，默认是3个

构建常用命令

// 刷新物化视图
REFRESH MATERIALIZED VIEW mv_name AUTO;

// 查看物化视图元数据信息
// database 为当前数据库
// mv_name 为物化视图名称
select * from mv_infos('database'='db_name') where Name = 'mv_name' \G 

// 查看任务元数据
select * from jobs("type"="mv") order by CreateTime limit 5;

// 查看任务执行信息 
// 这里面会展示任务执行的状态，如果失败会有失败原因
select * from tasks("type"="mv") where JobName = 'job_name';

FAQ

1. 物化视图没有命中是为什么？

a. 物化视图透明改写功能默认是关闭的，需要打开对应开关才可以改写，开关值见 异步物化视图相关开关(上方表格)

b. 可能物化视图不可用，导致透明改写不能命中，查看物化视图构建状态见下方问题2

c. 经过前两步的检查，如果物化视图还是不能命中，可能物化视图的定义 SQL 和查询 SQL 不在当前物化视图改写能力的范围内，详见物化视图透明改写能力

2. 怎么查看物化状态是否正常？

2.1 确认物化视图构建状态

  1. 首先运行 select * from mv_infos('database'='db_name') where Name = 'mv_name' \G 查看物化视图的 JobName。
  2. 其次根据 JobName 查看物化视图的任务状态，运行如下语句
    select * from tasks("type"="mv") where JobName = 'job_name';
  3. 查看最近执行的任务状态 Status 是否是 Success

2.2 确认物化视图数据一致的可用性

物化视图构建成功，但是因为数据变更，和 grace_period 的设置导致物化视图不可用。

查看物化视图数据一致性的方法

  1. 全量构建的物化视图
    运行select * from mv_infos('database'='db_name') where Name = 'mv_name' \G
    查看字段 SyncWithBaseTables 是否是1

  2. 分区构建的物化视图
    运行 show partitions from mv_name查看查询使用的分区是否有效

3. 构建物化时报错

报错信息如下：

ERROR 1105 (HY000): errCode = 2, detailMessage = Syntax error in line 1:
BUILD IMMEDIATE REFRESH AUTO ON MANUAL

a. 异步物化视图的语句，在新优化器下才支持，确保使用的是新优化器
SET enable_nereids_planner = true;

b. 可能是构建物化的语句使用的关键词写错或者物化定义SQL的语法有问题，可以检查下物化定义SQL和创建物化语句是否正确。

4. 构建分区物化视图报 Unable to find a suitable base table for partitioning

报这个错，通常指的是物化视图的 SQL 定义和物化视图分区字段的选择，导致不能分区增量更新，所以创建分区物化视图会报这个错。

物化视图想要分区增量更新，需要满足以下要求，详情见 物化视图刷新模式

举例如下：

CREATE TABLE `test1` (
`pre_batch_no` VARCHAR(100) NULL COMMENT 'pre_batch_no',
`batch_no` VARCHAR(100) NULL COMMENT 'batch_no',
`vin` VARCHAR(50) NULL COMMENT 'vin',
`car_type` VARCHAR(50) NULL COMMENT 'car_type',
`uuid` VARCHAR(100) NULL COMMENT 'uuid',
`upgrade_day` date NOT NULL COMMENT 'upgrade_day',
`command_time` VARCHAR(50) COMMENT 'command_time',
`update_time` datetime COMMENT 'update_time',
`calculate_day` date COMMENT 'calculate_day'
) ENGINE=OLAP
Duplicate KEY(`pre_batch_no`,`batch_no`,`vin`,`car_type`,`uuid`,`upgrade_day`)
COMMENT 'OLAP'
PARTITION BY RANGE(`upgrade_day`)
(
    FROM ("2024-03-20") TO ("2024-03-31") INTERVAL 1 DAY
)
DISTRIBUTED BY HASH(`uuid`) BUCKETS 10
PROPERTIES (
 "replication_num" = "1"
);


CREATE TABLE `test2` (
`pre_batch_no` VARCHAR(50) NULL COMMENT 'pre_batch_no',
`batch_no` VARCHAR(50) NULL COMMENT 'batch_no',
  `vin` VARCHAR(50) NULL COMMENT 'vin',
  `uuid` VARCHAR(50) NULL COMMENT 'uuid',
  `upgrade_day` date NOT NULL COMMENT 'upgrade_day',
  `result` VARCHAR(50) NULL COMMENT 'result',
  `normal_origin_ver` VARCHAR(50) NULL COMMENT 'normalOriginVer',
  `normal_version` VARCHAR(50) NULL COMMENT 'normalVersion',
  `reason` VARCHAR(1000) NULL,
  `upgrade_time` DATETIME NULL COMMENT 'upgradeTime',
  `process_time`  VARCHAR(50) NULL COMMENT 'processTime',
  `ecu` JSON NULL COMMENT 'ecu'
) ENGINE=OLAP
DUPLICATE KEY(`pre_batch_no`,`batch_no`,`vin`,`uuid`,`upgrade_day`)
COMMENT 'OLAP'
PARTITION BY RANGE(`upgrade_day`)
(
    FROM ("2024-03-20") TO ("2024-03-31") INTERVAL 1 DAY
)
DISTRIBUTED BY HASH(`uuid`) BUCKETS 10
PROPERTIES (
 "replication_num" = "1"
);

物化视图定义如下，是可以进行分区增量更新的，但是如果选择 t2.upgrade_day 作为物化视图的分区字段，就不能分区增量更新了。

CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
partition by(ud)
DISTRIBUTED BY HASH(uuid) BUCKETS 10
AS 
SELECT t1.upgrade_day as ud ,t1.pre_batch_no ,t1.batch_no,t1.vin,t1.uuid as uuid,t2.`result` ,t2.normal_origin_ver ,t2.normal_version ,t2.reason ,t2.upgrade_time ,t2.process_time ,t2.ecu, t2.upgrade_day 
from
test1 t1
left join 
test2 t2
on t1.uuid = t2.uuid;

5. 直查物化物化视图没有数据

可能物化在构建中，也有可能物化构建失败了。
通过如下语句查看物化构建的状态：

// 查看物化视图元数据信息
// database 为当前数据库
// mv_name 为物化视图名称
select * from mv_infos('database'='db_name') where Name = 'mv_name' \G 

// 查看任务元数据
select * from jobs("type"="mv") order by CreateTime limit 5;

// 查看任务执行信息 
// 这里面会展示任务执行的状态，如果失败会有失败原因
select * from tasks("type"="mv") where JobName = 'job_name';

6. 持续更新中

椰子Echo@SelectDB738
1 Answers

若以上内容未能解决您的问题，请参考【提问模板】进行提问，背景越详细越有助于问题快速解决～。

椰子Echo@SelectDB738