Load Hive partition from Hive view

Question

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?

score 0 · Accepted Answer · answered Oct 16 '14 at 08:35

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

score 0 · Answer 2 · edited Nov 08 '16 at 15:50

set hive.exec.dynamic.partition=true;

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


--partition table

create external table pracitse_part (
id int,   
first_name string,
last_name  string,
email string,
ip_address string
)
partitioned by (gender string)
row format delimited 
fields terminated by ',';

--create veiw table

create view practise_view as 
    select p.* 
    from practise p join practise_temp pt
    on p.id=pt.id 
    where p.id < 11;

--load data into partition table from view table 

insert overwrite table pracitse_part partition(gender)
select id,first_name,last_name,email,ip_address,gender from practise_view;

Load Hive partition from Hive view

2 Answers2