INSERT OVERWRITE HIVE TABLE

Question

I have table A in teradata which has count of 40k records, have same table in hive which has only 37k records, so im trying to update the hive table with the missing records from TD. I created a stage table in hive, to get all the 40k records from TD and then do a INSERT OVERWRITE into the final table in HIVE to update the missing records. HOw should the insert overwrite syntax be?

What i did was.

INSERT OVERWRITE TABLE A PARTITION (column X)
select ( column A
         column B
          ,,





        column Z)
from stage table stg
left join final table f on stg.x= f.x and
where f.x is NULL

basically, im looking to insert records into the final HIVE table, which are present in Teradata table, but not the HIVE table.

Want to know if the approach is correct or am i wrong? Thanks

UNION ALL_row_number filtering or FULL JOIN: https://stackoverflow.com/a/37744071/2700344 — leftjoin, Aug 11 '20 at 05:20

score 0 · Answer 1 · answered Aug 11 '20 at 01:38

Tried this and worked:

INSERT OVERWRITE TABLE A PARTITION (column X)
select ( column A
         column B
          ,,





        column Z)
from final table f
union
select
 column 1
 column 2
from stage table stg
left join final table f on stg.x= f.x and
where f.x is NULL

INSERT OVERWRITE HIVE TABLE

1 Answers1