跟着黑马学Hive - 第二周

🐎本文来源：整理自黑马程序员B站课程

🔗课程链接：www.bilibili.com/video/BV1L5…

✒️本文作者：Anthony_4926

1. Hive 分区表

1.1. 分区表产生背景

现有 6 份结构化数据文件，分别记录了《王者荣耀》中 6 种位置的英雄相关信息。

现要求通过建立一张表 t_all_hero，把 6 份文件同时映射加载。

路径下的所有文件都会映射加载，不会仅仅映射加载一个文件，这个不需要担心。

create table t_archer(
    id int comment 'id'
    , name string comment '英雄名称'
    , hp_max int comment '最大生命'
    , mp_max int comment '最大法力'
    , attack_max int comment '最高物攻'
    , defense_max int comment '最大物防'
    , attack_range string comment '攻击范围'
    , role_main string comment '主要定位'
    , role_assit string comment '次要定位'
)comment '王者荣耀射手信息'
row format delimited
fields terminated by '\t'
;

• 现要求查询 role_main 主要定位是射手并且 hp_max 最大生命大于 6000 的有几个，sql 语句如下：

select 
    count(*)
from t_all_hero
where role_main = 'archer' and hp_max > 6000
; -- 执行了1m

思考

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

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

1.2. 分区表创建

• 分区表语法

注意：分区字段不能是表中已经存在的字段，因为分区字印没最终也会以虚拟字段的形式显示在表结构上，此时表中就有了两个同名字段。可以为分区字段加一个partition_前缀来解决该问题

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

1.3. 分区表数据加载——静态分区

所谓静态分区指的是分区的属性值是由用户在加载数据的时候手动指定的

语法如下：

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

Local 参数用于指定待加载的数据是位于本地文件系统还是 HDFS 文件系统。关于 1oad 语句后续详细展开讲解。

--静态加载分区表数据
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(rrole='zhanshi');

1.4.本质

外表上看起来分区表好像没多大变化，只不过多了一个分区字取没。实际上分区表在底层管理数据的方式发生了改变

这里直接去 HDFS 查看区别。

• 分区的概念提供了一种将 Hive 表数据分离为多个文件/目录的方法。
• 不同分区对应着不同的文件夹，同一分区的数据存储在同一个文件夹下。
• 查询过滤的时候只需要根据分区值找到对应的文件夹，扫描本文文件夹下本分区下的文件即可，避免全表数据扫描。
• 这种指定分区查询的方式叫做分区裁剪。

于是，非分区表和分区表的查询方式就有了差别，通过指定分区，hive 就只会在该分区下扫描。

-- 非分区表，全表扫描过滤查询
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
;

1.5. 多重分区表

通过建表语句中关于分区的相关语法可以发现，Hive 支持多个分区字段：

PARTITIONED BY (partitionl data_type, partition2 data_type, ....)

多重分区下，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区

从 HDFS 的角度来看就是文件夹下继续划分子文件夹。比如：把乡全国人口数据首先根据省进行分区，然后根据市进行划分，如果你需要甚至可以继续根据区县再划分，此时就是 3 分区表。

-- 单分区表，按省份分区
create table t_user_provice(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='zhejaing', city='hangzhou', county='xiaoshan');
    
select * from t_user_province_city_county where province='zhejiang' and city = 'hangzhou';

思考

1. 在向 Hive 分区表加载数据的时候，我们把使用 load 命令手动指定分区值的方式叫做静态加载，那么有

没有动态加载？

2. 如果创建的分区很多，是否意味着复制粘贴修改很多 load 命令去执行，效率低。有没有高效的方法？

1.6. 分区数据表加载——动态分区

所谓动态分区指的是分区的字段值是基于查询结果（参数位置）目自动推断出来的。核心语法就是 insert+select

启用 hive 动态分区，需要在 hive 会话中设置两个参数：

#是否开启动态分区功能
set hive.exec.dynamic.partition=true;

#指定动态分区模式,分为nonstick非严格模式和strict严格模式。
#strict严格模式要求至少有一个分区为静态分区。
set hive.exec.dynamic.partition.mode=nonstrict;

使用动态分区的数据加载只能从另一张表中向分区表插入。表字段按顺序 select 完毕后，还需要按顺序 select 分区字段。

--执行动态分区插入
insert into table t_all_hero_part_dynamic partition(role)
select tmp.*,tmp.role_main from t_all_hero tmp;

1.7. 分区表注意事项

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

2. Hive 分桶表

分桶表也叫做桶表，叫法源自建表语法中 bucket 单词，是一种用于优化查询而设计的表类型。

