user3646519 user3646519 - 1 month ago 24
SQL Question

Load Hive partition from Hive view

I have a External Hive table with 4 partitions. I also have 4 hive views based on a different Hive table.

Every week I want the hive view to overwrite the partitions in the External Hive table.

I know I can create an unpartitioned hive table from a view like show below

CREATE TABLE hive_table AS SELECT * FROM hive_view;


But is there a way to overwrite partitions from view data?

Answer

Yes, there is a way:

INSERT OVERWRITE TABLE <table_name> 
PARTITION(<partition_clause>) 
SELECT <select_clause>

It is required to set hive.exec.dynamic.partition to true before such operations. See details here: Hive Language Manual DML - Dynamic Partitions