Handling NULL in Apache Impala

An significant part is the treatment of NULL values during data processing and reports. Sometimes it ends up with arithmetic overflow errors or null values if not done properly. There are lots of conditional functions in the Apache Impala that manage NULL values.

  • ISNULL
  • NVL
  • IFNULL
  • ZEROIFNULL

The above mentioned four functions does almost the same job. You can use any of them if your data has null values.

SELECT ISNULL(NULL,0)
Returns 0

SELECT NVL(NULL,0)
Returns 0

SELECT IFNULL(NULL,0)
Returns 0

SELECT ZEROIFNULL(0)
Returns 0

NULL in Impala1NULL in Impala2

ISNULL, IFNULL and NVL are identical and are same in behavior. The ZEROIFNULL function varies marginally from those three functions. The NULL value will be transformed to 0 by this function and nothing more can be achieved.

We’ll learn more about these features in the coming blogs.

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