您现在的位置是：首页 >学无止境 >hive物化视图网站首页学无止境

-- 物化视图 需要事务支持的
-- 物化视图 需要事务表上创建

-- 解决'create materialized view'报错(表前提必须是orc): Automatic rewriting for materialized view cannot be enabled if the materialized view uses non-transactional tables
ALTER table tb1111 SET TBLPROPERTIES ('transactional'='true');
ALTER TABLE tb1111 SET FILEFORMAT orc;
-- 开启环境 事务支持 否则select报错
set hive.support.concurrency=true;
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

set hive.execution.engine=mr; -- 根据你实际情况

-- 规避报错: java.io.IOException: [Error 30022]: Must use HiveInputFormat to read ACID tables (set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat)
-- 对应的datagrip直接报错是: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

-- 新建物化视图
create materialized view view_mate_1
stored as parquet
as
select * from tb1111;

select * from view_mate_1;
-- +------+---------------------------+
-- |name |info |
-- +------+---------------------------+
-- |thomas|[{"k1":"v1"},{"k2":"v2"}] |
-- |peter |[{"k4":"v4"},{"k66":"v66"}]|
-- +------+---------------------------+

insert into tb1111 values ('name_new','info_new');

select * from tb1111;
-- +--------+---------------------------+
-- |name |info |
-- +--------+---------------------------+
-- |name_new|info_new |
-- |thomas |[{"k1":"v1"},{"k2":"v2"}] |
-- |peter |[{"k4":"v4"},{"k66":"v66"}]|
-- +--------+---------------------------+


select * from view_mate_1; -- 可以看出 物化视图不会自动更新
-- +------+---------------------------+
-- |name |info |
-- +------+---------------------------+
-- |thomas|[{"k1":"v1"},{"k2":"v2"}] |
-- |peter |[{"k4":"v4"},{"k66":"v66"}]|
-- +------+---------------------------+

-- 物化视图的生命周期 超过1s 自动重新生成物化视图结果 https://techcommunity.microsoft.com/t5/analytics-on-azure-blog/hive-materialized-views/ba-p/2502785
-- 我测试没生效
-- SET hive.materializedview.rewriting.time.window=1min; -- 默认0min
-- ALTER table view_mate_1 SET TBLPROPERTIES ('hive.materializedview.rewriting.time.window'='1min');

-- 手动刷新物化视图
ALTER MATERIALIZED VIEW view_mate_1 REBUILD;
-- 可以看到 结果同步并正确了
select * from view_mate_1;
-- +--------+---------------------------+
-- |name |info |
-- +--------+---------------------------+
-- |name_new|info_new |
-- |thomas |[{"k1":"v1"},{"k2":"v2"}] |
-- |peter |[{"k4":"v4"},{"k66":"v66"}]|
-- +--------+---------------------------+

--------------