Hive alter table change column name gives 'NULL' to the renamed column

5
votes

I had tried to rename an existing column in a table to a new one. But after the name change, the new column is giving me only 'NULL' value.

The storage format for the table in Parquet.

For e.g,

'user' is a column in 'Test' table of string data type. Inserted a sample record with value as 'John'.

Select user from Test;

Result : John

I have renamed 'user' to 'user_name' without changing any data type.

ALTER TABLE Test CHANGE user user_name String;

Select user_name from Test;

Result : NULL

Please let me know how to fix this issue?

Whether MSCK Repair table command be of any use in this case?

Do I need to reload this table again to fix this issue?

Regards, Adarsh K S

1
hadoophivebigdatahiveqlparquet
Your existing Parquet files have the column names defined at write time. So the new column name doesn't match. – Samson Scharfrichter
Generally speaking, many ALTER TABLE commands apply only to future partitions and/or future data files. Sometimes you can also force application to existing partitions. But in your case that does not apply. – Samson Scharfrichter
If you just wanted to change the displayed name you could have done SELECT `user` AS user_name ... – Samson Scharfrichter

1 Answers

0
votes

What you can do is add the new field, execute one insert overwrite and then delete the old field. Something like this: 

ALTER TABLE temp.Test ADD COLUMNS (user_new string) CASCADE;

insert overwrite table temp.Test
select 
      user_a,
      a,
      b,
      c,
      user_a as user_new
from temp.test;

ALTER TABLE temp.test  REPLACE COLUMNS(user_new string, a string, b string, c string );