使用Spark SQL实现Hive表中两数组元素的全可能组合拼接

需求说明 #

给定Hive表中的两个数组列row和column，需生成新列output，规则如下：

• 当row为null时，直接返回column数组作为结果
• 当两个数组均非null时，对row的每个元素与column的每个元素进行组合：若column元素为空字符串，则保留row元素本身；否则将两个元素用下划线_拼接。最终将所有组合结果收集为一个数组。

实现代码 #

-- 替换为你的Hive表名
WITH original_table AS (
    SELECT 
        NULL AS row, 
        array('black', 'blue', 'orange') AS column
    UNION ALL
    SELECT 
        array('mom', 'dad', 'sister') AS row, 
        array('amazon', 'fiipkart', 'meesho', 'jiomart', '') AS column
)
SELECT 
    DISTINCT
    row,
    column,
    CASE
        WHEN row IS NULL THEN column
        ELSE collect_list(
            CASE WHEN col_element = '' THEN row_element ELSE concat(row_element, '_', col_element) END
        ) OVER (PARTITION BY row, column ORDER BY row_element, col_element)
    END AS output
FROM original_table
LATERAL VIEW explode(COALESCE(row, array())) exploded_row AS row_element
LATERAL VIEW explode(column) exploded_col AS col_element;

代码解释 #

1. 数据模拟：用CTEoriginal_table模拟你的Hive表数据，实际使用时直接替换为你的表名即可。
2. 数组展开：通过LATERAL VIEW explode()将两个数组的元素拆分为笛卡尔积形式的行，遍历所有元素组合。用COALESCE(row, array())处理row为null的情况，避免展开失败。
3. 拼接逻辑：嵌套CASE WHEN判断：若column的元素为空字符串，则直接保留row元素；否则执行下划线拼接。
4. 聚合数组：使用collect_list()结合窗口函数，将同一原始行的所有组合结果重新聚合为数组，ORDER BY保证结果顺序与预期一致。
5. 去重处理：添加DISTINCT是因为当row为null时，展开后会生成多行相同数据，去重后得到单一结果行。

预期结果 #

执行上述SQL后，输出将与需求完全匹配：

内容的提问来源于stack exchange，提问作者user7884