Hive知识点汇总

HIVE

一、Hive的优化

数据倾斜：shuffle之后Key的分布不均导致分配到Reduce端的数据不均匀，出现个别Reduce的数据过大，执行时间过长而出现的现象。

1、数据倾斜产生的原因：

①数据业务本身的特性，如某作为key值的字段重复较多

②join时，大表与小表关联小表字段作为key值

③join时，大表与大表关联，但是很多数据没关联上，导致产生了空值

④count(distinct) 后导致数据倾斜

⑤group by 时因为分区不合理导致数据倾斜，如group by 的字段某类值过多导致数据倾斜

2、数据倾斜的表现

而当其中每一组的数据量过大时，会出现其他组的计算已经完成而这里还没计算完成，其他节点的一直等待这个节点的任务执行完成，所以会看到一直map 100% reduce 99%的情况，大概率是发生了数据倾斜。

3、hive优化方案

3.1 Fetch抓取

某些情况不需要使用MR进行计算，例如查全表数据，此时可以简单读取表对应的存储目录下的文件，输出到控制台。可以在hive-default.xml.template 文件中 设置hive.fetch.task.conversion ，该属性有三个值[none，minimal，more]可选，默认是more，选择more之后在全局查找、字段查找、limit 查找等都不走 mapreduce。

3.2 本地模式

当输入数据量较小时，可以设置hive.exec.mode.local.auto = true，开启本地MR，开启之后会在本地进行MR计算，而不会使用集群计算，因为小数据量情况下集群的准备时间可能都高于计算时间。同时还因设置 本地模式的最大数据量：hive.exec.mode.local.auto.inputbytes.max，默认是128M，本地模式最大文件数：hive.exec.mode.local.auto.input.files.max，默认是4。

4、数据倾斜优化

①小表、大表join

将key相对分散，数据量较小的表放在join左边

②大表 join 大表

（1）空Key过滤：有时可能因为key为空的数据量过多，空值都到一个Reduce端，导致出现数据倾斜，此时我们可以在join之前将Key为空的数据过滤掉。

（2）空Key转换：有时可能某些Key为空，但对应的数据并不是异常数据，此时不能简单的进行过滤，我们可以通过将空Key进行转换，例如将Key转换成一个字符串加上一个随机数：concat(‘hive’ + rand())。

③MapJoin

当不指定MapJoin或不符合MapJoin的条件时，Hive会在Reduce阶段进行Join操作，容易导致数据倾斜。当进行小表大表join时，我们可以采用MapJoin，通过设置 hive.auto.convert.join = true，hive.mapjoin.smalltable.filesize =25000000（25M）;将小表先加载在Map端进行Join操作。以上两个参数为默认值。

④Group By

如果某个Key的数据特别大，那么在Reduce聚合时就可能导致数据倾斜。解决方案是先进行局部聚合，再进行全局聚合，使用两个MR过程。第一个MR过程为Key加上一个100以内的随机数，然后进行预聚合，并将结果输出到第二个MR作为输入。第二个MR将随机数去除再进行Reduce端的聚合，此时由于前一个MR已经对相同key的数据进行了部分聚合，那么这个Reduce端的数据量就明显减少了，就不容易再发生数据倾斜。并且这个过程也可以使用MapJoin，在Map端也进行一定的聚合。

⑤Count(Distinct)去重统计

当数据量毕竟较大时，count操作中只有一个Reduce，那么这个Reduce就会很难完成任务。此时，可以通过Group By + Count的方式替换count，先进行局部的count，就会有多个Reduce；在进行全局的count。这中方法的缺点是需要多一个Job，但这完全是值得的。

⑥笛卡尔积

尽量避免笛卡尔积，因为Hive只能使用一个Reduce来完成笛卡尔积。

⑦行列过滤

列处理：只拿需要的列，尽量使用分区过滤，少用select *。

行处理：进行join操作时副表的过滤操作如果写在最上层where的后面，则会先进行全表关联，因此我们应该先对副表进行子查询过滤操作，有一定的优化效果。

⑧用作join的字段数据类型要相同

可以减少类型转换消耗的资源

5、MR优化

Job数量：通过explain可以查看stage的数量，会执行的stage的数量就是job的数量。

map数量：map数量有四个影响因素：块的大小，文件大小，文件数量，splitsize大小（切片大小），splitsize=max(minimumsize,min(maximumsize,blocksize))；默认blocksize=128M。

reduce数量：在每个MR的main方法中设置，job.setNumReduceTasks()。

①合理设置Map数

小文件太多可以通过合并小文件ConbineHiveInputFormat；

