大数据Hive DDL高阶建表

1 Hive内、外部表

1.1 什么是内部表

内部表（Internal table）也称为被Hive拥有和管理的托管表（Managed table）。默认情况下创建的表就是内部表，Hive拥有该表的结构和文件。换句话说，Hive完全管理表（元数据和数据）的生命周期，类似于RDBMS中的表。
当您删除内部表时，它会删除数据以及表的元数据。

create table student(
    num int,
    name string,
    sex string,
    age int,
    dept string) 
row format delimited 
fields terminated by ',';

可以使用DESCRIBE FORMATTED itcast.student;来获取表的描述信息，从中可以看出表的类型。
在这里插入图片描述

1.2 什么是外部表

外部表（External table）中的数据不是Hive拥有或管理的，只管理表元数据的生命周期。要创建一个外部表，需要使用EXTERNAL语法关键字。
删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据。
而且外部表更为方便的是可以搭配location语法指定数据的路径。

create external table student_ext(
    num int,
    name string,
    sex string,
    age int,
    dept string)
row format delimited
fields terminated by ','
location '/stu';

可以使用DESC FORMATTED itcast. student_ext;来获取表的描述信息，从中可以看出表的类型。在这里插入图片描述

1.3 内部表、外部表差异

无论内部表还是外部表，Hive都在Hive Metastore中管理表定义及其分区信息。删除内部表会从Metastore中删除表元数据，还会从HDFS中删除其所有数据/文件。
删除外部表，只会从Metastore中删除表的元数据，并保持HDFS位置中的实际数据不变。
在这里插入图片描述

1.4 如何选择内部表、外部表

当需要通过Hive完全管理控制表的整个生命周期时，请使用内部表。
当文件已经存在或位于远程位置时，请使用外部表，因为即使删除表，文件也会被保留。

2 Hive分区表

2.1 分区表的引入、产生背景

现有6份数据文件，分别记录了《王者荣耀》中6种位置的英雄相关信息。现要求通过建立一张表t_all_hero，把6份文件同时映射加载。

create table t_all_hero(
    id int,
    name string,
    hp_max int,
    mp_max int,
    attack_max int,
    defense_max int,
    attack_range string,
    role_main string,
    role_assist string
)
row format delimited
fields terminated by "\t";

加载数据文件到HDFS指定路径下：在这里插入图片描述
现要求查询role_main主要定位是射手并且hp_max最大生命大于6000的有几个，sql语句如下：

select count(*) from t_all_hero where role_main="archer" and hp_max >6000;

思考一下：where语句的背后需要进行全表扫描才能过滤出结果，对于hive来说需要扫描表下面的每一个文件。如果数据文件特别多的话，效率很慢也没必要。本需求中，只需要扫描archer.txt文件即可，如何优化可以加快查询，减少全表扫描呢？

2.2 分区表的概念、创建

当Hive表对应的数据量大、文件多时，为了避免查询时全表扫描数据，Hive支持根据用户指定的字段进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段。比如把一整年的数据根据月份划分12个月（12个分区），后续就可以查询指定月份分区的数据，尽可能避免了全表扫描查询。
在这里插入图片描述
分区表建表语法：

CREATE TABLE table_name (column1 data_type, column2 data_type) PARTITIONED BY (partition1 data_type, partition2 data_type,….);

针对《王者荣耀》英雄数据，重新创建一张分区表t_all_hero_part，以role角色作为分区字段

create table t_all_hero_part(
       id int,
       name string,
       hp_max int,
       mp_max int,
       attack_max int,
       defense_max int,
       attack_range string,
       role_main string,
       role_assist string
) partitioned by (role string)
row format delimited
fields terminated by "\t";

需要注意：分区字段不能是表中已经存在的字段，因为分区字段最终也会以虚拟字段的形式显示在表结构上。在这里插入图片描述

2.3 分区表数据加载–静态分区

所谓静态分区指的是分区的字段值是由用户在加载数据的时候手动指定的。
语法如下：

load data [local] inpath ' ' into table tablename partition(分区字段='分区值'...);

