Hive中的排序与分桶技术详解
Hive排序与分桶技术综述
❒ ORDER BY
ORDER BY用于对 SQL 查询的最终输出结果进行全局排序。它通过一个Reducer任务完成排序，确保全局有序性。然而，当输入数据规模较大时，单一的 Reducer 任务可能导致计算时间较长。默认情况下，ORDER BY 按照递增顺序（ascending）进行排序。例如，以下 SQL 语句使用 ORDER BY 对 cust_id 进行排序：select distinct cust_id,id_no,part_date from ads_api_cda_basic_info_parquet_pt order by cust_id。
❒ SORT BY
SORT BY并非直接对 SQL 的最终输出结果进行排序，而是在数据进入 Reducer 之前，对Map 端的输出数据按照指定字段进行预排序。值得注意的是，SORT BY 不会改变 Reducer 的数量。它主要确保每个 Reducer 内部的数据维持一定的顺序，但并不保证 SQL 查询结果的全局有序性。以下是一个使用 SORT BY 的示例 SQL 语句：select distinct cust_id, id_no, part_date from ads_api_cda_basic_info_parquet_pt SORT BY cust_id。
❒ DISTRIBUTE BY
DISTRIBUTE BY是一种分发规则，它决定了将 MAP 端的输出记录分配给哪个 reducer 进行进一步处理。这种分配不会改变 reducer 的数量。在默认情况下，采用 hash 取模算法将具有相同 Distribute By 字段的 MAP 端输出数据分发给同一个 reducer。然而，需要注意的是，Distribute By 并不保证每个 reducer 内部的所有记录都保持顺序。以下是一个使用 DISTRIBUTE BY 的示例 SQL 语句：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 的排序字段可以互不相同。例如，以下 SQL 语句展示了这种配合使用的方式：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，可以有效地解决一系列问题，包括 Map 输出文件大小的不均、Reduce 输出文件大小的不均、小文件的过多以及文件过大等。
❒ CLUSTER BY
CLUSTER BY的作用相当于同时使用 DISTRIBUTE BY 和 SORT BY；在CLUSTER BY中，底层的 Distribute By 分区字段与 SORT BY 排序字段是相同的；CLUSTER BY 不会改变 REDUCER 的数量；示例 SQL 语句：select distinct cust_id, id_no, part_date from ads_api_cda_basic_info_parquet_pt cluster by cust_id；
CLUSTER BY 在 Spark Web UI 中的体现
使用 CLUSTER BY 时，在 Spark Web UI 中可以观察到其底层的工作机制。Distribute By 分区字段与 SORT BY 排序字段在 CLUSTER BY 中是统一的，这确保了数据在分区的同时也进行了排序，从而优化了查询性能。值得注意的是，CLUSTER BY不会改变 REDUCER 的数量，这也是其高效性的一个体现。
❒ BUCKET 桶表
在HIVE中，桶表（BUCKET）是一种特殊的数据表，它具有多个优势，包括高效取样、支持mapside join等。当声明桶表时，用户需要指定分桶字段和桶的数量，例如“CLUSTERED BY(user_id) INTO 31 BUCKETS”。在底层执行写入操作时，桶表会自动添加CLUSTER BY子语句，确保数据按照指定的分桶字段进行分布。此外，桶表的写入操作会涉及reducer，且reducer的数量会自动设置为声明的桶数。
通过合理选择分桶字段和数量，桶表能有效控制底层小文件的数量，从而减轻数据倾斜和小文件问题。而且，使用桶表来处理这些问题时，所有的更改都在DDL层面进行，无需修改DML语句添加CLUSTER/DISTRIBUTE BY子语句。由于DDL通常是系统上线或后续优化调整时的一次性操作，这进一步增加了系统的灵活性和运维的便捷性。
https://m.ibaotu.com/search/496380680.html
https://m.ibaotu.com/search/496380991.html
https://m.ibaotu.com/search/496380690.html
https://m.ibaotu.com/search/496381185.html
https://m.ibaotu.com/search/496381183.html
https://m.ibaotu.com/search/496381330.html
https://m.ibaotu.com/search/496380692.html
https://m.ibaotu.com/search/496381331.html
https://m.ibaotu.com/search/496380675.html
https://m.ibaotu.com/search/496381182.html
https://m.ibaotu.com/search/496381339.html
https://m.ibaotu.com/search/496380688.html
https://m.ibaotu.com/search/496380987.html
https://m.ibaotu.com/search/496479856.html
https://m.ibaotu.com/search/496380973.html
https://m.ibaotu.com/search/496380984.html
https://m.ibaotu.com/search/496380976.html
https://m.ibaotu.com/search/496380679.html
https://m.ibaotu.com/search/496380981.html
https://m.ibaotu.com/search/496380655.html
https://m.ibaotu.com/search/496380666.html
https://m.ibaotu.com/search/496380964.html
https://m.ibaotu.com/search/496380657.html
https://m.ibaotu.com/search/496380651.html
https://m.ibaotu.com/search/496380659.html
https://m.ibaotu.com/search/496412671.html
https://m.ibaotu.com/search/496381328.html
https://m.ibaotu.com/search/496380665.html
https://m.ibaotu.com/search/496380971.html
https://m.ibaotu.com/search/496380677.html
https://m.ibaotu.com/search/496380671.html
https://m.ibaotu.com/search/496381168.html
https://m.ibaotu.com/search/496381163.html
https://m.ibaotu.com/search/496380676.html
https://m.ibaotu.com/search/496381325.html
https://m.ibaotu.com/search/496479834.html
https://m.ibaotu.com/search/496380672.html
https://m.ibaotu.com/search/496380654.html
https://m.ibaotu.com/search/496381318.html
https://m.ibaotu.com/search/496380669.html
https://m.ibaotu.com/search/496380969.html
https://m.ibaotu.com/search/496380958.html
https://m.ibaotu.com/search/496380567.html
https://m.ibaotu.com/search/496380640.html
https://m.ibaotu.com/search/496380960.html
https://m.ibaotu.com/search/496380685.html
https://m.ibaotu.com/search/496380547.html
https://m.ibaotu.com/search/496380641.html
https://m.ibaotu.com/search/500568016.html
https://m.ibaotu.com/search/496380958.html
https://m.ibaotu.com/search/496380661.html
https://m.ibaotu.com/search/496412675.html
https://m.ibaotu.com/search/496380647.html
https://m.ibaotu.com/search/496380639.html
https://m.ibaotu.com/search/496380667.html
https://m.ibaotu.com/search/496380952.html
https://m.ibaotu.com/search/498615697.html
https://m.ibaotu.com/search/496380748.html
https://m.ibaotu.com/search/496380642.html
https://m.ibaotu.com/search/496381156.html
https://m.ibaotu.com/search/496380616.html
https://m.ibaotu.com/search/496380950.html
https://m.ibaotu.com/search/496380634.html
https://m.ibaotu.com/search/496381155.html
https://m.ibaotu.com/search/496380953.html
https://m.ibaotu.com/search/496381147.html
https://m.ibaotu.com/search/496380636.html
https://m.ibaotu.com/search/496380625.html
https://m.ibaotu.com/search/496380620.html
https://m.ibaotu.com/search/496380875.html
https://m.ibaotu.com/search/496380603.html
https://m.ibaotu.com/search/496380621.html
https://m.ibaotu.com/search/496381302.html
https://m.ibaotu.com/search/496380567.html
https://m.ibaotu.com/search/496380619.html
https://m.ibaotu.com/search/496479798.html
https://m.ibaotu.com/search/496380617.html
https://m.ibaotu.com/search/496380611.html
https://m.ibaotu.com/search/496380602.html
https://m.ibaotu.com/search/496380937.html
https://m.ibaotu.com/search/496380604.html
https://m.ibaotu.com/search/496380934.html
https://m.ibaotu.com/search/496380936.html
https://m.ibaotu.com/search/496381137.html
https://m.ibaotu.com/search/496380748.html
https://m.ibaotu.com/search/496380585.html
https://m.ibaotu.com/search/496380598.html
https://m.ibaotu.com/search/496381146.html
https://m.ibaotu.com/search/496381297.html
https://m.ibaotu.com/search/496380602.html
https://m.ibaotu.com/search/496380582.html
https://m.ibaotu.com/search/496380935.html
https://m.ibaotu.com/search/496380588.html
https://m.ibaotu.com/search/496381307.html
https://m.ibaotu.com/search/496381135.html
https://m.ibaotu.com/search/496380608.html
https://m.ibaotu.com/search/496380609.html
https://m.ibaotu.com/search/496380920.html
https://m.ibaotu.com/search/496380601.html
https://m.ibaotu.com/search/496380553.html
https://m.ibaotu.com/search/496380571.html
https://m.ibaotu.com/search/496380591.html
https://m.ibaotu.com/search/496380570.html
https://m.ibaotu.com/search/496380549.html
https://m.ibaotu.com/search/496380926.html
https://m.ibaotu.com/search/496380917.html
https://m.ibaotu.com/search/496380918.html
https://m.ibaotu.com/search/496380914.html
https://m.ibaotu.com/search/496380796.html
https://m.ibaotu.com/search/496381132.html
https://m.ibaotu.com/search/496380555.html
https://m.ibaotu.com/search/496380567.html
https://m.ibaotu.com/search/496380539.html
https://m.ibaotu.com/search/496380562.html
https://m.ibaotu.com/search/496381286.html
https://m.ibaotu.com/search/496380554.html
https://m.ibaotu.com/search/496380910.html
https://m.ibaotu.com/search/496479753.html
https://m.ibaotu.com/search/496380572.html
https://m.ibaotu.com/search/496380552.html
https://m.ibaotu.com/search/496380899.html
https://m.ibaotu.com/search/496381129.html
https://m.ibaotu.com/search/496380916.html
https://m.ibaotu.com/search/496479746.html
https://m.ibaotu.com/search/496380591.html
https://m.ibaotu.com/search/496380620.html
https://m.ibaotu.com/search/496380593.html
https://m.ibaotu.com/search/496380893.html
https://m.ibaotu.com/search/496381120.html
https://m.ibaotu.com/search/496381121.html
https://m.ibaotu.com/search/496380889.html
https://m.ibaotu.com/search/496380911.html
https://m.ibaotu.com/search/496381113.html
https://m.ibaotu.com/search/496381119.html
https://m.ibaotu.com/search/496381281.html
https://m.ibaotu.com/search/496380888.html
https://m.ibaotu.com/search/496380878.html
https://m.ibaotu.com/search/496380880.html
https://m.ibaotu.com/search/496380887.html
https://m.ibaotu.com/search/496381278.html
https://m.ibaotu.com/search/496380882.html
https://m.ibaotu.com/search/496380879.html
https://m.ibaotu.com/search/496380544.html
https://m.ibaotu.com/search/496381282.html
https://m.ibaotu.com/search/496380863.html
https://m.ibaotu.com/search/496380912.html
https://m.ibaotu.com/search/496381276.html
https://m.ibaotu.com/search/496380876.html
https://m.ibaotu.com/search/496380861.html
https://m.ibaotu.com/search/496380874.html
https://m.ibaotu.com/search/496380875.html
https://m.ibaotu.com/search/496381268.html
https://m.ibaotu.com/search/496479725.html
https://m.ibaotu.com/search/496381263.html
https://m.ibaotu.com/search/496479730.html
https://m.ibaotu.com/search/496381098.html
https://m.ibaotu.com/search/496381269.html
https://m.ibaotu.com/search/496380858.html
https://m.ibaotu.com/search/496381267.html
https://m.ibaotu.com/search/496380851.html
https://m.ibaotu.com/search/496381101.html
https://m.ibaotu.com/search/496380854.html
https://m.ibaotu.com/search/496381266.html
https://m.ibaotu.com/search/496380860.html
https://m.ibaotu.com/search/496380846.html
https://m.ibaotu.com/search/496381093.html
https://m.ibaotu.com/search/496380855.html
https://m.ibaotu.com/search/496479715.html
https://m.ibaotu.com/search/496381100.html
https://m.ibaotu.com/search/496380870.html
https://m.ibaotu.com/search/496380845.html
https://m.ibaotu.com/search/496381096.html
https://m.ibaotu.com/search/496381259.html
https://m.ibaotu.com/search/496381097.html
https://m.ibaotu.com/search/501365821.html
https://m.ibaotu.com/search/496380832.html
https://m.ibaotu.com/search/496380862.html
https://m.ibaotu.com/search/496381257.html
https://m.ibaotu.com/search/496479713.html
https://m.ibaotu.com/search/496380829.html
https://m.ibaotu.com/search/496380837.html
https://m.ibaotu.com/search/496380831.html
https://m.ibaotu.com/search/496381080.html
https://m.ibaotu.com/search/496381065.html