音信类别,信息种类

经过学生-课程关系表,熟习hive语句

一、在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;

贰、load数据到多少个表中。

load data local inpath ‘/home/student.txt’
overwrite into table student;
load data local inpath ‘/home/sc.txt’ overwrite into table sc;
load data local inpath ‘/home/course.txt’ overwrite into table
course;


95001,李勇,男,20,CS
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,数学
三,音讯种类
四,操作系统
5,数据结构
六,数据处理


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


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语句里一个聚合函数只可以有多个distinct。

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

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

  • 计量一号课程的学员平均成绩

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

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

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

Total MapReduce jobs = 2      

  • 求各类学科号及相应的选课人数 

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

图片 1

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

hive> select Sno from (select Sno,count(Cno)CountCno from sc group by
Sno)a where a.CountCno>3;
Total MapReduce jobs = 1

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

4、hive的Order By/Sort
By/Distribute By/Cluster 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,它1般发生在每贰个redcue里,“order by” 和“sort
by”的不一致在于,前者能给保障输出都是有各类的,而后者要是有四个reduce的时候只是保险了出口的1部分有序。set
mapred.reduce.tasks=<number>在sort by能够钦赐,在用sort
by的时候,假使未有钦点列,它会自由的分配到差异的reduce里去。

图片 2

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;

Cluster
By 能保险分配给同一个reduce的壹模1样的列周围,除distribute
by除形成的效果外。

5、Join

  • join只协助等值连接
    e.g.

询问每种学生及其选修课程的情况

  • 要是join
    on的key值不是如出1辙的话,会转化为一个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,福睿斯IGHT 和 FULL
    OUTE帕杰罗 关键字用于拍卖 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 表(OUTE大切诺基 JOIN),列出 a.val 和 b.val 的笔录。WHERE 从句中得以动用其余列作为过滤条件。可是,如前所述,要是 b 表中找不到相应 a 表的记录,b 表的全数列都会列出NULL,包括 ds 列。也等于说,join 会过滤 b 表中不可能找到相配 a 表 join
key 的有着记录。这样的话,LEFT
OUTE本田CR-V 就使得查询结果与 WHERE 子句非亲非故了。消除的不二等秘书籍是在 OUTE科雷傲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′)

那1询问的结果是优先在 join 阶段过滤过的,所以不会设有上述难点。那一逻辑也足以选取于 EnclaveIGHT 和 FULL 类型的 join 中。

Join 是不能够调换地点的。无论是 LEFT 照旧 QX56IGHT
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.val一,a.val二和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)

  • 万1二个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

  • 练习:

查询选修二号课程且战表在88分以上的具备学生。

hive> select student.Sname,sc.Grade from student join sc on
student.Sno=sc.Sno where  sc.Cno=2 and sc.Grade>90;

询问与“刘晨”在同3个系学习的学习者

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数,能使得进步质量。(比如,十w+级别的计量,用1陆十三个reduce,这是极度的荒废,二个10足)。
  • 打探数据分布,自身出手消除多少倾斜难点是个科学的选项。set hive.groupby.skewindata=true;那是通用的算法优化,但算法优化有时无法适应特定业务背景,开发职员驾驭事情,驾驭多少,能够透过业务逻辑精确有效的缓解数量倾斜难点。
  • 数据量较大的图景下,慎用count(distinct),count(distinct)不难生出倾斜难点。
  • 对小文件举办统壹,是行至有效的增加调度功能的法子,假设全数的作业设置合理的公文数,对云梯的完全调度功用也会时有爆发积极的正向影响。
  • 优化时把握总体,单个作业最优不及总体最优。

测试数据

course.txt
1,数据库
2,数学
3,消息种类
四,操作系统
5,数据结构
6,数据处理

sc.txt
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

students.txt
95001,李勇,男,20,CS
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

运用上面包车型大巴语句来成立表。

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;

选拔上面包车型地铁通令来导入数据:

load data local inpath ‘/root/sql_learn/students.txt’ overwrite into
table student;

load data local inpath ‘/root/sql_learn/sc.txt’ overwrite into table
sc;

load data local inpath ‘/root/sql_learn/course.txt’ overwrite into
table course;

询问全部学生的学号与姓名

select Sno,Sname from student;

查询选修了学科的上学的小孩子姓名

select distinct Sname from student inner join sc on(student.Sno=sc.Sno);

询问学生的总人数

