hive – is it possible to create a column from another column

I was wondering if it is possible to create a new column from an existing column in hive.

Lets say I have a table People (name string, age int) and I want to add a column is_old string which would be defined as if(age > 70, 'old', 'not_old'). Is there a way to do this?

The only way I can think of currently is to create a new table People_temp from the old one, delete the old table and rename the new table like so:

create table People_new as select name, age, if(age > 70, 'old', 'not_old') as is_old from People;
drop table People;
alter table People_new rename People;

** Is there a way of doing this without creating a temp table?
(for instance, oracle has the idea of calculated columns.)

Answer

Yes, there is a way to do it WITHOUT CREATING TEMP TABLE. Add column then insert overwrite table select from itself :

ALTER TABLE People ADD COLUMNS (is_old string);

INSERT OVERWRITE TABLE People 
SELECT name, age, if(age > 70, 'old', 'not_old') as is_old
  FROM People ;

Advertisement

Answer