Hive教程（05）- Hive命令汇总（下）

03 函数

hive里面以内置了很多的函数，查询的时候可以直接调用，同时也可以自定义函数，具体可以参考：

• 内置函数：https://www.hadoopdoc.com/hive/hive-built-in-function
• 自定义函数：https://www.hadoopdoc.com/hive/hive-udf-intro

举例：求绝对值

hive> select abs(-3.9) from iteblog;
3.9
hive> select abs(10.9) from iteblog;
10.9

04 join原理与机制

Hive 中 的 Join 可分为:

• Common Join（Reduce阶段完成join）
• Map Join（Map 阶段完成 join）

4.1 Common Join

Hive 解析器会默认执行 Common Join，即在 Reduce 阶段完成 join。整个过程包含 Map、Shuffle、Reduce 阶段。

• Map阶段：读取源表的数据，Map 输出时候以 Join on条件中的列为key，如果 Join 有多个关联键，则以这些关联键的组合作为 key；Map输出的value 为 join 之后所关心的（select 或者 where 中需要用到的）列，同时在 value中还会包含表的Tag信息，用于标明此 value 对应哪个表；
• Shuffle 阶段：根据 key 的值进行 hash，并将 key/value 按照 hash 值推送至不同的reduce 中，这样确保两个表中相同的key 位于同一个reduce 中；
• Reduce 阶段：根据 key 的值完成 join 操作，期间通过 Tag 来识别不同表中的数据。

以下面的HQL为例，图解其过程：

SELECT a.id,a.dept,b.age
FROM a join b
ON (a.id = b.id);

4.2 Map Join

MapJoin ：通常用于一个很小的表和一个大表进行 join 的场景。

具体小表有多小，由参数hive.mapjoin.smalltable.filesize来决定，默认值为 25M。满足条件的话 Hive 在执行时候会自动转化为 MapJoin，或使用hint提示/*+ mapjoin(table) */执行 MapJoin。

解析上图中的流程：

1. 首先 Task A在客户端本地执行，负责扫描小表b的数据，将其转换成一个HashTable 的数据结构，并写入本地的文件中，之后将该文件加载到 DistributeCache 中。
2. 接下来的Task B 任务是一个没有Reduce的 MapReduce，启动 MapTasks 扫描大表 a；
3. 在Map 阶段，根据a的每一条记录去和DistributeCache中 b 表对应的 HashTable关联，并直接输出结果，因为没有 Reduce，所以有多少个Map Task就有多少个结果文件。

Map Join的相关参数：

• hive.auto.convert.join：该参数表示是否自动把任务转为 map join。默认该配置为 true。当一个表的大小小于 25 MB（配置参数：hive.mapjoin.smalltable.filesize）时，表之间的 join 会被自动转成 map join。
• hive.auto.convert.join.noconditionaltask：是否将多个 map join 合并为一个。默认为 true。多个 mapjoin 转换成 1 个时，所有小表的文件大小总和的最大值由hive.auto.convert.join.noconditionaltask.size 配置项控制。
• hive.mapjoin.localtask.max.memory.usage：将小表转成 hashtable 的本地任务的最大内存使用率,默认0.9。
• hive.mapjoin.followby.gby.localtask.max.memory.usage：如果 mapjoin 后面紧跟着一个 group by 任务，这种情况下本地任务的最大内存使用率，默认是0.55。
• hive.mapjoin.check.memory.rows：localtask 每处理完多少行，就执行内存检查。默认为 100000。

注意：Map JOIN 不适合 FULL/RIGHT OUTER JOIN。

4.3 bucket map join

4.3.1 bucket map join概念

bucket map join：在Hive 中，当表非常大，而且所有需要关联的表都是分桶表，并且关联字段都是分桶字段，那么我们就可以使用 bucket map join 来关联表。

原理：多个表使用bucket map join 来关联的时候，关联操作只会在 mapper端进行。换一种方式来理解就是，mapper处理 A表的分桶1的时候，它只会从 B 表的分桶 1 取数据，即分桶之间做关联。

如果一个表的分桶数是 2 ，那么其他表的分桶数必须是 2 或者是 2 的倍数（2，4，6等等），因此如果满足了前面这些条件，那么表的 join 操作就只会在 mapper 端进行，否则，表的 join 就是一个普通的 inner join。

bucket map join 适用于以下场景：

• 所有的表都非常大
• 关联字段使用的是表的分桶字段
• 一个表的分桶数是另一个表的分桶数的倍数
• 所有的表都是不排序的

缺点：

• 使用 bucket map join 主要的缺点就是必须要根据 SQL代码的特点来对表做分桶，也就是说，SQL 中的关联字段和表的分桶字段要一致才行，只要关联字段和分桶字段不一致，我们就没办法使用 bucket map join 了。

4.3.2 bucket map join例子

首先，表的分桶字段与关联字段要保持一致。另外，在插入数据的时候要对表做分桶，其中有一种方式就是在插入数据之前把 hive.enforce.bucketing参数设置为true:

set hive.enforce.bucketing=true

比如：

create table b1(
col0 string,
col1 string,
col2 string,
col3 string,
col4 string,
col5 string,
col6 string
)
clustered by (col0) into 32 buckets;
create table b2(
col0 string,
col1 string,
col2 string,
col3 string,
col4 string,
col5 string,
col6 string
)
clustered by (col0) into 8 buckets;
set hive.enforce.bucketing = true;
insert OVERWRITE  table b1 select * limit 10000;
insert OVERWRITE  table b2 select * limit 10000;

在做关联的时候把下面参数设置为 true:

set hive.optimize.bucketmapjoin=true;

关联代码：

select /*+ MAPJOIN(b2) */ b1.*
from b1,b2
where b1.col0=b2.col0;

4.4 Join的几种类型

Hive 中 join 有 4 种类型。分别是：

• 内关联（inner join 或者简写成 join）
• 左关联（left outer join 或者简写成 left join）
• 右关联（right outer join 或者简写成 right join）
• 全关联（full outer join 或者简写成 full join）

05 文末

本文是结合以下文献整理的博客：

• https://blog.csdn.net/u013084266/article/details/80221918
• https://www.cnblogs.com/zzjhn/p/3855572.html

谢谢大家，本文完！