Insert into hive partitioned table SemanticException

Question

First I create a hive partitioned table:

hive> create table partition_table
    > (sid int ,sname string ,age int)            
    > partitioned by (sex string)     
    > row format delimited fields terminated by',';  
OK
Time taken: 1.232 seconds

The table desc is given below:

 hive> desc partition_table;
    OK
    sid                     int                                         
    sname                   string                                      
    age                     int                                         
    sex                     string                                      

# Partition Information      
# col_name              data_type               comment             

sex                     string                                      
Time taken: 0.34 seconds, Fetched: 9 row(s)

and then I insert some data into this table but it doesn't work.

hive> insert into table partition_table partition(sex='M')select sno ,sname ,age from student1 where sex ='M';
FAILED: SemanticException [Error 10006]: Line 1:44 Partition not found ''M''

To avoid this I wrote the following command and then executed my insert command, even then I get the same error repeatedly.

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

can you add select sno ,sname ,age from student1 where sex ='M' output to question. there is no issue with query. — sumitya

invoketheshell invoketheshell · Accepted Answer · 2017-07-27T17:44:07

add the partition before loading:

ALTER TABLE partition_table ADD PARTITION( sex= 'M' );
insert into table partition_table partition(sex='M') select sno ,sname ,age from student1 where sex ='M';

or try dynamic partitioning:

set hive.exec.dynamic.partition=true;
INSERT OVERWRITE TABLE partition_table PARTITION (sex)
SELECT sid, sname, age, sex
FROM student1;

Insert into hive partitioned table SemanticException

1 Answers