I need to drop specific rows from a Hive table, which is partitioned. These rows for deletion matches certain conditions, so entire partitions can not be dropped in order to do so. Lets say the table Table has three columns: partner, date and source_key, and it is partitioned by date and source_key.
It is known that there is no operation supported for deletion or update of a particular set of records in hive (See How to delete and update a record in Hive) .
Following this solution I successfully executed the following queries in order to keep only the records which matches some given conditions, say: belong to some given range of date, having source_key='heaven' and the column partner<>'angel'...
Create temporary empty copy of the table Table.
CREATE TABLE IF NOT EXISTS tmpTable LIKE Table;
Fill it with the current rows.
INSERT OVERWRITE TABLE tmpTable
PARTITION (date,source_key)
SELECT * FROM Table
WHERE
date >= '2020-05-01' AND date < '2020-11-30' AND
source_key = 'heaven';
Delete target partitions.
ALTER TABLE Table DROP IF EXISTS
PARTITION (source_key = 'heaven' , date >= '2020-05-01' , date < '2020-11-30' );
Insert the edited partitions to the target table. (couldn't manage to insert OVERWRITE because of syntax errors)
INSERT INTO Table
PARTITION (source_key,date)
SELECT * FROM tmpTable
WHERE
partner <> 'angel';
Drop temporary table.
DROP TABLE IF EXISTS tmpTable;
The query runs fine. Because the table Table is managed, when the partitions are dropped the hdfs files should be dropped within, but something is wrong (perhaps in the last INSERT INTO statement) because after the execution of all these queries the target table Table keeps all records with partner = 'angel' in the given range of dates and with , basically stays the same.
Where is the fault? What is missing? How to accurately delete specific rows matching certain conditions for such a Hive table?
Table partitions can be overwritten directly from select from itself + WHERE filter. The scenario is quite simple, you do not need any temporary table. Make backup table if you are not sure what will happen.
If you want to drop entire partitions (not overwrite), execute
ALTER TABLE TableName DROP IF EXISTS
PARTITION (<partition spec to be dropped>); --check partition spec to be dropped carefully
Skip this if no partitions to be dropped.
Overwrite other partitions with filtered rows:
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.allow.move.on.s3=true; --If you are on Qubole/S3
insert overwrite table TableName partition (date, source_key ) --partition spec should match table DDL
select * from TableName
where <condition> --condition should be True for rows which NOT be deleted
Your code is rather confusing because you created temporary table using LIKE but using different partition specification and selecting * (same order of columns like in original table). Order of columns shold match exactly, partition columns are the last ones, also in the same order.