Local表示数据是位于本地文件系统还是HDFS文件系统。关于load语句后续详细展开讲解。
静态加载数据操作如下，文件都位于Hive服务器所在机器本地文件系统上。
在这里插入图片描述

load data local inpath '/root/hivedata/archer.txt' into table t_all_hero_part partition(role='sheshou');
load data local inpath '/root/hivedata/assassin.txt' into table t_all_hero_part partition(role='cike');
load data local inpath '/root/hivedata/mage.txt' into table t_all_hero_part partition(role='fashi');
load data local inpath '/root/hivedata/support.txt' into table t_all_hero_part partition(role='fuzhu');
load data local inpath '/root/hivedata/tank.txt' into table t_all_hero_part partition(role='tanke');
load data local inpath '/root/hivedata/warrior.txt' into table t_all_hero_part partition(role='zhanshi');

2.4 分区表数据加载–动态分区

往hive分区表中插入加载数据时，如果需要创建的分区很多，则需要复制粘贴修改很多sql去执行，效率低。因为hive是批处理系统，所以hive提供了一个动态分区功能，其可以基于查询参数的位置去推断分区的名称，从而建立分区。
所谓动态分区指的是分区的字段值是基于查询结果自动推断出来的。核心语法就是insert+select。
启用hive动态分区，需要在hive会话中设置两个参数：

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

第一个参数表示开启动态分区功能，第二个参数指定动态分区的模式。分为nonstick非严格模式和strict严格模式。strict严格模式要求至少有一个分区为静态分区。
创建一张新的分区表t_all_hero_part_dynamic

create table t_all_hero_part_dynamic(
         id int,
         name string,
         hp_max int,
         mp_max int,
         attack_max int,
         defense_max int,
         attack_range string,
         role_main string,
         role_assist string
) partitioned by (role string)
row format delimited
fields terminated by "\t";

执行动态分区插入

insert into table t_all_hero_part_dynamic partition(role) select tmp.*,tmp.role_main from t_all_hero tmp;

动态分区插入时，分区值是根据查询返回字段位置自动推断的。

2.5 分区表的本质

外表上看起来分区表好像没多大变化，只不过多了一个分区字段。实际上在底层管理数据的方式发生了改变。这里直接去HDFS查看区别。
非分区表：t_all_hero
在这里插入图片描述
分区表：t_all_hero_part

分区的概念提供了一种将Hive表数据分离为多个文件/目录的方法。不同分区对应着不同的文件夹，同一分区的数据存储在同一个文件夹下。只需要根据分区值找到对应的文件夹，扫描本分区下的文件即可，避免全表数据扫描。

2.6 分区表的使用

分区表的使用重点在于：
一、建表时根据业务场景设置合适的分区字段。比如日期、地域、类别等；
二、查询的时候尽量先使用where进行分区过滤，查询指定分区的数据，避免全表扫描。
比如：查询英雄主要定位是射手并且最大生命大于6000的个数。使用分区表查询和使用非分区表进行查询，SQL如下：
–非分区表全表扫描过滤查询

select count(*) from t_all_hero where role_main="archer" and hp_max >6000;

–分区表先基于分区过滤再查询

select count(*) from t_all_hero_part where role="sheshou" and hp_max >6000;

想一想：底层执行性能来说，分区表的优势在哪里？

2.7 分区表的注意事项

一、分区表不是建表的必要语法规则，是一种优化手段表，可选；
二、分区字段不能是表中已有的字段，不能重复；
三、分区字段是虚拟字段，其数据并不存储在底层的文件中；
四、分区字段值的确定来自于用户价值数据手动指定（静态分区）或者根据查询结果位置自动推断（动态分区）
五、 Hive支持多重分区，也就是说在分区的基础上继续分区，划分更加细粒度

2.8 多重分区表

通过建表语句中关于分区的相关语法可以发现，Hive支持多个分区字段：PARTITIONED BY (partition1 data_type, partition2 data_type,….)。
多重分区下，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区。从HDFS的角度来看就是文件夹下继续划分子文件夹。比如：把全国人口数据首先根据省进行分区，然后根据市进行划分，如果你需要甚至可以继续根据区县再划分，此时就是3分区表。
–单分区表，按省份分区

