Hive DQL及优化

1.基础语法

1.1正则匹配

需要现开启正则SET hive.support.quoted.identifiers = none;反引号不在解释为其他含义，被解释为正则表达式

--查询匹配正则表达式的所有字段（c开头的库，任意表）
SET hive.support.quoted.identifiers = none; --反引号不在解释为其他含义，被解释为正则表达式
select `^c.*` from t_usa_covid19_p;
--查询当前数据库
select current_database(); --省去from关键字
--查询使用函数
select count(county) from t_usa_covid19_p;

1.2去重

1.3where筛选

• 因为where执行顺序在group by之前，所以不能使用聚合函数（可以使用如length()等普通非聚合函数）
• 分区查询（分区裁剪）：where后面指定 分区=xxx，减少全表扫描

1.4group by聚合

select的值可以是被聚合字段，也可以是使用聚合函数后的属性

2.DQL执行顺序

3.三种排序

3.1 order by全局排序

• order by 是对最终结果全局排序，因此当底层使用MapReduce引擎执行的时候，只会有一个reducetask执行。如果输出的行数太大，会导致需要很长的时间才能完成全局排序。

• order by默认升序ASC，ASC顺序的默认空排序顺序为NULLS FIRST，而DESC顺序的默认空排序顺序为NULLS LAST。当然，也可以手动指定空值在前or在后

• 在严格模式set hive.mapred.mode=strict;下，order by必须配合limit使用，无论数据量多少

3.2 cluster by字段分组，单用时组内正序排序

• 因为是分组，组内排序，所以比order by更快，但无法全局排序（仅组内有序）
• cluster by = （对同一字段）distribute by + sort by
• cluster by只能是组内升序
  

3.2.1查看reduce task个数

• Hive SQL底层走MR程序，因此cluster by就是有几个reduce task就分成几个部分
• 可以通过指令set mapreduce.job.reduces;查看reduce task的个数

3.2.2如何决定cluster by的分组个数

• 如果select的时候不指定set mapreduce.job.reduces=?;，Hive就会根据data size数据量大小自己决定reduce task的个数
• 如果select的时候手动指定了set mapreduce.job.reduces=?;那设置几就分为几组

3.3distribute by+sort by任意指定分组与排序

3.3.1分组与排序字段不一致

3.3.2分组与排序字段一样，且组内升序排序

3.4小结

4.Union和Union All

5.任意级别子查询

• Hive支持任意级别的子查询（可以任意嵌套）
• 子查询可以用AS指定别名，仅供本次DQL使用（子查询不是CTE）
• WHERE IN可以支持

5.1任意级别嵌套

5.2 IN、NOT IN不相关子查询–单列无关字段名

–不相关子查询，相当于IN、NOT IN,子查询只能选择一个列，避免歧义。

5.3 EXISTS、NOT EXISTS相关子查询–可以引用父查询

因为EXISTS本身的筛选级别是整行，因此可以SELECT多个列，还能在子查询中引用父查询

6.CTE表达式：临时结果集

• common table expression
• 仅针对本次SQL，CTE表达式不能单独执行
• 相比子查询，CTE临时表可以被多个DQL使用（减少额外的子查询开销）
• 以下SQL必须一次性执行，CTE才能生效
  
  通过临时结果集insert的两种方式
  

7. JOIN的6种方式

在Hive中，当下版本3.1.2总共支持6种join语法。分别是:

• inner join(内连接)、
• left join(左连接)、right join(右连接)
• full outer join(全外连接)、left semi join(左半开连接) 、cross join(交叉连接，也叫做笛卡尔乘积)。

7.1内连接

• 可以省略inner
• 当且仅当两个表都存在与连接条件相匹配的数据才会被保留

7.1.1早期的隐式连接

7.2 左右连接

• outer可以省略
• 左右连接一般是等价的（方向问题罢了），通常习惯用left join
• left join：以左表为基准关联右表，左表数据全部返回
• right join：以右表为基准关联左表，右表数据全部返回

7.3全外连接（全连接、外连接）

• full outer join 或者 full join
• 等价于：分别left join和rigth join，最后union distinct
  

7.4 左右半开连接（半开就是：内连接的“一半”）

这里以左半开连接left semi join为例：

• 效果上看：等价于inner join之后只返回左表包含的列（join对数据进行匹配筛选）例如：select tb1.a1, tb1.a2 from tb1 join tb2 on tb1.id = tb2.id，仅做筛选，不取tb2的列
• 效率比inner join更高

7.5交叉连接cross join

• 大表千万不能用，这个是返回两张表的笛卡尔积
• 即：没有on条件的inner join

8.Hive种对多连表JOIN的MR优化.

a join b…join c时，如果没有贯穿3表的条件字段（两个MR作业）

• MR作业1：把a和b表的匹配数据放入reduce缓存中（内存，后续比较更快）
• MR作业2：通过reduce流式传输（占用更少内存）与上述MR1产生的reduce缓存进行比较（快）
• 我们不能让内存占用过高，因此谁的表最大，谁放最后走reduce流式传输，两个小表放前面产生的reduce缓存占用更小

8.1一个or多个mr作业

注意最后一句话（join的底层mr执行逻辑）

8.2小表驱动大表

• 当出现多个MR作业时（没有贯穿多表的连接字段）
• 这里如果C表最大，那么把C表放在最后有助于减少reduce阶段的缓存占用