Hive中的排序与分桶技术详解

Hive排序与分桶技术综述❒ ORDER BYORDER 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 BYSORT 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 BYDISTRIBUTE 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 BYCLUSTER 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_idCLUSTER 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.htmlhttps://m.ibaotu.com/search/496380991.htmlhttps://m.ibaotu.com/search/496380690.htmlhttps://m.ibaotu.com/search/496381185.htmlhttps://m.ibaotu.com/search/496381183.htmlhttps://m.ibaotu.com/search/496381330.htmlhttps://m.ibaotu.com/search/496380692.htmlhttps://m.ibaotu.com/search/496381331.htmlhttps://m.ibaotu.com/search/496380675.htmlhttps://m.ibaotu.com/search/496381182.htmlhttps://m.ibaotu.com/search/496381339.htmlhttps://m.ibaotu.com/search/496380688.htmlhttps://m.ibaotu.com/search/496380987.htmlhttps://m.ibaotu.com/search/496479856.htmlhttps://m.ibaotu.com/search/496380973.htmlhttps://m.ibaotu.com/search/496380984.htmlhttps://m.ibaotu.com/search/496380976.htmlhttps://m.ibaotu.com/search/496380679.htmlhttps://m.ibaotu.com/search/496380981.htmlhttps://m.ibaotu.com/search/496380655.htmlhttps://m.ibaotu.com/search/496380666.htmlhttps://m.ibaotu.com/search/496380964.htmlhttps://m.ibaotu.com/search/496380657.htmlhttps://m.ibaotu.com/search/496380651.htmlhttps://m.ibaotu.com/search/496380659.htmlhttps://m.ibaotu.com/search/496412671.htmlhttps://m.ibaotu.com/search/496381328.htmlhttps://m.ibaotu.com/search/496380665.htmlhttps://m.ibaotu.com/search/496380971.htmlhttps://m.ibaotu.com/search/496380677.htmlhttps://m.ibaotu.com/search/496380671.htmlhttps://m.ibaotu.com/search/496381168.htmlhttps://m.ibaotu.com/search/496381163.htmlhttps://m.ibaotu.com/search/496380676.htmlhttps://m.ibaotu.com/search/496381325.htmlhttps://m.ibaotu.com/search/496479834.htmlhttps://m.ibaotu.com/search/496380672.htmlhttps://m.ibaotu.com/search/496380654.htmlhttps://m.ibaotu.com/search/496381318.htmlhttps://m.ibaotu.com/search/496380669.htmlhttps://m.ibaotu.com/search/496380969.htmlhttps://m.ibaotu.com/search/496380958.htmlhttps://m.ibaotu.com/search/496380567.htmlhttps://m.ibaotu.com/search/496380640.htmlhttps://m.ibaotu.com/search/496380960.htmlhttps://m.ibaotu.com/search/496380685.htmlhttps://m.ibaotu.com/search/496380547.htmlhttps://m.ibaotu.com/search/496380641.htmlhttps://m.ibaotu.com/search/500568016.htmlhttps://m.ibaotu.com/search/496380958.htmlhttps://m.ibaotu.com/search/496380661.htmlhttps://m.ibaotu.com/search/496412675.htmlhttps://m.ibaotu.com/search/496380647.htmlhttps://m.ibaotu.com/search/496380639.htmlhttps://m.ibaotu.com/search/496380667.htmlhttps://m.ibaotu.com/search/496380952.htmlhttps://m.ibaotu.com/search/498615697.htmlhttps://m.ibaotu.com/search/496380748.htmlhttps://m.ibaotu.com/search/496380642.htmlhttps://m.ibaotu.com/search/496381156.htmlhttps://m.ibaotu.com/search/496380616.htmlhttps://m.ibaotu.com/search/496380950.htmlhttps://m.ibaotu.com/search/496380634.htmlhttps://m.ibaotu.com/search/496381155.htmlhttps://m.ibaotu.com/search/496380953.htmlhttps://m.ibaotu.com/search/496381147.htmlhttps://m.ibaotu.com/search/496380636.htmlhttps://m.ibaotu.com/search/496380625.htmlhttps://m.ibaotu.com/search/496380620.htmlhttps://m.ibaotu.com/search/496380875.htmlhttps://m.ibaotu.com/search/496380603.htmlhttps://m.ibaotu.com/search/496380621.htmlhttps://m.ibaotu.com/search/496381302.htmlhttps://m.ibaotu.com/search/496380567.htmlhttps://m.ibaotu.com/search/496380619.htmlhttps://m.ibaotu.com/search/496479798.htmlhttps://m.ibaotu.com/search/496380617.htmlhttps://m.ibaotu.com/search/496380611.htmlhttps://m.ibaotu.com/search/496380602.htmlhttps://m.ibaotu.com/search/496380937.htmlhttps://m.ibaotu.com/search/496380604.htmlhttps://m.ibaotu.com/search/496380934.htmlhttps://m.ibaotu.com/search/496380936.htmlhttps://m.ibaotu.com/search/496381137.htmlhttps://m.ibaotu.com/search/496380748.htmlhttps://m.ibaotu.com/search/496380585.htmlhttps://m.ibaotu.com/search/496380598.htmlhttps://m.ibaotu.com/search/496381146.htmlhttps://m.ibaotu.com/search/496381297.htmlhttps://m.ibaotu.com/search/496380602.htmlhttps://m.ibaotu.com/search/496380582.htmlhttps://m.ibaotu.com/search/496380935.htmlhttps://m.ibaotu.com/search/496380588.htmlhttps://m.ibaotu.com/search/496381307.htmlhttps://m.ibaotu.com/search/496381135.htmlhttps://m.ibaotu.com/search/496380608.htmlhttps://m.ibaotu.com/search/496380609.htmlhttps://m.ibaotu.com/search/496380920.htmlhttps://m.ibaotu.com/search/496380601.htmlhttps://m.ibaotu.com/search/496380553.htmlhttps://m.ibaotu.com/search/496380571.htmlhttps://m.ibaotu.com/search/496380591.htmlhttps://m.ibaotu.com/search/496380570.htmlhttps://m.ibaotu.com/search/496380549.htmlhttps://m.ibaotu.com/search/496380926.htmlhttps://m.ibaotu.com/search/496380917.htmlhttps://m.ibaotu.com/search/496380918.htmlhttps://m.ibaotu.com/search/496380914.htmlhttps://m.ibaotu.com/search/496380796.htmlhttps://m.ibaotu.com/search/496381132.htmlhttps://m.ibaotu.com/search/496380555.htmlhttps://m.ibaotu.com/search/496380567.htmlhttps://m.ibaotu.com/search/496380539.htmlhttps://m.ibaotu.com/search/496380562.htmlhttps://m.ibaotu.com/search/496381286.htmlhttps://m.ibaotu.com/search/496380554.htmlhttps://m.ibaotu.com/search/496380910.htmlhttps://m.ibaotu.com/search/496479753.htmlhttps://m.ibaotu.com/search/496380572.htmlhttps://m.ibaotu.com/search/496380552.htmlhttps://m.ibaotu.com/search/496380899.htmlhttps://m.ibaotu.com/search/496381129.htmlhttps://m.ibaotu.com/search/496380916.htmlhttps://m.ibaotu.com/search/496479746.htmlhttps://m.ibaotu.com/search/496380591.htmlhttps://m.ibaotu.com/search/496380620.htmlhttps://m.ibaotu.com/search/496380593.htmlhttps://m.ibaotu.com/search/496380893.htmlhttps://m.ibaotu.com/search/496381120.htmlhttps://m.ibaotu.com/search/496381121.htmlhttps://m.ibaotu.com/search/496380889.htmlhttps://m.ibaotu.com/search/496380911.htmlhttps://m.ibaotu.com/search/496381113.htmlhttps://m.ibaotu.com/search/496381119.htmlhttps://m.ibaotu.com/search/496381281.htmlhttps://m.ibaotu.com/search/496380888.htmlhttps://m.ibaotu.com/search/496380878.htmlhttps://m.ibaotu.com/search/496380880.htmlhttps://m.ibaotu.com/search/496380887.htmlhttps://m.ibaotu.com/search/496381278.htmlhttps://m.ibaotu.com/search/496380882.htmlhttps://m.ibaotu.com/search/496380879.htmlhttps://m.ibaotu.com/search/496380544.htmlhttps://m.ibaotu.com/search/496381282.htmlhttps://m.ibaotu.com/search/496380863.htmlhttps://m.ibaotu.com/search/496380912.htmlhttps://m.ibaotu.com/search/496381276.htmlhttps://m.ibaotu.com/search/496380876.htmlhttps://m.ibaotu.com/search/496380861.htmlhttps://m.ibaotu.com/search/496380874.htmlhttps://m.ibaotu.com/search/496380875.htmlhttps://m.ibaotu.com/search/496381268.htmlhttps://m.ibaotu.com/search/496479725.htmlhttps://m.ibaotu.com/search/496381263.htmlhttps://m.ibaotu.com/search/496479730.htmlhttps://m.ibaotu.com/search/496381098.htmlhttps://m.ibaotu.com/search/496381269.htmlhttps://m.ibaotu.com/search/496380858.htmlhttps://m.ibaotu.com/search/496381267.htmlhttps://m.ibaotu.com/search/496380851.htmlhttps://m.ibaotu.com/search/496381101.htmlhttps://m.ibaotu.com/search/496380854.htmlhttps://m.ibaotu.com/search/496381266.htmlhttps://m.ibaotu.com/search/496380860.htmlhttps://m.ibaotu.com/search/496380846.htmlhttps://m.ibaotu.com/search/496381093.htmlhttps://m.ibaotu.com/search/496380855.htmlhttps://m.ibaotu.com/search/496479715.htmlhttps://m.ibaotu.com/search/496381100.htmlhttps://m.ibaotu.com/search/496380870.htmlhttps://m.ibaotu.com/search/496380845.htmlhttps://m.ibaotu.com/search/496381096.htmlhttps://m.ibaotu.com/search/496381259.htmlhttps://m.ibaotu.com/search/496381097.htmlhttps://m.ibaotu.com/search/501365821.htmlhttps://m.ibaotu.com/search/496380832.htmlhttps://m.ibaotu.com/search/496380862.htmlhttps://m.ibaotu.com/search/496381257.htmlhttps://m.ibaotu.com/search/496479713.htmlhttps://m.ibaotu.com/search/496380829.htmlhttps://m.ibaotu.com/search/496380837.htmlhttps://m.ibaotu.com/search/496380831.htmlhttps://m.ibaotu.com/search/496381080.htmlhttps://m.ibaotu.com/search/496381065.html