分桶表对应的数据文件在底层会被分解为若干个部分，通俗来说就是被拆分成若干个独立的小文件。

在分桶时，要指定根据哪个字段将数据分为几桶（几个部分）。

2.1. 分桶规则

• 分桶规则如下：桶编号相同的数据会被分到同一个桶当中。

• hash_function 取决于分桶字段 bucketing_column 的类型：
  • 如果是 int 类型，hash_function（int）=int；
  • 如果是其他比如 bigint，string 或者复杂数据类型，hash_function 比较棘手，将是从该类型派生的某个数字，比如 hashcode 值。

2.2. 完整语法树

CLUSTERED BY （col_name）表示根据哪个字段进行分桶；

INTONBUCKETS 表示分为几桶（也就是几个文件）。

需要注意的是，分桶的字段必须是表中已经存在的字段。

2.3. 分桶表的创建

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

字段含义：count_date（统计日期），county（县），state（州），fips（县编码 code），cases（累计确诊病例）

，deaths（累计死亡病例）。

2021-01-28,Jefferson,Alabama,01073,65992,1101
2021-01-28,Lamar,Alabama,01075,1230,24
2021-01-28,Lauderdale,Alabama,01077,8088,127
2021-01-28,Lawrence,Alabama,01079,2616,61
2021-01-28,Lee,Alabama,01081,13602,113
2021-01-28,Limestone,Alabama,01083,8564,89
2021-01-28,Lowndes,Alabama,01085,1195,38
2021-01-28,Macon,Alabama,01087,126038
2021-01-28,Madison,Alabama,01089,29098,248
2021-01-28,Marengo,Alabama,01091,2152,34
-- 根据州进行分桶
create table t_usa_covid19_bucket(
    count_date string
    , county string
    , state string
    , fips int
    , cases int
    , deaths int
)
clustered by(state) into 5 buckets;

2.4. 分桶表的数据加载

• 开启分桶功能，从 Hive 2.0 开始不需要再设置

set hive.enforce.bucketing=true;

分桶数据的加载也是需要查询插入的方式，不能直接映射，于是流程就变为了先创建一个可以直接映射的表，然后从该表中查询数据插入到分桶表中。

drop table if exists t_usa_covid19;
create table t_usa_covid19(
    count_date string
    , county string
    , state string
    , fips int
    , cases int
    , deaths int
)
row fomat delimited
fileds terminated by ','
;

-- 查询插入
insert into t_usa_covid19_bucket select * from t_usa_covid19;

2.5. 分桶表的好处

• 基于分桶字段查询时，减少全表扫描

--基于分桶字段state查询来自于NewYork州的数据
--不再需要进行全表扫描过滤
--根据分桶的规则hash_function(New York)mod 5计算出分桶编号
--查询指定分桶里面的数据 就可以找出结果 此时是分桶扫描而不是全表扫描
select *
from t_usa_covid19_bucket where state="New York";

• JOIN 时可以提高 MR（Map Reduce）程序效率，减少笛卡尔积数量。根据 join 的字段对表进行分桶操作（比如下图中 id 是 join 的字段）

• 分桶表数据进行高效抽样

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

3. Hive 事务表

3.1. Hive 事务背景知识

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

3.2. Hive 事务局限性

虽然 Hive 支持了具有 ACID 语义的事务，但是在使用起来，并没有有像在 MySQL 中使用那样方便，有很多局限性。原因很简单，毕竟 Hive 的设计目标不是为了支持事务操作，而是支持分听操作，且最终基于 HDFS 的底层存储机制使得文件的增加删除修改操作需要动一些小心思。

• 尚不支持 BEGIN，COMMIT 和 ROLLBACK。所有语言操作都是自动提交的。
• 仅支持 ORC 文件格式（STORED AS ORC）。
• 默认情况下事务配置为关闭。需要配置参数开启使用。
• 表必须是分桶表（Bucketed）才可以使用事务功能。
• 表参数 transactional 必须为 true；
• 外部表不能成为 ACID 表，不允许从非 ACID 会话读取/写入 ACID 表。

3.3. 创建使用 Hive 事务表

• 首先需要开启一些配置

set hive.support.concurrency = true;--Hive是否支持并发
Set hive.enforce.bucketing = true; -- KHive2.0开始不再需要是否开启分桶功能
set hive.exec.dynamic.partition.mode = nonstriict;--动态分区模式 非严格
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实例上运行多少个压缩程序工作线程

• 创建 Hive 表

create table trans_student(
    id int,
    name String,
    age int
)clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');
--注意事务表创建几个要素:开启参数、分桶表、存储格式orc、表属性

