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

Question

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

Your existing Parquet files have the column names **defined** at write time. So the new column name doesn't match. — Samson Scharfrichter, Aug 30 '18 at 11:01
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, Aug 30 '18 at 11:05
If you just wanted to change the _displayed_ name you could have done `SELECT \`user\` AS user_name ...` — Samson Scharfrichter, Aug 30 '18 at 11:08

score -1 · Answer 1 · answered Jul 24 '19 at 06:53

-1

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 );

answered Jul 24 '19 at 06:53

Above solution is not reasonable for partitioned tables, also replace columns will update the DDL but the extra column stays on for EXTERNAL tables. I'd caution against taking this approach, without understanding what is happening under the hood – Fremzy Jan 31 '23 at 00:05

1 Answers1