1

Currently, I try to union several tables in hive. After I achieved this, I found that some column type is not proper. I thought some should be float but some kind it becomes string. Then I ran alter command:alter table table_name change column_name column_name float; It returned error message:

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter table. The following columns have types incompatible with the existing columns in their respective positions

I thought this means that hive don't support convert string to float as showed in table. But I found that I can do cast on the same column and get wanted result. This makes me confused why there will be different result between alter and cast. What is the logic behind it. THX.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
G_cy
  • 994
  • 3
  • 13
  • 28

1 Answers1

1

It seems you have missed CHANGE keyword. 

alter table table_name CHANGE column_name column_name_new float;

See here: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-ChangeColumnName/Type/Position/Comment

Demo:

hive> create table t(a string);
OK
Time taken: 0.069 seconds
hive> alter table t change a a_new float;
OK
Time taken: 0.158 seconds
hive> describe formatted t;
OK
# col_name              data_type               comment

a_new                   float
leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • sorry for mistake. I have change keyword in my command. updated. – G_cy Aug 01 '17 at 06:50
  • @G_cy have checked it few times. It works with the same column name and different types: string, float, date, etc. Maybe the problem is in the Hive version. Mine is 1.2.1000.2.4.3.3-2 – leftjoin Aug 01 '17 at 07:08
  • my version is 2.0.1. based on the link:https://stackoverflow.com/questions/36837266/change-column-type-in-hive I thought this can't be done? Also I tried with new column name and failed again. – G_cy Aug 01 '17 at 07:48
  • Well, you always can reload into new table as a workaround or cast. What is the storage format: TEXT, ORC, etc? Is it related to storage format? – leftjoin Aug 01 '17 at 08:39
  • The original table was created by others. I just need to combine them all and push to es cluster. I thought original table storage format may be TEXT. – G_cy Aug 01 '17 at 08:43
  • On my Hive `hive.metastore.disallow.incompatible.col.type.changes=false` by default. I tried to set it `true` and also was able to change column type – leftjoin Aug 01 '17 at 08:48
  • Found this Jira https://issues.apache.org/jira/browse/HIVE-13515 - it Affects Version 2.1.0 and is not resolved yet. Most probably this is because this bug in your Hive version – leftjoin Aug 01 '17 at 09:00
  • Oh my hive. I tried to set the parameter to true. Failed again. I thought the issue is the reason cause my problem. – G_cy Aug 01 '17 at 09:45
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/150710/discussion-between-leftjoin-and-g-cy). – leftjoin Aug 01 '17 at 10:12