6）Hive（DQL：数据查询语言）

基本查询

1. 全表和特定列查询
   select * from emp;
select id, name from stu;

2. 列别名
   select dept_name as name, deptno dn from emp;

3. 常用函数
   1．求总行数（count）
   hive (default)> select count(*) cnt from emp;
   2．求工资的最大值（max）
   hive (default)> select max(sal) max_sal from emp;
   3．求工资的最小值（min）
   hive (default)> select min(sal) min_sal from emp;
   4．求工资的总和（sum）
   hive (default)> select sum(sal) sum_sal from emp;
   5．求工资的平均值（avg）
   hive (default)> select avg(sal) avg_sal from emp;

4. Limit语句(LIMIT子句用于限制返回的行数)
   hive (default)> select * from emp limit 5;

Where语句

案例:
查询出薪水大于1000的所有员工
hive (default)> select * from emp where sal >1000;

1. 比较运算符

   操作符	支持的数据类型	描述
   a [not] between b and c	基本数据类型	如果a,b,c任一为NULL，则结果为NULL。如果a的值大于等于b而且小于或等于c，则结果为TRUE，反之为FALSE。
   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是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，不是只需与其字符串匹配。

分组

1. Group By语句
   group by 语句经常会与聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作；
   示例1：
   计算emp表每个部门的平均工资：
   select avg(sal) from emp group by dept_id;
   计算emp每个部门中每个岗位的最高薪水
   select dept_id,job, max(sal) from emp group by dept_id,job;

2. Having语句

   1. having与where不同点:
      1. where针对表中的列发挥作用，而hiving是针对查询结果中的列发挥作用；
      2. where后面不能写分组函数，而hiving可以；
      3. having只用于group by分组统计语句；
   2. 案例实操：
      求每个部门的平均薪水大于2000的部门
      select dept_name,avg(sal) avg_sal group by dept_id having avg_asl>2000;

Join语句

1. 等值Join
   Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接;
   案例：根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称：
   select e.id,e.name,d.name from emp e join dept d on e.dept_id = d.dept_id;

2. 使用表的别名的优点：
   ①使用别名可以简化查询；
   ②使用表别名前缀可以提高执行效率；
   示例：合并员工表和部门表
   select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

3. 内连接
   只有两个连接表中都存在与连接条件相匹配的数据才会被保留下来：
   select e.eid,e.ename,d.did from emp e join dept d on e.did=d.did;

4. 左外连接
   保留左表要查询的全部数据和右表符合条件的部分：
   select * from emp e left join dept d on e.dept_id=d.dept_id;

5. 右外连接
   保留右表要查询的全部数据和左表符合条件的部分：
   select * from emp e right join dept d on e.dept_id=d.dept_id;

6. 满外连接
   满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代:
   select e.eid,e.ename,d.dept_id from emp e full join dept d on e.dept_id = d.dept_id;

7. 多表连接
   连接 n个表，至少需要n-1个连接条件

   select e.name,d.dept_id,l.name 
   from emp e 
   join dept d on e.dept_id = d.dept_id
   join location l on d.loc = l.loc;

   大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作(注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的)；

8. 笛卡尔积
   产生条件：省略连接条件、连接条件无效、所有表中的所有行相互连接；
   例：select empno, dname from emp, dept;

9. 连接谓词中不支持or
   select e.empno, e.ename,d.dept_id from emp e join dept d on e.dept_id =d.dept_id or e.ename=d.ename; 错误的

排序

1. 全局排序（Order By）
   Order By：全局排序，一个Reducer
   1. 使用 ORDER BY 子句排序：
      ASC（ascend）: 升序（默认）
      DESC（descend）: 降序
   2. ORDER BY 子句在SELECT语句的结尾
   3. 例：select * from emp order by sal [desc];
2. 按照别名排序
   例：select ename, sal*2 twosal from emp order by twosal;
3. 多个列排序
   例：按照部门和工资升序排序
   select e.name,e.sal from emp order by e.dept_id,e.sal;
4. 每个MapReduce内部排序（Sort By）
   1. sort by ：对每个Reducer内部排序，对全局结果集来说不是排序；
   2. 设置reduce个数：set mapreduce.job.reduces = 3;
   3. 查看reduce的个数：set mapreduce.job.reduce;
   4. 案例：根据部门编号降序查看员工信息
      select * from emp sort by dept_id desc;
5. 分区排序（Distribute By）
   1. Distribute By：类似于MR中partition，一般结合sort by 使用；
   2. Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前；
   3. 案例：先按照部门编号分区，再按照员工编号降序排序
      set mapreduce.job.reduces=3;
insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by dept_id sort by empno desc;
6. Cluster By
   ①当distribute by和sorts 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;
   注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去