Apache Hive--排序函数解析

在大数据处理与分析中，Apache Hive是一个至关重要的数据仓库工具。其丰富的函数库为数据处理提供了诸多便利，排序函数便是其中一类非常实用的工具。通过排序函数，我们能够在查询结果集中为每一行数据分配一个排名值，这对于数据分析、报表生成等工作具有重要意义。本文将深入探讨Apache Hive中的排序函数，通过具体的HQL代码和数据实例进行说明，并阐述它们之间的区别。

0. 排序函数：ORDER、SORT、CLUSTER

ORDER BY

1. 功能：ORDER BY 会对整个数据集按照指定的列进行全局排序，确保最终输出的结果是完全有序的。
2. 代码示例：
   假设我们有之前创建的 student_scores 表，包含 student_name（学生姓名）和 score（成绩）字段。

SELECT student_name, score
FROM student_scores
ORDER BY score DESC;

3. 结果展示：

student_name	score
David	95
Bob	90
Cathy	90
Alice	85
Charlie	85

整个结果集按照成绩从高到低进行了全局排序。

SORT BY

1. 功能：SORT BY 用于在每个Reducer内对数据进行排序。它不会对整个数据集进行全局排序，而是在每个Reducer的分区内进行排序，在处理大规模数据时可提高处理效率。
2. 代码示例：

SET mapreduce.job.reduces = 3; -- 设置Reducer数量为3
SELECT student_name, score
FROM student_scores
SORT BY score DESC;

4. 结果展示：
   由于 SORT BY 是在每个Reducer内排序，结果会根据Reducer的处理情况而有所不同。假设每个Reducer处理的数据如下（实际情况可能因数据分配方式不同而不同）：

• Reducer 1：

student_name	score
David	95

• Reducer 2：

student_name	score
Bob	90
Cathy	90

• Reducer 3：

student_name	score
Alice	85
Charlie	85

每个Reducer内的数据按成绩降序排列，但整体结果集并非全局有序。

CLUSTER BY

1. 功能：CLUSTER BY 用于对数据进行分桶操作，它会根据指定的列对数据进行哈希运算，将数据均匀分布到不同的桶（bucket）中，同时在每个桶内对数据按指定列进行排序。这在数据量较大时，有助于提升查询性能，特别是在进行连接（join）操作以及与排序相关的操作时。
2. 代码示例：

-- 创建按score分桶的学生成绩表
CREATE TABLE student_scores_clustered (student_name STRING,score INT
)
CLUSTERED BY (score) INTO 2 BUCKETS;-- 将数据插入到分桶表
INSERT INTO TABLE student_scores_clustered
SELECT student_name, score
FROM student_scores;-- 查询分桶表
SELECT student_name, score
FROM student_scores_clustered;

3. 结果展示：
   数据会根据 score 列的哈希值分配到不同的桶中，并且在每个桶内按 score 排序。假设分桶结果如下（实际情况可能因哈希算法和数据分布不同而不同）：

• Bucket 1：

student_name	score
David	95
Bob	90
Cathy	90

• Bucket 2：

student_name	score
Alice	85
Charlie	85

每个桶内的数据按 score 排序。

小结

ORDER BY 用于全局排序，适用于需要最终结果完全有序的场景，但处理大数据时性能可能较低。
SORT BY 在每个 Reducer 内排序，适用于大规模数据处理，提高处理效率，但不保证全局有序。
CLUSTER BY 进行分桶并在桶内排序，主要用于优化特定查询（如 join）的性能，同时结合了分桶和局部排序的功能。

1. ROW_NUMBER窗口函数

1.1 功能概述

ROW_NUMBER窗口函数为结果集中的每一行分配一个唯一的连续排名值，从1开始，按照ORDER BY子句指定的顺序递增。无论数据值是否相同，其排名都不会出现重复，且是连续的。

1.2 代码示例

假设有一个学生成绩表student_scores，包含student_name（学生姓名）和score（成绩）字段：

CREATE TABLE student_scores (student_name STRING,score INT
);INSERT INTO student_scores VALUES
('Alice', 85),
('Bob', 90),
('Cathy', 90),
('Charlie', 85),
('David', 95);

使用ROW_NUMBER函数对学生成绩进行排名的查询如下：

