Search code examples
hivehiveql

Deleting records from Hive Target table

I have a target table A from which i want to delete 3 duplicate records. I found that delete from table where id = 1 does not work in Hive (which is straightforward)

In turn, what im doing now is :

Step 1: Create a copy table of the target table

Step 2: Insert into copy table select * from target where id not in (1,2,3)

step 3: Truncate target table

step 4: Insert into target table select * from copy table

Is there any approach which is more straightforward than this?

Solution

  • You can insert overwrite from itself:

    --for partitioned table
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

--If you are on Qubole
set hive.allow.move.on.s3=true;

insert overwrite table target partition (col1,col2)
select * from target where id not in (1,2,3)