Hive小结

Hive

什么是hive

​ Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。操作接口采用类SQL语法，提供快速开发的能力。底层是MapReduce,也可以把底层执行引擎改为tez,spark等，提高运算速度，用sql取代了写mr,spark代码。Hive的延迟比较高，用于数据分析，不适合用于实时性较高的场景。适合处理大数据。

hive架构

Hive DDL

创建数据库

create database xxx

create database if not exists xxx

查看数据库

show databases;

切换数据库

use xxx;

创建内部表

create table if not exists student2(

id int, name string

)

row format delimited fields terminated by '\t'

location '/user/hive/warehouse/student2';

创建外部表

create external table if not exists default.dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

向外部表导入数据

 load data local inpath '/opt/module/datas/dept.txt' into table default.dept;

创建分区表

create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';

向分区表中导入数据

load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709');

二级分区表

create table dept_partition2(
    deptno int, dname string, loc string
)	
partitioned by (month string, day string)
row format delimited fields terminated by '\t';

导入数据

load data local inpath '/opt/module/datas/dept.txt' into table
 default.dept_partition2 partition(month='201709', day='13');

修复分区

先有分区目录，表还没有时要进行修复分区

msck repair table xxx

hive开启远程服务命令

hive --service metastore

hive动态分区

在创建分区的时候，如果一次性插入多个分区，使用静态分区太麻烦，这里可以使用动态分区一次性插入。

开启动态分区需要开启非严格模式和开启支持动态分区

开启支持动态分区

set hive.exec.dynamic.partition=true;

开启非严格模式

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

然后把含有多个分区的数据一次性插入到分区表中

from person21
insert overwrite table person22 partition(age, gender)  
select id, name,likes, address,age, gender  distribute by age, gender;

person22 为分区表 age, gender为分区字段

Hive 分桶

分桶常用于数据抽样，以及Mr中的hashPartition

分区和分桶的区别是分区针对的是数据的存储路径；分桶针对的是数据文件。

使用分桶需要开启分桶支持

set hive.enforce.bucketing=true;

创建分桶表

create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

向分桶表中插入数据

load data local inpath '/opt/module/datas/student.txt' into table stu_buck;

分桶抽样查询

语法：TABLESAMPLE(BUCKET x OUT OF y)

select * from stu_buck tablesample(bucket 1 out of 4 on id);

y必须是table总bucket数的倍数或者因子。table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。

x表示从哪个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y

当表总bucket数为32时

1. TABLESAMPLE(BUCKET 3 OUT OF 16)，抽取哪些数据？

共抽取2（32/16）个bucket的数据，抽取第3、第19（16+3）个bucket的数据

2. TABLESAMPLE(BUCKET 3 OUT OF 8)，抽取哪些数据？

共抽取4（32/8）个bucket的数据，抽取：3，11,19,27

3. TABLESAMPLE(BUCKET 3 OUT OF 256)，抽取哪些数据？

共抽取1/8（32/256）个bucket的数据，抽取第3个bucket的1/8数据

内部表和外部表的区别

创建表时：内部表不需要添加external关键字 外部表需要添加

删除数据时：

内部表：元数据、原始数据，全删除

外部表：元数据 只删除元数据

在公司生产环境下，什么时候创建内部表，什么时候创建外部表？

在公司中绝大多数场景都是外部表。

自己使用的临时表，才会创建内部表；

4个By的区别

1）Order By：全局排序，只有一个Reducer；

2）Sort By：分区内有序；

3） Distrbute By：类似 MR 中 Partition，进行分区，结合 sort by 使用。

4） Cluster By：当 Distribute by 和 Sorts by 字段相同时，可以使用 Cluster by 方式。Cluster

by 除了具有Distribute by 的功能外还兼具 Sort by 的功能。但是排序只能是升序排序，不能指定排序规则为ASC 或者 DESC。

在生产环境中Order By 用的比较少，容易导致 OOM。在生产环境中 Sort By+ Distrbute By 用的多。

系统函数

1） date_add、date_sub 函数（加减日期）

2） next_day 函数（周指标相关）

3） date_format 函数（根据格式整理日期）

4） last_day 函数（求当月最后一天日期）

6） get_json_object 解析 json 函数

7） NVL（表达式 1，表达式 2）

8）concat(str1, str2),concat_ws(separator, str1, str2,…)

9. COLLECT_SET(col) COLLECT_LIST(col) 前者去重后者不去重

10. split(str,xxx)把字符串拆分成数组

