Friday 23 December 2022

Hive: Conditional functions or statements

 

Below table summarizes the conditional functions supported in Hive.

 

Function or statement

Description

Example

assert_true(boolean condition)

Do nothing when the condition evaluates to true, else throw an exception.

hive> SELECT assert_true(true);

OK

NULL

Time taken: 1.16 seconds, Fetched: 1 row(s)

hive> SELECT assert_true(false);

OK

Failed with exception java.io.IOException:org.apache.hadoop.hive.ql.metadata.HiveException: ASSERT_TRUE(): assertion failed.

Time taken: 0.063 seconds

CASE [ expression ]

       WHEN val1 THEN result1

       WHEN val2 THEN result2

       ...

       WHEN val3 THEN resultn

       ELSE result

END

In Case statement, expression is optional. This is similar to a switch statement.

SELECT rating, CASE rating < 0

    WHEN True THEN "Bad performer"

    WHEN False THEN "Good Performer"

  END

FROM emp;

coalesce(value1,value2,...)

Return first non-null value from the given values. If all the values are null, then it return null.

hive> SELECT coalesce('Krishna', null, null);

OK

_c0

Krishna

Time taken: 0.042 seconds, Fetched: 1 row(s)

if(boolean condition, T trueValue, T falseValue)

This return trueValue when the condition evaluates to true, else falseValue.

SELECT name,if((gender_age.gender=='Male'),'M','F') FROM emp;

 

isnull(column)

Return true if the value of column is null, else false.

SELECT * FROM emp WHERE isnull(gender_age.age);

isnotnull(column)

Return true if the value of column is not null, else false.

SELECT * FROM emp WHERE isnotnull(gender_age.age);

nvl(T value, T defaultValue)

Return value if it is not null, else return default value

hive> SELECT gender_age.age, nvl(gender_age.age, 'not_exists') FROM emp;

OK

age    _c1

30     30

38     38

32     32

32     32

NULL not_exists

Time taken: 0.052 seconds, Fetched: 5 row(s)

 

I am using below sample data to demonstrate the examples.

 

Table definition

CREATE TABLE emp (
id INT,
name STRING,
hobbies ARRAY<STRING>,
technology_experience MAP<STRING,STRING>,
gender_age STRUCT<gender:STRING,age:INT>,
rating DOUBLE,
salary DOUBLE
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE;

 

Load the data to emp table

empInfo.txt

1|Hari|Football,Cricket|Java:3.4Yrs,C:4.5Yrs|Male,30|-1.5|1000000
2|Chamu|Trekking,Watching movies|Selenium:5.6Yrs|Female,38|3|2500000
3|Sailu|Chess,Listening to music|EmbeddedC:9Yrs|Female,32|2.5|1300000
4|Gopi|Cricket|Datastage:11Yrs|Male,32|-0.7|81000000
5|Rahim|||Male,|-0.7|500000 4|Gopi|Cricket|Datastage:11Yrs|Male,32|-0.7|81000000
5|Rahim||||-0.7|500000

 

Execute below command to load the data to emp table.

LOAD DATA LOCAL INPATH  '/home/cloudera/examples/hive/empInfo.txt' INTO TABLE emp;

hive> SELECT * FROM emp;
OK
emp.id  emp.name    emp.hobbies emp.technology_experience   emp.gender_age  emp.rating  emp.salary
1   Hari    ["Football","Cricket"]  {"Java":"3.4Yrs","C":"4.5Yrs"}  {"gender":"Male","age":30}  -1.5    1000000.0
2   Chamu   ["Trekking","Watching movies"]  {"Selenium":"5.6Yrs"}   {"gender":"Female","age":38}    3.0 2500000.0
3   Sailu   ["Chess","Listening to music"]  {"EmbeddedC":"9Yrs"}    {"gender":"Female","age":32}    2.5 1300000.0
4   Gopi    ["Cricket"] {"Datastage":"11Yrs"}   {"gender":"Male","age":32}  -0.7    8.1E7
5   Rahim   []  {}  {"gender":"Male","age":null}    -0.7    500000.0
Time taken: 0.034 seconds, Fetched: 5 row(s)

 

 

Previous                                                    Next                                                    Home

No comments:

Post a Comment