How to merge multiple rows sharing id into one single row (HQL)?

user_10599921 0
Viewed 331

At the end of a long JSON decode, I get this Hive table:

+----+--------+---------+--------+
| ID | field1 | field2  | field3 |
+----+--------+---------+--------+
|  1 | T      | NULL    | NULL   |
|  1 | NULL   | 0103210 | NULL   |
|  1 | NULL   | NULL    | NULL   |
|  1 | NULL   | NULL    | NULL   |
|  1 | NULL   | NULL    | NULL   |
|  1 | NULL   | NULL    | NULL   |
|  1 | NULL   | NULL    | NULL   |
|  1 | NULL   | NULL    | NULL   |
|  1 | NULL   | NULL    | NULL   |
|  2 | T      | NULL    | NULL   |
|  2 | NULL   | NULL    | NULL   |
|  2 | NULL   | NULL    | NULL   |
|  2 | NULL   | NULL    | NULL   |
|  2 | NULL   | NULL    | NULL   |
|  2 | NULL   | NULL    | NULL   |
|  3 | NULL   | NULL    | NULL   |
|  4 | U      | NULL    | NULL   |
|  4 | NULL   | 0103212 | NULL   |
|  4 | NULL   | NULL    | NULL   |
|  4 | NULL   | NULL    | NULL   |
|  4 | NULL   | NULL    | NULL   |
|  4 | NULL   | NULL    | NULL   |
|  4 | NULL   | NULL    | NULL   |
|  4 | NULL   | NULL    | NULL   |
|  4 | NULL   | NULL    | NULL   |
|  4 | NULL   | NULL    | NULL   |
|  4 | NULL   | NULL    | NULL   |
+----+--------+---------+--------+

This is the desired output:

+----+--------+---------+--------+
| ID | field1 | field2  | field3 |
+----+--------+---------+--------+
|  1 | T      | 0103210 | NULL   |
|  2 | T      | NULL    | NULL   |
|  3 | NULL   | NULL    | NULL   |
|  4 | U      | 0103212 | NULL   |
+----+--------+---------+--------+

So, I want to merge multiple rows with the same ID and within the same table in a single row. What query can get me the result I am looking for?

hiveql hive hadoop
1 Answers

Aggregate using min() or max():

select ID, 
       max(field1) as field1,
       max(field2) as field2, 
       max(field3) as field3
  from your_table
 group by ID
user_27003440
Related