Hive 如何实现行转列、列转行

在 Hive 中，行转列和列转行是常见的数据处理操作。以下是详细的实现方法：

一、行转列（多行转一行）

1. 使用 COLLECT_SET() 和 COLLECT_LIST()

-- 示例数据
-- id   name
-- 1    Alice
-- 1    Bob
-- 2    Charlie

-- 行转列：将同一id的name合并为数组
SELECT 
    id,
    COLLECT_LIST(name) as names_list,        -- 保留重复值，有序
    COLLECT_SET(name) as names_set           -- 去重，无序
FROM user_table
GROUP BY id;

-- 结果：
-- id   names_list       names_set
-- 1    ["Alice","Bob"]  ["Alice","Bob"]
-- 2    ["Charlie"]      ["Charlie"]

2. 使用 CONCAT_WS() 合并为字符串

SELECT 
    id,
    CONCAT_WS(',', COLLECT_LIST(name)) as names_string
FROM user_table
GROUP BY id;

-- 结果：
-- id   names_string
-- 1    Alice,Bob
-- 2    Charlie

二、列转行（一行转多行）

1. 使用 LATERAL VIEW + explode()

-- 示例数据
-- id   names
-- 1    ["Alice","Bob"]
-- 2    ["Charlie"]

-- 列转行：将数组展开为多行
SELECT 
    id,
    single_name
FROM user_array_table
LATERAL VIEW explode(names) name_table AS single_name;

-- 结果：
-- id   single_name
-- 1    Alice
-- 1    Bob
-- 2    Charlie

2. 使用 LATERAL VIEW OUTER + explode()

-- 处理空数组的情况（OUTER 会保留空数组的记录）
SELECT 
    id,
    single_name
FROM user_array_table
LATERAL VIEW OUTER explode(names) name_table AS single_name;

3. 处理 Map 类型数据的列转行

-- 示例数据
-- id   scores
-- 1    {"math":90, "english":85}
-- 2    {"math":95, "english":88}

SELECT 
    id,
    subject,
    score
FROM user_map_table
LATERAL VIEW explode(scores) score_table AS subject, score;

-- 结果：
-- id   subject   score
-- 1    math      90
-- 1    english   85
-- 2    math      95
-- 2    english   88

三、实际应用示例

场景1：用户标签行转列

-- 原始数据
-- user_id  tag
-- 1        A
-- 1        B
-- 2        A

-- 行转列：每个用户的标签合并
SELECT 
    user_id,
    CONCAT_WS(',', COLLECT_SET(tag)) as tags,
    COLLECT_LIST(tag) as tag_list
FROM user_tags
GROUP BY user_id;

-- 结果：
-- user_id  tags   tag_list
-- 1        A,B    ["A","B"]
-- 2        A      ["A"]

场景2：JSON数组数据列转行

-- 原始数据
-- order_id  products
-- 1001      ["apple","banana"]
-- 1002      ["orange"]

-- 列转行：展开订单商品
SELECT 
    order_id,
    product
FROM orders
LATERAL VIEW explode(products) product_table AS product;

-- 结果：
-- order_id  product
-- 1001      apple
-- 1001      banana
-- 1002      orange

场景3：复杂结构处理

-- 处理包含复杂结构的数组
SELECT 
    id,
    pos AS position,
    element
FROM complex_table
LATERAL VIEW posexplode(data_array) exploded_table AS pos, element;

四、注意事项

1. COLLECT_LIST vs COLLECT_SET：

   • COLLECT_LIST：保留所有值，包括重复，保持顺序
   • COLLECT_SET：去重，不保证顺序

2. 内存限制：

   • 行转列时注意数据量，避免单个分组数据过大

3. 空值处理：

   • 使用 LATERAL VIEW OUTER 处理空数组
   • 使用 COALESCE() 处理空值

4. 性能优化：

   • 对于大数据量，合理设置 reducer 数量
   • 考虑使用 DISTRIBUTE BY 和 SORT BY 优化

这些方法可以灵活处理 Hive 中的行列转换需求，根据具体场景选择合适的方式。