Hive的HQL语句及数据倾斜解决方案
[版权申明：本文系作者原创，转载请注明出处]
文章出处：http://blog.csdn.net/sdksdk0/article/details/51675005
作者： 朱培 ID：sdksdk0
Hive环境的搭建在这里也不重复说了，安装配置可以查看我的这篇文章：http://blog.csdn.net/sdksdk0/article/details/51512031。在这里主要是分享一下HQL语句实践及其函数的基本使用。
一、Hive的基本概念
在Hive中没有插入操作，但是可以通过load data批量导入数据文件。
Hive中分为内部表和外部表。
内部表：表数据存放在统一的/user/hive/warehouse目录下； drop表时会将表的数据及表的元信息全部清空。 外部表：表数据可以在hdfs的任意目录，没有统一约束； drop时只清除表的元信息，表的数据文件不会改变。
hive只能带等值的条件，不能有>或<的条件。具体的在数据操作部分会做详细介绍。
创建一个内部表：
CREATE TABLE page_view(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
STORED AS SEQUENCEFILE; 创建好之后的位置在hdfs位置中的/user/hive/warehouse目录下：
外部表的创建：
CREATE EXTERNAL TABLE tab_ip_ext(id int, name string,
ip STRING,
country STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/external/hive';其实就是加一个external关键字，还需要用Location关键字指定自定义的数据存放位置。
把ip.data.1这个文件上传到这个外部表中：
load data local inpath '/home/admin1/hadoop/lx/ip.data.1' into table hive;
在hdfs的目录是：/external/hive
二、Hive的分区
HIVE的分区通过在创建表时启用partitionby实现，用来partition的维度并不是实际数据的某一列，具体分区的标志是由插入内容时给定的。当要查询某一分区的内容时可以采用where语句，形似where tablename.partition_key >a来实现。
hive中的分区 就是再多建一个目录， 优点：便于统计，效率更高，缩小数据集。
首先我们可以做一个用户的IP区域划分的一个案例来说明这Hive。
首先我们可以建立两个数据源ip.china和ip.japan。
ip.china的内容如下:
1,张三,192.168.1.1,china,
2,李四,192.168.1.2,china,
3,王五,192.168.1.3,china,
4,makjon,192.168.1.4,china
ip.japan的内容如下：
1,aa,192.168.1.1,jpana
2,bb,192.168.1.2,jpana
3,cc,92.168.1.3,jpana
4,makjon,192.168.1.4,jpana
然后我们到bin/hive启动后的hive中去把这两个源文件添加到前面建的外部表中去，
load data local input '/home/admin1/hadoop/lx/ip.china' overwrite into table tab_ip_part partition(nation='china'); load data local input '/home/admin1/hadoop/lx/ip.japan' overwrite into table tab_ip_part partition(nation='japan');
然后执行查询操作;
select count(*) from tab_ip_part where nation='china';
在执行这种语句的时候其实是在执行了一个mapreduce.
三、Hive的基本操作
create table student(Sno int,Sname string,Sex string,Sage int,Sdept string)row format delimited fields terminated by ','stored as textfile; create table course(Cno int,Cname string) row format delimited fields terminated by ',' stored as textfile; create table sc(Sno int,Cno int,Grade int)row format delimited fields terminated by ',' stored as textfile;
student的内容是： students.txt
95002,刘晨,女,19,IS
95003,王敏,女,22,MA
95004,张立,男,19,IS
95005,刘刚,男,18,MA
95006,孙庆,男,23,CS
95007,易思玲,女,19,MA
95008,李娜,女,18,CS
95009,梦圆圆,女,18,MA
95010,孔小涛,男,19,CS
95011,包小柏,男,18,MA
95012,孙花,女,20,CS
95013,冯伟,男,21,CS
95014,王小丽,女,19,CS
95015,王君,男,18,MA
95016,钱国,男,21,MA
95017,王风娟,女,18,IS
95018,王一,女,19,IS
95019,邢小丽,女,19,IS
95020,赵钱,男,21,IS
95021,周二,男,17,MA
95022,郑明,男,20,MA
2,数学
3,信息系统
4,操作系统
5,数据结构
6,数据处理
95001,2,85
95001,3,88
95001,4,70
95002,2,90
95002,3,80
95002,4,71
95002,5,60
95003,1,82
95003,3,90
95003,5,100
95004,1,80
95004,2,92
95004,4,91
95004,5,70
95005,1,70
95005,2,92
95005,3,99
95005,6,87
95006,1,72
95006,2,62
95006,3,100
95006,4,59
95006,5,60
95006,6,98
95007,3,68
95007,4,91
95007,5,94
95007,6,78
95008,1,98
95008,3,89
95008,6,91
95009,2,81
95009,4,89
95009,6,100
95010,2,98
95010,5,90
95010,6,80
95011,1,81
95011,2,91
95011,3,81
95011,4,86
95012,1,81
95012,3,78
95012,4,85
95012,6,98
95013,1,98
95013,2,58
95013,4,88
95013,5,93
95014,1,91
95014,2,100
95014,4,98
95015,1,91
95015,3,59
95015,4,100
95015,6,95
95016,1,92
95016,2,99
95016,4,82
95017,4,82
95017,5,100
95017,6,58
95018,1,95
95018,2,100
95018,3,67
95018,4,78
95019,1,77
95019,2,90
95019,3,91
95019,4,67
95019,5,87
95020,1,66
95020,2,99
95020,5,93
95021,2,93
95021,5,91
95021,6,99
95022,3,69
95022,4,93
95022,5,82
95022,6,100
load data local inpath '/home/admin1/hadoop/lx/students.txt' overwrite into table student; load data local inpath '/home/admin1/hadoop/lx/sc.txt' overwrite into table sc; load data local inpath '/home/admin1/hadoop/lx/course.txt' overwrite into table course;
然后就是一些增删改查的练习了：
hive> select Sno,Sname from student;
查询选修了课程的学生姓名
hive> select distinct Sname from student inner join sc on student.Sno=Sc.Sno;
----hive的group by 和集合函数
查询学生的总人数
hive> select count(distinct Sno)count from student;
计算1号课程的学生平均成绩
hive> select avg(distinct Grade) from sc where Cno=1;
查询各科成绩平均分
hive> select Cno,avg(Grade) from sc group by Cno;
查询选修1号课程的学生最高分数
select Grade from sc where Cno=1 sort by Grade desc limit 1;
求各个课程号及相应的选课人数
hive> select Cno,count(1) from sc group by Cno;
查询选修了3门以上的课程的学生学号
hive> select Sno from (select Sno,count(Cno) CountCno from sc group by Sno)a where a.CountCno>3;
或 hive> select Sno from sc group by Sno having count(Cno)>3;
----hive的Order By/Sort By/Distribute By
Order By ，在strict 模式下（hive.mapred.mode=strict),order by 语句必须跟着limit语句，但是在非strict下就不是必须的，这样做的理由是必须有一个reduce对最终的结果进行排序，如果最后输出的行数过多，一个reduce需要花费很长的时间。
查询学生信息，结果按学号全局有序
hive> set hive.mapred.mode=strict;
hive> select Sno from student order by Sno;
FAILED: Error in semantic analysis: 1:33 In strict mode, if ORDER BY is specified, LIMIT must also be specified. Error encountered near token 'Sno'
Sort By，它通常发生在每一个redcue里，“order by” 和“sort by"的区别在于，前者能给保证输出都是有顺序的，而后者如果有多个reduce的时候只是保证了输出的部分有序。set mapred.reduce.tasks=<number>在sort by可以指定，在用sort by的时候，如果没有指定列，它会随机的分配到不同的reduce里去。distribute by 按照指定的字段对数据进行划分到不同的输出reduce中
此方法会根据性别划分到不同的reduce中 ，然后按年龄排序并输出到不同的文件中。
查询学生信息，结果区分性别按年龄有序
hive> set mapred.reduce.tasks=2;
hive> insert overwrite local directory '/home/hadoop/out'
select * from student distribute by Sex sort by Sage;
----Join查询,join只支持等值连接
查询每个学生及其选修课程的情况
hive> select student.*,sc.* from student join sc on (student.Sno =sc.Sno);
查询学生的得分情况。
hive>select student.Sname,course.Cname,sc.Grade from student join sc on student.Sno=sc.Sno join course on sc.cno=course.cno;
查询选修2号课程且成绩在90分以上的所有学生。
hive> select student.Sname,sc.Grade from student join sc on student.Sno=sc.Sno
where sc.Cno=2 and sc.Grade>90;
----LEFT，RIGHT 和 FULL OUTER JOIN
查询所有学生的信息，如果在成绩表中有成绩，则输出成绩表中的课程号
hive> select student.Sname,sc.Cno from student left outer join sc on student.Sno=sc.Sno;
如果student的sno值对应的sc在中没有值，则会输出student.Sname null.如果用right out join会保留右边的值，左边的为null。
Join 发生在WHERE 子句之前。如果你想限制 join 的输出，应该在 WHERE 子句中写过滤条件——或是在join 子句中写。
----LEFT SEMI JOIN Hive 当前没有实现 IN/EXISTS 子查询，可以用 LEFT SEMI JOIN 重写子查询语句
重写以下子查询为LEFT SEMI JOIN
SELECT a.key, a.value
FROM a
WHERE a.key exist in
(SELECT b.key
FROM B);
可以被重写为：
SELECT a.key, a.val
FROM a LEFT SEMI JOIN b on (a.key = b.key)
查询与“刘晨”在同一个系学习的学生
hive> select s1.Sname from student s1 left semi join student s2 on s1.Sdept=s2.Sdept and s2.Sname='刘晨';
四、自定义函数
1515566005,http://www.163.com,20,3009
1385566005,http://www.163.com,20,200
1389990045,http://www.163.com,2000,3900
1370876045,http://www.163.com,2000,5690
package cn.tf.hive;
import java.util.HashMap;
import org.apache.hadoop.hive.ql.exec.UDF;
public class ConvertUDF extends UDF{
private static HashMap<String,String> provinceMap=new HashMap<String,String>();
static{
provinceMap.put("137", "北京");
provinceMap.put("138", "上海");
provinceMap.put("139", "天津");
provinceMap.put("151", "深圳");
}
public String evaluate(String phone){
String gsd=provinceMap.get(phone.substring(0,3));
if(gsd==null){
gsd="other";
}
return gsd;
}
public long evaluate(long upflow,long downflow){
return (upflow+downflow);
}
}
自定义函数调用过程：
1.添加jar包（在hive命令行里面执行）
hive> add jar /home/admin1/hadoop/lx/udf.jar;
hive>load data local inpath '/home/admin1/hadoop/lx/flow.txt' into table t_flow;
hive>CREATE TEMPORARY FUNCTION convert AS 'cn.tf.hive.ConvertUDF';
3、建表：
hive> create table t_flow(phone string,url string,upflow int,downflow int)
> row format delimited
> fields terminated by ',';4、执行函数查询结果
select phone,convert(phone),url,convert(upflow,downflow) from t_flow;
5、保存查询出的结果
hive> create table t_flow_result
> as
> select phone,convert(phone),url,convert(upflow,downflow) from t_flow;五、Hive中的数据倾斜
5.1 空值数据倾斜
场景：如日志中，常会有信息丢失的问题，比如全网日志中的user_id，如果取其中的user_id和bmw_users关联，会碰到数据倾斜的问题。
解决方法1： user_id为空的不参与关联
Select * From log a Join bmw_users b On a.user_id is not null And a.user_id = b.user_id Union all Select * from log a where a.user_id is null;
解决方法2 ：赋与空值分新的key值
Select * from log a left outer join bmw_users b on case when a.user_id is null thenconcat(‘dp_hive’,rand() ) else a.user_id end = b.user_id;
结论：方法2比方法效率更好，不但io少了，而且作业数也少了。方法1 log读取两次，jobs是2。方法2 job数是1 。这个优化适合无效id(比如-99,’’,null等)产生的倾斜问题。把空值的key变成一个字符串加上随机数，就能把倾斜的数据分到不同的reduce上 ,解决数据倾斜问题。附上hadoop通用关联的实现方法（关联通过二次排序实现的，关联的列为parition key,关联的列c1和表的tag组成排序的group key,根据parition key分配reduce。同一reduce内根据group key排序）
5.2 不同数据类型关联产生数据倾斜
场景：一张表s8的日志，每个商品一条记录，要和商品表关联。但关联却碰到倾斜的问题。s8的日志中有字符串商品id,也有数字的商品id,类型是string的，但商品中的数字id是bigint的。猜测问题的原因是把s8的商品id转成数字id做hash来分配reduce，所以字符串id的s8日志，都到一个reduce上了，解决的方法验证了这个猜测。
解决方法：把数字类型转换成字符串类型
Select * from s8_log a Left outer join r_auction_auctions b On a.auction_id = cast(b.auction_id asstring);
5.3 Join的数据偏斜
MapReduce编程模型下开发代码需要考虑数据偏斜的问题，Hive代码也是一样。数据偏斜的原因包括以下两点：
1. Map输出key数量极少，导致reduce端退化为单机作业。
2. Map输出key分布不均，少量key对应大量value，导致reduce端单机瓶颈。
Hive中我们使用MapJoin解决数据偏斜的问题，即将其中的某个表（全量）分发到所有Map端进行Join，从而避免了reduce。这要求分发的表可以被全量载入内存。
极限情况下，Join两边的表都是大表，就无法使用MapJoin。
这种问题最为棘手，目前已知的解决思路有两种：
1. 如果是上述情况1，考虑先对Join中的一个表去重，以此结果过滤无用信息。这样一般会将其中一个大表转化为小表，再使用MapJoin 。
一个实例是广告投放效果分析，例如将广告投放者信息表i中的信息填充到广告曝光日志表w中，使用投放者id关联。因为实际广告投放者数量很少（但是投放者信息表i很大），因此可以考虑先在w表中去重查询所有实际广告投放者id列表，以此Join过滤表i，这一结果必然是一个小表，就可以使用MapJoin。
2. 如果是上述情况2，考虑切分Join中的一个表为多片，以便将切片全部载入内存，然后采用多次MapJoin得到结果。
一个实例是商品浏览日志分析，例如将商品信息表i中的信息填充到商品浏览日志表w中，使用商品id关联。但是某些热卖商品浏览量很大，造成数据偏斜。例如，以下语句实现了一个inner join逻辑，将商品信息表拆分成2个表：
select * from ( select w.id, w.time, w.amount, i1.name, i1.loc, i1.cat from w left outer join i sampletable(1 out of 2 on id) i1 ) union all ( select w.id, w.time, w.amount, i2.name, i2.loc, i2.cat from w left outer join i sampletable(1 out of 2 on id) i2 ) );
以下语句实现了left outer join逻辑：
select t1.id, t1.time, t1.amount,
coalease(t1.name, t2.name),
coalease(t1.loc, t2.loc),
coalease(t1.cat, t2.cat)
from (
select w.id, w.time, w.amount, i1.name, i1.loc, i1.cat
from w left outer join i sampletable(1 out of 2 on id) i1
) t1 left outer join i sampletable(2 out of 2 on id) t2;
上述语句使用Hive的sample table特性对表做切分。
