Dynamic partition cannot be the parent of a static partition '3'

Question

While inserting data into table hive threw the error "Dynamic partition cannot be the parent of a static partition '3'" using below query

INSERT INTO TABLE student_partition PARTITION(course , year = 3) SELECT name, id, course FROM student1 WHERE year = 3;

Please explain the reason..

Aluminous

Answer 1

The reason of this Exception is because partitions are hierarchical folders. course folder is upper level and year is nested folders for each year.

When you creating partitions dynamically, upper folder should be created first (course) then nested year=3 folder.

You are providing year=3 partition in advance (statically), even before course is known.

Vice-versa is possible: Static parent partition and dynamic child partition:

INSERT INTO TABLE student_partition PARTITION(course='chemistry' , year)  --static course partition
SELECT name, id, 3 as year --or just simply year 
  FROM student1 WHERE year = 3;

In the HDFS partitions folders are like this:

/student_table/course=chemistry/year=3
/student_table/course=chemistry/year=4
/student_table/course=philosophy/year=3

Static partition should exist. But it cannot exist if parent is not defined yet.

Alternatively you can make year partition dynamic as well:

INSERT INTO TABLE student_partition PARTITION(course , year) 
SELECT name, id, course, 3 as year --or just simply year 
  FROM student1 WHERE year = 3;

Hatten