物化视图SQL示例

创建物化视图

（创建物化视图时，只创建了物化视图的定义，数据填充需要使用refresh materialized view name刷新物化视图数据）

创建永不过期的物化视图

create materialized view mv.default.mv1 with(storage_table='hive.default.mv11') AS select id from hive.mvschema.t1;

• storage_table：物化视图数据物化成物理表的位置
• 创建物化视图时的catalog必须指定mv，schema可以自行创建
• AS SELECT子句需注意创建物化视图的“AS SELECT”的子句列出的事项

创建有效期为1天不启动自动刷新的物化视图

create materialized view mv.default.mv1 with(storage_table='hive.default.mv11', mv_validity = '24h') AS select id from hive.mvschema.t1;

mv_validity：物化视图的有效期

创建每小时自动刷新一次数据的物化视图

create materialized view mv.default.mv1 with(storage_table='hive.default.mv1', need_auto_refresh = true, mv_validity = '1h', start_refresh_ahead_of_expiry = 0.2, refresh_priority = 3, refresh_duration = '5m') AS select id from hive.mvschema.t1;

• need_auto_refresh：是否启用自动刷新
• start_refresh_ahead_of_expiry：刷新任务在“mv_validity*（1-start_refresh_ahead_of_expiry）”的时间触发一次更新状态为“可刷新”
• refresh_priority：刷新任务优先级
• refresh_duration：刷新任务的最大允许时间

列举物化视图

列举catalog名为“mv”，schema名为“mvschema”的所有物化视图

show materialized views from mvschema;

mvschema是schema的名称，catalog固定为“mv”

根据子句“LIKE”筛选视图名满足规则运算表达式的物化视图

show MATERIALIZED VIEWs in mvschema tables like '*mvtb_0001';

mvschema是schema的名称

查询物化视图的创建语句

查询mv.default.mv1的物化视图创建语句

show create materialized view mv.default.mv1;

mv1是物化视图的名称

查询物化视图

查询mv.default.mv1的数据

select * from mv.default.mv1;

mv1是物化视图的名称

刷新物化视图

刷新mv.default.mv1的物化视图

refresh materialized view mv.default.mv1;

-

修改物化视图属性

修改mv.default.mv1的物化视图的属性

Alter materialized view mv.mvtestprop.pepa_ss set PROPERTIES refresh_priority = 2;

refresh_priority = 2是物化视图的属性

修改物化视图状态

修改mv.default.mv1的物化视图的状态

alter materialized view mv.default.mv1 set status SUSPEND;

SUSPEND是物化视图的状态，状态还包括：

• SUSPEND：暂停使用状态，暂停使用的物化视图不会参与改写
• ENABLE：可使用状态
• REFRESHING：正在刷新物化视图数据，不可用于改写
• DISABLE：关闭使用

仅支持ENABLE和SUSPEND相互转换，以及将DISABLE状态修改为SUSPEND或ENABLE

删除物化视图

删除mv.default.mv1的物化视图

drop materialized view mv.default.mv1;

-

启用使用物化视图改写SQL进行优化

在session级别启用使用物化视图改写SQL进行优化

set session materialized_view_rewrite_enabled=true;

-

验证查询是否能通过改写成物化视图进行SQL优化

验证查询SQL语句能否被mv.default.mv1改写优化

verify materialized view mvname(mv.default.mv1) originalsql select id from hive.mvschema.t1;

-

SQL级别使用指定的物化视图进行SQL改写优化

在查询中强制使用mv.default.mv1进行优化

/*+ REWRITE(mv.default.mv1) */ select id from hive.mvschema.t1;

-

SQL级别禁用物化视图进行SQL改写优化

在查询中禁止物化视图进行优化

/*+ NOREWRITE */ select id from hive.mvschema.t1;

-

刷新物化视图元数据信息缓存

同步不同租户间物化视图元数据信息缓存

refresh catalog mv;

-