hive之group by详解

亦凉 2022-05-25 07:19 395阅读 0赞

#### 有部分是转自 https://blog.csdn.net/lzm1340458776/article/details/43231707 ####

hive distribute by 和group by 的区别：
    group by是对检索结果的保留行进行单纯分组，一般总爱和聚合函数一块用例如AVG（），COUNT（），max（），main（）等一块用。

group by操作表示按照某些字段的值进行分组，有相同的值放到一起，语法样例如下：

\[java\]  [view plain][]   [copy][view plain]

1.  select col1,col2,count(1),sel\_expr(聚合操作)  
2.  from tableName  
3.  where condition  
4.  group by col1,col2  
5.  having...

注意：

(1)：select后面的非聚合列必须出现在group by中(如上面的col1和col2)。

(2)：除了普通列就是一些聚合操作。

group的特性：

(1)：使用了reduce操作，受限于reduce数量，通过参数mapred.reduce.tasks设置reduce个数。

(2)：输出文件个数与reduce数量相同，文件大小与reduce处理的数量有关。

问题：

(1)：网络负载过重。

(2)：出现数据倾斜(我们可以通过hive.groupby.skewindata参数来优化数据倾斜的问题)。

下面我们通过一些语句来深入体会group by相关的操作：

\[java\]  [view plain][]   [copy][view plain]

1.  insert overwrite table pv\_gender\_sum  
2.  select pv\_users.gender count(distinct pv\_users.userid)  
3.  from pv\_users  
4.  group by pv\_users.gender;

附：上述语句是从pv\_users表中查询出性别和去重后的总人数，并且根据性别分组，然后将数据覆盖插入到pv\_gender\_sum中。

在select语句中可以有多个聚合操作，但是如果多个聚合操作中同时使用了distinct去重，那么distinct去重的列必须相同，如下语句不合法：

\[java\]  [view plain][]   [copy][view plain]

1.  insert overwrite table pv\_gender\_agg  
2.  select pv\_users.gender,count(distinct pv\_users.userid),count(distinct pv\_users.ip)  
3.  from pv\_users  
4.  group by pv\_users.gender;

注：上述语句之所以不合法，是因为distinct关键字去重的列不一样。一个是对userid去重，一个是对ip去重！

对上述非法语句做如下修改及将distinct的类改为一致就正确：

\[java\]  [view plain][]   [copy][view plain]

1.  insert overwrite table pv\_gender\_agg  
2.  select pv\_users.gender,count(distinct pv\_users.userid),count(distinct pv\_users.userid)  
3.  from pv\_users  
4.  group by pv\_users.gender;

注：上述语句正确无误。

还有一个要注意的就是文章开头所说的知识点即select后面的非聚合列必须出现在group by中，否则非法，如下：

\[java\]  [view plain][]   [copy][view plain]

1.  select uid,name,count(sal)  
2.  from users  
3.  group by uid;

注：上述语句是非法的因为select中出现了两个两个非聚合列即uid和name，但是group by中只有uid,所以非法。

修改上述语句即将name也加到group by后面。

\[java\]  [view plain][]   [copy][view plain]

1.  select uid,name,count(sal)  
2.  from users  
3.  group by uid,name;

注：上述语句就正确。  
  
  
下面我们来看看一些优化的属性：

(1)：Reduce的个数设置

设置reduce的数量：mapred.reduce.tasks，默认为1个，如下图：

当将reduce的个数设置为3个的时候，如下：

![70][]

(2)：group by的Map端聚合

hive.map.aggr控制如何聚合，我使用的版本是0.90，默认是开启的即为true，这个时候Hive会在Map端做第一级的聚合。这通常提供更好的效果，但是要求更多的内存才能运行效果。

\[java\]  [view plain][]   [copy][view plain]

1.  hive>   
2.  hive> set hive.map.aggr=**true**;  
3.  hive> select count(1) from employees;

(3)：数据倾斜

hive.groupby.skewdata属性设定是否在数据分布不均衡，即发生倾斜时进行负载均衡，当选项hive.groupby.skewdata=true时，生成的查询计划会有两个MapReduce即产生两个Job，在第一个MapReduce中，Map的输出结果会随机的分布到不同的Reduce中，对Reduce做部分聚合操作并输出结果，此时相同的group by key有可能分发到不同的reduce中，从而达到负载均衡的目的，第二个MapReduce任务根据预处理的数据按照group by key分布到Reduce中(此时Key相同就分布到同一个Reduce中)，最后完成聚合操作。

参数设置前只启动了一个Job，如下图：

![70 1][]

参数设置前只启动了两个Job，如下图：

![70 2][]

distribute by是控制在map端如何拆分数据给reduce端的。hive会根据distribute by后面列，对应reduce的个数进行分发，默认是采用hash算法。sort by为每个reduce产生一个排序文件。在有些情况下，你需要控制某个特定行应该到哪个reducer，这通常是为了进行后续的聚集操作。distribute by刚好可以做这件事。因此，distribute by经常和sort by配合使用。
    注：Distribute by和sort by的使用场景
    1.Map输出的文件大小不均。
    2.Reduce输出文件大小不均。
    3.小文件过多。
    4.文件超大。

[view plain]: https://blog.csdn.net/lzm1340458776/article/details/43231707#
[70]: /images/20220525/85219b7578e448b889e659dd3d7102b5.png
[70 1]: /images/20220525/8814d1a8ca884866ab5a65db68735bee.png
[70 2]: /images/20220525/c3b62a7ed12a4dcd9b4dbaf9a118b342.png