CODEWITHSUNDEEP
hadoophivepartitioninghdfshiveql
Ramesh.kbvr
Ramesh.kbvr

Reputation: 783

convert normal column as partition column in hive

I have a table with 3 columns. now i need to modify one of the column as a partition column. Is there any possibility? If not, how can we add partition to existing table. I used the below syntax: create table t1 (eno int, ename string ) row format delimited fields terminated by '\t'; load data local '/....path/' into table t1; alter table t1 add partition (p1='india');

i am getting errors.........

Any one know how to add partition to existing table......?

Thanks in advance.

Upvotes: 1

Views: 4529

Answers (3)

Pastor
Pastor

Reputation: 318

You can't add a partition to a created table. But you can do something like these steps. Create a new table and insert data from the old table to the new one.

/*Original table structure*/
CREATE  TABLE original_table(
    c1 string,
    c2 string,
    c3 string)
STORED AS ORC;



/*Partitioned table structure*/
CREATE  TABLE partitioned_table(
    c1 string,
    c2 string)
partitioned by (c3 string)  
STORED AS ORC;


/*load data from original_table to partitioned_table*/
insert into 
table partitioned_table partition(c3)
 select c1, c2, c3 
from  original_table;


/*rename original_table to old_table. You can just drop it if you want it*/
ALTER TABLE original_table RENAME TO old_table;


/*rename partitioned_table to original_table*/
ALTER TABLE partitioned_table RENAME TO original_table;

Upvotes: 2

Harvey
Harvey

Reputation: 1

I think there is no way to convert an existing column of a table to partition. If you want to add a partition in a table use ALTER command as you have already done. If you are dealing with the external table then specify the location field as well. I am not sure whether a partition can be added using ALTER command for managed tables.

Upvotes: 0

Joe K
Joe K

Reputation: 18424

I don't think this is directly possible. Hive would have to completely rearrange and split the files in HDFS because adding the partition would impose a new directory structure.

What I suggest you do is simply create a new table with the desired schema and partition, and insert everything from the first into the second.

Upvotes: 1

Related Questions