大文件增加Map数，通过computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M 公式，调整maxSize大小小于blocksize就可以增加map数量。

②合理设置Reduce数

默认reduce处理的数据量时256M

③map端处理的小文件过多

要合并小文件，可以通过set hive.merge.mapfiles=true来解决。

⑤map端数据量大，需要扩充map的数量或reduce的数量

set mapred.map.tasks个数，set mapred.reduce.tasks个数

二、HQL底层执行过程

1、HQL执行顺序

首先hql的执行顺序如下

from … where … mapjoin … on … select（筛选有用字段） … group by ||… join … on … select（筛选输出字段） … having … distinct … order by … limit … union/union all

||前是map阶段执行，后的reduce阶段执行

以如下hql为例：

select
  sum(b.order_amount) sum_amount,
  count(a.userkey) count_user
from user_info a
left join user_order b
  on a.idno=b.idno
where a.idno > '112233'
group by a.idno
  having count_user>1
limit 10;

hql执行顺序如下：

Map 阶段：

1. 执行 from，进行表的查找与加载，注意要join的表也要加载进来（MapJoin除外）；
2. 执行 where，注意：sql 语句中 left join 写在 where 之前的，但是实际执行先执行 where 操作，因为 Hive 会对语句进行优化，如果符合谓词下推规则，将进行谓词下推；
3. 如果join的是小表，可以执行 Map join 操作，按照 key 进行表的关联；
4. 执行输出列的操作，注意： select 后面只有两个字段（order_amount，userkey），此时 Hive 是否只输出这两个字段呢，当然不是，因为 group by 的是 idno，如果只输出 select 的两个字段，后面 group by 将没有办法对 idno 进行分组，所以此时输出的字段有三个：idno，order_amount，userkey，所以这个select的作用是筛选出需要用到的字段，所以我们在写hql时最好不要用select *；
5. 执行 map 端的 group by，此时的分组方式采用的是哈希分组，按照 idno 分组，进行
   order_amount 的 sum 操作和 userkey 的 count 操作，最后按照 idno 进行排序（group by 默认会附带排序操作）；

Reduce 阶段：

1. 执行 reduce 端的 group by，此时的分组方式采用的是合并分组，对 map 端发来的数据按照 idno 进行分组合并，同时进行聚合操作 sum(order_amount)和 count(userkey)；
2. 执行 select，此时输出的就只有 select 的两个字段：sum(order_amount) as sum_amount，count(userkey) as count_user;
3. 执行 having，此时才开始执行 group by 后的 having 操作，对 count_user 进行过滤，注意：因为上一步输出的只有 select 的两个字段了，所以 having 的过滤字段只能是这两个字段；
4. 执行 limit，限制输出的行数为 10。

若join不是map join，那么在reduce阶段是如何执行join的呢？
图片转载链接： https://blog.csdn.net/u013660881/article/details/54600768.

三、HIVE分区与分桶

1、什么是分区

分区的目的就是提高查询效率，查询分区数据的方式就是指定分区名，指定分区名之后就不再全表扫描，直接从指定分区中查询，从hdfs的角度看就是从相应的文件系统中你指定的分区名会生成一个单独的文件，去这个指定文件中查找特定的数据

这个分区字段形式上存在于数据表中，在查询时会显示到客户端上，但并不真正在存储在数据表文件中，是所谓伪列。所以，千万不要以为是对属性表中真正存在的列按照属性值的异同进行分区。比如上面的分区依据的列name并不真正的存在于数据表中，是我们为了方便管理添加的一个伪列，这个列的值也是我们人为规定的

2、什么是分桶

分桶是相对分区进行更细粒度的划分。分桶将整个数据内容安装某列属性值得hash值进行区分，如要安装name属性分为3个桶，就是对name属性值的hash值对3取摸，按照取模结果对数据分桶。

与分区不同的是，分区依据的不是真实数据表文件中的列，而是我们指定的伪列，但是分桶是依据数据表中真实的列而不是伪列。所以在指定分区依据的列的时候要指定列的类型，因为在数据表文件中不存在这个列，相当于新建一个列。而分桶依据的是表中已经存在的列，这个列的数据类型显然是已知的，所以不需要指定列的类型。

3、为什么要进行分桶

可提高查询效率，如：我们要对两张在同一列上进行了分桶操作的表进行JOIN操作的时候，只需要对保存相同列值的桶进行JOIN操作即可。同时分桶也能让取样（Sampling）更高效。

四、内部表与外部表

未被external修饰的是内部表（managed table），被external修饰的为外部表（external table）；
区别：
内部表数据由Hive自身管理，外部表数据由HDFS管理；
内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定（如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里）；
删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除，且删除后如果重新create相同的外部表数据不会变；

