十三、Hive 行列转换_hive 行转列
作者：IvanCodes
日期：2025年5月19日
专栏：Hive教程
在Hive中，数据的形态转换是数据清洗、分析和报表制作中的核心环节。行列转换尤为关键，它能将数据从一种组织形式变为另一种，以适应不同的业务洞察需求。本笔记将深入探讨Hive中实现行转列和列转行的多种技巧，包括聚合函数、集合操作以及强大的UDTF。
一、行转列
行转列的目标是将某一列的不同值（类别）提升为结果集中的新列名，并将与之对应的数值或信息填充到这些新列中。
1. CASE WHEN 结合聚合函数 (最灵活、最通用)
这是实现行转列的基石方法，适应性强。
场景示例：
student_scores 表：
期望输出：
Hive SQL 实现:
SELECTstudent_name,MAX(CASE WHEN subject = \'Math\' THEN score ELSE NULL END) AS Math_score,MAX(CASE WHEN subject = \'English\' THEN score ELSE NULL END) AS English_score,MAX(CASE WHEN subject = \'Physics\' THEN score ELSE NULL END) AS Physics_scoreFROMstudent_scoresGROUP BYstudent_name;核心逻辑: 为每个要成为新列的subject值创建一个 CASE WHEN 表达式，并配合聚合函数 (如 MAX, SUM, AVG) 在 GROUP BY 子句定义的粒度上提取值。
2. COLLECT_LIST / COLLECT_SET (聚合为数组/集合)
这两个函数本身不直接完成典型的行转列（即每个类别一个独立列），而是将属于同一分组的多行数据的某一列值聚合成一个数组 (COLLECT_LIST 保留重复，COLLECT_SET 去重) 或集合。这通常是一个中间步骤，后续可能需要UDF或其他处理来进一步转换。
场景示例：获取每个学生所有科目的成绩列表。
Hive SQL 实现:
SELECTstudent_name,COLLECT_LIST(score) AS all_scores_list, -- 所有成绩，可能重复COLLECT_SET(subject) AS distinct_subjects_set -- 所有不重复的科目FROMstudent_scoresGROUP BYstudent_name;输出可能形式:
进阶用法: 可以结合 CONCAT_WS 将数组元素连接成字符串，或者使用 MAP 数据类型和 STR_TO_MAP 来更灵活地组织数据，但这已偏离严格的“列名固定”的行转列。
二、列转行
列转行是将一行中的多个列值“铺开”成多行，每行通常包含一个标识列、一个原列名（或其代表的类别）和原列的值。
1. UNION ALL (基础且通用)
对于固定且数量不多的列，UNION ALL 非常直观。
场景示例：
monthly_sales 表:
期望输出：
Hive SQL 实现:
SELECT product_id, \'Jan\' AS month, sales_jan AS sales FROM monthly_salesUNION ALLSELECT product_id, \'Feb\' AS month, sales_feb AS sales FROM monthly_salesUNION ALLSELECT product_id, \'Mar\' AS month, sales_mar AS sales FROM monthly_sales;2. LATERAL VIEW EXPLODE 配合 MAP 或 ARRAY (更灵活、可扩展)
当列多或需要动态处理时，此方法更为推荐。
Hive SQL 实现 (使用 MAP):
SELECTt1.product_id,t2.month_name,t2.sales_valueFROMmonthly_sales t1LATERAL VIEW EXPLODE(MAP(\'Jan\', sales_jan,\'Feb\', sales_feb,\'Mar\', sales_mar)) t2 AS month_name, sales_value;核心逻辑: 先将要转换的列构建成一个 MAP (键为新类别，值为原列值)，然后使用 EXPLODE 将其“炸开”成多行。LATERAL VIEW 使得我们能够在 SELECT 中引用 EXPLODE 生成的新列。
Hive SQL 实现 (使用 ARRAY OF STRUCTS):
SELECTt1.product_id,t2.month_struct.month_name AS month,t2.month_struct.sales_value AS salesFROMmonthly_sales t1LATERAL VIEW EXPLODE(ARRAY(STRUCT(\'Jan\' AS month_name, sales_jan AS sales_value),STRUCT(\'Feb\' AS month_name, sales_feb AS sales_value),STRUCT(\'Mar\' AS month_name, sales_mar AS sales_value))) t2 AS month_struct;说明: 使用 ARRAY of STRUCTS 可以更好地组织每一对 (类别名, 值)，特别是当“值”本身也是复合结构时。
3. Hive STACK UDTF (简洁高效)
Hive 内置的 STACK 函数专门为列转行设计，语法简洁。
Hive SQL 实现:
SELECTproduct_id,month_data.month_name,month_data.sales_valueFROMmonthly_salesLATERAL VIEW STACK(3, -- 要生成的行数 (等于要转换的列的组数)\'Jan\', sales_jan,\'Feb\', sales_feb,\'Mar\', sales_mar) month_data AS month_name, sales_value;STACK(N, c1_key, c1_val, c2_key, c2_val, ..., cN_key, cN_val): N 指定输出的行数，后面的参数成对提供新类别名和对应的列值。
4. CONCAT_WS 和 SPLIT 结合 LATERAL VIEW EXPLODE (字符串拼接与拆分)
这种方法略显迂回，但有时在特定场景下或处理已拼接的字符串数据时有用。
场景: 先将多个列用特定分隔符拼接成一个字符串，然后拆分。
Hive SQL (概念性，不直接用于上述monthly_sales场景，因其不是最优):
假设要转换 col_A, col_B。
-- 步骤1: (可选) 如果数据已经是拼接好的字符串列，则跳过-- SELECT id, CONCAT_WS(\',\', col_A, col_B) AS combined_string FROM some_table;-- 步骤2: 拆分 (假设combined_string列存在)SELECTid,exploded_valueFROM(SELECT id, CONCAT_WS(\':\', \'col_A_name\', col_A_val, \',\', \'col_B_name\', col_B_val) AS combined_string_with_names FROM ...) -- 构造一个包含名称和值的字符串LATERAL VIEW EXPLODE(SPLIT(combined_string_with_names, \',\')) t1 AS single_pair_stringLATERAL VIEW EXPLODE(SPLIT(single_pair_string, \':\')) t2 AS item; -- 这一步还需要更复杂的逻辑来分离name和value，通常不这么做注意: 直接使用 CONCAT_WS 和 SPLIT 进行严格的列转行不如 MAP/ARRAY+EXPLODE 或 STACK 简洁和高效。CONCAT_WS 更常用于将多列数据或数组元素合并成一个展示性的字符串。
三、练习题
通用数据表:
employee_skills 表:
device_logs 表:
- 行转列 (CASE WHEN): 将
device_logs表转换为每行显示 device_id 和 log_date，列为 CPU_Usage, Memory_Usage, DiskIO_Rate。 - 行转列 (COLLECT_LIST/CONCAT_WS): 从
employee_skills表，为每个员工生成一列all_skills_string，其中包含该员工的主要技能和次要技能，用逗号分隔 (例如: “Java,Python”)。如果次要技能为空，则只显示主要技能。 - 行转列 (更复杂的CASE WHEN): 基于
device_logs表，统计每个 device_id 在每个 log_date 的 CPU 平均使用率和 Memory 最大使用率。输出列：device_id, log_date, Avg_CPU, Max_Memory。 - 列转行 (UNION ALL): 将
employee_skills表中的 skill_primary 和 skill_secondary 以及它们对应的 years_exp_primary 和 years_exp_secondary 转换为长表。输出列：emp_name, dept, skill_type (‘Primary’ 或 ‘Secondary’), skill_name, years_experience。 - 列转行 (LATERAL VIEW EXPLODE MAP): 使用
LATERAL VIEW EXPLODE(MAP(...))将employee_skills表的 skill_primary 和 skill_secondary 列转换为长表。输出列：emp_name, skill_category (‘PrimarySkill’ 或 ‘SecondarySkill’), skill. - 列转行 (STACK): 使用
STACK函数将employee_skills表中的 years_exp_primary 和 years_exp_secondary 转换为长表。输出列：emp_name, experience_type (‘PrimaryExp’ 或 ‘SecondaryExp’), years. - 行转列 (动态类别处理思路 - 先聚合再处理): 从
device_logs表中，统计每个device_id下所有metric_type的metric_value的总和，结果存储在一个Map中。输出列：device_id, metrics_sum_map。 (提示:STR_TO_MAP可能与COLLECT_LISTofCONCAT_WS结合) - 列转行 (ARRAY of STRUCTS): 将
employee_skills表的主要技能和次要技能及其经验年限，使用ARRAYofSTRUCTS配合LATERAL VIEW EXPLODE转换为长表。输出列：emp_name, dept, skill_info (STRUCT包含skill_name和years_exp)。 - 行转列 (带条件的字符串聚合): 从
device_logs表，为每个 device_id 和 log_date，将所有 CPU 使用率大于0.7的metric_value用逗号连接成一个字符串，列名为high_cpu_values。 - 列转行 (综合应用): 假设
employee_skills表有额外的project_A_role和project_B_role两列。使用最合适的方法将skill_primary,skill_secondary,project_A_role,project_B_role都转换为长表，包含 emp_name, category (‘Skill’ 或 ‘ProjectRole’), description (对应的值)。
四、练习题答案
答案1:
SELECTdevice_id,log_date,MAX(CASE WHEN metric_type = \'CPU\' THEN metric_value ELSE NULL END) AS CPU_Usage,MAX(CASE WHEN metric_type = \'Memory\' THEN metric_value ELSE NULL END) AS Memory_Usage,MAX(CASE WHEN metric_type = \'DiskIO\' THEN metric_value ELSE NULL END) AS DiskIO_RateFROMdevice_logsGROUP BYdevice_id, log_date;答案2:
SELECTemp_name,dept,CONCAT_WS(\',\', skill_primary, IF(skill_secondary IS NOT NULL AND skill_secondary != \'\', skill_secondary, NULL)) AS all_skills_string -- 更严谨的IF处理空或空字符串FROMemployee_skills;-- 或者更简单的，如果次要技能为NULL，CONCAT_WS会自动忽略它（但空字符串不会）-- SELECT emp_name, dept, CONCAT_WS(\',\', skill_primary, skill_secondary) AS all_skills_string FROM employee_skills;答案3:
SELECTdevice_id,log_date,AVG(CASE WHEN metric_type = \'CPU\' THEN metric_value ELSE NULL END) AS Avg_CPU,MAX(CASE WHEN metric_type = \'Memory\' THEN metric_value ELSE NULL END) AS Max_MemoryFROMdevice_logsGROUP BYdevice_id, log_date;答案4:
SELECT emp_name, dept, \'Primary\' AS skill_type, skill_primary AS skill_name, years_exp_primary AS years_experience FROM employee_skillsUNION ALLSELECT emp_name, dept, \'Secondary\' AS skill_type, skill_secondary AS skill_name, years_exp_secondary AS years_experience FROM employee_skills WHERE skill_secondary IS NOT NULL;答案5:
SELECTt1.emp_name,t2.skill_category,t2.skillFROMemployee_skills t1LATERAL VIEW EXPLODE(MAP(\'PrimarySkill\', skill_primary,\'SecondarySkill\', skill_secondary)) t2 AS skill_category, skillWHERE t2.skill IS NOT NULL;答案6:
SELECTemp_name,exp_data.experience_type,exp_data.yearsFROMemployee_skillsLATERAL VIEW STACK(2,\'PrimaryExp\', years_exp_primary,\'SecondaryExp\', years_exp_secondary) exp_data AS experience_type, yearsWHERE exp_data.years IS NOT NULL;答案7:
SELECTdevice_id,STR_TO_MAP(CONCAT_WS(\',\', COLLECT_LIST(CONCAT_WS(\':\', metric_type, CAST(SUM(metric_value) AS STRING))))) AS metrics_sum_mapFROMdevice_logsGROUP BYdevice_id, metric_type -- 先按metric_type聚合得到SUMGROUP BY device_id; -- 再按device_id聚合得到MAP-- 上述写法比较复杂且可能不是最优，更常见的是直接聚合到MAP:-- SELECT device_id, MAP_FROM_ENTRIES(COLLECT_LIST(STRUCT(metric_type, SUM(metric_value)))) AS metrics_sum_map-- FROM device_logs GROUP BY device_id, metric_type GROUP BY device_id; -- Hive版本支持MAP_FROM_ENTRIES-- 如果Hive版本不支持MAP_FROM_ENTRIES，通常聚合到数组，然后外部处理或UDF更正和简化第7题思路 (直接用Hive内建功能构建Map，假设目标是每个metric_type对应其总和):
SELECTdevice_id,MAP_AGG(metric_type, sum_metric_value) AS metrics_sum_mapFROM (SELECT device_id, metric_type, SUM(metric_value) as sum_metric_valueFROM device_logsGROUP BY device_id, metric_type) subGROUP BY device_id;-- 注意: MAP_AGG 是 Hive 2.2.0+ 的功能. 如果版本较低，思路会更复杂，如上面注释的collect_list of structs.答案8:
SELECTt1.emp_name,t1.dept,t2.skill_info.skill_name AS skill_name,t2.skill_info.years_exp AS years_experienceFROMemployee_skills t1LATERAL VIEW EXPLODE(ARRAY(STRUCT(skill_primary AS skill_name, years_exp_primary AS years_exp),STRUCT(skill_secondary AS skill_name, years_exp_secondary AS years_exp))) t2 AS skill_infoWHERE t2.skill_info.skill_name IS NOT NULL;答案9:
SELECTdevice_id,log_date,CONCAT_WS(\',\', COLLECT_LIST(CASE WHEN metric_type = \'CPU\' AND metric_value > 0.7 THEN CAST(metric_value AS STRING) ELSE NULL END)) AS high_cpu_valuesFROMdevice_logsGROUP BYdevice_id, log_date;(如果想确保没有符合条件时不输出空字符串而是NULL，可以再包一层IF)
SELECTdevice_id,log_date,IF(SIZE(COLLECT_LIST(CASE WHEN metric_type = \'CPU\' AND metric_value > 0.7 THEN CAST(metric_value AS STRING) ELSE NULL END)) > 0,CONCAT_WS(\',\', COLLECT_LIST(CASE WHEN metric_type = \'CPU\' AND metric_value > 0.7 THEN CAST(metric_value AS STRING) ELSE NULL END)),NULL) AS high_cpu_valuesFROMdevice_logsGROUP BYdevice_id, log_date;答案10:
SELECTemp_name,items.category,items.descriptionFROMemployee_skillsLATERAL VIEW STACK(4, -- 假设project_A_role 和 project_B_role 列存在\'Skill\', skill_primary,\'Skill\', skill_secondary,\'ProjectRole\', project_A_role,\'ProjectRole\', project_B_role) items AS category, descriptionWHERE items.description IS NOT NULL;
