51cto赵强HADOOP学习（十六）hive的数据查询

简单查询

查询的语法

查询所有员工的所有信息。
select * from emp;

查询员工信息：员工号 姓名 月薪
select empno,ename,sal from emp;

查询员工信息：员工号 姓名 月薪 年薪
select empno,ename,sal,sal*12 from emp;

查询员工信息：员工号 姓名 月薪 年薪 奖金 年收入
select empno,ename,sal,sal*12,comm,sal*12+nvl(comm) from emp;

查询奖金为null的员工
select * from emp where comm=null;
select * from emp where comm is null;

使用distinct来去掉重复记录
select distinct deptno from emp;

简单查询的Fetch Task功能
从Hive0.10.0版本开始支持

配置方式

- set hive.fetch.task.conversion=more;
- hive --hiveconf hive.fetch.task.conversion=more
- 修改hive-site.xml文件

>set hive.fetch.task.conversion=more;
select empno,ename,sal,sal*12 from emp;
>hive --hiveconf.fetch.task.conversion=more;
vi hive-site.xml文件
<property>
    <name>hive.fetch.task.conversion</name>
    <value>more</value>
</property>

过滤和排序

在查询中使用过滤

--查询10号部门的员工
select * from emp6 where deptno=10;

--查询名叫KING的员工
select * from emp6 where ename='KING';

--查询部门号是10，薪水小于2000的员工；
select * from emp6 where deptno=10 and sal<2000;

--模糊查询：查询名字以S开头的员工
select empno,ename,sal from emp6 where ename like 'S%';

--模糊查询：查询名字含有下划线的员工
select empno,ename,sal from emp6 where ename like '%_%'; //这个会把所有的都列出来
select empno,ename,sal from emp6 where ename like '%\_%' escape '\'; //在oracle里面是正确的，但在Hive里面是错的
select empno,ename,sal from emp6 where ename like '%\\_%';

在查询中使用排序

--查询员工信息： 员工号 姓名 月薪 按照月薪排序
select empno,ename,sal from emp6 order by sal;   //默认是升序
select empno,ename,sal from emp6 order by sal desc;

order by 后面 跟: 列，表达式，别名，序号
--查询员工信息：员工号 姓名 月薪 年薪 按照年薪排序（表达式）
select empno,ename,sal,sal*12 from emp6 order by sal*12;

--查询员工信息：员工号 姓名 月薪 年薪 按照年薪排序（别名）
select empno,ename,sal,sal*12 annsal from emp6 order by annsal;

--查询员工信息：员工号 姓名 月薪 年薪 按照年薪排序(序号)
select empno,ename,sal,sal*12 annsal from emp6 order by 4;  //这里并没有出来想要的结果，需要设置一个参数
set hive.groupby.orderby.position.alias=true;

--查询员工信息： 按照奖金排序  --->null排序：升序，在最前面 降序，在最后面
select empno,ename,sal,comm from emp6 order by comm;

Hive的表连接

等值连接

--查询员工信息：员工号，姓名，月薪，部门名称

select e.empno,e.ename,e.sal,d.dname
from emp6 e,dept d
where e.deptno=d.deptno;

不等值连接

--查询员工信息：员工号，姓名，月薪，工资级别
select e.empno,e.ename,e.sal,s.grade
from emp6 e,salgrade s
where e.sal between s.losal and s.hisal;

外连接

按部门统计员工人数：部门号，部门名称，人数
select d.deptno,d.dname,count(e.empno)
from emp6 e,dept d
where e.deptno=d.deptno
group by d.deptno,d.dname;

通过外连接将对于连接条件不成立的记录任然包含在最后的结果中。
左外连接


右外连接
select d.deptno,d.dname,count(e.empno)
from emp6 e right outer join dept d
on (e.deptno=d.deptno)
group by d.deptno,d.dname;

自连接

--查询员工的姓名和员工的老板姓名
select e.ename,b.ename
from emp6 e,emp6 b
where e.mgr=b.empno;

自连接的核心：通过表的别名将同一张表视为多张表

hive的子查询

hive只支持:from和where子句中的子查询

hive只支持:from和where子句中的子查询

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries
示例：查询名叫KING和SCOTT的员工信息

select * from emp6 e1 where e1.empno in (select e2.empno from emp6 e2 where e2.ename='SCOTT' or e2.ename='KING');



注意的问题：
    语法中的括号
    合理的书写风格
    Hive中只支持WHERE和FROM子句中的子查询
    主查询和子查询可以不是同一张表
    子查询中的空值问题
select * from (select empno,ename from emp6) t1;
select * from (select empno,ename,sal,sal*12 annsal from emp6) t1;

select e.ename from emp6 e where e.deptno in (select d.deptno from dept d where d.dname='SALES' or d.dname='ACCOUTING');

select * from emp6 e where e.empno not in (select e1.mgr from emp6 e1);
select * from emp6 e where e.empno not in (select e1.mgr from emp6 e1 where e1.mgr is not null);