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;
load data local inpath '/home/sc.txt' overwrite into table sc;
load data local inpath '/home/course.txt' overwrite into table course;
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
1,数据库
2,数学
3,信息系统
4,操作系统
5,数据结构
6,数据处理
95001,1,81
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
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
hive.map.aggr控制我们怎样去聚合,默认值为false,如果设置为ture后,会在map任务里执行第一级别的聚合,通常这样会有 高的效率,但是需要更大的内存。
- 查询学生的总人数
Total MapReduce jobs = 1
- 计算1号课程的学生平均成绩
hive> select avg(distinct Grade) from sc where Cno=1;
- 查询选修1号课程的学生最高分数
- 求各个课程号及相应的选课人数
Total MapReduce jobs = 1
- 查询选修了3门以上的课程的学生学号
Total MapReduce jobs = 1
Total MapReduce jobs = 1
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'
- join只支持等值连接 e.g.
- 如果join on的key值不是相同的话,会转化为2个map/reduc e.g. 如果key相同的话会转化为一个map/reduce job,由于第一个job的map/reduce的结果会缓冲起来,然后再跟第二个进行join,所以为了减少内存,必须把量少的放在第一个join上。
- LEFT,RIGHT和FULL OUTER关键字用于处理join中空记录的情况。
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)
- LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现。Hive 当前没有实现 IN/EXISTS 子查询,所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN 的限制是, JOIN 子句中右边的表只能在 ON 子句中设置过滤条件,在 WHERE 子句、SELECT 子句或其他地方过滤都不行。
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
- 练习:
- 好的模型设计事半功倍。
- 解决数据倾斜问题。
- 减少job数。
- 设置合理的map reduce的task数,能有效提升性能。(比如,10w+级别的计算,用160个reduce,那是相当的浪费,1个足够)。
- 了解数据分布,自己动手解决数据倾斜问题是个不错的选择。set hive.groupby.skewindata=true;这是通用的算法优化,但算法优化有时不能适应特定业务背景,开发人员了解业务,了解数据,可以通过业务逻辑精确有效的解决数据倾斜问题。
- 数据量较大的情况下,慎用count(distinct),count(distinct)容易产生倾斜问题。
- 对小文件进行合并,是行至有效的提高调度效率的方法,假如所有的作业设置合理的文件数,对云梯的整体调度效率也会产生积极的正向影响。
- 优化时把握整体,单个作业最优不如整体最优。