1、Select

hive (default)> select * from XX; //全表查询    
hive (default)> select 列名，列名 from XX; //特定列查询

注意：
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。

列别名

重命名一个列，便于计算。紧跟列名，也可以在列名和别名之间加入关键字‘AS’
查询名称和部门
hive (default)> select ename AS name, deptno dn from emp;

算术运算符

常用函数

求总行数（count）
hive (default)> select count(*) cnt from emp;

求工资的最大值（max）
hive (default)> select max(sal) max_sal from emp;

Snipaste_2020-06-08_16-06-56.png

求工资的最小值（min）
hive (default)> select min(sal) min_sal from emp;

求工资的总和（sum）
hive (default)> select sum(sal) sum_sal from emp;

求工资的平均值（avg）
hive (default)> select avg(sal) avg_sal from emp;

比较运算符

Limit

典型的查询会返回多行数据。LIMIT 子句用于限制返回的行数。
hive (default)> select * from emp limit 5;

where

使用WHERE 子句，将不满足条件的行过滤掉
WHERE 子句紧随 FROM 子句
hive (default)> select * from emp where sal >1000;

Like、RLike

查找以 2 开头薪水的员工信息
hive (default)> select * from emp where sal LIKE '2%';
查找第二个数值为 2 的薪水的员工信息
hive (default)> select * from emp where sal LIKE '_2%';
查找薪水中含有 2 的员工信息
hive (default)> select * from emp where sal RLIKE '[2]';

Snipaste_2020-06-08_16-19-33.png

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

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

Snipaste_2020-06-08_16-20-17.png

分组

Group By

GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组， 然后对每个组执行聚合操作
（1）计算emp 表每个部门的平均工资

hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

image.png

（2）计算emp 每个部门中每个岗位的最高薪水

hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;

image.png

Having语句

having 只用于 group by 分组统计语句

求每个部门的平均薪水大于 2000 的部门

select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

image.png

Join

Hive 支持通常的 SQL JOIN 语句，但是只支持等值连接，不支持非等值连接。
根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称
//取出 e.empno, e.ename, d.deptno, d.dname四列。命名为e，join dept，命名为d， jion条件on e.deptno = d.deptno

hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d
on e.deptno = d.deptno;

Snipaste_2020-06-08_18-07-37.png

表别名

好处：
使用别名可以简化查询。
使用表名前缀可以提高执行效率
合并员工表和部门表

select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno= d.deptno;

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d 
on e.deptno= d.deptno;

左外连接：JOIN 操作符左边表中符合WHERE 子句的所有记录将会被返回。

hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

右外连接：JOIN 操作符右边表中符合WHERE 子句的所有记录将会被返回。

hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

全（满）外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 值替代。

hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

多表连接

实践

create table if not exists default.location( loc int,
loc_name string
)
row format delimited fields terminated by '\t';

load data local inpath '/home/data/loc.txt' into table location;

Snipaste_2020-06-08_21-50-17.png

多表连接查询

SELECT e.ename, d.deptno, l.loc_name 
FROM    emp e
JOIN    dept d
ON  d.deptno = e.deptno 
JOIN    location l
ON  d.loc = l.loc;

join.png

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

排序

全局排序(Order By)

Order By：全局排序，一个 Reducer

使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序

ORDER BY 子句在SELECT 语句的结尾
案例实操:
查询员工信息按工资升序排列
select * from emp order by sal;
查询员工信息按工资降序排列
select * from emp order by sal desc;
按照员工薪水的 2 倍排序
select ename, sal*2 twosal from emp order by twosal;
按照部门和工资升序排序
select ename, deptno, sal from emp order by deptno, sal ;

orderby.png

每个 MapReduce 内部排序（Sort By）

Sort By：每个 Reducer 内部进行排序，对全局结果集来说不是排序，多个reduce个数

设置reduce 个数
set mapreduce.job.reduces=3;

根据部门编号降序查看员工信息
select * from emp sort by empno desc;

分区排序（Distribute By）

Distribute By：类似 MR 中 partition，进行分区，结合 sort by 使用。
注意，Hive 要求 DISTRIBUTE BY 语句要写在SORT BY 语句之前。
对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by的效果。

先按照部门编号分区，再按照工资降序排序

set mapreduce.job.reduces=3;

insert overwrite local directory '/home/out/dis-by'
select * from emp distribute by deptno sort by sal desc;

Snipaste_2020-06-08_22-20-29.png

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;