Sort By、Distribute By 使用说明书

╰半夏微凉° 2022-10-16 05:58 276阅读 0赞

### 本文目录 ###

*  数据准备阶段
 *   *  1.Order By(全局排序)
     *  2.Sort By(每个reduce内部排序)
     *  3.Distribute By(指定分区规则)
     *  4.Cluster By(分区字段和排序字段相同时使用)

> Hive 官方文档，关于此部分的介绍，参考：[https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy][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.截图：**  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70]

## 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 进行全局排序。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70 1]

> **注意：**  
>   在公司中，order by 是不建议使用的，效率太低了！！！在数据特别少时可以使用。生产环境中数据过多，放一个reducer中排序，一般一个reducer是根本跑不成功的，会报错。**下文我们会对 order by 进行优化，即引入sort by、distribute by 等**。  
>    
>   Hive 中可以针对 order by 设置使用模式，严格模式下，只要使用了 order by，在运行时就会直接报错的。需要添加响应配置。后面会有配置。(就是添加配置，不允许使用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;

**测试结果**  
![在这里插入图片描述][20210527150538961.png]

> **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执行**  
![在这里插入图片描述][2021052715205717.png]

**SQL语句**

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

**测试结果**  
![在这里插入图片描述][20210527151850796.png]  
  通过 `select xxx from xxx sort by deptno desc;` 将结果全部打印出来，通过上图，可以猜测`前6条数据在1个分区，后4条数据在另1个分区`。我们也可以通过 insert 导出查询结果的方式，查看每个分区具体的内容。 参考：[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导出结果查看**  
![在这里插入图片描述][20210527152627604.png]  
**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 降序` 返回结果  
![在这里插入图片描述][20210527154813683.png]

>   这个需求 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合并、数据压缩）][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;

**测试结果**  
![在这里插入图片描述][2021052716061754.png]

通过 `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]

**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导出结果查看**  
![在这里插入图片描述][20210527161401600.png]  
**Hive中如何使用自定义分区呢？**

> 参考：[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 分区表 & 分桶表][Hive _ _]

--------------------

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

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

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

[https_cwiki.apache.org_confluence_display_Hive_LanguageManual_SortBy]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70]: /images/20221014/757e1f5b03504290ad1e51a8963e76f6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x6YjM0ODExMDE3NQ_size_16_color_FFFFFF_t_70 1]: /images/20221014/7738db072d464d1d854c6fd5b44d7bc3.png
[20210527150538961.png]: /images/20221014/7747ee64854a453baa94a6a0df7b8d41.png
[2021052715205717.png]: /images/20221014/f541f274edab4bee94799c518cc4dd4a.png
[20210527151850796.png]: /images/20221014/01bd486a68354fd2b186a713ccca912b.png
[insert]: https://blog.csdn.net/lzb348110175/article/details/117250309
[20210527152627604.png]: /images/20221014/97ce16a7b8ed4e9eb4071f16cb449072.png
[20210527154813683.png]: /images/20221014/189562ad5a8e45dfb4b6e1e7746c2209.png
[MR_Shuffle_Partition_WritableComparable_Combiner]: https://blog.csdn.net/lzb348110175/article/details/115857369
[2021052716061754.png]: /images/20221014/11f6aa0e2af44ad49d81cfe6d4e743af.png
[20210527161401600.png]: /images/20221014/35a337c8aea04f9a837e01cdd3c4afd4.png
[Hive]: https://blog.csdn.net/weixin_41875224/article/details/111395872
[Hive _ _]: https://blog.csdn.net/lzb348110175/article/details/117332288