[大数据]Hive的进阶操作

1.前置准备

数据准备

18999001	王述龙	男	1998-12-10	上海	98	100	2000
18999002	孙宇鹏	男	1999-11-17	沈阳	51	500	
18999003	王应龙	男	2000-02-04	沈阳	59	100	
18999004	张琼宇	女	1999-07-01	大连	89	200	
18999005	宋传涵	女	1999-07-20	上海	86	100	1000
18999006	李亚楠	女	1998-01-24	杭州	97	200	2000
18999007	侯楠楠	男	2000-01-29	北京	79	200	
18999008	陈姝元	女	1999-06-24	北京	96	200	1500
18999009	陆春宇	男	1998-01-18	沈阳	87	300	1000
18999010	孙云琳	女	1997-07-15	上海	56	300	
18999011	尤骞梓	女	1999-04-25	杭州	86	200	1000
18999012	张爱林	男	1999-05-16	北京	92	400	1500
18999013	曹雪东	男	2000-11-20	北京	78	300	
18999014	贾芸梅	女	2000-06-12	大连	88	400	1000
18999015	温勇元	男	1999-08-08	上海	65	500	
18999016	张微微	女	1998-01-27	北京	90	400	1500
18999017	李君年	男	1998-03-21	上海	78	500	
18999018	卢昱泽	女	1998-08-01	上海	57	500	
18999019	赵旭辉	男	1999-02-18	北京	75	500	
18999020	张矗年	男	1997-07-26	重庆	86	300	1000

2.流程实操

创建一个数据仓库hrsystem，并切换到该库中。

create database hrsystem;

use hrsystem;

show databases;

在hrsystem数据仓库中创建一张外部表：学生表emp。

create external table if not exists emp(
    sno int comment"学号",
    sname string comment"姓名",
    gender string comment"性别",
    bday string comment"出生日期",
    area string comment"地区",
    score double comment"成绩",
    deptno string comment"所在学院",
    scholarship double comment"奖学金"
)row format delimited fields terminated by '\t';

将外部表转换为内部表。

alter table emp set tblproperties('EXTERNAL'='FLASE');

查询表结构的详细信息。

desc formatted emp;

将本地文件/opt/datas/emp.txt导入到学生表emp中。

load data local inpath '/opt/datas/emp.txt' into table emp;

在浏览器中查看HDFS上学生表emp的数据。

查询奖学金scholarship不为空的所有学生信息。

select * from emp where scholarship is not null;

查询学生表emp中平均成绩小于70分的学号。

Having和Where的区别
where作用于表中的列，having作用于查询结果中的列
where后不能写分组函数，having后可以使用分组函数

select sno, avg(score) avg_score 
from emp group by sno
having avg_score < 70;

查询学生表emp中平均成绩小于70分的部门。

select deptno, avg(score) avg_score
from emp group by deptno
having avg_score < 70;

查询出生日期中含有5的学生的姓名和生日。

select sname,bday
from emp where bday RLIKE '[5]';

查询学生表emp的信息，并按部门降序排列。

-- 内部排序
select * from emp sort by deptno desc;

select * from emp order by deptno desc;

按照学生成绩的2倍排序。

select sname, score*2 twoscore 
from emp order by twoscore;

查询emp表中每个部门的平均成绩。

select deptno, avg(score) avg_score 
from emp group by deptno;

查询成绩大于95分，或者系别是100的学生信息。

select *
from emp where score>95 or deptno=100;

随机抽样学生表emp中的10行记录，其中包括学生姓名sname和成绩score。

select sname,score 
from emp tablesample(10 rows);

利用条件函数，查询学生表emp中不同部门男女人数。

select  deptno,
  sum(case gender when '男' then 1 else 0 end) male_count,
  sum(case gender when '女' then 1 else 0 end) female_count
from emp
group by deptno;

了解更多知识请戳下：

@Author:懒羊羊