Is it possible to add new column partition to already existing partitioned table in hive

Question

I have partition table called employee_part.This table is partitioned by hiredate. It has metadata as given below

When I tried to add new column partition to the employee_part table Im getting an error saying

 ALTER TABLE employee_part ADD PARTITION (gender='M') location 'hdfs://user/hive/warehouse/maprpoc.db/employee_part/hiredate=1985-11-21';

FAILED: SemanticException Partition spec {gender=M} contains non-partition columns

Please clarify on this! Thanks in advance..

Answer 1

That is because you have partitioned data on hiredate, but trying add partition on gender column.

Create partition on hiredate i.e.

 ALTER TABLE employee_part ADD PARTITION (hiredate='1985-11-21') location 'hdfs://user/hive/warehouse/maprpoc.db/employee_part/hiredate=1985-11-21';

Answer 2

This command can't add a new partition column, you can use it to add a new partition on existing column.

ALTER TABLE employee_part ADD PARTITION (gender='M') location 'hdfs://user/hive/warehouse/maprpoc.db/employee_part/hiredate=1985-11-21';

For this reason you are getting this message:

FAILED: SemanticException Partition spec {gender=M} contains non-partition columns

It does not mean "add a new column partition called gender which has the data located somewhere". It means "add a new partition ( read : new data ) on gender ( column ), but gender is not a partition column and this is the error you are getting".

ADD PARTITION is really useful on PARTITIONED EXTERNAL TABLES, when new data are available on HDFS, you can add them to the table using it.

So the answer is no. You can't add a new partition column on existing tables.