SELECTstudent_name,score,ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROMstudent_scores;

上述代码中，ROW_NUMBER() OVER (ORDER BY score DESC)表示按照score降序排列，为每一行数据分配一个唯一的排名。

1.3 结果展示

执行上述查询后，结果如下：

student_name	score	rank
David	95	1
Bob	90	2
Cathy	90	3
Alice	85	4
Charlie	85	5

2. RANK窗口函数

2.1 功能概述

RANK窗口函数同样用于为结果集的行分配排名。但当遇到相同值时，会分配相同的排名，并且下一个排名会跳过相应的数量。例如，如果有两个并列第2名，那么下一个排名将是第4名。

2.2 代码示例

仍以上述student_scores表为例，使用RANK函数进行排名的查询为：

SELECTstudent_name,score,RANK() OVER (ORDER BY score DESC) AS rank
FROMstudent_scores;

2.3 结果展示

执行该查询后，结果如下【相同分数的排名是随机的】：

student_name	score	rank
David	95	1
Bob	90	2
Cathy	90	2
Alice	85	4
Charlie	85	4

可以看到，Bob和Cathy成绩相同，排名都是2，下一个排名直接跳到了4。

3. DENSE_RANK窗口函数

3.1 功能概述

DENSE_RANK窗口函数也用于排名，与RANK函数不同之处在于，当遇到相同值时，虽然也会分配相同的排名，但下一个排名不会跳过。即即使有并列情况，排名依然是连续的。

3.2 代码示例

还是针对student_scores表，使用DENSE_RANK函数排名的查询为：

SELECTstudent_name,score,DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROMstudent_scores;

3.3 结果展示

执行查询后，结果如下【相同分数的排名是随机的】：

student_name	score	rank
David	95	1
Bob	90	2
Cathy	90	2
Alice	85	3
Charlie	85	3

这里Bob和Cathy并列第2名，下一个排名是第3名，没有跳过。

结合partition by 进行使用实现组内排序

在 Apache Hive 中，PARTITION BY 子句与排序窗口函数结合使用时，会先将数据按照指定的列进行分区，然后在每个分区内分别应用排序函数。这在处理需要分区统计排名的场景中非常有用。
ROW_NUMBER 窗口函数结合 PARTITION BY
功能说明：在每个分区内，ROW_NUMBER 函数为每一行分配一个唯一的连续排名值，从 1 开始，按照 ORDER BY 子句指定的顺序递增。不同分区之间的排名相互独立。
代码示例：假设 student_scores 表新增 class（班级）字段，现在要查询每个班级内学生成绩的排名。

-- 创建包含班级字段的学生成绩表
CREATE TABLE student_scores (student_name STRING,score INT,class STRING
);-- 插入数据
INSERT INTO student_scores VALUES
('Alice', 85, 'Class1'),
('Bob', 90, 'Class1'),
('Charlie', 85, 'Class2'),
('David', 95, 'Class2');-- 使用ROW_NUMBER函数结合PARTITION BY查询
SELECTstudent_name,score,class,ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS rank
FROMstudent_scores;

结果展示：

student_name	score	class	rank
Bob	90	Class1	1
Alice	85	Class1	2
David	95	Class2	1
Charlie	85	Class2	2

在这个结果中，PARTITION BY class 将数据按班级分为 Class1 和 Class2 两个分区，ROW_NUMBER 函数在每个分区内分别对学生成绩进行排名。
RANK 窗口函数结合 PARTITION BY
功能说明：与 ROW_NUMBER 类似，不过在每个分区内，当遇到相同值时，RANK 函数会分配相同的排名，并且下一个排名会跳过相应的数量。
代码示例：

SELECTstudent_name,score,class,RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank
FROMstudent_scores;

结果展示：

student_name	score	class	rank
Bob	90	Class1	1
Alice	85	Class1	2
David	95	Class2	1
Charlie	85	Class2	2

同样，在每个班级分区内，按照成绩排名，相同成绩的学生排名相同，下一个排名会跳过相应数量。
DENSE_RANK 窗口函数结合 PARTITION BY
功能说明：在每个分区内，DENSE_RANK 函数遇到相同值时也会分配相同的排名，但下一个排名不会跳过，保持排名的连续性。
代码示例：

