CODEWITHSUNDEEP
sqlhivehiveql
anthonybell
anthonybell

Reputation: 5998

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

Upvotes: 5

Views: 6951

Answers (2)

leftjoin
leftjoin

Reputation: 38290

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 ;

Upvotes: 4

Blank
Blank

Reputation: 12378

You are almost there, you should try it.

Use CASE WHEN:

,CASE WHEN age > 70 THEN ''old' ELSE 'not_old' END AS is_old string

use IF:

,IF(age > 70, 'old', 'not_old') AS is_old string

Upvotes: 2

Related Questions