HIVE多行转多列与多列转多行实践：str_to_map与casewhen方法

在Hive中进行数据转换时，两种常见的操作是将多行数据转化为多列（即扁平化）和将多列数据转化为多行（即键值对拆分）。这两种操作在处理特定类型的数据结构时非常有用，比如当源数据中包含复合键值对时。 1. Hive多行转多列（使用str_to_map函数） 源数据可能包含一个CAMERA_NO字段和RESULT_DATA字段，需要将RESULT_DATA的值按照CAMERA_NO作为列名展开。例如，原始数据可能是： ```sql LOT_NO | LOT_NAME | MEASURE_NO | CAMERA_NO | RESULT_DATA ---------------------------------------------- A | X | M1 | S1 | 123 A | X | M1 | T1 | 456 A | X | M2 | S2 | 789 ``` 使用`str_to_map`函数，我们可以将这些数据转换成： ```sql LOT_NO | LOT_NAME | MEASURE_NO | S1 | T1 | S2 | ... (其他相机号) -------------------------------------------------------------- A | X | M1 | 123 | 456 | NULL | ... A | X | M2 | NULL | NULL | 789 | ... ``` `str_to_map`函数首先通过`concat_ws(',', collect_set(...))`收集每个CAMERA_NO对应的RESULT_DATA，并用逗号分隔形成字符串，然后使用`str_to_map`函数将这些字符串解析为键值对的映射。 SQL语句示例： ```sql insert overwrite table ods.iot.iot_5060_iotdaq.5060_aac_mtf_meas_results_new partition (date='{DATE}', hour='{HOUR}', minutes='{MINUTES}') SELECT ... FROM ... WHERE ... GROUP BY ... ``` 在这个例子中，`ods.iot.iot_5060_iotdaq.5060_aac_mtf_meas_results_new`是目标表，`date`, `hour`, `minutes`是分区键。 2. Hive多列转多行（使用case_when函数） 另一种方法是利用`case_when`函数，当需要根据不同的条件选择不同列的值时，可以逐个定义每个条件。例如，将S1, T1等相机号的结果数据分开： ```sql SELECT ... CASE WHEN CAMERA_NO = 'S1' THEN RESULT_DATA ELSE NULL END AS S1, CASE WHEN CAMERA_NO = 'T1' THEN RESULT_DATA ELSE NULL END AS T1, ... FROM ... WHERE ... ``` 这种方法适用于CAMERA_NO字段具有预定义的有限列表，且每个camera_no与RESULT_DATA之间是一对一的关系。 总结来说，Hive中的多行转多列和多列转多行操作是处理复杂数据结构的重要工具，可以帮助我们更好地组织和分析数据。理解并熟练运用`str_to_map`和`case_when`等函数，能够提高数据处理的效率和查询性能。同时，确保数据清洗和转换的正确性对于后续的数据分析至关重要。