11） explode() 把数组或map炸裂成多行

12）from_unixtime( unix_timestamp,format ) 把时间戳转换成字符串

13）unix_timestamp (str,format ) 把字符串转换成时间戳

14）str_to_map(str,sepatator1,sepatator2) sepatator1把文本分成K-V对，sepatator2把K-V对分成K，V

cube维度查询

select col1,col2,col3,col4, --维度字段
         count(user_id),  --聚合字段
         GROUPING__ID, --聚合选取的组号(二进制表示,但是这里打印出来的是十进制)
         rpad(reverse(bin(cast(GROUPING__ID AS bigint))),4,'0') --对其二进制化就能明白了,注意中间是两个下划线,因为在反转的时候会把末尾的0去掉,需要用rpad补充至维度个数
from table 
group by col1,col2,col3,col4  --维度字段都要出现在group by中,这里不能使用1,2,3,4代替
with cube; --使用cube函数

自定义UDF,UDTF函数

（1） 自定义UDF：继承UDF，重写 evaluate 方法

（2） 自定义 UDTF：继承自 GenericUDTF，重写 3 个方法：initialize(自定义输出的列名和类型)，process（将结果返回 forward(result)），close

打包=》上传集群路径=》在hive客户端注册

为什么要自定义UDF/UDTF？

因为自定义函数，可以自己埋点 Log 打印日志，出错或者数据异常，方便调试。

窗口函数

1. rank() over (partiton by xxx order by xxx) partiton by xxx可以省略

排序相同时会重复，总数不会变

2）dense_rank() 排序相同时会重复，总数会减少

3. row_number() 会根据顺序计算

4. sum(pv) over (partiton by xxx1 order by xxx2) 从起点到当前行累加

sum(pv) over (partiton by xxx1 order BY xxx2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) --从起点到当前行
SUM(pv) OVER(PARTITION BY cookie_id ORDER BY create_time ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv3, --当前行+往前3行
SUM(pv) OVER(PARTITION BY cookie_id ORDER BY create_time ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv4, --当前行+往前3行+往后1行
SUM(pv) OVER(PARTITION BY cookie_id ORDER BY create_time ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv5 —当前行+往后所有行

Hive优化

MapJoin

​ 如果不指定 MapJoin 或者不符合 MapJoin 的条件，那么Hive 解析器会将 Join 操作转换成 Common Join，即：在 Reduce 阶段完成 join。容易发生数据倾斜。可以用 MapJoin 把小表全部加载到内存在 map 端进行 join，避免 reducer 处理。

行列过滤

​ 列处理：在 SELECT 中，只拿需要的列，如果有，尽量使用分区过滤，少用 SELECT *。

​ 行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在 Where 后面，那

么就会先全表关联，之后再过滤。

采用分区技术

开启map端combiner(不影响最终业务逻辑)

set hive.map.aggr=true；

小文件如何产生的？

（1） 动态分区插入数据，产生大量的小文件，从而导致 map 数量剧增；

（2） reduce 数量越多，小文件也越多（reduce 的个数和输出文件是对应的）；

（3） 数据源本身就包含大量的小文件。

开启JVM重用

set mapreduce.job.jvm.numtasks=10

压缩（选择快的）

设置 map 端输出、中间结果压缩。（不完全是解决数据倾斜的问题，但是减少了 IO 读写和网络传输，能提高很多效率）

set hive.exec.compress.intermediate=true --启用中间数据压缩set mapreduce.map.output.compress=true --启用最终数据压缩
set mapreduce.map.outout.compress.codec=…; --设置压缩方式

解决数据倾斜

1）set hive.map.aggr=true; //map端部分聚合

2） 当遇到一个大表和一个小表进行join操作时 小表在join左侧，大表在右侧，或使用mapjoin 将小表加载到内存中。

3） 遇到需要进行join的但是关联字段有数据为null，如表一的id需要和表二的id进行关联，null值的reduce就会落到一个节点上

解决方法1：子查询中过滤掉null值，id为空的不参与关联

解决方法2：用case when给空值分配随机的key值（字符串+rand()）

4） 不同数据类型关联产生数据倾斜

使用cast(b.auction_id as string)转换成同一类型

5） 当HiveQL中包含count（distinct）时

如果数据量非常大，执行如select a,count(distinct b) from t group by a;类型的SQL时，会出现数据倾斜的问题。

解决方法：使用sum…group by代替。如select a,sum(1) from (select a, b from t group by a,b) group by a;