error when inserting data to a partitioned table in Hive

Question

I am seeing an error when I try to insert data into a partitioned table in hive, here is the details:

The table:

CREATE　TABLE partitionedemp(emp_no int, birth_date string, first_name string, last_name string) Partitioned By(gender string, hire_date string) stored as sequencefile;

The data looks like below:

The insert script:

insert into table partitionedemp partition(gender, hire_date) select emp_no, birth_date, first_name, last_name, gender, hire_date from emp;

Here is the error I received:

FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

I followed some online suggestions by executing the following two lines first:

SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;

And here is the new error message I received:

AT the end it says:

FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 14.39 sec   HDFS Read: 0 HDFS Write: 0 FAIL
Total MapReduce CPU Time Spent: 14 seconds 390 msec

Updated: I followed the suggestion below by @leftjoin, and here is the new update, still not working. Any missing or wrong scripting?

Recreate table with one partition field. It seems like there are two partitioned fields and you inserting only in partition(hire_date) — leftjoin

leftjoin leftjoin · Accepted Answer · 2016-07-01T20:23:03

Follow suggestions in the exception message and increase these values:

For example

set hive.exec.max.dynamic.partitions=100000;  
set hive.exec.max.dynamic.partitions.pernode=10000;

And I suggest you to reduce the number of partitions if possible. Remove gender, it will reduce the number of partitions. Too many small files is not good idea for hadoop

error when inserting data to a partitioned table in Hive

1 Answers