HIVE Insert overwrite into a partitioned Table

Question

I ran a insert overwrite on a partitioned table. After the command, say for example the below partitions are created. a,b,c,d,e

Now when I rerun the Insert overwrite table, but this time with completely different set of data. Say for example, after the 2nd insert, below partitions get created. f,g,h,i,j

Question- Will the data from second insert not overwrite the data belonging to first insert. I still see the folders a,b,c,d,e in HDFS after the 2nd insert. But when I query them through HIVE, partitions a,b,c,d,e doesn't show up.

Is this the expected behavior of Insert overwrite table? or Am I missing anything here?

Narvarth Narvarth · Accepted Answer · 2018-12-05T14:53:46

Here are some sample scripts and their effects.

Let's say we have a table SOME_TABLE with three fields, A, B, and C, and two partition fields, YEAR, and MONTH. Let's assume that we want to delete the data in the table but not the structure of the table.

1. No partition specifications

If we run

INSERT OVERWRITE TABLE SOME_TABLE
PARTITION
(
     YEAR
    ,MONTH
)
SELECT A,B,C,YEAR,MONTH
FROM SOME_TABLE
WHERE FALSE

then the query executes but the data stays there.

2. Partial partition specifications

If we run

INSERT OVERWRITE TABLE SOME_TABLE
PARTITION
(
     YEAR=2018
    ,MONTH
)
SELECT A,B,C,MONTH
FROM SOME_TABLE
WHERE FALSE

then the same happens: the data stays there.

3. Full partition specifications

If we run

INSERT OVERWRITE TABLE SOME_TABLE
PARTITION
(
     YEAR=2018
    ,MONTH=11
)
SELECT A,B,C
FROM SOME_TABLE
WHERE FALSE

only then will the data be erased, and only for the given year and month. The data in other partitions doesn't get deleted.

Note that no partitions have been dropped, and a SHOW PARTITIONS SOME_TABLE returns all partitions, including those that existed before running an INSERT OVERWRITE.

Side note: I'd be curious to know if there is a way to delete all existing data, but keep the table structure. DROP TABLE would drop the structure. ALTER TABLE ... DROP PARTITION would only delete the targeted partitions, so we would have to know in advance what range of values each partition field takes. In this example, we could do

ALTER TABLE SOME_TABLE DROP PARTITION(year>=0,month>0) PURGE;

but it still seems too complex a solution.

HIVE Insert overwrite into a partitioned Table

3 Answers