CODEWITHSUNDEEP
sql-serverhadoophivesqoop
user3521180
user3521180

Reputation: 1130

remedy for duplicate data in HIVE

Below is my data flow

say RDBMS has 10 records

step -1
RDBMS --> SQOOP --> HIVE(e.g. table A (initial load))

step -2
RDBMS(modified record {record 2}) --> SQOOP(incremental)--> HDFS --> temp HIVE tbl(table B) --> HIVE(table A)

While moving data in step 2 from table B to table A, I use "insert" command in HIVE with the "APPEND" option. So, obviously, now the table A will have duplicate data.

How to get rid of these duplicate data? is there a way? if yes, what are they? if no, then what is the industry standard?

Upvotes: 0

Views: 637

Answers (1)

Rahib
Rahib

Reputation: 512

Yes, you can use a staging table for this. I created two extra tables.

DBMS_old: For old data that I want to keep in final table but is not there in the new result. DBMS_new: For new data that I want to append in the final table.

Step1: Get all the old data in the staging table using left outer join, This will get all your old data into dbms_old.

insert overwrite table DBMS_old
select final.* from DBMS_results final
left outer join DBMS_new new
on final.primary_key = new.primary_key and final.col2 = new.col2
where new.primary_key is null;

Step2: Append the old data and new data using union all.

insert overwrite table DBMS
select * from 
(select * from DBMS_old
union all
select * from DBMS_new) temp;

Hope it helps.

Upvotes: 1

Related Questions