Hive之查询操作
目录
一、查询总述
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference 来自什么表
[WHERE where_condition] 过滤
[GROUP BY col_list] 分组查询
[HAVING col_list] 分组后过滤
[ORDER BY col_list] 排序
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list]
[SORT BY col_list]
]
[LIMIT number] 限制输出的行数select执行顺序
select
count(*)
from join on where group by having order by limit
from >join on>where>group by >count(*)>having>select >order by >limit 执行次序
from >join on>where>group by >select>count(*)>having >order by >limit 书写次寻二、基本查询
select * from emp; //全表查询
select empno, ename from emp; //特定查询
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。重命名
select ename as name, deptno dn from emp;算术运算符
|
运算符 |
描述 |
|
A+B |
A和B 相加 |
|
A-B |
A减去B |
|
A*B |
A和B 相乘 |
|
A/B |
A除以B |
|
A%B |
A对B取余 |
|
A&B |
A和B按位取与 |
|
A|B |
A和B按位取或 |
|
A^B |
A和B按位取异或 |
|
~A |
A按位取反 |
常用函数
select count(*) cnt from emp; //总行数
select max(sal) max_sal from emp; //最大值
select max(sal) max_sal from emp; //最小值
select sum(sal) sum_sal from emp; //求和
select avg(sal) avg_sal from emp; //平均值Limit语句
select * from emp limit 5; //限制返回行数
select * from emp limit 2,3; //从第二行开始返回 返回三行Where语句
select * from emp where sal >1000 //过滤比较运算符
|
操作符 |
支持的数据类型 |
描述 |
|
A=B |
基本数据类型 |
如果A等于B则返回TRUE，反之返回FALSE |
|
A<=>B |
基本数据类型 |
如果A和B都为NULL，则返回TRUE，如果一边为NULL，返回False |
|
A<>B, A!=B |
基本数据类型 |
A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE |
|
A<B |
基本数据类型 |
A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE |
|
A<=B |
基本数据类型 |
A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE |
|
A>B |
基本数据类型 |
A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE |
|
A>=B |
基本数据类型 |
A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE |
|
A [NOT] BETWEEN B AND C |
基本数据类型 |
如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。 |
|
A IS NULL |
所有数据类型 |
如果A等于NULL，则返回TRUE，反之返回FALSE |
|
A IS NOT NULL |
所有数据类型 |
如果A不等于NULL，则返回TRUE，反之返回FALSE |
|
IN(数值1, 数值2) |
所有数据类型 |
使用 IN运算显示列表中的值 |
|
A [NOT] LIKE B |
STRING 类型 |
B是一个SQL下的简单正则表达式，也叫通配符模式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。 |
|
A RLIKE B, A REGEXP B |
STRING 类型 |
B是基于java的正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。 |
Like和RLike
//查找名字以S开头的员工信息
select * from emp where ename LIKE 'S%';
select * from emp where ename RLIKE '^S';
//查找名字以S结尾的员工信息
select * from emp where ename LIKE '%S';
select * from emp where ename RLIKE 'S$';
//查找名字中带有S的员工信息
select * from emp where ename LIKE '%S%';
select * from emp where ename RLIKE '[S]';逻辑运算符
|
操作符 |
含义 |
|
AND |
逻辑并 |
|
OR |
逻辑或 |
|
NOT |
逻辑否 |
三、分组查询
Group By语句
//计算emp表每个部门的平均工资
select deptno, avg(sal) avg_sal from emp group by deptno;
//计算emp每个部门中每个岗位的最高薪水
select deptno, job, max(sal) max_sal from emp group by deptno, job;Having语句
having与where不同点
（1）where后面不能写分组聚合函数，而having后面可以使用分组聚合函数。
（2）having只用于group by分组统计语句。
//求每个部门的平均薪水大于2000的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;四、Join语句
等值Join
select e.empno, e.ename, d.dname from emp e join dept d on e.deptno = d.deptno;内连接
只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
左外连接
JOIN操作符左边表中符合WHERE子句的所有记录将会被返回
select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;右外连接
JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;满外连接
将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;多表连接
SELECT e.ename, d.dname, l.loc_name
FROM emp e
JOIN dept d
ON d.deptno = e.deptno
JOIN location l
ON d.loc = l.loc;大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。Hive总是按照从左到右的顺序执行的。
笛卡尔积
select empno, dname from emp, dept; //一定要避免使用笛卡尔积五、排序
全局排序（Order By）
Order By：全局排序，只有一个Reducer
//查询员工信息按工资升序排列
select * from emp order by sal;
//查询员工信息按工资降序排列
select * from emp order by sal desc;重命名排序
select ename, sal*2 s2 from emp order by s2;多个列排序
select ename, deptno, sal from emp order by deptno, sal; //先按照deptno排序 然后按照sal排序每个Reduce内部排序（Sort By）
Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。
1）设置reduce个数
set mapreduce.job.reduces=3;
2）查看设置reduce个数
set mapreduce.job.reduces;
3）根据部门编号降序查看员工信息
select * from emp sort by deptno desc;
4）将查询结果导入到文件中（按照部门编号降序排序）
insert overwrite local directory '/opt/module/hive/datas/sortby-result'
select * from emp sort by deptno desc;分区distribute by
distribute by指定某个特定行应该到哪个reducer，结合sort by使用
set mapreduce.job.reduces=3;
insert overwrite local directory '/opt/module/hive/datas/distribute-result' select * from emp distribute by deptno sort by sal desc;
//distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。
//Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
分区排序（Cluster By）
当distribute by和sort by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;