Apache Hive – ISNULL vs NVL

Unlike in relational database systems like SQL Server, the ISNULL function works in a different way in Apache Hive. In SQL Server, ISNULL(col1, 0) returns 0 if the col1 value is null. Same implementation will return the below error in Apache Hive.

isnull

This is because the Apache Hive ISNULL function is a conditional assertion that returns TRUE or FALSE.

isnull_working
If you are looking for a ISNULL equivalent function in Apache Hive, then use NVL.

Below are the descriptions of the functions.

ISNULL:
Returns TRUE if the provided column value is NULL, false otherwise.

NVL:
Returns column value if value is not null else returns the value provided by the user.

This slideshow requires JavaScript.

2 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s