create table t_user_province (id int, name string,age int) partitioned by (province string);

–双分区表，按省份和市分区

create table t_user_province_city (id int, name string,age int) partitioned by (province string, city string);

–三分区表，按省份、市、县分区

create table t_user_province_city_county (id int, name string,age int) partitioned by (province string, city string,county string);

多分区表的数据插入和查询使用

load data local inpath '文件路径' into table t_user_province partition(province='shanghai');

load data local inpath '文件路径' into table t_user_province_city_county partition(province='zhejiang',city='hangzhou',county='xiaoshan');

select * from t_user_province_city_county where province='zhejiang' and city='hangzhou';

3 Hive分桶表

3.1 分桶表的概念

分桶表也叫做桶表，源自建表语法中bucket单词。是一种用于优化查询而设计的表类型。该功能可以让数据分解为若干个部分易于管理。
在分桶时，我们要指定根据哪个字段将数据分为几桶（几个部分）。默认规则是：Bucket number = hash_function(bucketing_column) mod num_buckets。
可以发现桶编号相同的数据会被分到同一个桶当中。hash_function取决于分桶字段bucketing_column的类型：
如果是int类型，hash_function(int) == int;
如果是其他类型，比如bigint,string或者复杂数据类型，hash_function比较棘手，将是从该类型派生的某个数字，比如hashcode值。

3.2 分桶表的语法

–分桶表建表语句

CREATE [EXTERNAL] TABLE [db_name.]table_name
[(col_name data_type, ...)]
CLUSTERED BY (col_name)
INTO N BUCKETS;

其中CLUSTERED BY (col_name)表示根据哪个字段进行分；
INTO N BUCKETS表示分为几桶（也就是几个部分）。
需要注意的是，分桶的字段必须是表中已经存在的字段。

3.3 分桶表的创建

现有美国2021-1-28号，各个县county的新冠疫情累计案例信息，包括确诊病例和死亡病例，数据格式如下所示：

2021-01-28,Juneau City and Borough,Alaska,02110,1108,3
2021-01-28,Kenai Peninsula Borough,Alaska,02122,3866,18
2021-01-28,Ketchikan Gateway Borough,Alaska,02130,272,1
2021-01-28,Kodiak Island Borough,Alaska,02150,1021,5
2021-01-28,Kusilvak Census Area,Alaska,02158,1099,3
2021-01-28,Lake and Peninsula Borough,Alaska,02164,5,0
2021-01-28,Matanuska-Susitna Borough,Alaska,02170,7406,27
2021-01-28,Nome Census Area,Alaska,02180,307,0
2021-01-28,North Slope Borough,Alaska,02185,973,3
2021-01-28,Northwest Arctic Borough,Alaska,02188,567,1
2021-01-28,Petersburg Borough,Alaska,02195,43,0

字段含义如下：count_date（统计日期）,county（县）,state（州）,fips（县编码code）,cases（累计确诊病例）,deaths（累计死亡病例）。
根据state州把数据分为5桶，建表语句如下：

CREATE TABLE itcast.t_usa_covid19(
    count_date string,
    county string,
    state string,
    fips int,
    cases int,
    deaths int)
CLUSTERED BY(state) INTO 5 BUCKETS;

在创建分桶表时，还可以指定分桶内的数据排序规则
–根据state州分为5桶每个桶内根据cases确诊病例数倒序排序

CREATE TABLE itcast.t_usa_covid19_bucket_sort(
      count_date string,
      county string,
      state string,
      fips int,
      cases int,
      deaths int)
CLUSTERED BY(state) sorted by (cases desc) INTO 5 BUCKETS;

3.4 分桶表的数据加载

–step1:开启分桶的功能从Hive2.0开始不再需要设置

set hive.enforce.bucketing=true;

–step2:把源数据加载到普通hive表中

