一篇文章彻底掌握 hive 中的 ORDER/SORT/CLUSTER/DISTRIBUTE BY 和 BUCKET 桶表

大家好，我是明哥！

本片文章，我们来总结下，HIVE 中的 order/sort/cluster/distribute by 和 BUCKET 桶表。

1 ORDER BY

• ORDER BY 会对 SQL 的最终输出结果数据做全局排序;
• ORDER BY 底层只会有一个Reducer 任务 (多个Reducer无法保证全局有序)；
• 当然只有一个 Reducer 任务时，如果输入数据规模较大，会消耗较长的计算时间；
• ORDER BY 默认的排序顺序是递增 ascending (ASC).
• 示例语句：select distinct cust_id,id_no,part_date from ads_api_cda_basic_info_parquet_pt order by cust_id;

2 SORT BY

• SORT BY 不是对 SQL 的最终输出结果数据做排序，而是对 MAP 端的输出数据，在进入 reducer 前，根据指定的字段进行排序；
• SORT BY 不会影响 REDUCER 的个数；
• SORT BY 只会保证每个 reducer 内部的数据的顺序性，而不会保证 SQL 最终输出结果的全局有序性；
• 示例语句：select distinct cust_id,id_no,part_date from ads_api_cda_basic_info_parquet_pt SORT by cust_id;

3 DISTRIBUTE BY

• DISTRIBUTE BY 指定了将 MAP 端的输出记录交给哪个 reducer 进一步处理的分发规则;
• DISTRIBUTE BY 不会影响 REDUCER 的个数；
• 具有相同的 Distribute By 字段的MAP端的输出数据，会被分发给同一个 reducer 进行处理 (默认是采用hash取模算法)；
• Distribute By 并不会保证每个 REDUCER 内部的所有记录的顺序性；
• 示例语句：select distinct cust_id,id_no,part_date from ads_api_cda_basic_info_parquet_pt distribute by cust_id;

• DISTRIBUTE BY 和 SORT BY 配合使用，可以保证每个 REDUCER 内部的所有记录的顺序性（此时，Distribute By 分区字段 和 SORT BY 排序字段，可以是不同的字段）；
• 示例语句：select distinct cust_id,id_no,part_date from ads_api_cda_basic_info_parquet_pt distribute by cust_id sort by id_no;
• DISTRIBUTE BY 和 SORT BY 配合使用，并选用合适的 DISTRIBUTE BY 字段，可以解决以下问题：

• Map输出的文件大小不均；
• Reduce输出文件大小不均；
• 小文件过多；
• 文件超大；

4 CLUSTER BY

• CLUSTER BY 等价于配合使用 DISTRIBUTE BY 和 SORT BY；
• CLUSTER BY底层的 Distribute By 分区字段 和 SORT BY 排序字段，是同一个字段；
• CLUSTER BY 不会影响 REDUCER 的个数；
• 示例语句：select distinct cust_id,id_no,part_date from ads_api_cda_basic_info_parquet_pt cluster by cust_id;

5 BUCKET 桶表

HIVE中有 BUCKET 桶表，桶表具有以下优势：

• 桶表可以支持高效的 sampling 取样；
• 桶表对高效的 mapside joins 的支持更好；
• 声明桶表时，需要指定分桶字段和桶的个数（CLUSTERED BY(user_id) INTO 31 BUCKETS）；
• 桶表的写入操作，在底层执行时，会自动添加 CLUSTER BY 子语句 以按桶表声明时指定的分桶字段来分布数据；（如果是 0.x 或 1.x 的 HIVE 版本，需要配置参数 set hive.enforce.bucketing = true; HIVE 2.X 后，该参数被remove了，等价于总是TURE;）
• 桶表的写入操作，在底层执行时，会有reducer，且reducer的个数，会自动使用声明桶表时指定的桶的个数；（如果是 0.x 或 1.x 的 HIVE 版本，需要配置参数 set hive.enforce.bucketing = true; HIVE 2.X 后，该参数被remove了，等价于总是TURE;）
• 通过选用合适的分桶字段和分桶数，桶表可以有效管控表底层的小文件的个数，进而缓解数据倾斜问题和小文件问题；
• 使用桶表来缓解数据倾斜问题和小文件问题时，所有的更改都在 DDL 这一层，不需要更改 DML 语句添加 CLUSTER/DISTRIBUTE BY 子语句，同时由于 DDL 是系统上线时或后续运维优化调整时的一次性的操作，增加了系统的弹性和运维优化的便利性；
• 可以使用类似以下 DDL 语句来声明 BUCKET 桶表，类似以下DML语句来操作桶表：

## DDL
CREATE TABLE bucket_tableA(user_id BIGINT, firstname STRING, lastname STRING)
COMMENT 'A bucketed copy of user_info'
PARTITIONED BY(ds STRING)
CLUSTERED BY(user_id) INTO 31 BUCKETS;
## DML
INSERT OVERWRITE bucket_tableA select * from xx；

note:

SPARK 对 HIVE 的桶表的支持尚不完善，具体可以参见以下JIRS和帖子：

Hive bucketing write supportissues.apache.org/jira/browse/SPARK-19256

Enable creating hive bucketed tablesissues.apache.org/jira/browse/SPARK-17729

https://towardsdatascience.com/best-practices-for-bucketing-in-spark-sql-ea9f23f7dd53towardsdatascience.com/best-practices-for-bucketing-in-spark-sql-ea9f23f7dd53