Impala Miscellaneous Functions


Impala Conditions with Example

Impala supports the following conditional functions for testing equality, comparison operators, and nullity:

‘Case’ Example:

1) If else

select case when 20 > 10 then 20 else 15 end;

Output: 20

2) If else if

select case when 9 > 10 then 20 when 1 > 2 then 1.0 else 15 end;

Output: 15

=====================================================================================

‘Coalesce’ Function Example:

The COALESCE function in Impala returns the first non-NULL expression among its arguments.

Simple Example:

select coalesce (NULL, ‘b’, ‘c’);

Output: b

select coalesce (‘a’, ‘Null’, ‘c’);

Output: a

 

USECASE: we want to find out the best way to contact each person according to the following rules:

  1. If a person has a mobile phone, use the mobile phone number.
  2. If a person does not have a mobile phone and has a home phone, use the home phone number.
  3. If a person does not have a mobile phone, does not have a cell phone, and has a office phone, use the office phone number.

Create Table:

Insert Data :

Query:

OUTPUT:

+——-+————————————–+————+

| name | coalesce (mobileno, homeno, officeno) | city |

+——-+————————————–+————+

| user3 | 9874561 | Vijayawada |

| user2 | 1234567 | Chennai |

| user1 | 6654756 | Hyderabad |

+——-+————————————–+————+

================================================================

DECODE Funcation Example:

Create Table:

>create table decodetable (empid INT, empname STRING, empcountry STRING, empage INT);

Insert Data:

Query:

Output:

+——-+———–+————+——–+

| empid | shortcode | empcountry | empage |

+——-+———–+————+——–+

| 14 | PN | Bangalore | 15 |

| 12 | RJ | Delhi | 35 |

| 13 | RH | Chennai | 40 |

| 11 | AZ | Vijayawada | 21 |

+——-+———–+————+——–+

‘shortcode’ is the name given to the column with the DECODE statement

==================================================================================

ISNULL ():

If that column had null value, then it will replace with give value

Sample Table:

Query: select * from emp1;

+——-+———+————+——–+

| empid | empname | empcountry | empage |

+——-+———+————+——–+

| NULL | Azmal | NULL | 15 |

| 12 | Raj | Delhi | 35 |

| 11 | Ravi | hyd | 32 |

| 13 | Rahul | chennai | 40 |

| 14 | Phani | Bangalore | 15 |

+——-+———+————+——-+

ISNull Query:

select isnull (empid ,15) from emp1;

 

Output:

+——————-+

| isnull (empid, 15)|

+——————-+

| 12 |

| 15 |

| 13 |

| 11 |

| 14 |

+——————-+

==================================================================================

NULLIF ():

The syntax for the NULLIF function in Impala is:

NULLIF (expression1, expression2)

*expression1, expression2

The expressions that will be compared. Values must be of the same datatype.

*Note:

expression1 can be an expression that evaluates to NULL, but it can not be the literal NULL

 

Simple Examples:

SELECT NULLIF (‘Azmal Sheik’, ‘Azmal Sheik’);

Result: NULL

(returns NULL because values are the same)

 

SELECT NULLIF(‘hadooptutorial.info’, ‘google.com’);

Result: ‘hadooptutorial.info’ (returns first value because values are different)

 

SELECT NULLIF (12, 12);

Result: NULL

(returns NULL because values are the same)

 

SELECT NULLIF (12, 45);

Result: 12

(returns first value because values are different)

==================================================================================

NULLIFZERO:

It is one of the very important Impala functions converting zero to null value when divide-by-zero problem comes into picture.

Sample Table:

Query: select * from NullIfZeroTable;

+——-+————-+————+——–+

| empid | empname | empcountry | empage |

+——-+————-+————+——–+

| 13 | Rahul | Chennai | 40 |

| 18 | Sheik Azmal | Hyderabad | 0 |

| 14 | Phani | Bangalore | 15 |

| 12 | Raj | Delhi | 35 |

| 11 | Ravi | Vijayawada | 32 |

+——-+————-+————+——–+

Fetched 5 row(s) in 0.36s

Query: select nullifzero (empage) from decodetable

Output:

+——————–+

| nullifzero(empage) |

+——————–+

| 32 |

| NULL |

| 40 |

| 15 |

| 35 |

+——————–+

Fetched 5 row(s) in 0.37s

 

Related Functions:

ZEROIFNULL

