Hive - 6、HQL操作-DQL命令 - 《大数据》

第 1 节基本查询
第 2 节 where子句
- 比较运算符
- 逻辑运算符
第 3 节 group by子句
第 4 节表连接
第 5 节排序子句【重点】

DQL — Data Query Language 数据查询语言
select语法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]
  [LIMIT [offset,] rows]

SQL语句书写注意事项:

SQL语句对大小写不敏感
SQL语句可以写一行(简单SQL)也可以写多行(复杂SQL)
关键字不能缩写，也不能分行
各子句一般要分行
使用缩进格式，提高SQL语句的可读性(重要)

创建表，加载数据

-- 测试数据 /home/hadoop/data/emp.dat
7369,SMITH,CLERK,7902,2010-12-17,800,,20
7499,ALLEN,SALESMAN,7698,2011-02-20,1600,300,30
7521,WARD,SALESMAN,7698,2011-02-22,1250,500,30
7566,JONES,MANAGER,7839,2011-04-02,2975,,20
7654,MARTIN,SALESMAN,7698,2011-09-28,1250,1400,30
7698,BLAKE,MANAGER,7839,2011-05-01,2850,,30 
7782,CLARK,MANAGER,7839,2011-06-09,2450,,10
7788,SCOTT,ANALYST,7566,2017-07-13,3000,,20
7839,KING,PRESIDENT,,2011-11-07,5000,,10
7844,TURNER,SALESMAN,7698,2011-09-08,1500,0,30
7876,ADAMS,CLERK,7788,2017-07-13,1100,,20
7900,JAMES,CLERK,7698,2011-12-03,950,,30
7902,FORD,ANALYST,7566,2011-12-03,3000,,20
7934,MILLER,CLERK,7782,2012-01-23,1300,,10

-- 建表并加载数据
CREATE TABLE emp (
    empno int,
    ename string,
    job string,
    mgr int,
    hiredate DATE,
    sal int,
    comm int,
    deptno int
)row format delimited fields terminated by ",";

-- 加载数据
LOAD DATA LOCAL INPATH '/home/hadoop/data/emp.dat' INTO TABLE emp;

第 1 节基本查询

