Dynamic partition cannot be the parent of a static

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..

Answer 1

The reason is because partitions folders are hierarchical. course folder is upper level and year is nested folders foe 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), before course is known yet. Vice-versa is possible: Static parent partition and dynamic child partition.

In the HDFS partitions folders are like this:

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

Static partition should exist. But it cannot exist if parent does not exist 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;