Replace NULL values with 0

 

 

NVL ():

This function is used to replace NULL value with another value. It is similar to the IFNULL Function in Imapala and the ISNULL Function.

Query: select * from emp1;

+——-+———+————+——–+

| empid | empname | empcountry | empage |

+——-+———+————+——–+

| NULL | Azmal | NULL | 15 |

| 12 | Raj | Delhi | 35 |

| 11 | Ravi | hyd | 32 |

| 13 | Rahul | chennai | 40 |

| 14 | Phani | Bangalore | 15 |

+——-+———+————+——–+

Fetched 5 row(s) in 0.40s

Query: select null(empid,20) from emp1

+—————-+

|null (empid, 20)|

+—————-+

| 20 |

| 11 |

| 11 |

| 13 |

| 14 |

+—————-+

 

Impala String Functions

Char_lenght ():

Returns the length in characters of the argument string. Aliases for the length () function.

Query: select char_length(‘Impala’);

Output:

+—————————–+

| char_length(‘impala’) |

+—————————–+

| 6 |

+—————————–+

 

 

Concat ():

This function is used to concatenate two strings to form a single string.

Query:

select concat (empname, empcountry), empage, empcountry from emp1;

Output:

+—————————–+——–+————+

| concat (empname, empcountry) | empage | empcountry|

+—————————–+——–+————+

| RaviVijayawada | 32 | Vijayawada |

| PhaniBangalore | 15 | Bangalore |

| Sheik AzmalHyderabad | 0 | Hyderabad |

| RahulChennai | 40 | Chennai |

| RajDelhi | 35 | Delhi |

+—————————–+——–+————+

 

 

Find_in_set ():

FIND_IN_SET function returns the position of a string in a comma-delimited string list.

Query:

select FIND_IN_SET (‘b’, ‘a, b, c, d, e, f’);

Output:

+—————————————-+

| find_in_set (‘b’, ‘a, b, c, d, e, f’) |

+—————————————-+

| 2 |

+—————————————-+

Fetched 1 row(s) in 0.01s

 

Repeat ():

Returns the argument string repeated a specified number of times.

 

Query:

select repeat (‘Azmal ‘ ,5);

 

Output:

+——————————-+

| repeat (‘azmal ‘, 5) |

+——————————-+

| Azmal Azmal Azmal Azmal Azmal |

+——————————-+

Fetched 1 row(s) in 0.01s

 

For Reference take a look:

reverse (string a)

Purpose: Returns the argument string with characters in reversed order.

Return type: string

 

rpad (string str, int len, string pad)

Purpose: Returns a string of a specified length, based on the first argument string. If the specified string is too short, it is padded on the right with a repeating sequence of the characters from the pad string. If the specified string is too long, it is truncated on the right.

Return type: string

rtrim (string a)

Purpose: Returns the argument string with any trailing spaces removed from the right side.

Return type: string

space (int n)

Purpose: Returns a concatenated string of the specified number of spaces. Shorthand for repeat (‘ ‘, n).

Return type: string

Strleft (string a, int num_chars)

Purpose: Returns the leftmost characters of the string. Shorthand for a call to substr () with 2 arguments.

Return type: string

 

Strright (string a, int num_chars)

Purpose: Returns the rightmost characters of the string. Shorthand for a call to substr () with 2 arguments.

Return type: string

substr (string a, int start [, int len]), substring (string a, int start [, int len])

Purpose: Returns the portion of the string starting at a specified point, optionally with a specified maximum length. The characters in the string are indexed starting at 1.

Return type: string

 

Translate (string input, string from, string to)

Purpose: Returns the input string with a set of characters replaced by another set of characters.

Return type: string

 

Trim (string a)

Purpose: Returns the input string with both leading and trailing spaces removed. The same as passing the string through both ltrim () and rtrim ().

Return type: string

 

Upper (string a), ucase (string a)

Purpose: Returns the argument string converted to all-uppercase.

Return type: string

=======================================================

Regexp_Extract ():

For Reference:


About Siva

Senior Hadoop developer with 4 years of experience in designing and architecture solutions for the Big Data domain and has been involved with several complex engagements. Technical strengths include Hadoop, YARN, Mapreduce, Hive, Sqoop, Flume, Pig, HBase, Phoenix, Oozie, Falcon, Kafka, Storm, Spark, MySQL and Java.

Leave a comment

Your email address will not be published. Required fields are marked *