• 执行一条更新语句

update trans_student set age = 20 where id = 1; -- 执行了49s

一条更新语句执行了 49s，属实慢的很

3.4. Hive 是如何执行的 update 呢？

通过看文件系统可以发现，他是重新写了一份文件，并且把原本的文件标记为了删除

4. Hive 视图

4.1. 概念

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

4.2. 相关语法

-- Hive中有一张真实的基础表 
select * from t_usa_covid19;

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

-- 从视图中创建视图
create view v_usa_covid19_from_view as
select * v_usa_covid19 limit 2;

-- 显示当前已经有的视图
show tables;
show views; -- hive 2.2.0 之后支持

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

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

-- 删除视图
drop table v_usa_covid19_from_view;

-- 更改视图属性
alter view v_usa_covid19
set talproperties('comment'='This is a view');

-- 更改视图定义
alter view v_usa_covid19 as
select county, deaths from t_uas_covid19 limit 2;

4.3.使用视图的好处

• 将真实表中特定的列数据提供给用户，保护数据隐私（你看到的，都是我想让你看到的）
• 降低查询的复杂度，优化查询语句。

5. Hive Database | Schema （数据库） DDL 操作

5.1. 整体概述

• 在 Hive 中，DATABASE 的概念和 RDBMS 中类似，我们称之为数据库，DATABASE 和 SCHEMA 是可互换的，都可以使用。
• 默认的数据库叫做 default，存储数据位置位于/user/hive/warehouse下。
• 用户自己创建的数据库存储位置是/user/hive/warehouse/database_name.db下。

5.2. Create Database

create database 用于创建新的数据库

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT message]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

• COMMENT：数据库的注释说明语句
• LOCATION：指定数据库在 HDFS 存储位置，默认/user/hive/warehouse/dbname.db
• WITH DBPROPERTIES：用于指定一些数据库的属性配置。

create database if not exists test
comment "this is a test database"
with dbproperties ('createdBy'='Anthony')

5.3. Describe database

显示 Hive 中数据库名称，注释及其在文件系统中的位置等信息。

DESCRIBE DATABASE/SCEHMA [EXTENDED] db_name;

EXTENDED 关键字用于显示更多信息。可以将关键字 describe 简写成 desc 使用。

5.4. Drop database

删除数据库

默认行为是 RESTRICT，这意味着仅在数据库为空时才删除它。

要删除带有表的数据库（不为空的数据库），我们可以使用 CASCADE。

DROP(DATABASE]SCHEMA) [IF EXISTS] database_name [RESTRICT]CASCADE];

5.5. Alter database

更改与 Hive 中的数据库关联的元数据

--更改数据库属性
ALTER(DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...)
-- 更改数据库所有者
ALTER(DATABASESCHEMA) database_name SET OWNER USER user; 
--更改数据库位置
ALTER(DATABASESCHEMA) database_name SET LOCATION hdfs_path;

6. Hive Table （表） DDL 操作

6.1. 整体概述

Hive 中针对表的 DDL 操作可以说是 DDL 中的核心操作，包括建表、修改表、删除表、描述表元数据信息。

其中以建表语句为核心中的核心，详见 Hive DDL 建表语句。

可以说表的定义是否成功直接影响着数据能够成功映射，进而影响是否可以顺利的使用 Hive 开展数据分析。

由于 Hive 建表之后加载映射数据很快，实际中如果建表有问题，可以不用修改，直接删除重建。

6.2. Describe table

• 显示 Hive 中表的元数据信息

如果指定了 EXTENDED 关键字，则它将以 Thrift 序列化形式显示表的所有元数据。

如果指定了 FORMATTED 关键字，则它将以表格格式显示元数据。

6.3. Drop table

删除该表的元数据和数据

如果已配置垃圾桶且未指定 PURGE，则该表对应的数据实际上将移动到 HDFS 垃圾桶，而元数据完全丢失。

删除 EXTERNAL 表时，该表中的数据不会从文件系统中删除，只删除元数据。

如果指定了 PURGE，则表数据跳过 HDFS 垃圾桶直接被删除。因此如果 DROP 失败，则无法挽回该表数据。

DROP TABLE [IF EXISTS] table_name [PURGE];

6.4. Truncate table

从表中删除所有行。

可以简单理解为清空表的所有数据但是保留表的元数据结构。

如果 HDFS 启用了垃圾桶，数据将被丢进垃圾桶，否则将被删除。

6.5. Alter table

