Add PARTITION after creating TABLE in hive

Question

i have created a non partitioned table and load data into the table,now i want to add a PARTITION on the basis of department into that table,can I do this? If I do:

ALTER TABLE Student ADD PARTITION (dept='CSE') location '/test';

It gives me error:

FAILED: SemanticException table is not partitioned but partition spec exists: {dept=CSE}

please help. Thanks

Can you provide create table statement. You can't create a partition on a column which exists on your create table statement. It looks like you have added column `CSE` in your table. — Sandeep Singh, Jun 18 '15 at 06:13
If you want to create partition on column `CSE` then you should remove that column from your table first then add partition. — Sandeep Singh, Jun 18 '15 at 06:17
create table emp(id INT,name string,sal INT,dept string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as TEXTFILE; — rinku buragohain, Jun 18 '15 at 06:28
Your create table statement in wrong. Please see the ans below provided by a user. — Sandeep Singh, Jun 18 '15 at 06:33
so you mean to say we cannot add PARTITION later on for a non-partitioned table right,we have to use partitioned by during creating the table then only we can add on new PARTITION into the table. — rinku buragohain, Jun 18 '15 at 06:36
`partitioned by` clause says that later on you are going to create partition on that column. hence It is mandatory. — Sandeep Singh, Jun 18 '15 at 06:40

score 15 · Accepted Answer · answered Jun 18 '15 at 06:28

15

First create a table in such a way so that you don't have partition column in the table.

create external table Student(col1 string, col2 string) partitioned by (dept string) location 'ANY_RANDOM_LOCATION';

Once you are done with the creation of the table then alter the table to add the partition department wise like this :

alter table Student add partition(dept ='cse') location '/test';

I hope this will help.

answered Jun 18 '15 at 06:28

Naresh

5,073
12
67
124

Ya when i created table its a non partioned table, so can i create partition later on,thats my question – rinku buragohain Jun 18 '15 at 06:32
1

Answer is no. But why at the first place you are changing a non partitioned table to partitioned table. Just create a table with partition of any name. and later you can add as many location you want to add. – Naresh Jun 18 '15 at 06:41
OK so it should be pre decide during creation of table which column we want to be partitioned and later on we can add on more columns on it right . – rinku buragohain Jun 18 '15 at 06:45

score 11 · Answer 2 · edited Mar 31 '23 at 12:47

You can't alter table partition if you didn't define partition while creation of table.

If, when altering a un-partitioned table to add partition, you get this error: "Semantic Exception table is not partitioned but partition spec exists: {dept=CSE}," it means you are trying to include the partitioned in the table itself.

You are not getting syntax error because the syntax of the command is correct and used to alter the partition column.

Add PARTITION after creating TABLE in hive

2 Answers2