五、Hive 的函数：UDF、UDAF、UDTF 的区别

1、区别

UDF: 单行进入，单行输出
UDAF: 多行进入，单行输出
UDTF: 单行输入，多行输出

2、UDTF函数

①对array类型数据

hive> create table student_array(
    > name string,
    > course_score array<string>
    > )
    > row format delimited fields terminated by '\t'
    > collection items terminated by ','
    > stored as textfile;
hive> select * from student_array;
-chgrp: '*' does not match expected pattern for group
Usage: hadoop fs [generic options] -chgrp [-R] GROUP PATH...
OK
zs	["语文:86","数学:87.5","英语:90"]
ls	["语文:76","数学:93","英语:88"]
ww	["语文:88","数学:90","英语:95"]

对array类型的数据可以用explode函数将数组中的元素拆分，按行输出每个元素

hive> select explode(course_score) from student_array;
-chgrp: '*' does not match expected pattern for group
Usage: hadoop fs [generic options] -chgrp [-R] GROUP PATH...
OK
语文:86
数学:87.5
英语:90
语文:76
数学:93
英语:88
语文:88
数学:90
英语:95
select split(course_score,':')[1] from student_array

②对map类型的数据

hive> create table student_map(
    > name string,
    > course_score map<string ,float>
    > )
    > row format delimited fields terminated by '\t'
    > collection items terminated by ','
    > map keys terminated by ':'
    > stored as textfile;
hive> select * from student_map;
-chgrp: '*' does not match expected pattern for group
Usage: hadoop fs [generic options] -chgrp [-R] GROUP PATH...
OK
zs	{"语文":86.0,"数学":87.5,"英语":90.0}
ls	{"语文":76.0,"数学":93.0,"英语":88.0}
ww	{"语文":88.0,"数学":90.0,"英语":95.0}

对于map类型的数据，可以使用explode分开key和value类型的数据

hive> select explode(course_score) from student_map;
-chgrp: '*' does not match expected pattern for group
Usage: hadoop fs [generic options] -chgrp [-R] GROUP PATH...
OK
语文	86.0
数学	87.5
英语	90.0
语文	76.0
数学	93.0
英语	88.0
语文	88.0
数学	90.0
英语	95.0

③对于结构体类型的数据（map和array的集合）

hive> create table student_map(
    > name string,
    > course_score array<map<string ,float>>
    > )
    > row format delimited fields terminated by '\t'
    > collection items terminated by ','
    > map keys terminated by ':'
    > stored as textfile;
hive> select * from student_struct_array;
-chgrp: '*' does not match expected pattern for group
Usage: hadoop fs [generic options] -chgrp [-R] GROUP PATH...
OK
zs	[{"course":"语文","score":86.0},{"course":"数学","score":87.5},{"course":"英语","score":90.0}]
ls	[{"course":"语文","score":76.0},{"course":"数学","score":93.0}]
ww	[{"course":"语文","score":88.0},{"course":"数学","score":90.0},{"course":"英语","score":95.0}]
Time taken: 0.19 seconds, Fetched: 3 row(s)

可以使用inline函数进行拆分，注：不能使用UDTF函数进行嵌套

hive> select inline(course_score) from student_struct_array;
-chgrp: '*' does not match expected pattern for group
Usage: hadoop fs [generic options] -chgrp [-R] GROUP PATH...
OK
语文	86.0
数学	87.5
英语	90.0
语文	76.0
数学	93.0
语文	88.0
数学	90.0
英语	95.0

3、UDAF函数

①count，返回值为bigint

count（*）：返回指定列检索到的行数，包括空值

count（expr）：expr表达式不是NULL的行的数量

count(distinct) ：返回去除重复后的行数

②sum，min，max，avg

常用函数，不予详细介绍

③返回值类型为array的函数

select filter_name ,
	collect_list(path_id),
	collect_set(path_id),
	concat_ws('@',collect_list(path_id)) a,
	concat_ws('@',collect_set(path_id)) b,
	concat_ws('@',collect_set(market_type)) c
from FDM_SOR.T_FIBA_MULTI_UBA_CFG_PATH_DETAIL_D 
where path_id >89
group by filter_name

4、UDF函数

①数学函数

函数名称	说明
round(double d, int n)	返回保留n位小数的近似d值
bin(int d)	计算二进制值d的string值
rand(int seed)	返回随机数,seed是随机因子
ceil(double d)	返回大于d的最小整值
floor(double d)	返回小于d的最大整值

②日期函数

函数名称	说明
to_date(string timestamp)	返回时间字符串中的日期部分,如to_date(‘1970-01-01 00:00:00’)=‘1970-01-01’
current_date	返回当前日期
year(date)	返回日期date的年,类型为int如year(‘2019-01-01’)=2019
month(date)	返回日期date的月,类型为int,如month(‘2019-01-01’)=1
day(date)	返回日期date的天,类型为int,如day(‘2019-01-01’)=1
weekofyear(date1)	返回日期date1位于该年第几周。如weekofyear(‘2019-03-06’)=10
datediff(date1,date2)	返回日期date1与date2相差的天数，如datediff(‘2019-03-06’,‘2019-03-05’)=1
date_add(date1,int1)	返回日期date1加上int1的日期,如date_add(‘2019-03-06’,1)=‘2019-03-07’
date_sub(date1,int1)	返回日期date1减去int1的日期,如date_sub(‘2019-03-06’,1)=‘2019-03-05’
months_between(date1,date2)	返回date1与date2相差月份,如months_between(‘2019-03-06’,‘2019-01-01’)=2
add_months(date1,int1)	返回date1加上int1个月的日期，int1可为负数。如add_months(‘2019-02-11’,-1)=‘2019-01-11’
last_day(date1)	返回date1所在月份最后一天。如last_day(‘2019-02-01’)=‘2019-02-28’
next_day(date1,day1)	返回日期date1的下个星期day1的日期。day1为星期X的英文前两字母如next_day(‘2019-03-06’,‘MO’) 返回’2019-03-11’
trunc(date1,string1)	返回日期最开始年份或月份。string1可为年(YYYY/YY/YEAR)或月(MONTH/MON/MM)。如trunc(‘2019-03-06’,‘MM’)=‘2019-03-01’，trunc(‘2019-03-06’,‘YYYY’)=‘2019-01-01’
unix_timestamp()	返回当前时间的unix时间戳，可指定日期格式。如unix_timestamp(‘2019-03-06’,‘yyyy-mm-dd’)=1546704180
from_unixtime()	返回unix时间戳的日期，可指定格式。如select from_unixtime(unix_timestamp(‘2019-03-06’,‘yyyy-mm-dd’),‘yyyymmdd’)=‘20190306’

③条件函数

函数名称	说明
if(boolean,t1,t2)	若布尔值成立，则返回t1,反正返回t2。如if(1>2,100,200)返回200
case when boolean then t1 else t2 end	若布尔值成立，则t1,否则t2,可加多重判断
coalesce(v0,v1,v2)	返回参数中的第一个非空值,若所有值均为null,则返回null。如coalesce(null,1,2)返回1
isnull(a)	若a为null则返回true，否则返回false

④字符串函数

函数名称	说明
length(string1)	返回字符串长度
concat(string1,string2)	返回拼接string1及string2后的字符串
concat_ws(sep,string1,string2)	返回按指定分隔符拼接的字符串
lower(string1)	返回小写字符串
upper(string1)	返回大写字符串
trim(string1)	去字符串左右空格
split(string1,pat1)	以pat1字符分隔字符串string1,返回数组。如split(‘a,b,c’,’,’)返回[“a”,“b”,“c”]
substr(string1,index1,int1)	以index位置起截取int1个字符。如substr(‘abcde’,1,2)返回’ab’

5、HQL解析json格式数据

①解析json单个字符项

get_json_object

• 语法：get_json_object(json_string, '$.key')
• 说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。这个函数每次只能返回一个数据项。
• 示例：

select 
get_json_object('{"name":"zhangsan","age":18}','$.name'); 

• 结果：

name
zhangsan

如果既要解析name字段，也解析age字段，则可以这样写：

select 
get_json_object('{"name":"zhangsan","age":18}','$.name'),
get_json_object('{"name":"zhangsan","age":18}','$.age');

①解析json多个字符项

json_tuple

• 语法： json_tuple(json_string, k1, k2 ...)
• 说明：解析json的字符串json_string,可指定多个json数据中的key，返回对应的value。如果输入的json字符串无效，那么返回NULL。
• 示例：

select 
b.name
,b.age
from tableName a lateral view
json_tuple('{"name":"zhangsan","age":18}','name','age') b as name,age;

• 结果:

name	age
zhangsan	18

注意：上面的json_tuple函数中没有$.

如果在使用json_tuple函数时加上$.就会解析失败：

select 
b.name
,b.age
from tableName a lateral view
json_tuple('{"name":"zhangsan","age":18}','$.name','$.age') b as name,age;

结果:

name	age
NULL	NULL

字段全是NULL，所以json_tuple函数不需要加$.了，否则会解析不到。

③解析jsonb格式数据

如果有一个hive表，表中 json_str 字段的内容如下：

json_str
[{“website”:“baidu.com”,“name”:“百度”},{“website”:“google.com”,“name”:“谷歌”}]

我们想把这个字段解析出来，形成如下的结构：

website	name
baidu.com	百度
google.com	谷歌

要解析这个json数组，仅用上面介绍的两个函数就解析不出来了：

此时可以使用子查询通过explode将json数组转换为jsoin再使用上面介绍的两个函数进行解析

select
json_tuple(a.t,'website','name') b as website,name
from (
	select explode('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]') 
as json) t
) a

六、HIVE数据仓库

1、数据仓库搭建步骤

①用户行为数据采集平台搭建
②业务数据采集平台搭建
③数据仓库维度建模
④采用即席查询工具，随时进行指标分析
⑤对集群性能进行监控，发生异常需要报警

2、技术选型

数据采集传输:Flume，Kafka，Sqoop
数据存储: MySql，HDFS
数据计算:Hive，Spark
数据查询: Presto，Kylin
数据可视化:Superset
任务调度: Azkaban
集群监控: Zabbix

3、数据流图

4、数据分层

ODS（原始数据层）：原始数据层，存放原始数据，直接加载原始日志、数据，数据保持原貌不做处理。

DWD层（明细数据层）:对ODS层数据进行清洗(去除空值，脏数据，超过极限范围的数据)、脱敏等。保存明细数据，一行信息代表一次业务行为， 例如一次下单。

DWS层（服务数据层）：以DWD为基础，对数据进行轻度汇总。如一行信息代表一个主题对象一天的汇总行为， 例如一个用户一天下单次数

DWT层（数据主题层）：以DWS为基础，对数据进行累积汇总。如一行信息代表一个主题对象的累积行为，例如一个用户从注册那天开始至今-共下了多少次单

ADS层（数据应用层）：前端应用直接读取的数据源；根据报表、专题分析需求而计算生成的数据。

ODS层是原始数据层也可被称为接口层

中间三层也被称为数据仓库层，每一层都是对上一层的累积和汇总。一般都是对数据关联的日期进行拆分，使得其更具体的分类，如拆分成年、月、日等，主要是明细数据，除此之外还有一些基于中间层数据统计出来的汇总数据

数据仓库从各数据源获取数据及在数据仓库内的数据转换和流动都可以认为是ETL（抽取Extra, 转化Transfer, 装载Load）的过程，ETL是数据仓库的流水线，也可以认为是数据仓库的血液，它维系着数据仓库中数据的新陈代谢，而数据仓库日常的管理和维护工作的大部分精力就是保持ETL的正常和稳定。

建设数据仓库犹如创造一条新的生命，分层架构只是这条生命的逻辑骨架而已。想要在骨架上长出血肉，就必须进行合适的数据建模，数据仓库的强壮还是孱弱，健美还是丑陋，就取决于建模的结果。

5、数据分层的优点

➢1)把复杂问题简单化 将复杂的任务分解成多层来完成，每一层只处理简单的任务，方便定位问题。

➢2) 减少重复开发 规范数据分层，通过的中间层数据，能够减少极大的重复计算，增加一次计算结果的复用性。

➢3) 隔离原始数据 不论是数据的异常还是数据的敏感性，使真实数据与统计数据解耦开。

➢4)用空间换时间 通过大量的预处理来提升应用系统的用户体验（效率），因此数据仓库会存在大量冗余的数据。

➢5)通过数据分层管理可以简化数据清洗的过程 因为把原来一步的工作分到了多个步骤去完成，相当于把一个复杂的工作拆成了多个简单的工作，把一个大的黑盒变成了一个白盒，每一层的处理逻辑都相对简单和容易理解，这样我们比较容易保证每一个步骤的正确性，当数据发生错误的时候，往往我们只需要局部调整某个步骤即可。

6、维度建模

①事实表和维度表

**事实表（Fact Table）**是指存储有事实记录的表，如系统的日志、销售记录、用户访问日志等信息，事实表的记录是动态的增长的，所以体积是大于维度表。

如：用户访问日志（事实表）：用户名、url、时间…

维度表（Dimension Table）也称为查找表（Lookup Table）是与事实表相对应的表，这个表保存了维度的属性值，可以跟事实表做关联，相当于是将事实表中经常重复的数据抽取、规范出来用一张表管理，常见的有日期（日、周、月、季度等属性）、地区表等，所以维度表的变化通常不会太大。

常见的维度表有：地市维表、区县维表、资费维表、活动维表、渠道维表等