HIVE是互联网数据分析从业者必须掌握的技能,也是数据分析师招聘的最基本要求。本文将会分享自己关于HIVE需要掌握知识点的汇总,对于数据分析师或者想要学习HIVE的朋友,掌握这些知识点就能满足各互联网公司对SQL或者HIVE的要求。需要注意到的是,文中很多知识点没有做太多细节讲解,更多偏向于重要知识点的汇总。
一、HQL基本书写
1.1表的增删改查
删除表:DROP TABLE IF EXISTS XXX;
修改表:ALTER TABLE ADD/CHANGE/REPLACE XXX;不可以直接删除表某一列,用REPLACE来实现。
查询表:HQL数据查询很简单,学过HQL的基本都会的,但是难点在于搞不清楚查询的先后顺序是怎么样的。
SELECT —–输出数据
FROM table_name —–输入目录
WHERE —–过滤条件
GROUP BY —–分组
HAVING —–过滤条件(聚合后的结果)
ORDER BY —–排序
LIMIT —–限制输出行数
1.2表的连接
内连接:JOIN
左连接:LEFT JOIN
右连接:RIGHT JOIN
外链接:FULL OUTER JOIN
具体不赘述,记住左表和右表连接的时候只能等号连接,不能用非等号连接
1.3表的子查询
目前HQL是支持子查询IN的;但是不支持NOT IN,而是用NOT EXISTS
1.4常用函数
一般主要分为以下几大类,但因为涉及到的函数太多,不做列举(个人有一份hive函数大全,有需要可私信我)
- 时间处理函数
- 字符串处理函数
- 数值处理函数
- 条件处理函数
- 集合统计函数
- 复杂类型访问和统计函数
二、HQL窗口函数
所谓窗口函数,是分为窗口和函数两部分。窗口就是由partition by划分出来的范围,函数就是求和、计数等作用于窗口范围内数据的函数。所以很容易看出窗口函数和普通函数的区别主要在:函数作用的范围。
2.1序号函数
可以输出在XX列的分组窗口下,按照YY列排序的排序序号;主要用于Top N问题的计算,如:计算每个用户消费金额最大的2笔订单,XX就是用户,YY就是消费金额。
相同值不同的排序值(1、2、3、4):
row_number () over (partition by XX orer by YY desc)
相同值一样的排序值,排名相等会在名次中留下空位(1、1、3、4):
rank() over (partition by XX order by YY desc)
相同值一样的排序值,排名相等会在名次中不会留下空位(1、1、2、3):
dense_rank() over(partition by XX order by YY desc)
2.2前后函数
可以实现在XX列的分组窗口下,按照YY列排序后,col列前(后)的第n个值;可以用于,如:员工本次和上次的薪水,XX就是员工ID,YY是发薪水时间,col列是薪水金额。
col列往下n行(前面的第n个值):
lag(col,n) over(partition by XX order by YY)
col列往上n行(后面的第n个值):
lead(col,n) over(partition by XX order by YY)
2.3头尾函数
可以实现在XX列的分组窗口下,按照YY列排序后,col列第一个(最后一个)值。
取分组内排序后,截止到当前行,第一个值:
first_value(col) over(partition by XX order by YY)
取分组内排序后,截止到当前行,最后一个值:
last_value(col) over() partition by XX order by YY
取分组内排序后,截止到当前行,第n个值:
nth_value(n) over() partition by XX order by YY
2.4分布函数
可以实现在XX列的分组窗口下,按照YY列排序后,小于等于当前值的行数占分组内总行数比例。一般用于计算累计占比。
cume_dist()over( partition by XX order by YY)
可以实现在XX列的分组窗口下,按照YY列排序后,小于当前值的行数占分组内总行数减去一的比例。这个函数用途比较少。
percent_rank()over( partition by XX order by YY)
2.5其他函数
可以实现在XX列的分组窗口下,按照YY列排序后,将数据平均切分成n片,返回当前记录所在的切片值。一般用于计算一定百分比间的值,如:所有店铺中,收益前30%店铺的平均收益。
ntlile(n) over( partition by XX order by YY)
三、HQL计算优化
HQL优化主要做好三点:
- 提前筛选数据,减少进入计算的数量量
- 避免数据倾斜
- join的时候,可使用mapjoin,将小表放在左边作驱动表
而实际数据分析查询计算中,最容易遇到也最需要优化的就是“数据倾斜”问题。曾优化HQL使得数据任务的计算时长由300分钟降到20分钟,所以掌握优化数据倾斜的方法非常非常重要。
数据倾斜是什么:
由于某个字段数据值或者函数作用后的数据值分布不均匀,导致在reduce阶段,某个rudece节点的数据量太大,计算时间非常久;而其他reudce数据量少计算快,计算完需要等所有节点计算完,任务才能完成。
数据倾斜的表现:
看执行日志,rudece的进度一直在99%。
数据倾斜的优化:
数据倾斜一般出现在join部分和group by部分;join主要是左右表某个表的关联key分布不均匀或者某些key的量特别大;
对于join时候数据倾斜,我一般会尝试以下几种优化方法:
- 有小表,优先用mapjoin
- join的key有null值,一律设置成字符串+随机数
- 若某一个key数据量异常大,可以将这个key的数据单独拿出来处理
- 若非常大的表和比较小的表join,且大表多个key的数据量都比较大;可以给大表以key+随机数(0-9)的方式构建新key;小表也添加新key(key+0-9)并且以不同的新key并(union all)10次;再左右两个表基于新的key来join。此方法非常非常有效,当一二三种方法解决不了倾斜问题可以尝试此方法。
对于group by数据倾斜,解决方法类似:
- 若某一个key数据量异常大,可以将这个key的数据单独拿出来处理
- 添加随机数把数据量集中的key打乱
- sum(1) group by 替换count(distinct XX)
四、HIVE其他小知识点
1、内部表和外部表的区别
内部表数据由Hive自身管理,外部表数据由HDFS管理;
删除内部表会直接删除元数据(metadata)及存储数据;删除外部表仅仅会删除元数据,HDFS上的文件并不会被删除;
对内部表的修改会将修改直接同步给元数据,而对外部表的表结构和分区进行修改,则需要修复(MSCK REPAIR TABLE table_name;)
2、分区表 VS 普通表
普通表:对全表的数据进行查询,然后再进行过滤操作分区表:
分区表:通过分区块直接加载对应路径下的数据,提高查询检索的效率。
4、order by 和 sort by
order by全局排序;
sort by在一个reduce上排序。
5、动态分区插入
添加参数:hive.exec.dynamic.partiton = true
可以同时使用多个字段的动态分区,也可以静态分区和动态分区同时使用(一般静态分区在前面)
6、复杂类型数据结构
HIVE有三类复杂类型数据结构map、array和struct,这个是mysql没有的。
以上,就是我想要分享的HIVE需要知道的知识点,希望这些内容能帮助你更系统的知道自己学习HIVE需要掌握哪些点。从而能针对性的学习或者应对面试准备。