CREATE TABLE itcast.t_usa_covid19(
       count_date string,
       county string,
       state string,
       fips int,
       cases int,
       deaths int)
row format delimited fields terminated by ",";

–将源数据上传到HDFS，t_usa_covid19表对应的路径下

hadoop fs -put us-covid19-counties.dat /user/hive/warehouse/itcast.db/t_usa_covid19

–step3:使用insert+select语法将数据加载到分桶表中

insert into t_usa_covid19_bucket select * from t_usa_covid19;

到HDFS上查看t_usa_covid19_bucket底层数据结构可以发现，数据被分为了5个部分。
在这里插入图片描述
并且从结果可以发现，只要hash_function(bucketing_column)一样的，就一定被分到同一个桶中。

3.5 分桶表的使用好处

和非分桶表相比，分桶表的使用好处有以下几点：
1、基于分桶字段查询时，减少全表扫描
–基于分桶字段state查询来自于New York州的数据
–不再需要进行全表扫描过滤
–根据分桶的规则hash_function(New York) mod 5计算出分桶编号
–查询指定分桶里面的数据就可以找出结果此时是分桶扫描而不是全表扫描

select *
from t_usa_covid19_bucket where state="New York";

2、 JOIN时可以提高MR程序效率，减少笛卡尔积数量
对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了分桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

3、分桶表数据进行抽样
当数据量特别大时，对全体数据进行处理存在困难时，抽样就显得尤其重要了。抽样可以从被抽取的数据中估计和推断出整体的特性，是科学实验、质量检验、社会调查普遍采用的一种经济有效的工作和研究方法。

4 Hive Transactional Tables事务表

4.1 Hive事务背景知识

Hive本身从设计之初时，就是不支持事务的，因为Hive的核心目标是将已经存在的结构化数据文件映射成为表，然后提供基于表的SQL分析处理，是一款面向分析的工具。且映射的数据通常存储于HDFS上，而HDFS是不支持随机修改文件数据的。
这个定位就意味着在早期的Hive的SQL语法中是没有update，delete操作的，也就没有所谓的事务支持了，因为都是select查询分析操作。
从Hive0.14版本开始，具有ACID语义的事务已添加到Hive中，以解决以下场景下遇到的问题：
➢ 流式传输数据。使用如Apache Flume或Apache Kafka之类的工具将数据流式传输到Hadoop集群中。虽然这些工具可以每秒数百行或更多行的速度写入数据，但是Hive只能每隔15分钟到一个小时添加一次分区。频繁添加分区会很快导致表中大量的分区。因此通常使用这些工具将数据流式传输到现有分区中，但是这会使读者感到脏读（也就是说，他们将在开始查询后看到写入的数据），并将许多小文件留在目录中，这将给NameNode带来压力。通过事务功能，同时允许读者获得一致的数据视图并避免过多的文件。
➢ 尺寸变化缓慢。在典型的星型模式数据仓库中，维度表随时间缓慢变化。例如，零售商将开设新商店，需要将其添加到商店表中，或者现有商店可能会更改其平方英尺或某些其他跟踪的特征。这些更改导致插入单个记录或更新记录（取决于所选策略）。
➢ 数据重述。有时发现收集的数据不正确，需要更正。从Hive 0.14开始，可以通过INSERT，UPDATE和 DELETE支持这些用例。

4.2 Hive事务表局限性

虽然Hive支持了具有ACID语义的事务，但是在使用起来，并没有像在MySQL中使用那样方便，有很多局限性。原因很简单，毕竟Hive的设计目标不是为了支持事务操作，而是支持分析操作，且最终基于HDFS的底层存储机制使得文件的增加删除修改操作需要动一些小心思。具体限制如下：
⚫ 尚不支持BEGIN，COMMIT和ROLLBACK。所有语言操作都是自动提交的。
⚫ 仅支持ORC文件格式（STORED AS ORC）。
⚫ 默认情况下事务配置为关闭。需要配置参数开启使用。
⚫ 表必须是分桶表（Bucketed）才可以使用事务功能。
⚫ 表参数transactional必须为true；
⚫ 外部表不能成为ACID表，不允许从非ACID会话读取/写入ACID表。

