Hive物化视图介绍

Hive物化视图是基于Hive内部表的查询结果得到的特殊表，物化视图可以看做一张中间表，存储实际的数据，占用物理空间。物化视图赖以建立的这些表称为物化视图的基表。

物化视图主要用于预先计算并保存表连接或聚合等耗时较多的操作的结果。在执行查询时，可以将原本基于基表查询的查询语句重写成基于物化视图查询，这样就可以避免进行join、group by等耗时的操作，从而快速的得到结果。

创建物化视图

语法

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
  [COMMENT materialized_view_comment]
  DISABLE REWRITE
    [ROW FORMAT row_format]
    [STORED AS file_format]
      | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
AS
<query>;

案例

1. 登录Hive客户端，执行命令开启以下参数，具体操作请参考使用Hive客户端。

   set hive.support.concurrency=true;

   set hive.exec.dynamic.partition.mode=nonstrict;

   set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

2. 创建基表，插入数据。

   create table tb_emp(
   empno int,ename string,job string,mgr int,hiredate TIMESTAMP,sal float,comm float,deptno int
   )stored as orc 
   tblproperties('transactional'='true');
   
   insert into tb_emp values(7369, 'SMITH', 'CLERK',7902, '1980-12-17 08:30:09',800.00,NULL,20),
   (7499, 'ALLEN', 'SALESMAN',7698, '1981-02-20 17:12:00',1600.00,300.00,30),
   (7521, 'WARD', 'SALESMAN',7698, '1981-02-22 09:05:34',1250.00,500.00,30),
   (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 10:14:13',2975.00,NULL,20),
   (7654, 'MARTIN', 'SALESMAN',7698, '1981-09-28 08:36:17',1250.00,1400.00,30),
   (7698, 'BLAKE', 'MANAGER',7839, '1981-05-01 11:12:55',2850.00,NULL,30),
   (7782, 'CLARK', 'MANAGER',7839, '1981-06-09 15:45:28',2450.00,NULL,10),
   (7788, 'SCOTT', 'ANALYST',7566, '1987-04-19 14:05:34',3000.00,NULL,20),
   (7839, 'KING', 'PRESIDENT',NULL, '1981-11-17 10:18:25',5000.00,NULL,10),
   (7844, 'TURNER', 'SALESMAN',7698, '1981-09-08 09:05:34',1500.00,0.00,30),
   (7876, 'ADAMS', 'CLERK',7788, '1987-05-23 15:07:44',1100.00,NULL,20),
   (7900, 'JAMES', 'CLERK',7698, '1981-12-03 16:23:56',950.00,NULL,30),
   (7902, 'FORD', 'ANALYST',7566, '1981-12-03 08:48:17',3000.00,NULL,20),
   (7934, 'MILLER', 'CLERK',7782, '1982-01-23 11:45:29',1300.00,NULL,10);

3. 基于tb_emp的查询，创建物化视图。

   create materialized view group_mv disable rewrite
   row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe' 
   stored as textfile 
   tblproperties('mv_content'='Total compensation of each department')
   as select deptno,sum(sal) sum_sal from tb_emp group by deptno;

应用物化视图

将原本基于基表查询的查询语句重写成基于物化视图查询，从而达到提升查询效率的效果

案例

现有查询语句如下：

select deptno,sum(sal) from tb_emp group by deptno having sum(sal)>10000;

基于所创建的物化视图，可将查询语句改写成：

select deptno, sum_sal from group_mv where sum_sal>10000;

查看物化视图

语法

SHOW MATERIALIZED VIEWS [IN database_name] ['identifier_with_wildcards’];

DESCRIBE [EXTENDED | FORMATTED] [db_name.]materialized_view_name;

案例

show materialized views;

describe formatted group_mv;

删除物化视图

语法

DROP MATERIALIZED VIEW [db_name.]materialized_view_name;

案例

drop materialized view group_mv;

重建物化视图

创建物化视图的时候，基表数据会填充到物化视图中，但是后续增删改基表的数据，这部分数据是不会自动的同步到物化视图中的。因此，我们在更新数据后，需要手动对视图进行重建。

语法

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name REBUILD;

案例

alter materialized view group_mv rebuild;