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: