Hive的DQL（数据查询及优化）

目录

一、练习数据

二、单表查询

三、多表连接查询

四、综合练习题

五、查询优化

---

一、练习数据

7369	SMITH	CLERK	7902	1980-12-17 00:00:00	800.00		20
7499	ALLEN	SALESMAN	7698	1981-02-20 00:00:00	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-02-22 00:00:00	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-04-02 00:00:00	2975.00		20
7654	MARTIN	SALESMAN	7698	1981-09-28 00:00:00	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-05-01 00:00:00	2850.00		30
7782	CLARK	MANAGER	7839	1981-06-09 00:00:00	2450.00		10
7788	SCOTT	ANALYST	7566	1987-04-19 00:00:00	1500.00		20
7839	KING	PRESIDENT		1981-11-17 00:00:00	5000.00		10
7844	TURNER	SALESMAN	7698	1981-09-08 00:00:00	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-05-23 00:00:00	1100.00		20
7900	JAMES	CLERK	7698	1981-12-03 00:00:00	950.00		30
7902	FORD	ANALYST	7566	1981-12-03 00:00:00	3000.00		20
7934	MILLER	CLERK	7782	1982-01-23 00:00:00	1300.00		10

10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

1、准备数据：

[hdp@hdp02 demo]$ vi emp.txt
[hdp@hdp02 demo]$ vi dept.txt
[hdp@hdp02 demo]$ hive

      -- 员工表
hive> CREATE TABLE emp(
      empno INT comment "-员工表编号",
      ename STRING comment '员工姓名',
      job STRING comment '职位类型',
      mgr INT comment '领导编号' ,   
      hiredate TIMESTAMP comment '雇佣日期',
      sal DECIMAL(7,2) comment '工资',
      comm DECIMAL(7,2) comment '奖金',
      deptno INT comment '部门编号'
      ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
      -- 导入数据
hive> load data local inpath '/home/hdp/demo/emp.txt' into table emp;

      -- 部门表
hive> create table dept (
      deptno INT comment '部门编号',
      dname STRING comment '部门名称',
      loc STRING comment '部门所在的城市'
      )row format delimited fields terminated by '\t';
      -- 导入数据
hive> load data local inpath '/home/hdp/demo/dept.txt' into table dept;

-- 动态分区表
      -- 开启动态分区
hive> set hive.exec.dynamic.partition = true;
hive> set hive.exec.dynamic.partition.mode = nonstrict;
      -- 创建分区表
hive> CREATE EXTERNAL TABLE emp_ptn(
      empno INT,
      ename STRING,
      job STRING,
      mgr INT,
      hiredate TIMESTAMP,
      sal DECIMAL(7,2),
      comm DECIMAL(7,2)
      )PARTITIONED BY (deptno INT)   -- 按照部门编号进行分区
      ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
      -- 往分区表导入数据
hive> insert into table emp_ptn partition(deptno)
      select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;
hive> show partitions emp_ptn; -- 查看分区

---

注意：hive的select ...不支持一下类型

• 1、非等值连接
• 2、or类型的多条件连接
• 3、默认情况下笛卡尔积不支持

二、单表查询

0、查看当前数据库

SELECT current_database();

1、全表扫描查询

select * from emp;
select * from dept;
select * from emp_ptn;

2、where 条件查询

-- 查询 10 号部门中员工编号大于 7782 的员工信息
select * from emp_ptn where deptno = 10 and empno > 7782;

3、DISTINCT 去重

-- 查询所有工作类型
select distinct job from emp;

4、分区查询

-- 查询分区表中部门编号在[20,30]之间的员工(不用全表扫描，会快很多)
select emp_ptn.* from emp_ptn 
where emp_ptn.deptno >=20 and emp_ptn.deptno <=30;

5、LIMIT 使用

-- 查询薪资最高的 5 名员工
select * from emp order by sal desc limit 5;

6、GROUP BY 分组聚合

-- 查询各个部门薪酬总和，平均薪资
select deptno,sum(sal),avg(sal) from emp group by deptno;

7、HAVING 对分组数据进行过滤

-- 查询工资总和大于 9000 的所有部门
select deptno,sum(sal) from emp 
group by deptno having sum(sal)>9000;

8、Order by 或者 Sort by 排序

• 使用 ORDER BY 时会有一个 Reducer 对全部查询结果进行排序，可以保证数据的全局有序性；
• 使用 SORT BY 时只会在每个 Reducer 中进行排序，这可以保证每个 Reducer 的输出数据是有序的，但不能保证全局有序。

由于 ORDER BY 的时间可能很长，如果你设置了严格模式 (hive.mapred.mode = strict)，则其后面必须再跟一个 limit 子句。

注 ：hive.mapred.mode 默认值是 nonstrict ，也就是非严格模式

9、distribute by 把具有相同 Key 值的数据分发到同一个 Reducer 进行处理（主要应用于分桶查询/插入）

• 默认情况下，MapReduce 程序会对 Map 输出结果的 Key 值进行散列，并均匀分发到所有 Reducer 上。如果想要把具有相同 Key 值的数据分发到同一个 Reducer 进行处理，这就需要使用 DISTRIBUTE BY 字句。
• 需要注意的是，DISTRIBUTE BY 虽然能保证具有相同 Key 值的数据分发到同一个 Reducer，但是不能保证数据在 Reducer 上是有序的。情况如下：

把以下 5 个数据发送到两个 Reducer 上进行处理：

k1
k2
k4
k3
k1

Reducer1 得到如下乱序数据：

k1
k2
k1

Reducer2 得到数据如下：

k4
k3

如果想让 Reducer 上的数据时有序的，可以结合 SORT BY 使用 (示例如下)，或者使用下面我们将要介绍的 CLUSTER BY。

-- 将数据按照部门分发到对应的 Reducer 上处理
SELECT empno, deptno, sal FROM emp DISTRIBUTE BY deptno SORT BY deptno ASC;

10、 cluster by（主要应用于分桶查询/插入）

如果 SORT BY 和 DISTRIBUTE BY 指定的是相同字段，且 SORT BY 排序规则是 ASC，此时可以使用 CLUSTER BY 进行替换，同时 CLUSTER BY 可以保证数据在全局是有序的。

-- 将数据按照部门分发到对应的 Reducer 上处理
SELECT empno, deptno, sal FROM emp CLUSTER  BY deptno ;

---

三、多表连接查询

需要特别强调：JOIN 语句的关联条件必须用 ON 指定，不能用 WHERE 指定，否则就会先做笛卡尔积，再过滤，这会导致你得不到预期的结果。

1、[inner] join ... on... 内连接

语法：SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)...;

