Sort By、Distribute By 使用说明书

Hive 官方文档，关于此部分的介绍，参考：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy

数据准备阶段

1.准备工作：
 1.创建数据库 test，库下创建表 dept_info(部门表) 和 user_info(用户表)。

# 建表语句
# dept_info 部门表
create table dept_info(dept_no int,dept_name string) 
row format delimited fields terminated by ',';
#user_info 用户表
create table user_info(user_id int,user_name string,dept_no int,sallary double) 
row format delimited fields terminated by ',';

 2.准备数据，使用load 方式将数据装载至对应表
 
2.对应数据：
dept_info.txt
1,数据部
2,测试部
3,运维部
4,架构部
 
user_info.txt
1,Lucy,1,500
2,Lily,3,1000
3,James,4,300
4,Bob,2,600
5,John,2,400
6,Mary,3,800
7,Paul,1,1200
8,Slide,3,200
9,Clark,4,600
10,Smith,2,900
 
3.截图：

1.Order By(全局排序)

  Order By 用于结果集的排序。也可以称之为全局排序。对于 MR 任务来说，如果我们使用了 Order By 排序，意味着MR 任务只会有一个 Reducer 参与排序。

  在 Hive 中执行脚本时，我们可以通过 set mapreduce.job.reduces = 10 来设置 reduce 的个数为 10。但只要使用了 Order By 排序，即使设置了 10 个reduce ，也是不会生效的。Order By 就是一个全局排序，只能用一个 Reduce 进行全局排序。

注意：
  在公司中，order by 是不建议使用的，效率太低了！！！在数据特别少时可以使用。生产环境中数据过多，放一个reducer中排序，一般一个reducer是根本跑不成功的，会报错。下文我们会对 order by 进行优化，即引入sort by、distribute by 等。
 
  Hive 中可以针对 order by 设置使用模式，严格模式（即hive.mapred.mode = strict）下，只要使用了 order by，在运行时就会直接报错的，order by子句必须后跟一个 limit 子句。如果将hive.mapred.mode设置为非限制，则不需要限制子句。原因是为了强加所有结果的总顺序，必须有一个redcue对最终输出进行排序。如果输出中的行数太大，则单个还原器可能需要很长时间才能完成。

Hive 中不允许使用order by，需要添加相应配置。后面会有配置。

SQL语句

select
    d.dept_no,
    d.dept_name,
    u.user_id,
    u.user_name,
    u.sallary
from
    dept_info d
    join user_info u on d.dept_no = u.dept_no
order by
    u.sallary desc;

测试结果

TODO 添加如何禁用 order by

2.Sort By(每个reduce内部排序)

  Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用 sort by。Sort by 在每个 Reducer 内部进行排序，即使每个 reduce 内部是有序的，但是对于全局结果集 来说也还是乱序的。

  Hive 任务需要使用几个 reduce ，完全取决于任务。Hive 默认 reduce 配置 mapreduce.job.reduces=-1，可以手动配置。设置2个reduce运行，set mapreduce.job.reduces=2，根据部门编号 dept_no ，采用默认分区规则，示例如下：

设置2个reduce执行

SQL语句

select
    user_id,
    user_name,
    dept_no,
    sallary
from
    user_info
sort by
    dept_no desc;

测试结果

  通过 select xxx from xxx sort by deptno desc; 将结果全部打印出来，通过上图，可以猜测前6条数据在1个分区，后4条数据在另1个分区。我们也可以通过 insert 导出查询结果的方式，查看每个分区具体的内容。 参考：insert 导出查询结果

insert方式导出查询结果至 sort-by 目录下

insert overwrite local directory
'/opt/module/hive/export/sort-by'
select
    user_id,
    user_name,
    dept_no,
    sallary
from
    user_info
sort by
    dept_no desc;

insert导出结果查看

sort by 按什么规则分区：

1. 使用 sort by 时，通常会跟 distribute by 字段一起连用，通过 distribute by 来指定分区规则。

2. 当不指定分区规则时(只使用sort by )，则使用默认的内部算法进行分区。

  如果按照查询语句中的某个字段进行分区。因为字段的不确定性，很大可能会导致数据倾斜，所以单独使用 sort by 不使用 distribute by 指定分区规则的话，它采用内部默认算法进行分区
 
接下来继续学习 Distribute by ↓↓↓↓

