25.COLLECT_SET / COLLECT_LIST 函数深度解析

Hive COLLECT_SET / COLLECT_LIST 函数深度解析

目录

1. 函数概述
2. 语法定义与版本要求
   • 2.1 COLLECT_SET 语法
   • 2.2 COLLECT_LIST 语法
3. 参数与返回值机制
   • 3.1 参数说明
   • 3.2 返回值类型与特性
4. 核心原理：行转列与集合聚合逻辑
   • 4.1 去重与顺序保留的底层差异
   • 4.2 与 CONCAT_WS 的黄金组合
   • 4.3 与 SORT_ARRAY 的排序组合
   • 4.4 数组顺序问题的本质原因
5. NULL 值与边界情况处理
6. 使用示例详解
   • 6.1 基础行转列示例
   • 6.2 用户画像与行为序列分析
   • 6.3 数据清洗与多列收集
   • 6.4 实现可控顺序的方案一：子查询 + DISTRIBUTE BY / SORT BY
   • 6.5 实现可控顺序的方案二：使用 SORT_ARRAY 排序
7. 性能优化与避坑指南
   • 7.1 数据倾斜处理
   • 7.2 内存溢出 (OOM) 风险与防范
   • 7.3 列值顺序的“假性”保持与解决方案
   • 7.4 类型限制与转换
8. 跨引擎行为差异与迁移指南
   • 8.1 Hive vs Spark SQL vs Presto/Trino vs MySQL
   • 8.2 迁移检查清单
9. 总结

1. 函数概述

COLLECT_SET 和 COLLECT_LIST 是 Hive SQL 中用于实现行转列（Row to Column）的强大聚合函数。它们能将同一个分组（Group）内多行数据的值，收集到一个数组（ARRAY）中。这两个函数是构建用户画像、分析行为路径、生成多值标签等复杂数据处理场景的核心工具。

• 函数名称：COLLECT_SET（集合收集）、COLLECT_LIST（列表收集）
• 函数类型：聚合函数 (Aggregate Functions, UDAF)
• 主要功能：
  • COLLECT_LIST：将分组内某列的所有值（不去重）收集到一个数组中。
  • COLLECT_SET：将分组内某列的所有值（去重后）收集到一个数组中。
• 应用场景：用户行为序列聚合、生成多值标签、数据透视表的中间步骤、与 EXPLODE 函数配合实现复杂的行列转换。

关键认知：这两个函数是处理多对一数据关系的利器，但它们的性能和对数据顺序的处理方式有显著差异，需要谨慎使用。

2. 语法定义与版本要求

2.1 COLLECT_SET 语法

COLLECT_SET(col)

• 功能：将列 col 在分组内的所有不重复值收集到一个 ARRAY 中。
• 返回值：ARRAY<T> (T 为 col 的数据类型)。
• 注意事项：返回的数组是无序的，并且会自动过滤掉 NULL 值。

2.2 COLLECT_LIST 语法

COLLECT_LIST(col)

• 功能：将列 col 在分组内的所有值（不去重）收集到一个 ARRAY 中。
• 返回值：ARRAY<T> (T 为 col 的数据类型)。
• 注意事项：返回的数组理论上保留数据在输入时的原始顺序，但实践中受数据分布和处理方式影响，顺序可能不保证。NULL 值同样会被忽略。

版本要求：COLLECT_LIST 函数在 Hive 0.13.0 及之后版本中作为内置 UDAF 提供。COLLECT_SET 也应在此版本或相近版本引入。

3. 参数与返回值机制

3.1 参数说明

3.2 返回值类型与特性

• 返回类型：ARRAY<type>，其中 type 是 col 的数据类型。
• 特性：
  • 去重与否：COLLECT_SET 去重，COLLECT_LIST 不去重。
  • 顺序性：COLLECT_LIST 保留输入顺序，COLLECT_SET 无序。
  • NULL 处理：两个函数都会自动忽略 NULL 值，不会将其收集到结果数组中。
  • 类型限制：通常要求 col 为基本数据类型。对于复杂类型（如 STRUCT, MAP），可能需要特殊处理或不受支持。

4. 核心原理：行转列与集合聚合逻辑

