Hive : Insert overwrite multiple partitions

Question

I have a Hive table partitioned on date. I want to be able to selectively overwrite the partitions for the last 'n' days (or custom list of partitions).

Is there a way to do it without writing "INSERT OVERWRITE DIRECTORY" statement for each partition?

Any help is greatly appreciated.

Answer 1

Hive supports dynamic partitioning, so you can build a query where the partition is just one of the source fields.

INSERT OVERWRITE TABLE dst partition (dt) 
SELECT col0, col1, ... coln, dt from src where ...

The where clause can specify which values of dt you want to overwrite.

Just include the partition field (dt in this case) last in the list from the source, you can even do SELECT *, dt if the dt field is already part of the source or even SELECT *,my_udf(dt) as dt, etc

By default, Hive wants at least one of the partitions specified to be static, but you can allow it to be nonstrict; so for the above query, you can set the following before the running:

set hive.exec.dynamic.partition.mode=nonstrict;