-- 省略from子句的查询
select 8*888 ;
select current_date ;
-- 使用列别名
select 8*888 product;
select current_date as currdate;
-- 全表查询
select * from emp;
-- 选择特定列查询
select ename, sal, comm from emp;
-- 使用函数
select count(*) from emp;
-- count(colname) 按字段进行count，不统计NULL select sum(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
-- 使用limit子句限制返回的行数 
select * from emp limit 3;

第 2 节 where子句

WHERE子句紧随FROM子句，使用WHERE子句，按条件过滤数据;
where 子句中不能使用列的别名;

select * from emp where sal > 2000;

where子句中会涉及到较多的比较运算和逻辑运算。

比较运算符

官方文档:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ UDF

逻辑运算符

 -- 比较运算符，null参与运算 
 select null=null; 
 select null==null;
 select null<=>null;

 -- 使用 is null 判空
select * from emp where comm is null;
-- 使用 in
select * from emp where deptno in (20, 30);
-- 使用 between ... and ...
select * from emp where sal between 1000 and 2000;
-- 使用 like
select ename, sal from emp where ename like '%L%';
-- 使用 rlike。正则表达式，名字以A或S开头
select ename, sal from emp where ename rlike '^(A|S).*';

第 3 节 group by子句

GROUP BY语句通常与聚组函数一起使用，按照一个或多个列对数据进行分组，对每个组进行聚合操作。

-- 计算emp表每个部门的平均工资
select deptno, avg(sal) from emp group by deptno;
-- 计算emp每个部门中每个岗位的最高薪水
select deptno, job, max(sal) from emp group by deptno, job;

where子句针对表中的数据发挥作用;having针对查询结果(聚组以后的结果)发挥作用
where子句不能有分组函数;having子句可以有分组函数
having只用于group by分组统计之后
```
-- 求每个部门的平均薪水大于2000的部门
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > 2000;
```
第 4 节表连接
Hive支持通常的SQL JOIN语句，仅支持等值连接，不支持非等值连接。

JOIN 语句中经常会使用表的别名。使用别名可以简化SQL语句的编写，使用表名前缀可以提高SQL的解析效率。

连接查询操作分为两大类:内连接和外连接，而外连接可进一步细分为三种类型:

1. 内连接: [inner] join 
2. 外连接 (outer join)
    - 左外连接。 left [outer] join，左表的数据全部显示 
  - 右外连接。 right [outer] join，右表的数据全部显示 
  - 全外连接。 full [outer] join，两张表的数据都显示

演示案例

-- 准备数据 
u1.txt数据: 
1,a
2,b
3,c
4,d
5,e
6,f

u2.txt数据: 
4,d
5,e
6,f
7,g 
8,h 
9,i

create table if not exists u1(
  id int,
  name string
) row format delimited fields terminated by ',';

create table if not exists u2(
  id int,
  name string
) row format delimited fields terminated by ',';

load data local inpath '/home/hadoop/data/u1.txt' into table u1;
load data local inpath '/home/hadoop/data/u2.txt' into table u2;

-- 内连接
select * from u1 join u2 on u1.id = u2.id;
-- 左外连接
select * from u1 left join u2 on u1.id = u2.id;
-- 右外连接
select * from u1 right join u2 on u1.id = u2.id;
-- 全外连接
select * from u1 full join u2 on u1.id = u2.id;

多表连接

连接 n张表，至少需要 n-1 个连接条件。例如:连接四张表，至少需要三个连接条件。
多表连接查询，查询老师对应的课程，以及对应的分数，对应的学生:

select *
  from techer t left join course c on t.t_id = c.t_id
                left join score  s on s.c_id = c.c_id
                left join student stu on s.s_id = stu.s_id;

Hive总是按照从左到右的顺序执行，Hive会对每对 JOIN 连接对象启动一个 MapReduce 任务。
上面的例子中会首先启动一个 MapReduce job 对表 t 和表 c 进行连接操作;然后再启动一个 MapReduce job 将第一个 MapReduce job 的输出和表 s 进行连接操作; 然后再继续直到全部操作;

笛卡尔积

满足以下条件将会产生笛卡尔集:

没有连接条件
连接条件无效
所有表中的所有行互相连接

如果表A、B分别有M、N条数据，其笛卡尔积的结果将有 M*N 条数据;缺省条件下 hive不支持笛卡尔积运算;

set hive.strict.checks.cartesian.product=false;
select * from u1, u2;

第 5 节排序子句【重点】

全局排序(order by)

order by 子句出现在select语句的结尾;
order by子句对最终的结果进行排序; 默认使用升序(ASC);可以使用DESC，跟在字段名之后表示降序;

order by执行全局排序，只有一个reduce;

-- 普通排序
select * from emp order by deptno;
-- 按别名排序
select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm, deptno
from emp
order by salcomm desc;
-- 多列排序
select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm, deptno
from emp
order by deptno, salcomm desc;
-- 排序字段要出现在select子句中。以下语句无法执行(因为select子句中缺少 deptno):
select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm
from emp
order by deptno, salcomm desc;

每个MR内部排序(sort by)

对于大规模数据而言order by效率低; 在很多业务场景，我们并不需要全局有序的数据，此时可以使用sort by;
sort by为每个reduce产生一个排序文件，在reduce内部进行排序，得到局部有序的结果; ```sql — 设置reduce个数 set mapreduce.job.reduces=2;

— 按照工资降序查看员工信息 select * from emp sort by sal desc;

— 将查询结果导入到文件中(按照工资降序)。生成两个输出文件，每个文件内部数据按工资降序排列 insert overwrite local directory ‘/home/hadoop/output/sortsal’ select * from emp sort by sal desc;

<a name="DYY5P"></a>
### 分区排序(distribute by)

- distribute by 将特定的行发送到特定的reducer中，便于后继的聚合与排序操作;
- distribute by 类似于MR中的分区操作，可以结合sort by操作，使分区数据有序;
- distribute by 要写在sort by之前;
```sql
-- 启动2个reducer task;先按 deptno 分区，在分区内按 sal+comm 排序 
set mapreduce.job.reduces=2;
-- 将结果输出到文件，观察输出结果
insert overwrite local directory '/home/hadoop/output/distBy' select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm from emp distribute by deptno sort by salcomm desc;
-- 上例中，数据被分到了统一区，看不出分区的结果
-- 将数据分到3个区中，每个分区都有数据
set mapreduce.job.reduces=3;
insert overwrite local directory '/home/hadoop/output/distBy1' select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm from emp distribute by deptno sort by salcomm desc;

Cluster By

当distribute by 与 sort by是同一个字段时，可使用cluster by简化语法;
cluster by 只能是升序，不能指定排序规则;

-- 语法上是等价的
select * from emp distribute by deptno sort by deptno; 
select * from emp cluster by deptno;

排序小结

order by。执行全局排序，效率低。生产环境中慎用
sort by。使数据局部有序(在reduce内部有序)
distribute by。按照指定的条件将数据分组，常与sort by联用，使数据局部有序
cluster by。当distribute by 与 sort by是同一个字段时，可使用cluster by简化语法

6、HQL操作-DQL命令

第 1 节 基本查询