select count(1) from student;

计算****1****号课程的学习者平均战表

select avg(Grade) from sc group by Cno having Cno=1;

询问各科成绩平均分

select Cname,avg(Grade) as average

from course inner join sc on (course.Cno=sc.Cno)

group by course.Cname,sc.Cno;

此处学到了一招:select 后的字段,须要求么包蕴在group
by中,要么使用聚合函数。

询问选修****1****号课程的学员最高分数

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

下一场查了弹指间,发现order
by只可以用2个reducer来成功职分,能够动用上面的语句来提升作用。

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

规律是sort
by使用分组排序。遵照Grade进行Hash,将结果1律的松开区别的Reducer里面。

可是要求专注的是:若是用sort
by举办排序,并且安装mapred.reduce.tasks>一,则sort
by只会确认保证各个reducer的出口有序,并不保证全局有序。sort by分化于order
by,它不受Hive.mapred.mode属性的影响,sort
by的数量只可以保障在同二个reduce中的数据能够按钦赐字段排序。使用sort
by你能够钦点执行的reduce个数(通过set
mapred.reduce.tasks=n来钦点),对出口的多少再实践归并排序,即可取得全数结出。

Distribute by****和****sort by****的使用情状

  1. Map输出的文件大小不均。

  2. Reduce输出文件大小不均。

  3. 小文件过多。

  4. 文本超大。

求各种学科号及相应的选课人数

select Cno,count(1) from sc group by Cno;

查询选修了****3****门以上的课程的上学的儿童学号

select Sno from sc group by Sno having count(1)>3;

询问学生音讯,结果按学号全局有序

select * from student order by Sno asc;

查询学生新闻,结果区分性别按年龄有序

select * from student order by Sex, Sage asc;

询问每一个学员及其选修课程的景观

select student.*,sc.Cno,sc.Grade from student inner join sc
on(student.Sno=sc.Sno);

查询学生的得分景况

select student.Sname,course.Cname,sc.Grade

from student inner join sc on(student.Sno=sc.Sno) inner join course
on(course.Cno=sc.Cno);

查询选修****2****号课程且战表在****90****分以上的有所学生。

select Sname from student inner join sc on(student.Sno=sc.Sno) where
sc.Cno=2;

查询全体学员的新闻,若是在实际业绩表中有成就,则输出战表表中的学科号

select student.*,Cno from student left outer join sc
on(student.Sno=sc.Sno);

—-LEFT SEMI JOIN Hive****脚下从未达成 IN/EXISTS
子查询,可以用 LEFT SEMI JOIN 重写子查询语句。

重写以下子查询

SELECT a.key, a.value

FROM a

WHERE a.key in

(SELECT b.key

FROM B);

重写后的SQL查询如下:

select a.key,a.value from a left outer join b on(a.key=b.key) where
b.key is not null;

select a.key,a.value from a left semi join b on(a.key=b.key)

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

select s2.* from student as s1 inner join student as s2
on(s2.Sdept=s1.Sdept) where s1.Sname=’刘晨’;

留意比较:

select * from student s1 left join student s2 on s1.Sdept=s2.Sdept and
s2.Sname=’刘晨’;

select * from student s1 right join student s2 on s1.Sdept=s2.Sdept and
s2.Sname=’刘晨’;

select * from student s1 inner join student s2 on s1.Sdept=s2.Sdept and
s2.Sname=’刘晨’;

select * from student s1 left semi join student s2 on s1.Sdept=s2.Sdept
and s2.Sname=’刘晨’;

select s1.Sname from student s1 right semi join student s2 on
s1.Sdept=s2.Sdept and s2.Sname=’刘晨’;

询问每科课程的成就前两名的学号,姓名,课程号和实际业绩

hive> select s.sno,sname,cno,grade from student s inner join (select
sno,cno,grade from(select sno,cno,grade,row_number() over(partition by
cno order by grade desc) as a from sc) test where test.a<3)b on
s.sno=b.sno;

95013 冯伟 1 98

95008 李娜 1 98

95018 王一 2 100

95014 王小丽 2 100

95006 孙庆 3 100

95005 刘刚 3 99

95015 王君 4 100

95014 王小丽 4 98

95017 王风娟 5 100

95003 王敏 5 100

95022 郑明 6 100

95009 梦圆圆 6 100

Time taken: 31.19 seconds, Fetched: 12 row(s)

相关文章