-- 内连接
select * from emp 
join dept on emp.deptno=dept.deptno;

2、LEFT [OUTER] JOIN... on ...左连接

-- 左连接
select * from emp 
left join dept on emp.deptno=dept.deptno;

-- 左表独有连接
select * from emp t1 
LEFT JOIN dept t2 ON t1.deptno = t2.deptno where t2.deptno is null; 

3、RIGHT [OUTER] JOIN... on ...右连接

--右连接
select e.*,d.* from emp e 
right join dept d on e.deptno = d.deptno;

-- 右表独有
select * from emp t1 
right join dept t2 on t1.deptno = t2.deptno where t1.deptno is null;

4、FULL [OUTER] JOIN ...on ... 全连接

SELECT e.*,d.* FROM emp e 
FULL JOIN  dept d ON e.deptno = d.deptno;

select * from emp t1 LEFT JOIN dept t2 ON t1.deptno = t2.deptno where t2.deptno is null 
union 
select * from emp t1 right join dept t2 on t1.deptno = t2.deptno where t1.deptno is null;

5、LEFT SEMI JOIN

LEFT SEMI JOIN （左半连接）是 IN/EXISTS 子查询的一种更高效的实现。

• JOIN 子句中右边的表只能在 ON 子句中设置过滤条件;
• 查询结果只包含左边表的数据，所以只能 SELECT 左表中的列。

-- 查询在纽约办公的所有员工信息
SELECT emp.*
FROM emp LEFT SEMI JOIN dept 
ON emp.deptno = dept.deptno AND dept.loc="NEW YORK";