这两个函数的核心思想是将多行数据压缩为一行，从而将一个分组的详细信息聚合到一个字段中。

4.1 去重与顺序保留的底层差异

• COLLECT_LIST：其行为类似于一个追加型列表 (List)，它会按数据到来的顺序将每个值添加到列表末尾。因此，它能保留数据的原始顺序。
• COLLECT_SET：其行为类似于一个哈希集 (HashSet)。为了去重，它会将值放入一个集合数据结构中，这个过程会打乱原始顺序。因此，返回的数组是无序的。

4.2 与 CONCAT_WS 的黄金组合

这是这两个函数最经典的用法。聚合后得到的数组可读性较差，通常需要结合 CONCAT_WS 函数将数组元素拼接成单个、可读的字符串，作为最终产出或供下游使用。

SELECT 
    user_id,
    CONCAT_WS(',', COLLECT_SET(visited_city)) AS cities_visited
FROM user_travel_log
GROUP BY user_id;
-- 结果示例：user_id: 1, cities_visited: "beijing,shanghai,guangzhou"

4.3 与 SORT_ARRAY 的排序组合

无论 COLLECT_SET 或 COLLECT_LIST 返回的数组，都可以通过 SORT_ARRAY 函数进行排序，以满足特定的展示或处理需求。

SELECT 
    user_id,
    CONCAT_WS(',', SORT_ARRAY(COLLECT_SET(visited_city))) AS cities_visited_sorted
FROM user_travel_log
GROUP BY user_id;
-- 结果示例：user_id: 1, cities_visited_sorted: "beijing,guangzhou,shanghai"

4.4 数组顺序问题的本质原因

在分布式计算框架中，COLLECT_LIST 虽然理论上保留顺序，但实际顺序由数据在各个 Map/Reduce 任务中的处理顺序决定。即使子查询中用 ORDER BY 排了序，聚合函数也不保证全局有序。根本原因是 ORDER BY 产生的是全局有序结果，但聚合过程是分布式的，COLLECT_LIST 在每个 reducer 内部保留顺序，但多个 reducer 结果的合并顺序是不确定的。

5. NULL 值与边界情况处理

• 对 NULL 的处理：两个函数都会自动忽略 NULL 值，不会将其包含在结果数组中。这与 COUNT(col) 等聚合函数行为一致。
• 全 NULL 值分组：如果一个分组内 col 列的所有值都是 NULL，则这两个函数都会返回一个空数组 ([])，而非 NULL。
• 空字符串 ('')：会被正常收集。它被视作一个有效的、非 NULL 的值。

6. 使用示例详解

6.1 基础行转列示例

-- 1. 收集每个学生的所有课程（不去重）
SELECT 
    stu_id, 
    stu_name,
    COLLECT_LIST(course) as course_list
FROM student_score
GROUP BY stu_id, stu_name;

-- 2. 收集每个学生的所有课程（去重）
SELECT 
    stu_id, 
    stu_name,
    COLLECT_SET(course) as unique_courses
FROM student_score
GROUP BY stu_id, stu_name;

6.2 用户画像与行为序列分析

-- 3. 分析用户过去30天点击的商品类目（去重）
SELECT 
    user_id,
    CONCAT_WS('|', COLLECT_SET(category)) as interested_categories
FROM user_click_log
WHERE dt >= DATE_SUB(CURRENT_DATE(), 30)
GROUP BY user_id;

-- 4. 还原用户按时间顺序的购买商品序列（配合子查询排序）
SELECT 
    user_id,
    CONCAT_WS('->', COLLECT_LIST(product_id)) as purchase_sequence
FROM (
    SELECT user_id, product_id, order_time
    FROM orders
    DISTRIBUTE BY user_id 
    SORT BY user_id, order_time ASC  -- 确保在 reducer 内按时间排序
) t
GROUP BY user_id;

6.3 数据清洗与多列收集

-- 5. 收集某用户的所有曾用昵称（假设有变更记录表）
SELECT 
    user_id,
    COLLECT_SET(nickname) as historical_nicknames
FROM user_nickname_history
GROUP BY user_id;

