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

1）下面表中描述了谓词操作符，这些操作符同样可以用于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相匹配，而不是只需与其字符串匹配。

distinct去重

如果去重的是多个字段，那么是按多个字段一起看：
如：A-B-C 和 A-B-D就不是重复字段，无法根据A去去重。

Like和RLike

1）使用LIKE运算选择类似的值

2）选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。

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

4）案例实操
（1）查找名字以A开头的员工信息

  1. hive (default)> select * from emp where ename LIKE 'A%';
  2. hive (default)> select * from emp where ename RLIKE '^A';

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

  1. hive (default)> select * from emp where ename LIKE '_A%';
  2. hive (default)> select * from emp where ename RLIKE '^.A';

（3）查找名字中带有A的员工信息

  1. hive (default)> select * from emp where ename  LIKE '%A%';
  2. hive (default)> select * from emp where ename  RLIKE '[A]';

Group By

本质：

reduce阶段，将key相同的数据进行分

image.png
java中：
相当于创建了自定义分组，重写了相关方法：
如果不重写则默认调用上一级方法
image.png

image.png

多重分组：

  1. GROUP BY xx，yy
  3.     如各部门的各工种<br />        组里面再细分工<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/21361442/1619164314794-a3c62e9b-59a2-438a-b128-6de05e14756e.png#height=204&id=uP5Zr&margin=%5Bobject%20Object%5D&name=image.png&originHeight=407&originWidth=453&originalType=binary&ratio=1&size=123462&status=done&style=none&width=226.5)<br />group by 出现的字段都要出现在select中

Order by

order by 出现的字段都要出现在select中（hive有子查询之后的bug）

Where语句

1）使用WHERE子句，将不满足条件的行过滤掉
2）WHERE子句紧随FROM子句
3）案例实操
查询出薪水大于1000的所有员工

  1. hive (default)> select * from emp where sal >1000;

注意：where子句中不能使用字段别名。

Having语句

1）having与where不同点
（1）where后面不能写分组聚合函数，而having后面可以使用分组聚合函数。
（2）having只用于group by分组统计语句。
2）案例实操
（1）求部门平均薪水大于2000的部门
求每个部门的平均工资

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

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

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

Join语句

等值Join

Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
案例实操
根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；

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

内连接

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

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

左外连接

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

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

右外连接

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

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

满外连接

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

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

多表连接

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。
数据准备 在/opt/module/hive/datas/下：vim location.txt
1700 Beijing
1800 London
1900 Tokyo
创建位置表

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

导入数据

  1. hive (default)> load data local inpath '/opt/module/hive/datas/location.txt' into table location;

多表连接查询

  1. hive (default)>SELECT e.ename, d.dname, l.loc_name
  2. FROM  emp e 
  3. JOIN  dept d
  4. ON    d.deptno = e.deptno 
  5. JOIN  location l
  6. 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。

总结:
reduce进行分组的时候是根据key分组（每次只能传入一个key）的，所以当键相同的时候，只要封装好了，就不用再启动一个job了，所以：
①当多表相join时，键值相等那么只要传入这个相同的key值即可进行分组而无序再启动几个job进行join。

②当多表向join时，键值不同，那么需要传入不同的key进行join。

笛卡尔积

hive处理大数据，尽量避免笛卡尔积造成数据膨胀
1）笛卡尔集会在下面条件下产生
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
2）案例实操

  1. hive (default)> select empno, dname from emp, dept;

排序

多个列排序

按照部门和工资升序排序

  1. hive (default)> select ename, deptno, sal from emp order by deptno, sal ;

全局排序（Order By）

Order By：全局排序，只有一个Reducer
一定要+limit，因为数据量太大了
1）使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序

2）ORDER BY子句在SELECT语句的结尾

每个Reducer内部排序（Sort By）

Sort By：对于大规模的数据集order by的效率非常低（只有一个reduce）。在很多情况下，并不需要全局排序，此时可以使用sort by。因为多个迸发的reduce能提高效率。
Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。

热改：

1）设置reduce个数（默认：mapreduce.job.reduces=-1）

  1. hive (default)> set mapreduce.job.reduces=3;

