hive(0.11版本)里面关于sort by/group by/row_number等内置函数实例

1、在hive中创建以下三个表。

use test;
drop table if exists tab_demo_student;
CREATE TABLE IF NOT EXISTS tab_demo_student(
Sno int,
Sname string,
Sex string,
Sage int,
Sdept string)
row format delimited fields terminated by ','stored as textfile;

use test;
drop table if exists tab_demo_course;
CREATE TABLE IF NOT EXISTS tab_demo_course(
Cno int,
Cname string) 
row format delimited fields terminated by ',' stored as textfile;

use test;
drop table if exists tab_demo_sc;
CREATE TABLE IF NOT EXISTS tab_demo_sc
(Sno int,Cno int,Grade int) 
row format delimited fields terminated by ',' stored as textfile;

2、load数据到三个表中。

load data local inpath '/home/student.txt' overwrite into table test.tab_demo_student;
load data local inpath '/home/sc.txt' overwrite into table test.tab_demo_sc;
load data local inpath '/home/course.txt' overwrite into table test.tab_demo_course;

注意：以上的文本数据想要把本地Windows上的文件上传至Linux服务器上的时候，注意要将文本文件以utf-8的格式进行保存，否则的话，在Linux上显示的文本将会出现乱码，hive表里面的对应数据也会出现乱码。

3、hive的select

SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ] [LIMIT number]

• 查询全体学生的学号与姓名

hive> select sno,sname from student;
Total MapReduce jobs = 1

• 查询选修了课程的学生姓名

hive> select distinct sname from student inner join sc on student.sno=sc.sno;
Total MapReduce jobs = 2

3、hive的group by 和集合函数

在一个query里可以出现多个聚合函数，但是一个query语句里2个聚合函数只能有一个distinct。

hive.map.aggr控制我们怎样去聚合，默认值为false，如果设置为ture后，会在map任务里执行第一级别的聚合，通常这样会有 高的效率，但是需要更大的内存。

• 查询学生的总人数

hive> select count(distinct sno)count from student;
Total MapReduce jobs = 1

• 计算1号课程的学生平均成绩

hive> select avg(distinct Grade) from sc where sno=1;

• 查询选修1号课程的学生最高分数

  select Grade from sc where Cno=1 sort by Grade desc limit 1;      

Total MapReduce jobs = 2      

OK
grade
98

• 求各个课程号及相应的选课人数 

hive> select Cno,count(1) from sc group by Cno;
Total MapReduce jobs = 1

•  查询选修了3门以上的课程的学生学号

hive>select sno form (select sno,count(cno) as countcno from test.tab_demo_sc group by sno) a where a.countcno>3;
Total MapReduce jobs = 1

OK
sno
95002
95004
95005
95006
95007
95011
95012
95013
95015
95018
95019
95022

hive> select sno from test.tab_demo_sc group by sno having count(cno)>3;
Total MapReduce jobs = 1

OK
sno
95002
95004
95005
95006
95007
95011
95012
95013
95015
95018
95019
95022

hive> select sno,count(cno) as count_cno from test.tab_demo_sc group by sno having count_cno>3;
Total MapReduce jobs = 1
Launching Job 1 out of 1

OK
sno     count_cno
95002   4
95004   4
95005   4
95006   6
95007   4
95011   4
95012   4
95013   4
95015   4
95018   4
95019   5
95022   4

4、hive的Order By/Sort By/Distribute By/Cluster By

Order By ，在strict 模式下（hive.mapred.mode=strict， 默认是nonstrict),order by 语句必须跟着limit语句，但是在非strict下就不是必须的，这样做的理由是必须有一个reduce对最终的结果进行排序，如果最后输出的行数过多，一个reduce需要花费很长的时间。

hive> set hive.mapred.mode=strict;
hive> select sno from test.tab_demo_student order by sno;
FAILED: SemanticException 1:47 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里去。

hive>set mapred.reduce.tasks=4;

hive> select sno from test.tab_demo_student sort by sno;

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;

sno     sname   sex     sage    sdept
95021   周二    男      17      MA
95008   李娜    女      18      CS
95017   王风娟  女      18      IS
95015   王君    男      18      MA
95005   刘刚    男      18      MA
95011   包小柏  男      18      MA
95009   梦圆圆  女      18      MA
95007   易思玲  女      19      MA
95014   王小丽  女      19      CS
95002   刘晨    女      19      IS
95019   邢小丽  女      19      IS
95018   王一    女      19      IS
95010   孔小涛  男      19      CS
95004   张立    男      19      IS
95022   郑明    男      20      MA
95012   孙花    女      20      CS
NULL    李勇    男      20      CS
95013   冯伟    男      21      CS
95016   钱国    男      21      MA
95020   赵钱    男      21      IS
95003   王敏    女      22      MA
95006   孙庆    男      23      CS