-- 6. 将同一个用户的多个手机号收集起来，并用逗号分隔
SELECT 
    user_id,
    CONCAT_WS(',', COLLECT_SET(phone_number)) as phone_numbers
FROM user_contacts
GROUP BY user_id;

6.4 实现可控顺序的方案一：子查询 + DISTRIBUTE BY / SORT BY

这是最推荐的方法，通过 DISTRIBUTE BY 确保相同 user_id 进入同一 reducer，并用 SORT BY 保证 reducer 内排序，最后 COLLECT_LIST 会保留此顺序。

SELECT 
    user_id,
    CONCAT_WS('->', COLLECT_LIST(item_id)) as item_sequence
FROM (
    SELECT user_id, item_id, action_time
    FROM user_actions
    DISTRIBUTE BY user_id
    SORT BY user_id, action_time
) t
GROUP BY user_id;

6.5 实现可控顺序的方案二：使用 SORT_ARRAY 排序

如果需要的是基于值本身的自然排序（如字母、数字大小），可以在聚合后直接使用 SORT_ARRAY 函数，无需复杂的子查询。

SELECT 
    user_id,
    CONCAT_WS('->', SORT_ARRAY(COLLECT_SET(item_id))) as sorted_item_list
FROM user_actions
GROUP BY user_id;

7. 性能优化与避坑指南

7.1 数据倾斜处理

如果某个分组键（如某个大V的 user_id）的数据量极大，会导致该 Reducer 处理的数据量远大于其他 Reducer，造成数据倾斜。

• 方案一：启用 Hive 的自动倾斜处理 SET hive.groupby.skewindata = true;。
• 方案二：在 DISTRIBUTE BY 时添加随机前缀（加盐），将热点数据打散到多个 Reducer，之后再聚合。但这会使 COLLECT_LIST 的顺序难以保证。

7.2 内存溢出 (OOM) 风险与防范

由于这两个函数需要将整个分组的数据收集到一个内存的数组中，当分组内数据量极大时，很容易导致内存溢出。

• 解决方案：
  • 增加 Reducer 内存：调整 mapreduce.reduce.memory.mb 和 mapreduce.reduce.java.opts 参数。
  • 提前过滤：在聚合前，先用 WHERE 条件、ROW_NUMBER() 或 LIMIT 减少每个分组的数据量。例如，只收集前 100 条记录。
  • 业务逻辑限制：在收集前按时间或重要性排序，只取 Top N。

7.3 列值顺序的“假性”保持与解决方案

很多开发者误以为在子查询中使用了 ORDER BY，外层的 COLLECT_LIST 就一定能保持全局有序。这在分布式环境下通常是错误的。要实现可控顺序，应使用方案一（DISTRIBUTE BY + SORT BY）或方案二（SORT_ARRAY）。

7.4 类型限制与转换

• 基本类型限制：COLLECT_SET 和 COLLECT_LIST 通常只接受基本数据类型（如 INT, STRING, DOUBLE）。如果要收集复杂类型（如 STRUCT），可能需要先将其转换为 STRING 或使用其他方式。
• 类型一致：收集的列必须具有相同的数据类型，或能隐式转换为同一类型。

8. 跨引擎行为差异与迁移指南

8.1 Hive vs Spark SQL vs Presto/Trino vs MySQL

8.2 迁移检查清单

9. 总结

• 核心区别：COLLECT_LIST 不去重，COLLECT_SET 去重。这是选择它们的关键。
• 黄金搭档：CONCAT_WS 是它们的最佳拍档，用于将数组转换为可读的字符串。
• 顺序保证：不要依赖 COLLECT_LIST 的隐式顺序。要实现可控顺序，请使用 SORT_ARRAY（基于值排序）或 子查询 + DISTRIBUTE BY + SORT BY（基于其他字段排序）。
• 性能陷阱：这两个函数是内存密集型操作，需警惕数据倾斜和超大分组导致的内存溢出。在生产环境中使用时，应评估分组大小并做好相应优化。
• 跨平台差异：COLLECT_SET/COLLECT_LIST 是 Hive/Spark 特有的函数。迁移到其他数据库时，需要替换为对应的替代语法。