--1、更改表名
ALTER TABLE table_name RENAME TO new_table_name; 
--2、更改表属性
ALTER TABLE table_name SET TBLPROPERTIES (property_name property_value, ...) 
--更改表注释
ALTER TABLE student SET TBLPROPERTIES ('comment'="new comment for student table"); 
--3、更改SerDe属性
ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES (property_name property_value,... )]
ALTER TABLE table_name [PARTITION partition spec] SET SERDEPROPERTIES serde_properties; 
ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim'=',');
--移除 SerDe属性
ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, .. )
--4、更改表的文件存储格式该操作仅更改表元数据。现有数据的任何转换都必须在Hive之外进行。
ALTER TABLE table name SET FILEFORMAT file_format; 
--5、更改表的存储位置路径
ALTER TABLE table name SET LOCATION "new location";

7. Hive Partition （分区） DDL 操作

7.1. Add partition

ADD PARTITION 会更改表元数据，但不会加载数据。如果分区位置中不存在数据，查询时将不会返回结果。

因此需要保证增加的分区位置路径下，数据已经存在，或者增加完分区之后导入分区数据。

--1、增加分区
ALTER TABLE table name ADD PARTITION (dt='20170101') location /user/hadoop/warehouse/table_name/dt='20170101';
-- 一次添加一个分区
ALTER TABLE table_name ADD 
    PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808'
    PARTITION(dt='2008-08-09', country='us') location '/path/to/us/part080809';
-- 一次添加多个分区

7.2. Rename partition

--2、重命名分区
ALTER TABLE table_name PARTITION partition_spec RENAMETO PARTITION partition_spec;
ALTER TABLE table_name PARTITION (dt='2008-08-09') RENAMIE TO PARTITION (dt='20080809')

7.3. Delete partition

删除表的分区。这将删除该分区的数据和元数据。

--3、删除分区
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us');
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (cdt='2008-08-08', country='us') PURGE;--直接删除数据不进垃圾桶

7.4. 修复分区

• Hive 将每个表的分区列表信息存储在其 metastore 中。但是，如果将新分区直接添加到 HDFS（例如通过使用 hadoop fs -put 命令）或从 HDFS 中直接删除分区文件夹，则除非用户 ALTTER TABLE table_name ADD/DROP PARTITION 在每个新添加的分区上运行命令，否则 metastore（也就是 Hive）将不会意识到分区信息的这些更改。
• MSCK 是 metastore check 的缩写，表示元数据检查操作，可用于元数据的修复。

--4、修复分区
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS]

【案例】Hive MSCK 修复 partition

1. 创建一张分区表，直接使用 HDFS 命令在表文件夹下创建分区文件夹并上传数据，此时在 Hive 中查询是无法显示表数据的，因为 metastore 中没有记录，使用 MSCK ADD PARTITIONS 进行修复。
2. 针对分区表，直接使用 HDFS 命令删除分区文件夹，此时在 Hive 中查询显示分区还在，因为 metastore 中还没有被删除，使用 MSCK DROP PARTITIONNS 进行修复。

8. Hive show 语法

Show 相关的语句提供了一种查询 Hive metastore 的方法。可以帮助用户查询相关信息。

比如我们最常使用的查询当前数据库下有哪些表 show tables。

-1、显示所有数据库SCHEMAS和DATABASES的用法功能一样
show databases;
show schemas;
--2、显示当前数据库所有表/视图/物化视图/分区/索引
show tables;
SHOWTABLES [IN database_name]; --指定某个数据库
--3、显示当前数据库下所有视图
Show Views;
SHOW VIEWS 'test_*'; -- show all views that start with"test_"
SHOW VIEWS FROM test1; -- show views from database test1
SHOW VIEWS [IN/FROM database_name];
--4、显示当前数据库下所有物化视图
SHOW MATERIALIZED VIEWS [IN/FROM database_name];
--5、显示表分区信息,分区按字母顺序列出,不是分区表执行该语句会报错
show partitions table_name;
--6、显示表/分区的扩展信息
SHOW TABLE EXTENDED [IN|FROM database_name] LIKEtable_name;
show table extended like student;
--7、显示表的属性信息
SHOW TBLPROPERTIES table_name;
show tblproperties student;
--8、显示表、视图的创建语句
SHOW CREATE TABLE ([db_name.]table_name|view_namee);
show create table student;
--9、显示表中的所有列,包括分区列。
SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_rname]
show columns in student;
--10、显示当前支持的所有自定义和内置的函数
show functions;
--11、Describedesc
--查看表信息
desc extended table_name;
--查看表信息(格式化美观)
desc formatted table_name;
-查看数据库相关信息
describe database database_name;