3.Distribute By(指定分区规则)

  Distribute By 是用来指定分区规则的，它结合 Sort By 一起使用。上文介绍的单独使用 Sort By 对部门编号排序，因为没有指定分区规则，Sort By 则随机分区排序。Distribute By 类似 MR 中 partition（自定义分区），进行分区，结合 sort by 使用。通常是先用 Distribute By 指定分区规则，然后再使用 Sort By 对分区内数据排序。

需求：

将如下数据，根据 dept_no 升序，sallary 降序 返回结果

  这个需求 order by 也能够实现，但是 order by 只有1个 reduce。数据量少时是ok 的。此处就不过多介绍 order by ，我们来介绍 Distribute By 和 Sort By 如何实现这一需求

需求分析：

  一共有4个部门，我们可以按照 dept_no 进行分区，此处设置 reduce 个数为 4(set mapreduce.job.reduces=4)。那么 1、2、3、4 共四个部门，会进入到4个不同的分区，然后再使用 sort by 对每个分区内的数据，按照 sallary 字段降序即可。

分区规则：

  因为 Sort By 是在每个 reduce 内部排序。hive 运行在 MR 上，Map 阶段负责对数据进行切片，Reduce 阶段负责对每个切片的数据进行汇总计算。此处接涉及到 MR 的切片规则了
 
  默认情况下，Hadoop的分区规则是：HashPartitioner， 默认分区是根据 key 的 hashCode 对 ReduceTasks 个数取模得到的，用户无法控制哪个key 存储到哪个分区。默认分区规则源码，如下所示：

/** Partition keys by their {@link Object#hashCode()}. */
@InterfaceAudience.Public
@InterfaceStability.Stable
public class HashPartitioner<K, V> extends Partitioner<K, V> {
 	/** Use {@link Object#hashCode()} to partition. */
 	public int getPartition(K key, V value, int numReduceTasks) {
   		return (key.hashCode() & Integer.MAX_VALUE) % numReduceTasks;
 	}
}

提示：
  分区详细规则、自定义分区规则，可参考：MR之Shuffle机制（Partition分区、WritableComparable排序、Combiner合并、数据压缩）

分区情况：

部门编号 1、2、3、4
1%4 = 1，部门1，分在1号分区  【使用 distribute by，用户1、7会分在1号分区】
2%4 = 2，部门2，分在2号分区  【使用 distribute by，用户4、5、10会分在2号分区】
3%4 = 3，部门3，分在3号分区  【使用 distribute by，用户2、6、8会分在3号分区】
4%4 = 0，部门4，分在0号分区  【使用 distribute by，用户3、9会分在0号分区】

设置4个reduce

set mapreduce.job.reduces=4;

SQL语句

select
    user_id,
    user_name,
    dept_no,
    sallary
from
    user_info
distribute by    -- 指定分区规则
    dept_no
sort by   -- 指定分区内排序规则
    sallary desc;

测试结果

  通过 select xxx from xxx distribute by dept_no sort by sallary desc; 将结果全部打印出来，通过上图可以猜测前2条数据在1个分区，3-4条数据在1个分区，5-7条数据在1个分区，最后3条在1个分区。我们也可以通过 insert 导出查询结果的方式，查看每个分区具体的内容。 参考：insert 导出查询结果

insert方式导出查询结果至 distribute-by 目录下

insert overwrite local directory
'/opt/module/hive/export/distribute-by'
select
    user_id,
    user_name,
    dept_no,
    sallary
from
    user_info
distribute by
    dept_no
sort by
    sallary desc;

insert导出结果查看

Hive中如何使用自定义分区呢？

参考：Hive自定义分区器流程

4.Cluster By(分区字段和排序字段相同时使用)

  当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。

  cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。Cluster by 不常用的。

接上文需求，如果新需求，需要按照 dept_no 分区，分区内再按照 dept_no 排序。
 
1.使用distribute by 和 sort by，语句如下：
 select xxx from user_info distribute by dept_no sort by dept_no;
 
2.使用cluster by可简化语句，语句如下
 select xxx from user_info cluster by dept_no;

结束语：
  order by、sort by、distribute by、cluster by 这4个关键字用起来很简单，但是经常放在一起去比较，面试中也经常会被问到，本文拿出来简单做一下介绍，初学并进行一下小的记录。

下一篇：Hive 分区表 & 分桶表

---

博主写作不易，加个关注呗

求关注、求点赞，加个关注不迷路 ヾ(◍°∇°◍)ﾉﾞ

我不能保证所写的内容都正确，但是可以保证不复制、不粘贴。保证每一句话、每一行代码都是亲手敲过的，错误也请指出，望轻喷 Thanks♪(･ω･)ﾉ