Adding a column which is the result of some formula in Hive

Question

I have a table of values. I want another column which, for example, is thrice the first column plus one (or whatever other formula).

If I write something like this:

SELECT item, value, value*3+1 FROM myTable;

I get something like this:

A    | 1     | 4
B    | 2     | 7
C    | 3     | 10

I want to store the third column permanently. Doing this:

ALTER TABLE myTable ADD COLUMN (value*3+1 AS value2 FLOAT);

Does not work. However, this does work:

CREATE TABLE myTable2 AS SELECT 
    item, value, value*3+1 AS value2 
    FROM myTable;

However, I don't want a new table, I want to insert a column into an existing table. I'm thinking I might need to ADD COLUMN and then INSERT OVERWRITE? Bonus points if your method can use UDFs like avg() or sum(), but I'll take just basic arithmetic for now.

I tried searching and neither this not this answered my question.

score 1 · Accepted Answer · answered Aug 04 '15 at 20:48

You would have a problem maintaining your table if you store calculated columns. This would require a trigger operation in a database, which would insert a value into that column after a new row gets inserted. The other way is to have a view with the calculated column.

w.r.t Hive, alter table.. syntax is wrong as a calculation can't be specified here. It should be

ALTER TABLE myTable ADD COLUMNS (value2 FLOAT);

Adding a column which is the result of some formula in Hive

1 Answers1