4.3 案例：创建使用Hive事务表

如果不做任何配置修改，直接针对Hive中已有的表进行Update、Delete、Insert操作，可以发现，只有insert语句可以执行，Update和Delete操作会报错。
在这里插入图片描述

Insert插入操作能够成功的原因在于，底层是直接把数据写在一个新的文件中的。
下面看一下如何在Hive中配置开启事务表，并且进行操作

--Hive中事务表的创建使用
--1、开启事务配置（可以使用set设置当前session生效 也可以配置在hive-site.xml中）
set hive.support.concurrency = true; --Hive是否支持并发
set hive.enforce.bucketing = true; --从Hive2.0开始不再需要  是否开启分桶功能
set hive.exec.dynamic.partition.mode = nonstrict; --动态分区模式  非严格
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; --
set hive.compactor.initiator.on = true; --是否在Metastore实例上运行启动线程和清理线程
set hive.compactor.worker.threads = 1; --在此metastore实例上运行多少个压缩程序工作线程。

--2、创建Hive事务表
create table trans_student(
    id int,
    name String,
    age int
)clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');

--3、针对事务表进行insert update delete操作
insert into trans_student (id, name, age)
values (1,"allen",18);

update trans_student
set age = 20
where id = 1;

delete from trans_student where id =1;

select *
from trans_student;

5 Hive View视图

5.1 View的概念

Hive中的视图（view）是一种虚拟表，只保存定义，不实际存储数据。通常从真实的物理表查询中创建生成视图，也可以从已经存在的视图上创建新视图。
创建视图时，将冻结视图的架构，如果删除或更改基础表，则视图将失败，并且视图不能存储数据，操作数据，只能查询。
概况起来就是：视图是用来简化操作的，它其实是一张虚表，在视图中不缓冲记录，也没有提高查询性能。

5.2 View相关语法

–hive中有一张真实的基础表t_usa_covid19

select *
from itcast.t_usa_covid19;

--1、创建视图
create view v_usa_covid19 as select count_date, county,state,deaths from t_usa_covid19 limit 5;

--能否从已有的视图中创建视图呢  可以的
create view v_usa_covid19_from_view as select * from v_usa_covid19 limit 2;

--2、显示当前已有的视图 
show tables;
show views;--hive v2.2.0之后支持

--3、视图的查询使用
select *
from v_usa_covid19;

--能否插入数据到视图中呢？
--不行 报错  SemanticException:A view cannot be used as target table for LOAD or INSERT
insert into v_usa_covid19 select count_date,county,state,deaths from t_usa_covid19;

--4、查看视图定义
show create table v_usa_covid19;

--5、删除视图
drop view v_usa_covid19_from_view;
--6、更改视图属性
alter view v_usa_covid19 set TBLPROPERTIES ('comment' = 'This is a view');
--7、更改视图定义
alter view v_usa_covid19 as  select county,deaths from t_usa_covid19 limit 2;

5.3． View的好处
1、将真实表中特定的列数据提供给用户，保护数据隐式

--通过视图来限制数据访问可以用来保护信息不被随意查询:

create table userinfo(firstname string, lastname string, ssn string, password string);
create view safer_user_info as select firstname, lastname from userinfo;

--可以通过where子句限制数据访问，比如，提供一个员工表视图，只暴露来自特定部门的员工信息:
create table employee(firstname string, lastname string, ssn string, password string, department string);
create view techops_employee as select firstname, lastname, ssn from userinfo where department = 'java';

2、降低查询的复杂度，优化查询语句

--使用视图优化嵌套查询
from (
   select * from people join cart
   on(cart.pepople_id = people.id) where firstname = 'join'
     )a select a.lastname where a.id = 3;

--把嵌套子查询变成一个视图
create view shorter_join as
select * from people join cart
                          on (cart.pepople_id = people.id) where firstname = 'join';
--基于视图查询
select lastname from shorter_join where id = 3;

6 Hive3.0新特性：物化视图materialized views

6.1 物化视图概念

