07 Hive QL高级功能

与SQL类似，Hive QL具有一般查询、聚合函数、distinct去除重复、order排序、连接查询等功能。

一、查询

select * from emp;
select ename,deptid,degree from emp;
select * from dept;
select deptid,dname from dept;

注意：在Hive早期版本中，只有使用查询全部字段才不会触发MapReduce操作，如果以select后指定查询某些字段，则会触发MapReduce操作。但在Hive新版本中，不管使用查询全部字段，还是使用select后指定查询某些字段，都不会触发MapReduce操作。

二、函数

在查询中使用upper等函数，同样不会触发MapReduce操作。

select upper(ename), deptid, degree from emp;

三、统计函数

与SQL语句相同

• count()：统计记录条数
• max()：求最大值
• min()：求最小值
• sum()：求和
• avg()：求平均值

使用这些函数都会触发 MapReduce 操作

select count(*),max(degree),min(degree),avg(degree),sum(degree) from emp;

四、distinct去除重复值

select distinct(deptid) from emp;

五、 limit限制返回记录的条数

与SQL语句相同，使用limit限制返回记录的最大条数

SELECT upper(ename), deptid,degree FROM emp LIMIT 3;

六、为列名取别名

与SQL语句相同，使用as为列指定别名，但Hive QL并不显示列名称，所以别名意义不大

SELECT upper(ename) as empname，deptid as empdeptid from emp LIMIT 3;

七、case when then多路分支

类似于Java中的switch…case语法，可以用case when then实现多路分支的效果

-- 级别低于3为follower（跟随者），级别高于5为leader（领导者），否则为中间层。
select ename ,case when degree<3 then 'follower' when degree>5 then 'leader' else 'middle' end as newdegree from emp;

八、like模糊查询

like如同SQL语句，可以对字符型字段进行模糊查询

-- '%o%'中的%表示字母o前后可以是任意字符，结果查出了姓名中含有o的有3个人
select * from emp where ename like '%o%';

九、group by分组统计

与SQL语句相同，group by语句表示分组统计

select deptid, avg (degree) from emp group by deptid;

分部门统计每个部门员工的平均级别。注意：使用group by分组统计的场合，select后只能出现分组字段和聚合函数，而不能有其他字段。

十、having过滤分组统计结果

与SQL语句相同，having后面跟聚合函数，对分组统计的结果进行过滤

• 条件having avg(degree)>3过滤掉了平均级别低于3的部门员工

select deptid, avg (degree) from emp group by deptid having avg(degree)>3;

十一、inner join内联接

与 SQL 一样，inner join表示内联接

select e.*, d.* from emp e inner join dept d on e.deptid=d.deptid;
-- inner关键字可省略
select e.*, d.* from emp ejoin dept d on e.deptid=d.deptid;

十二、left outer join和right outer join外联接

-- left outer join表示左外联接
select e.*, d.* from emp e left outer join dept d on e.deptid=d.deptid;
-- right outer join表示右外联接
select e.*, d.* from emp e right outer join dept d on e.deptid=d.deptid;

• left outer join显示左表全部数据。如果右表没有数据与之对应，则显示NULL

• right outer join显示右表全部数据，如果左表没有数据与之对应，则显示NULL（因为emp表中并没有35、36、37这3个部门的员工，所以左边全部显示为NULL）

十三、full outer join 外部链接

full outer join表示完全外部链接，执行后会显示full outer join左右两表的全部数据；如果左表或右表中没有对应数据，则显示为NULL。

select e. *, d . * from  emp  e full outer join dept d on e . deptid =d.deptid;

十四、order by 排序

order by 后面指明排序字段，默认是按 asc 升序排序，也可用 desc 指示降序排序。

select  * from emp  order by deptid;

或使用如下命令

-- 按deptid字段由大至小进行了降序排序
select  * from emp  order by deptid desc;

十五、where 查找

按指定条件进行查找使用 where

-- 查询结果为31这个部门的所有员工
select * from emp where deptid=31;

如果要查询sales部门的所有员工信息，hive并不支持SQL中的子查询，正确的做法是利用联接查询来实现

select e. *, d.dname from emp e join dept d on e.deptid=d.deptid where d.dname='sales';

总结

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行。其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，而不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。