Cluster By  能保证分配给同一个reduce的相同的列邻近，除 distribute by 除完成的功能外。

5、Join

• join只支持等值连接 e.g.

查询每个学生及其选修课程的情况

• 如果join on的key值不是相同的话，会转化为2个map/reduc e.g. 如果key相同的话会转化为一个map/reduce job，由于第一个job的map/reduce的结果会缓冲起来，然后再跟第二个进行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;

Total MapReduce jobs = 2

• 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 子句中写。这里面一个容易混淆的问题是表分区的情况：

  SELECT a.val, b.val FROM a

  LEFT OUTER JOIN b ON (a.key=b.key)

  WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

会 join a 表到 b 表（OUTER JOIN），列出 a.val 和 b.val 的记录。WHERE 从句中可以使用其他列作为过滤条件。但是，如前所述，如果 b 表中找不到对应 a 表的记录，b 表的所有列都会列出NULL，包括 ds 列。也就是说，join 会过滤 b 表中不能找到匹配 a 表 join key 的所有记录。这样的话，LEFT OUTER 就使得查询结果与 WHERE 子句无关了。解决的办法是在 OUTER JOIN 时使用以下语法：

  SELECT a.val, b.val FROM a LEFT OUTER JOIN b

  ON (a.key=b.key AND

      b.ds='2009-07-07' AND

      a.ds='2009-07-07')

这一查询的结果是预先在 join 阶段过滤过的，所以不会存在上述问题。这一逻辑也可以应用于 RIGHT 和 FULL 类型的 join 中。

Join 是不能交换位置的。无论是 LEFT 还是 RIGHT join，都是左连接的。

  SELECT a.val1, a.val2, b.val, c.val

  FROM a

  JOIN b ON (a.key = b.key)

  LEFT OUTER JOIN c ON (a.key = c.key)

先 join a 表到 b 表，丢弃掉所有join key 中不匹配的记录，然后用这一中间结果和 c 表做 join。这一表述有一个不太明显的问题，就是当一个 key 在 a 表和 c 表都存在，但是 b 表中不存在的时候：整个记录在第一次 join，即 a JOIN b 的时候都被丢掉了（包括a.val1，a.val2和a.key），然后我们再和 c 表 join 的时候，如果c.key 与 a.key 或 b.key 相等，就会得到这样的结果：NULL, NULL, NULL, c.val。

• LEFT SEMI JOIN  是 IN/EXISTS 子查询的一种更高效的实现。Hive 当前没有实现 IN/EXISTS 子查询，所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。

  SELECT a.key, a.value
  FROM a
  WHERE a.key in
   (SELECT b.key
    FROM B);
可以被重写为：
   SELECT a.key, a.val
   FROM a LEFT SEMI JOIN b on (a.key = b.key)

• 如果2个join的表非常小的话，join的过程可能只在mapper中就可以完成。
• 如果有两个表join on的值为buckets列，并且buckets数量相同的话，可以只在mapper阶段完成。e.g.

 SELECT /*+ MAPJOIN(b) */ a.key, a.value
  FROM a join b on a.key = b.key

在mapper阶段，可以对b的值按buckets进行取值，a表的一个buckets取b表的一个buckets进行join，这样做并不是模式的，必须设置。

set hive.optimize.bucketmapjoin = true

• 练习：

查询选修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;

查询与“刘晨”在同一个系学习的学生

hive> select s1.Sname from student s1 left semi join student s2 on s1.Sdept=s2.Sdept and s2.Sname='刘晨';

6、hive的优化

• 好的模型设计事半功倍。
• 解决数据倾斜问题。
• 减少job数。
• 设置合理的map reduce的task数，能有效提升性能。(比如，10w+级别的计算，用160个reduce，那是相当的浪费，1个足够)。
• 了解数据分布，自己动手解决数据倾斜问题是个不错的选择。set hive.groupby.skewindata=true;这是通用的算法优化，但算法优化有时不能适应特定业务背景，开发人员了解业务，了解数据，可以通过业务逻辑精确有效的解决数据倾斜问题。
• 数据量较大的情况下，慎用count(distinct)，count(distinct)容易产生倾斜问题。
• 对小文件进行合并，是行至有效的提高调度效率的方法，假如所有的作业设置合理的文件数，对云梯的整体调度效率也会产生积极的正向影响。
• 优化时把握整体，单个作业最优不如整体最优。