Hive Combine Multiple Map Values into One column

Question

Table (Input data)

+--------+---------+
| col_id | col_val |
+--------+---------+
| axc    | col_x   |
| bdf    | col_f   |
| cde    | col_x   |
| yhc    | col_f   |
| idx    | col_a   |
| dft    | col_b   |
+--------+---------+

Tranpose logic. basically wanted to combine col_a or col_b values into one column as col_ab. tried as below. but running into an error. please help

SELECT a.col_id,IF(array_contains(collect_list(a.map_values['col_x']),'1'),'Y','N') AS col_x,
IF(array_contains(collect_list(a.map_values['col_y']),'1'),'Y','N') AS col_y,
IF(array_contains(collect_list(a.map_values['col_a']),'1'),'Y','N') OR IF(array_contains(collect_list(a.map_values['col_b']),'1'),'Y','N') AS col_ab,
IF(array_contains(collect_list(a.map_values['col_f']),'1'),'Y','N') AS col_f 
 FROM (
       SELECT col_id,
              col_val,
              map(col_val, '1') map_values
       FROM   transpose) a GROUP BY a.col_id;

Output Data

+--------+-------+-------+-----------+-------+
| col_id | col_x | col_y | col_ab    | col_f |
+--------+-------+-------+-----------+-------+
| axc    | Y     | N     | N         | N     |
| bdf    | N     | N     | N         | Y     |
| cde    | Y     | N     | N         | N     |
| dft    | N     | Y     | Y         | N     |
| idx    | N     | N     | Y         | N     |
| yhc    | N     | N     | N         | Y     |
+--------+-------+-------+-----------+-------+

score 0 · Answer 1 · answered Dec 02 '19 at 06:40

Use case statements with aggregation:

select  col_id, 
        max( case col_val = 'col_x' then 'Y' alse 'N' end )               as col_x, 
        max( case col_val = 'col_y' then 'Y' alse 'N' end )               as col_y,
        max( case col_val in ( 'col_a', 'col_b')  then 'Y' alse 'N' end ) as col_ab,
        max( case col_val = 'col_f' then 'Y' alse 'N' end )               as col_f
from your_table
 group by col_id;

score 0 · Accepted Answer · answered Dec 02 '19 at 10:55

SELECT col_id, 
       IF(map_values['col_x'],'Y','N') as col_x,
       IF(map_values['col_y'],'Y','N') as col_y,
       IF((map_values['col_a'] || map_values['col_b']),'Y','N') as col_ab, 
       IF(map_values['col_f'],'Y','N') as col_f
FROM
(SELECT col_id,
       to_map(col_val, true) map_values
FROM   transpose 
GROUP BY col_id)a

Hive Combine Multiple Map Values into One column

2 Answers2