How to use insert statement for a Hive partitioned table?

2
votes

I have a hive table dynpart.

id                      int                                 
name                    char(30)                                    
city                    char(30)                                    
thisday                 string                                      

# Partition Information      
# col_name              data_type               comment                      
thisday                 string

It is partitioned by 'thisday' whose datatype is STRING. How can I insert a single record into the table in a particular partition. I know there is load command to load an entire file data into hive table. I just want to know how an Insert statement can be written for a partitioned table. I tried to write command like below but this is taking data from another table.

insert into droplater partition(thisday='30/03/2017') select * from dynpart;

The table: Droplater has the same structure as dynpart. But the above command is to insert the data from another table. What I'd like to learn is to write a simple insert command into a partition, like: insert into tabname values(1,"abcd","efgh");into the table.

1
hadoophive

1 Answers

3
votes

This will work for primitive types only (no arrays, structs etc.)

insert into tabname partition (thisday='30/03/2017') values (1,"abcd","efgh");

This will work for all types

insert into tabname partition (thisday='30/03/2017') select 1,"abcd","efgh";

P.s.

By all means, partition your table by date ((thisday date) ) 

insert into tabname partition (thisday=date '2017-03-30') ...

or at least use the ISO date format

insert into tabname partition (thisday='2017-03-30') ...