How to delete duplicate records from Hive table?

You can use insert overwrite statement to update data

insert overwrite table dynpart select distinct * from dynpart;

Just in case when your table has duplicate rows on few or selected columns. Suppose you have a table structure as shown down below:

id  Name    Technology
1   Abcd    Hadoop
2   Efgh    Java       --> Duplicate
3   Ijkl    Mainframe
2   Efgh    Python     --> Duplicate

Here id & Name columns having duplicate rows. You can use analytical function to get the duplicate row as:

select * from
(select Id,Name,Technology,
row_Number() over (partition By Id,Name order by id desc) as row_num
from yourtable)tab
where row_num > 1;

This will give you output as:

id  Name    Technology  row_num
2   Efgh    Python           2

When you need to get both the duplicate rows:

select * from
(select Id,Name,Technology,
count(*) over (partition By Id,Name order by id desc) as duplicate_count
from yourtable)tab
where duplicate_count> 1;

Output as:

id  Name    Technology  duplicate_count
2   Efgh    Java             2
2   Efgh    Python           2

you can insert distinct records into some other table

create table temp as select distinct * from dynpart