--上面的语句就等价于
SELECT emp.* FROM emp
WHERE emp.deptno IN (SELECT deptno FROM dept WHERE loc="NEW YORK");

6、JOIN  严格模式下 (hive.mapred.mode = strict)不支持笛卡尔积连接

笛卡尔积连接，这个连接日常的开发中可能很少遇到，且性能消耗比较大，基于这个原因，如果在严格模式下 (hive.mapred.mode = strict)，Hive 会阻止用户执行此操作。

SELECT * FROM emp JOIN dept;（严格模式下不支持）

四、综合练习题

1、查询总员工数

 select count(distinct empno) from emp;

2、查询总共有多少个职位

select count(distinct job) from emp;

3、统计每个职位有多少个员工，并且按照数量从大到小排序

select job,count(distinct empno) c from emp 
group by job order by c desc;

4、查询入职最早的员工

select * from emp join dept on emp.deptno=dept.deptno 
where emp.hiredate in (select min(hiredate) from emp);

5、统计出每个岗位的最高工资和平均工资

select job,max(sal) m_sal,round(avg(sal),2) av_sal from emp group by job;

6、查询出每个地区工资最高的员工

select a.loc,emp.* from emp join 
(select dept.loc loc,dept.deptno deptno,max(emp.sal) m_sal 
from emp join dept on emp.deptno=dept.deptno 
group by dept.loc,dept.deptno) a 
on emp.deptno=a.deptno and emp.sal=a.m_sal;

7、查询上半年入职员工最多的地区

select loc,a.m from dept join 
(select deptno,count(substr(hiredate,6,2)) m from emp
where substr(hiredate,6,2)<=6
group by deptno
order by m desc
limit 1) a
on dept.deptno = a.deptno;

五、查询优化

1、本地模式

在上面演示的语句中，大多数都会触发 MapReduce, 少部分不会触发，比如 select * from emp limit 5 就不会触发 MR，此时 Hive 只是简单的读取数据文件中的内容，然后格式化后进行输出。在需要执行 MapReduce 的查询中，你会发现执行时间可能会很长，这时候你可以选择开启本地模式。

--本地模式默认关闭，需要手动开启此功能
SET hive.exec.mode.local.auto=true;

启用后，Hive 将分析查询中每个 map-reduce 作业的大小，如果满足以下条件，则可以在本地运行它：

• 作业的总输入大小低于：hive.exec.mode.local.auto.inputbytes.max（默认为 128MB）；
• map-tasks 的总数小于：hive.exec.mode.local.auto.tasks.max（默认为 4）；
• 所需的 reduce 任务总数为 1 或 0。

因为我们测试的数据集很小，所以你再次去执行上面涉及 MR 操作的查询，你会发现速度会有显著的提升。

2、STREAMTABLE 

在多表进行联结的时候，如果每个 ON 字句都使用到共同的列（如下面的 b.key），此时 Hive 会进行优化，将多表 JOIN 在同一个 map / reduce 作业上进行。同时假定查询的最后一个表（如下面的 c 表）是最大的一个表，在对每行记录进行 JOIN 操作时，它将尝试将其他的表缓存起来，然后扫描最后那个表进行计算。因此用户需要保证查询的表的大小从左到右是依次增加的（先小后大）。

`SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key) JOIN c ON (c.key = b.key)`

然后，用户并非需要总是把最大的表放在查询语句的最后面，Hive 提供了 /*+ STREAMTABLE() */ 标志，用于标识最大的表，示例如下：

SELECT /*+ STREAMTABLE(d) */  e.*,d.* 
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE job='CLERK';

3、 MAPJOIN

如果所有表中只有一张表是小表，那么 Hive 把这张小表加载到内存中。这时候程序会在 map 阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在 map 就进行了 JOIN 操作，从而可以省略 reduce 过程，这样效率可以提升很多。Hive 中提供了 /*+ MAPJOIN() */ 来标记小表，示例如下：

SELECT /*+ MAPJOIN(d) */ e.*,d.* 
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE job='CLERK';