Hive 1.1.0 Alter table partition type from int to string

Question

I have a table which has a partition of type int but which I want to convert to string. However, I can't figure out how to do this.

The table description is:

Col1 timestamp
Col2 string
Col3 string
Col4 string
Part_col int

# Partition information
# col_name data_type comment

Part_col int

The partitions I have created are Part_col=0, Part_col=1, ..., Part_col=23

I want to change them to Part_col='0' etc

I run this command in hive:

set hive.exec.dynamic.partitions = true;
Alter table tbl_name partition (Part_col=0) Part_col Part_col string;

I have also tried using "partition (Part_col)" to change all partitions at once.

I get the error "Invalid column reference Part_col"

I am using the example from https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types for conversion of decimal columns but can't figure out what dec_column_name represents.

Thanks

Solution

A bit of digging revealed that there was a hive JIRA to have a command exactly for updating partition column data type (https://issues.apache.org/jira/browse/HIVE-3672)

alter table {table_name} partition column ({column_name} {column_type});

According to JIRA the command was implemented, but it's apparent that it was never documented on Hive Wiki.

I used it on my Hive 0.14 system and it worked as expected.

Comments

[Chris Njuguna]

Great! This worked. I am curious how to do this with the alter table statement. I only had a few partitions so this wasn't too hard. But I can imagine a situation where one has a great number of columns. Yes you can probably write a program or script to do this but shouldn't the command 'just work'? It would be beautiful to do this in one line.

[dbustosp]

@ChrisNjuguna think about this, you told to the metastore that your table was partitioned by a specific column and that column is an int. Now that you already have defined your table schema, you can add or drop all partitions that you want, but they are gonna be "strings" types. I think the best way to do what you want is to do what I told you, once you have defined the new table schema, you can run the command msck repair table (cwiki.apache.org/confluence/display/Hive/…

[Chris Njuguna]

Thanks @dbustosp I get your solution and it was a perfect quick fix for my situation. However according to the article in the URL I posted it is possible to alter partition data types with a single command. I think you would agree it is potentially the best way to do this. Remember the partition column is quite independent of the data so I should be able to do this without dropping and recreating the table and partitions. Please review the link I posted in the original question. Why does the "alter table partition" command not work? Am I doing it wrong?