在传统的数据库领域基本已经都实现了物化视图, 属于数据库的高级功能。物化视图（Materialized View）是一个包括查询结果的数据库对像，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果。这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。使用物化视图的目的就是通过预计算，提高查询性能，当然需要占用一定的存储空间。
但是在SQL On Hadoop领域里支持这个特性的还不多，比较令人期待。Hive3.0开始尝试引入物化视图，并提供对于物化视图的查询自动重写（基于Apache Calcite实现）。值得注意的是，3.0中提供了物化视图存储选择机制，可以本地存储在hive，同时可以通过用户自定义storage handlers存储在其他系统（如Druid）。
Hive引入物化视图的目的就是为了优化数据查询访问的效率,相当于从数据预处理的角度优化数据访问。Hive从3.0丢弃了index索引的语法支持，推荐使用物化视图和列式存储文件格式来加快查询的速度。

6.2 物化视图、视图区别

视图是虚拟的，逻辑存在的，只有定义没有存储数据。
物化视图是真实的，物理存在的，里面存储着预计算的数据。
不同于视图，物化视图能够缓存数据，在创建物化视图的时候就把数据缓存起来了，hive把物化视图当成一张“表”，将数据缓存。而视图只是创建一个虚表，只有表结构，没有数据，实际查询的时候再去改写SQL去访问实际的数据表。
视图的目的是简化降低查询的复杂度，而物化视图的目的是提高查询性能。

6.3 物化视图语法

