CODEWITHSUNDEEP
hive
Iam619
Iam619

Reputation: 795

dynamically determine the value of a column in Hive

I would like to determine the value of a column in a Hive table by some statement.

e.g., the column is age:

if(some condition are satisfied) the value is 30, otherwise, the value is 10

so after inserting all the rows, the age of rows which the statement is satisfied are 30 while other rows are 10.

I'm using query like:

insert overwrite table test_table select A.age from (select IF(condition, 30, 10) as age from some_other_table ) A;

But the if statement seems only works for true and false. Thanks for any help!

Upvotes: 0

Views: 1334

Answers (2)

Nishu Tayal
Nishu Tayal

Reputation: 20830

You are using select A from (output of another select) this looks somewhat ambiguous. Better, you can try like this :

insert overwrite table test_table 
        (select IF(condition, 30, 10) as age from some_other_table )

where condition should be like this :

select IF(height >=170 and gender="male", 30, 10) as age from some_other_table

Hope, it helps you.

Upvotes: 1

Lukas Vermeer
Lukas Vermeer

Reputation: 5940

The condition should indeed return a boolean value, but you can construct requirements of arbitrary complexity using the relational and logical operators.

If, for instance, you want all males taller than 160cm to be assigned age 30 and all other age 10, you could do the following.

select if(sex = "M" AND height >= 160, 30, 10) as age 
from some_other_table

Hope that helps.

Upvotes: 2

Related Questions