2）查看设置reduce个数

  1. hive (default)> set mapreduce.job.reduces;

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

  1. hive (default)> select * from emp sort by deptno desc;

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

  1. hive (default)> insert overwrite local directory '/opt/module/hive/datas/sortby-result'
  2.  select * from emp sort by deptno desc;

冷改

参数配置方式

1）查看当前所有的配置信息

  1. hive>set;

2）参数的配置三种方式
（1）配置文件方式
默认配置文件：hive-default.xml
用户自定义配置文件：hive-site.xml
注意：用户自定义配置会覆盖默认配置。另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。配置文件的设定对本机启动的所有Hive进程都有效。

（2）命令行参数方式
启动Hive时，可以在命令行添加-hiveconf param=value来设定参数。
例如：

  1. [atguigu@hadoop103 hive]$ bin/hive -hiveconf mapred.reduce.tasks=10;

或者

  1. [atguigu@hadoop103 hive]$ bin/beeline -u jdbc:hive2://hadoop102:10000 -n atguigu -hiveconf mapred.reduce.tasks=10;

注意：仅对本次hive启动有效

查看参数设置： 

  1. hive (default)> set mapred.reduce.tasks;

（3）参数声明方式
可以在HQL中使用SET关键字设定参数
例如：

  1. hive (default)> set mapred.reduce.tasks=100;

注意：仅对本次hive启动有效。
查看参数设置

  1. hive (default)> set mapred.reduce.tasks;

上述三种设定方式的优先级依次递增。即配置文件<命令行参数<参数声明。注意某些系统级的参数，例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在会话建立以前已经完成了。

分区（Distribute By）少用

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

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

1）案例实操：
（1）先按照部门编号分区，再按照员工编号降序排序。

  1. hive (default)> set mapreduce.job.reduces=3;
  2. hive (default)> insert overwrite local directory '/opt/module/hive/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

注意：
Ø distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。
Ø Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
Ø 演示完以后mapreduce.job.reduces的值要设置回-1，否则下面分区or分桶表load跑mr的时候有可能会报错

*无关的说明：
如果不进行设置reduce的个数，mapreduce.job.reduces=-1，则reduce根据：
分桶表进行划分
或文件大小自动划分：
（注：mapreduce.job.reduces在hive里默认-1，在hadoop里默认为1）

  1. //每个reducer任务处理的数据量,本里中是256M
  2. hive> set hive.exec.reducers.bytes.per.reducer;
  3. hive.exec.reducers.bytes.per.reducer=256000000
  5. //每个任务的最大reducer数量,默认是1009
  6. hive> set hive.exec.reducers.max ;
  7. hive.exec.reducers.max=1009

举个例子:
如果map端输出的总大小是9G,那么reducer数量就是9000/256=35
要减少reducer个数,可以调大:

  1. hive.exec.reducers.bytes.per.reducer=256000000

比如调大到500M,reducer数目就是18个

  1. hive.exec.reducers.bytes.per.reducer=500000000

分区排序（Cluster By）少用

当distribute by和sort by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

（1）以下两种写法等价

  1. hive (default)> select * from emp cluster by deptno;
  2. hive (default)> select * from emp distribute by deptno sort by deptno;

注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。

抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。
查询表stu_buck中的数据。

  1. hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。
y代表将表格分成4份，x代表其中的第x份

union 和 union all

union去重
union all 不去重

使用小技巧
合并可以设置为0后相加
image.png

多维聚合

grouping set
grouping sets后面的字段必须是group by里面的字段

  1. select 字段，...,聚合函数 from 表 group by 字段,..grouping sets((字段1)，（字段2，字段3）,...)

如：

  1. select A,B,C,count(1) num from P group by A,B,C grouping sets( (A),(B),(A,B),())

等价于:

  1. select A,null B,null C,count(1) num from P group by A
  2.   union all
  3.   select null A,B,null C,count(1) num from P group by B
  4.   union all
  5.   select A,B,null C,count(1) num from P group by A,B
  6.   union all
  7.   select null A,null B,null C,count(1) num from P

效果是将三个维度的表union起来，效果和union一致。
如果用的是union则会有5个job，而使用多维聚合1个job即可完成。