CODEWITHSUNDEEP
hivehiveql
jahan
jahan

Reputation: 153

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?

Upvotes: 1

Views: 189

Answers (1)

leftjoin
leftjoin

Reputation: 38335

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)

Upvotes: 1

Related Questions