Hive 1.1.0 Alter table partition type from int to string

7
votes

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

2
hadoophivepartitioningddl

2 Answers

17
votes

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.

0
votes

I think yo should redefine the table's schema and redefine that your partition value is not gonna be a integer anymore and this is now gonna be a string type.

What I recommend you to do is:

  1. Make your table external (in case you define this as a non-external table). In this case you can drop the table without removing the data in the directories.
  2. Drop the table.
  3. Create again the table with the new schema (Partition value as a string).

The steps above, physically (structure folders) is not gonna make any difference with the structure that you already had. The difference is gonna be in the Hive metastore, specifically in the "virtual column" created when you make partitions.

Also, now instead making queries like: part_col = 1, now you are gonna be able to make queries like: part_col = '1'.

Try this and tell me how this goes.