HIve SQL学习笔记

HIve SQL学习笔记

最近面试的时候一直听到面试官说Hive SQL，今天刷了一点资料稍微系统的学习一下，在这里做一个小结。

首先介绍一下Hive SQL：

• Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为MapReduce任务运行。它使得针对Hadoop进行SQL查询成为可能。
• 简单来说，HiveSQL与我们之前见到的MySQL等关系型数据库在用法上是差不多的。

Hive SQL 与 SQL的区别：

• 1.HQL不支持行级别的增、改、删，所有数据在加载时就已经确定，不可更改
• 2.不支持事务
• 3.支持分区存储
• 4.查询速度响应不如SQL迅速

Hive SQL需要注意的地方：

• 1.Hive SQL不支持top函数，在Hive SQL中找前几的话可以使用limit函数
• 2.执行顺序与MySQL一致
• 3.日常处理中需要注意map数不要过万
• 4.Hive SQL不能很好的识别分号，当分号不是作为结束标志时，需要加转义字符/

Hive SQL在业务上：

• 1.Hive SQL专用函数 from_unixtime()、datediff()、date_add()、date_sub()
• 2.Hive SQL独有的窗口函数使用起来效率更高
• 3.union all 使用速度要比full join 速度更快，一般常使用unoin all +数字0占位来实现full join的功能；同时union all常搭配coalesce()函使用
• 4.业务中如果出现：存在某某表但不存在某某表当中时，考虑使用left join查询效率更高
• 5.当需要查询数据的前百分之多少数据时，考虑采用ntile()函数

Hive SQL常用技巧

• 1.去重时使用group by代替distinct能提升查询效率
• 2.在使用union all时可开启并发执行，参数设置为：set hive.exec.parallel=true

利用窗口函数进行多重分组：

• grouping sets（）：紧跟group by后进行多重分组的设置
• cube（）：根据group by 维度的所有组合进行聚合；使用方法：with cube 跟在group by后
• rollup（）：以最左侧的维度为主，进行层次聚合，例子：年月日的分类汇总；使用方法：with rollup 跟在group by后

行转列函数：

• lateral view 一般在使用行转列时，通常需要搭配lateral view explode（split （字段名称，‘分隔符’））；
• 若当字段里面的分隔符不一致时需要使用replace（）函数将其转化成一致的；
• 同理：列转行为：concat_ws（’,’,collect_set(字段名称)）

表连接的优化：

• 在内连接的情况下尽量做到小表在前大表在后，因为Hive SQL会默认将后面的表认为是大表并将其进行缓存；
• 尽量使用相同的连接键进行连接
• 尽早过滤数据

发生数据倾斜的原因及解决方法：

• 1.空值产生的数据倾斜： 由于在表连接的过程中连接键的某些值为空，此时Hive SQL会难以识别，如进程一直是99%无法查询出数据。
• 解决方案： on a.user_id = b.user_id and a.user_id is not null
• 2.大小表连接时出现数据倾斜： 这种情况是由于大小表的数据量过于悬殊
• 解决方案： SELECT /* + mapjoin(a)*/ （将小表放在内存里，在map里面做join即可，在括号中加入表名即可）
• 3.表连接条件的字段数据类型不一致
• 解决方案： on a.user_id = cast( b.iser_id as string );使用cast（）函数转换字段的数据类型

学习参考资料：

• https://www.bilibili.com/video/BV1uy4y1m7mf （b站这个开课吧老师的课很不错，推荐大家去听，有一些案例很贴近工作）
• 《Hive编程指南》