SELECTstudent_name,score,class,DENSE_RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank
FROMstudent_scores;

结果展示：

student_name	score	class	rank
Bob	90	Class1	1
Alice	85	Class1	2
David	95	Class2	1
Charlie	85	Class2	2

在每个班级分区内，排名是连续的，即使有相同成绩的学生，下一个排名也不会跳过。

小结

• ROW_NUMBER：分配唯一且连续的排名，无论数据值是否重复，排名都不会间断。
• RANK：相同数据值分配相同排名，下一个排名会跳过相应数量，导致排名可能不连续。
• DENSE_RANK：相同数据值分配相同排名，但下一个排名不会跳过，排名始终连续。

排序函数的优化

在Apache Hive中优化排序函数的性能，可从以下几个关键方面着手：

1. 数据预处理

• 数据过滤：在使用排序函数前，尽量通过WHERE子句对数据进行过滤，减少参与排序的数据量。例如，在上述student_scores表中，如果我们只关心成绩大于80分的学生排名，可在查询中添加WHERE条件：

SELECTstudent_name,score,ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROMstudent_scores
WHEREscore > 80;

• 数据抽样：对于大规模数据集，可先进行抽样处理，对抽样数据进行排序分析，获取大致结果。这在对数据整体趋势有初步了解时很有用。比如，从海量销售数据中抽取1%的数据来分析销售排名趋势。

2. 合理使用分区

• 分区表设计：将数据按合适的列进行分区，可显著提高排序性能。比如，在销售数据中，按日期分区，查询某段时间内的销售排名时，Hive可直接在相关分区内操作，减少扫描的数据量。

-- 创建按日期分区的销售表
CREATE TABLE sales (product STRING,quantity INT
)
PARTITIONED BY (sale_date STRING);

• 分区裁剪：查询时，Hive会自动进行分区裁剪，只读取相关分区的数据。例如：

SELECTproduct,quantity,RANK() OVER (ORDER BY quantity DESC) AS rank
FROMsales
WHEREsale_date BETWEEN '2023-01-01' AND '2023-01-31';

3. 选择合适的排序函数

• 根据业务需求：明确业务场景对排名的具体要求，合理选择ROW_NUMBER、RANK或DENSE_RANK。如果需要唯一且连续的排名，ROW_NUMBER是最佳选择；若允许并列排名且排名可间断，RANK更合适；若要并列排名且排名连续，DENSE_RANK是正确之选。避免因错误选择函数导致不必要的计算。
• 函数性能差异：虽然这三个排序函数在功能上有差异，但性能差异相对较小。不过，ROW_NUMBER由于不需要处理并列排名情况，在数据量极大且无并列值的情况下，理论上可能会稍快一些。

4. 配置参数调整

• 内存分配：适当增加Hive任务的内存分配，可使排序操作更高效。通过修改hive-site.xml文件中的相关参数，如mapreduce.map.memory.mb和mapreduce.reduce.memory.mb，为排序操作提供足够内存。
• 并行度调整：合理调整MapReduce任务的并行度，可充分利用集群资源。例如，根据集群节点数量和数据量，设置mapreduce.job.maps和mapreduce.job.reduces参数，提高排序任务的执行效率。

5. 索引使用【高版本hive】

• 创建索引：对排序依据的列创建索引，能加快排序速度。例如，在student_scores表中，对score列创建索引：

CREATE INDEX score_index ON TABLE student_scores(score);

• 索引维护：定期维护索引，确保其有效性。当数据发生大量插入、更新或删除操作后，重建或优化索引，以保证排序性能。

总结

Apache Hive的排序函数在多种场景下都有广泛应用。在数据分析中，当我们需要明确数据的先后顺序，如找出成绩排名前几的学生、销售额排名靠前的产品等，ROW_NUMBER函数可提供精确且唯一的排名，适用于严格区分先后顺序的场景。而在一些竞赛排名、成绩评级等场景中，如果允许并列排名且需要体现排名的间断性，RANK函数更为合适。对于希望在并列排名时保持排名连续性的场景，比如分析员工绩效等级，DENSE_RANK函数则能满足需求。这些排序函数为数据处理和分析提供了灵活多样的方式，帮助数据分析师和工程师更高效地从海量数据中提取有价值的信息。