大数据:hive4:查询

创建部门表
create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';
 
创建员工表
create table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';
导入数据
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table
dept;
hive (default)> load data local inpath '/opt/module/datas/emp.txt' into table emp;
 
1．全表查询
hive (default)> select * from emp;
2．选择特定列查询
select empno,ename from emp;
注意：
（1）SQL 语言大小写不敏感。 
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。

列别名

1．重命名一个列
2．便于计算
3．紧跟列名，也可以在列名和别名之间加入关键字‘AS’ 
4．案例实操
查询名称和部门
select ename AS name, deptno dn from emp;

算术运算符

实例操作

查询出所有员工的薪水加1显示

select (cast(sal as int ) +1) from emp;

求总行数
 select count(1) from emp;
select count(*) from emp;
求工资的最大值 
select max(sal) maxsal from emp;
求工资的最小值
select min(sal) min_sal from emp
求工资的总和
select sum(sal)  sum_sal from emp 
求工资的平均值
select avg(sal) avg_sal from emp 
 
limit语句
select * from emp limit 5;
 
where语句
 
使用where子句，将不满足条件的行过滤掉
where子句紧跟from子句
案例实操
查询出薪水大于1000的所有的员工
select * from emp where sal >=1000;
注意:where子句中不能使用字段别名
 
 

比较运算符（Between/In/ Is Null）

下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中

查询薪水等于5000的所有员工
select * from emp where sal = 5000;
查询薪水在500 到 100 的员工信息
 select * from emp where sal <= 1000 and sal>= 100;
不可以用between 
查询comm为空的所有员工信息
select * from emp where comm is null;
查询工资是1500或5000的员工信息
select * from emp where sal in (1500,5000);

Like和RLike

使用like运算选择类似的值

选择可以包含字符或数字

%代表零个或多个字符（任意个字符）

RLike 子句是hive中这个功能一个扩展，其可以通过java的正则表达式这个更强大的语方来匹配条件

案例实例

查找以2开头薪水的员工信息

select * from emp where sal like '2%';
查找第二个数值为2的薪水的员工信息
 select * from emp where sal like '_2%'
查找新水中含有2的员工信息
select * from emp where sal RLike '[2]';

逻辑运算符（And/Or/Not）

实例实操

查询薪水大于1000，部门是30
select * from emp where sal>1000 and deptno=30;
查询薪水大于1000或者部门是30
select * from emp where sal >1000 or deptno=30;
查询除了20部门和30部门以外员工信息
select * from emp where deptno not in (30,20)

分组

group by 语句

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

计算emp表每个部门的平均工资
select deptno,name,avg(sal) from  emp group by deptno;
计算emp每个部门中每个岗位最高薪水
select t.deptno,t.job,max(t.sql) max_sal from emp t group by t.deptno,t.job
 
having语句
having与where不同点
where后面不以写分组函数，而having后面可以使用分组函数
having只能用于group by 分组统计语句
 
 （1）求每个部门的平均薪水大于2000的部门
求每个部门的平均工资
hive (default)> select deptno, avg(sal) from emp group by deptno;
	  求每个部门的平均薪水大于2000的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having
 avg_sal > 2000;

join语句

等值join
hive支持通常sql join语句，但是只支持等值连接，不支持非等值连接
安例实操
根据员工表，和部门表中的编号民相号，查询员工编号，员工名称和部门名称
select e.empno,e.ename ,d.deptno ,d.dname from emp e join dept d  on e.deptno = d.deptno;
二个相等
select e.empno,e.ename ,d.deptno ,d.dname  from emp e ,dept d where e.deptno=d.deptno;
join语句
hive支持通常sql join语句，但是只支持等值连接，不支持非等值连接
案例实操
根据员工表和部门表中的部门编号相等，查询员工编号，员工名称和部门名称
select e.empno,e.ename ,d.deptno,d.dname from emp e join dept d on e.deptno = d.deptno
 
表的别名
好处：
    使用别名可以简化查询
    使用表名前可以提高执行效率
 
内连接
内连接只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
 = d.deptno;
 
左外连接
左连接join操作
右连接
select e.empno,e.ename ,d.deptno ,d.dname from emp e right   join dept d  on e.deptno = d.deptno;
左连接
select e.empno,e.ename ,d.deptno ,d.dname from emp e left join dept d  on e.deptno = d.deptno;
满连接
select e.empno,e.ename ,d.deptno ,d.dname from emp e full join dept d  on e.deptno = d.deptno;
 
 

多表连接

注意连接n个表，到少需要n-1个连接条，例如连接三个到少需要两个连接条件

1．创建位置表
create table if not exists location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';
2.导入数据
 load data local inpath '/home/hive/location.txt' into table location;
3.多表连接查询
hive (default)>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;进行连接操作。
注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。
优化：当对3个或者更多表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。
笛卡尔积
1．笛卡尔集会在下面条件下产生
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
2．案例实操
hive (default)> select empno, dname from emp, dept;
连接谓词中不支持or
hive join目前不支持在on子句中使用谓词or
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
= d.deptno or e.ename=d.ename;   错误的

排序

6.5.1 全局排序（Order By）
Order By：全局排序，只有一个Reducer
1．使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
2．ORDER BY 子句在SELECT语句的结尾
3．案例实操 
	（1）查询员工信息按工资升序排列
hive (default)> select * from emp order by sal;
	（2）查询员工信息按工资降序排列
hive (default)> select * from emp order by sal desc;

按照员工薪水的2倍排序

select ename ,sal*2 twosal from emp order by twosal;

按照别名排序
按照员工薪水的2倍排序
hive (default)> select ename, sal*2 twosal from emp order by twosal;
6.5.3 多个列排序
按照部门和工资升序排序
hive (default)> select ename, deptno, sal from emp order by deptno, sal ;

每个mapreduce内部排充sort by 

sort by:对于大规模的数据集order by 的效率低，在很多情况下，并不需要全局排序，此时可以使用sort by

sort by为每个reduce 产生一个排序文件，每个reducer内部进行排序，对全局结果集来说不是排序

1．设置reduce个数
hive (default)> set mapreduce.job.reduces=3;
2．查看设置reduce个数
hive (default)> set mapreduce.job.reduces;
3．根据部门编号降序查看员工信息
hive (default)> select * from emp sort by deptno desc;
	4．将查询结果导入到文件中（按照部门编号降序排序）
hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result'
 select * from emp sort by deptno desc;
 
分区排序（Distribute By）
Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。 
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
案例实操：
	（1）先按照部门编号分区，再按照员工编号降序排序。
hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;
注意：
1．	distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。
2．	Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
 
Cluster By
当distribute by和sorts by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
1）以下两种写法等价
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。