--物化视图的创建语法
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
    [DISABLE REWRITE]
    [COMMENT materialized_view_comment]
    [PARTITIONED ON (col_name, ...)]
    [CLUSTERED ON (col_name, ...) | DISTRIBUTED ON (col_name, ...) SORTED ON (col_name, ...)]
    [
    [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 SELECT ...;

语法说明：
（1）物化视图创建后，select查询执行数据自动落地，"自动"也即在query的执行期间，任何用户对该物化视图是不可见的
（2）默认该物化视图可被用于查询优化器optimizer查询重写（在物化视图创建期间可以通过DISABLE REWRITE参数设置禁止使用）
（3）SerDe和storage format非强制参数，可以用户配置，默认可用hive.materializedview.serde、 hive.materializedview.fileformat
（4）物化视图可以使用custom storage handlers存储在外部系统（如druid）例如：

CREATE MATERIALIZED VIEW druid_wiki_mv
            STORED AS 'org.apache.hadoop.hive.druid.DruidStorageHandler'
AS
SELECT __time, page, user, c_added, c_removed
FROM src;

目前支持物化视图的drop和show操作，后续会增加其他操作

-- Drops a materialized view
DROP MATERIALIZED VIEW [db_name.]materialized_view_name;
-- Shows materialized views (with optional filters)
SHOW MATERIALIZED VIEWS [IN database_name];
-- Shows information about a specific materialized view
DESCRIBE [EXTENDED | FORMATTED] [db_name.]materialized_view_name;
当数据源变更（新数据插入inserted、数据修改modified），物化视图也需要更新以保持数据一致性，目前需要用户主动触发rebuild
ALTER MATERIALIZED VIEW [db_name.]materialized_view_name REBUILD;

6.4 基于物化视图的查询重写

物化视图创建后即可用于相关查询的加速，用户提交查询query，若该query经过重写后可命中已建视图，则被重写命中相关已建视图实现查询加速。
是否重写查询使用物化视图可以通过全局参数控制，默认为true：
SET hive.materializedview.rewriting=true;
用户可选择性的失能物化视图的重写：
ALTER MATERIALIZED VIEW [db_name.]materialized_view_name ENABLE|DISABLE REWRITE;

6.5 案例：物化视图查询重写

--1、新建一张事务表 student_trans
set hive.support.concurrency = true; --Hive是否支持并发
set hive.enforce.bucketing = true; --从Hive2.0开始不再需要  是否开启分桶功能
set hive.exec.dynamic.partition.mode = nonstrict; --动态分区模式  非严格
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; --
set hive.compactor.initiator.on = true; --是否在Metastore实例上运行启动线程和清理线程
set hive.compactor.worker.threads = 1; --在此metastore实例上运行多少个压缩程序工作线程。

CREATE TABLE student_trans (
      sno int,
      sname string,
      sdept string)
clustered by (sno) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');


--2、导入数据到student_trans中
insert overwrite table student_trans
select sno,sname,sdept
from student;

select *
from student_trans;

--3、对student_trans建立聚合物化视图

CREATE MATERIALIZED VIEW student_trans_agg
AS SELECT sdept, count(*) as sdept_cnt from student_trans group by sdept;

--注意 这里当执行CREATE MATERIALIZED VIEW，会启动一个MR对物化视图进行构建
--可以发现当下的数据库中有了一个物化视图
show tables;
show materialized views;

--4、对原始表student_trans查询
--由于会命中物化视图，重写query查询物化视图，查询速度会加快（没有启动MR，只是普通的table scan）
SELECT sdept, count(*) as sdept_cnt from student_trans group by sdept;

--5、查询执行计划可以发现 查询被自动重写为TableScan alias: itcast.student_trans_agg
--转换成了对物化视图的查询  提高了查询效率
explain SELECT sdept, count(*) as sdept_cnt from student_trans group by sdept;

思考:物化视图会随时更新吗?
翻阅资料
Materialized view maintenance
When data in the source tables used by a materialized view changes, e.g., new data is inserted or existing data is modified, we will need to refresh the contents of the materialized view to keep it up-to-date with those changes. Currently, the rebuild operation for a materialized view needs to be triggered by the user. In particular, the user should execute the following statement:
.
ALTER MATERIALIZED VIEW [db_name.]materialized_view_name REBUILD;
Hive supports incremental view maintenance, i.e., only refresh data that was affected by the changes in the original source tables. Incremental view maintenance will decrease the rebuild step execution time. In addition, it will preserve LLAP cache for existing data in the materialized view.
.
By default, Hive will attempt to rebuild a materialized view incrementally, falling back to full rebuild if it is not possible. Current implementation only supports incremental rebuild when there were INSERT operations over the source tables, while UPDATE and DELETE operations will force a full rebuild of the materialized view.
.
To execute incremental maintenance, following conditions should be met:
(1) The materialized view should only use transactional tables, either micromanaged or ACID.
(2) If the materialized view definition contains a Group By clause, the materialized view should be stored in an ACID table, since it needs to support MERGE operation. For materialized view definitions consisting of Scan-Project-Filter-Join, this restriction does not exist.
.
A rebuild operation acquires an exclusive write lock over the materialized view, i.e., for a given materialized view, only one rebuild operation can be executed at a given time.

翻译:

物化视图维护
当实例化视图使用的源表中的数据发生更改（例如，插入新数据或修改现有数据）时，我们将需要刷新实例化视图的内容，以使其与这些更改保持最新。当前，物化视图的重建操作需要由用户触发。用户尤其应执行以下语句：

ALTER MATERIALIZED VIEW [db_name.]materialized_view_name REBUILD;
Hive支持增量视图维护，即仅刷新受原始源表中的更改影响的数据。增量视图维护将减少重建步骤的执行时间。此外，它将为实例化视图中的现有数据保留LLAP缓存。
默认情况下，Hive将尝试以增量方式重建物化视图，如果不可能，则回落到完全重建。当前实现仅INSERT 在对源表进行操作时才支持增量重建 ，而 UPDATE 和 DELETE 操作将强制对物化视图进行完全重建。
要执行增量维护，应满足以下条件：
（1）物化视图应仅使用微管理表或ACID事务表。
（2）如果实例化视图定义包含Group By子句，则该实例化视图应存储在ACID表中，因为它需要支持MERGE操作。对于由Scan-Project-Filter-Join组成的实例化视图定义，此限制不存在。
重建操作将在物化视图上获取排他写锁定，即，对于给定的物化视图，在给定的时间只能执行一个重建操作。

posted @ 2021-03-27 10:28 赵广陆阅读(132) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部