Pfthb

Using Hive, I have duplicate rows and i want to drop duplicate rows (selecting distinct row with non empty column) when a particular column is empty. But I want to keep the rows when the column is empty but not in duplicate row.

e.g. Input is

id | name | fathername | address
 1 | bob | john | street1
 1 | bob | john | 
 2 | amir | khan |
 3 | roby | johanson | street3

Output

id | name | fathername | address
 1 | bob | john | street1
 2 | amir | khan |
 3 | roby | johanson | street3

We dropped row for id 1 when address was empty because it was a duplicated row. Although address for id 2 is missing, we still want to keep the row because its not a duplicated row. I need it for hive. There are many columns in actual problem and solution need to work with selecting * rather than particular columns.

You can use GROUP BY with MAX:

select id, name, fathername, max(address)
from data
group by id, name, fathername

Or if you want to use select *:

select *
from data
where address is not null
union
select *
from data 
where address is null and id not in (
 select id 
 from data
 where address is not null
)

You can prioritize the non-null address row in an order by using row_number.

select *
from (select t.*
 ,row_number() over(partition by id order by case when address is not null then 1 else 2 end) as rnum
 from tbl t
 ) t
where rnum = 1

Note: If there is more than one non-null row, you might have to specify one or more columns to break the ties.