Hive——一文搞懂Hive的查询

查询语句语法

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 number]

1、基本查询（Select…From）

1.1、全表和特定列查询

• 数据准备

  • 原始数据

  • 创建部门表

    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';
    

  • 导入数据

    load data local inpath '/opt/module/datas/dept.txt' into table dept;
    load data local inpath '/opt/module/datas/emp.txt' into table emp;
    

• 全表查询

  select * from emp;
  select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp ;
  

• 选择特定列查询

  select empno, ename from emp;
  

  • SQL 语言大小写不敏感；
  • SQL 可以写在一行或者多行；
  • 关键字不能被缩写也不能分行；
  • 各子句一般要分行写；
  • 使用缩进提高语句的可读性。

1.2、列别名

• 重命名一个列

• 便于计算

• 紧跟列名，也可以在列名和别名之间加入关键字AS

• 案例实操

  查询名称和部门

  select ename AS name, deptno dn from emp;
  

1.3、算术运算符

运算符	描述
A+B	A 和 B 相加
A-B	A 减去 B
A*B	A 和 B 相乘
A/B	A 除以 B
A%B	A 对 B 取余
A&B	A 和 B 按位取与
`A	B`
A^B	A 和 B 按位取异或
~A	A 按位取反

实操案例：查询出所有员工的薪水后加 1 显示。

select sal +1 from emp;

1.4、常用函数

• 求总行数（count）

  select count(*) cnt from emp; 
  

• 求工资的最大值（max）

  select max(sal) max_sal from emp; 
  

• 求工资的最小值（min）

  select min(sal) min_sal from emp;
  

• 求工资的总和（sum）

  select sum(sal) sum_sal from emp;
  

• 求工资的平均值（avg）

  select avg(sal) avg_sal from emp; 
  

1.5、Limit语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

select * from emp limit 5; 
select * from emp limit 2;

1.6、Where语句

• 使用 WHERE 子句，将不满足条件的行过滤掉

• WHERE 子句紧随 FROM 子句

• 案例实操

  # 查询出薪水大于 1000 的所有员工
  select * from emp where sal >1000;
  

  WHERE 子句中不能使用字段别名。

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

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

  操作符	支持的数据类型	描述
  A=B	基本数据类型	如果 A 等于 B 则返回 TRUE，反之返回 FALSE
  A<=>B	基本数据类型	如果 A 和 B 都为 NULL，则返回 TRUE，如果一边为 NULL， 返回 FALSE
  A<>B, A!=B	基本数据类型	A 或者 B 为 NULL 则返回 NULL；如果 A 不等于 B，则返回 TRUE，反之返回 FALSE
  A<B	基本数据类型	A 或者 B 为 NULL，则返回 NULL；如果 A 小于 B，则返回 TRUE，反之返回 FALSE
  A<=B	基本数据类型	A 或者 B 为 NULL，则返回 NULL；如果 A 小于等于 B，则返 回 TRUE，反之返回 FALSE
  A>B	基本数据类型	A 或者 B 为 NULL，则返回 NULL；如果 A 大于 B，则返回 TRUE，反之返回 FALSE
  A>=B	基本数据类型	A 或者 B 为 NULL，则返回 NULL；如果 A 大于等于 B，则返 回 TRUE，反之返回 FALSE
  A [NOT] BETWEEN B AND C	基本数据类型	如果 A，B 或者 C 任一为 NULL，则结果为 NULL。如果 A 的 值大于等于 B 而且小于或等于 C，则结果为 TRUE，反之为FALSE。 如果使用 NOT 关键字则可达到相反的效果。
  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 是基于 Java 的正则表达式，如果 A 与其匹配，则返回TRUE；反之返回 FALSE。匹配使用的是 JDK 中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串 A 相匹配，而不是只需与其字符串匹配。

• 案例实操

  • 查询出薪水等于 5000 的所有员工

    select * from emp where sal =5000;
    

  • 查询工资在 500 到 1000 的员工信息

    select * from emp where sal between 500 and 1000;
    

  • 查询 comm 为空的所有员工信息

    select * from emp where comm is null; 
    

  • 查询工资是 1500 或 5000 的员工信息

    select * from emp where sal IN (1500, 5000); 
    

1.8、Like 和 RLike

• 使用 LIKE 运算选择类似的值

• 选择条件可以包含字符或数字

  • %： 代表零个或多个字符(任意个字符)。
  • _： 代表一个字符。

• RLIKE 子句

  RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大 的语言来指定匹配条件。

• 案例实操

  • 查找名字以 A 开头的员工信息

    select * from emp where ename LIKE 'A%';
    

  • 查找名字中第二个字母为 A 的员工信息

    select * from emp where ename LIKE '_A%';
    

  • 查找名字中带有 A 的员工信息

    select * from emp where ename RLIKE '[A]';
    

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

操作符	含义
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);
  

2、分组

2.1、Group By 语句

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

案例实操

• 计算 emp 表每个部门的平均工资

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

• 计算emp 每个部门中每个岗位的最高薪水

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

2.2、Having语句

2.2.1、Having与 where不同点

• where 后面不能写分组函数，而 having 后面可以使用分组函数。
• having 只用于 group by 分组统计语句。

2.2.2、实操案例

• 求每个部门的平均工资

  select deptno, avg(sal) from emp group by deptno; 
  

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

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

3、Join语句

3.1、等值 Join

Hive 支持通常的 SQL JOIN 语句。

实操案例

根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；

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

3.2、表的别名

3.2.1、优点

• 使用别名可以简化查询。
• 使用表名前缀可以提高执行效率。

3.2.2、实操案例

合并员工表和部门表

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

3.3、内连接

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

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

3.4、左外连接

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

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

3.5、右外连接

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

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

3.6、满外连接

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

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

3.7、多表连接

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

• 创建位置表

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

• 导入数据

  load data local inpath '/opt/module/datas/location.txt' into table location;
  

• 多表连接查询

  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。

3.8、笛卡儿积

3.8.1、笛卡尔集产生的条件

• 省略连接条件
• 连接条件无效
• 所有表中的所有行互相连接

3.8.2、实操案例

select empno, dname from emp, dept; 

4、排序

4.1、全局排序（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;
    

4.2、按照别名排序

按照员工薪水的 2 倍排序。

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

4.3、多个列排序

按照部门和工资升序排序。

select ename, deptno, sal from emp order by deptno, sal;

4.4、每个 Reduce 内部排序（Sort By）

Sort By： 对于大规模的数据集 ORDER BY 的效率非常低。在很多情况下，并不需要全局排 序，此时可以使用 Sort by。Sort by 为每个 Reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集 来说不是排序。

• 设置 Reducer 个数

  set mapreduce.job.reduces=3; 
  

• 查看设置 Reducer 个数

  set mapreduce.job.reduces; 
  

• 根据部门编号降序查看员工信息

  select * from emp sort by deptno desc; 
  

• 将查询结果导入到文件中（按照部门编号降序排序）

  insert overwrite local directory '/opt/module/data/sortby-result'
  select * from emp sort by deptno desc;
  

4.5、分区（Distribute By）

Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 Reducer，通常是为 了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似MR 中 partition（自定义分区），进行分区，结合 Sort by 使用。

对于 distribute by 进行测试，一定要分配多 Reducer 进行处理，否则无法看到 distribute by 的效果。

• distribute by 的分区规则是根据分区字段的 hash 码与 Reducer 的个数进行模除后， 余数相同的分到一个区。
• Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。

4.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 号部门分到一 个分区里面去。