Redshift NULLIF and NULL Commands Simplified 101

Redshift is a completely managed Data Warehouse based on a subscription model offered by Amazon Web services. Redshift follows PostgreSQL standards for querying. It can support up to 2 Petabytes of data. Redshift is well known for its super-fast querying abilities accomplished through massive Parallel Processing clusters. Since it follows PostgreSQL querying standards, it is widely used as a replacement for PostgreSQL-based Data Warehouse systems in Enterprises. Architecture based on massively Parallel Processing also ensures the highest levels of availability and robustness.

Beyond the standard SQL querying abilities, a Data Warehouse often needs specialized functions to handle the typical problems that are generally not faced in a Transactional Database. Since Data in a Warehouse can come from multiple sources and are often not cleansed, null or empty values find their way into a warehouse in many cases. It then becomes the responsibility of the analysts to weed out and act accordingly.

Upon a complete walkthrough of this article, you will gain a decent understanding of Amazon Redshift. You will also learn how the Redshift NULLIF and NULL Commands are used to handle Null values in Amazon Redshift. Read along to learn more about Redshift NULLIF and NULL Commands!

Table of Contents

• Introduction to Amazon Redshift
• Redshift NULL Commands
• Checking for NULL Values in Redshift
• Using NULL Values in Expressions and Operations
  • NVL or COALESCE Expression
  • NVL2 Expression
• Redshift NULLIF Expression
• Conclusion

Introduction to Amazon Redshift

Amazon Redshift derives its strength from the Massively Parallel Processing Architecture that is made of a collection of Compute and Storage instances. Redshift allows customers to choose from two kinds of instances – Dense Compute and Dense Storage Instances. Dense Compute Instances are suitable for workloads that involve complex query processing. If your use case involves simple query processing and needs cheap storage, you can choose the Dense Storage Instances. The main difference between them is that Dense Storage Instances come with HDDs and Dense Compute Instances come with SSDs.  A detailed article on Redshift pricing can be found here.

Redshift’s Architecture contains Leader Nodes and a collection of Secondary Nodes. Leader nodes are responsible for client communication, execution plan design, task assignment, etc. Compute nodes handle specific chunks of data and execute the query on the data they handle upon request from the leader node. A detailed explanation of Redshift architecture can be found here.

Redshift’s ace up the sleeve is its ability to scale horizontally or vertically without much impact. Scaling horizontally means adding more nodes and scaling vertically means upgrading the nodes. Redshift’s Concurrent Scaling allows users to scale the cluster automatically within the budget limits set by the user. Concurrent Scaling is a separate paid feature but an hour of Concurrent Scaling is offered free for every 24 hours the Redshift cluster stays operational. There is another feature called Redshift Spectrum that allows users to take advantage of Redshift’s Computing capability without using its Storage Layer. Redshift Spectrum can process data stored in S3, without adding them as Redshift tables. 

Such flexibility and rich feature set makes Redshift a popular choice for implementing highly reliable ELT or ETL systems. For further information on Amazon Redshift, you can click here to check out their official website.

Redshift NULL Commands

A Null happens when a column in a row is Missing, Unknown, or Not Applicable. A Null means the value does not exist. It is not equal to an empty string or zero. A null can happen in a field of any data type. All arithmetic expressions involving Null as a parameter will evaluate to a NULL value. 

You might wonder what is the purpose of NULL in a Data Warehouse. In a Data Warehouse, data comes from multiple sources and it is not always possible to control the content or data type. It can also come from the result of erroneous queries. Since a Data Warehouse is meant as an accumulator of all data in an organization, it is inevitable to encounter NULL at some point in time. 

The following sections describe how to handle NULL values in Redshift using Redshift NULLIF and NULL Commands like ISNull Redshift.

Checking For NULL Values in Redshift

Redshift offers two Constructs – IS NULL and IS NOT NULL to check the existence of NULL. As the name suggests, IS NULL condition returns true in case of NULL values and IS NOT NULL condition returns true in case of valid values. 

For example, if there is a table customer with Fields, Name, Age, and Address. In order to get a count of all customers whose Address field is NULL, execute the below query:

SELECT COUNT(*) FROM CUSTOMER WHERE ADDRESS IS NULL

To get a count of all customers whose Age is NOT NULL, execute the below query:

SELECT COUNT(*) FROM CUSTOMER WHERE AGE IS NOT NULL

Using NULL Values in Expressions and Operations

A side effect of having NULLs in the database is that you need additional steps to use them in your calculation. In a nutshell, you have to check for NULL values and assign an alternate value to make sense of it. Redshift offers the following two expressions to handle this:

1) NVL or COALESCE Expression

This expression takes a list of expressions and returns the first Non-Null value it sees as the output. This is very useful in cases where you have to use a different column if the existing column is NULL in your calculation. 

For example, consider a scenario where there are two columns home_address and office_address. To select a shipping address, you have to use the office address if exists or else the home address.

Firstly, create a table to envision this scenario.

CREATE TABLE CUSTOMER( 
id INTEGER,
office_address VARCHAR(30),
home_address VARCHAR(30)
);

Now it is time to insert some values into the table.

INSERT INTO CUSTOMER VALUES(1,’114,BURLINGTON ROAD’, NULL);
INSERT INTO CUSTOMER VALUES(2,’113,BURLINGTON ROAD’, ‘1080 COMMERCIAL STREET’);
INSERT INTO CUSTOMER VALUES(3,NULL,118 HOMEBUSH);

To get the first Non-Null value as the shipping address, use the query given below:

SELECT NVL(office_address,home_address) as shipping_address from customer;

The result will be as follows.

shipping_address
-------------------------
‘114, BURLINGTON ROAD’
’113, BURLINGTON ROAD’
‘1080 COMMERCIAL STREET’

Instead of NVL, you can use COALESCE too. These are synonyms. 

2) NVL2 Expression

NVL2 takes an expression, a value to be returned in case the expression evaluates to NULL and a value to be returned in case the expression is evaluated to NOT NULL. The data type of the value to be returned in case of the NOT NULL scenario decides the data type of the return value. 

In the earlier case, assume that, you want to return the value ‘NO_OFFICE_ADDRESS’ in case the office_address column is NULL. This can be handled using the below query:

SELECT NVL2(office_address,office_address,’NO_OFFICE_ADDRESS’) as address from CUSTOMER;

The same result can be accomplished using another construct called DECODE too. DECODE is a generic expression that can compare a column to any value including NULL and return one of the two choices as the return value.

Redshift NULLIF Expression

Redshift NULLIF expression takes two input values and returns NULL if they match. If they do not match, it returns the first argument.

Redshift NULLIF expression is useful when you have to return NULL in case of empty strings.

To demonstrate the functioning of Redshift NULLIF function, Insert a row with the office_address table as an empty string into the CUSTOMER table that you created earlier.

INSERT INTO CUSTOMER VALUES(4,’’,’118 BURLINGTON ROAD’’);

Note that a NULL is very different from an empty string that is denoted by just an empty quote symbol.

To get the office_address value as NULL in case it is empty, use the below statement.

SELECT NULLIF(office_address,'') FROM CUSTOMER;

The result will be as follows:

office_address
-------------------------
‘114, BURLINGTON ROAD’
’113, BURLINGTON ROAD’
NULL
NULL

In the above result, the third value is NULL because it is NULL in the database. The fourth value is NULL, because Redshift NULLIF statement found an empty string in the column and replaced it with a NULL.

This concludes the short tutorial on handling NULL values in Redshift using Redshift NULLIF and NULL Commands. NULLs are an important aspect of a Data Engineer’s job and if not properly handled, they can result in issues that are very hard to debug. 

Conclusion

This article attempted to provide details on how to easily handle NULL values in Redshift using Redshift NULLIF and NULL Commands. Redshift is a great choice to be used as a Data Warehouse because of its Columnar nature, Ability to Scale seamlessly, Super-fast querying capabilities. While AWS provides multiple tools to make your ETL tasks with Redshift easy, most of them are tailor-made for the AWS ecosystem. With your Data Warehouse, Amazon Redshift live and running, you’ll need to extract data from multiple platforms to carry out your analysis. However, integrating and analyzing your data from a diverse set of data sources can be challenging and this is where Hevo Data comes into the picture.

Visit our Website to Explore Hevo

Hevo Data is a No-code Data Pipeline and has awesome 100+ pre-built integrations that you can choose from. Hevo can help you integrate data from numerous sources and load it into a Data Warehouse/Database like Amazon Redshift to analyze real-time data with a BI tool and create your Dashboards. It will make your life easier and make data migration hassle-free.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of learning about Redshift NULLIF and NULL Commands. Tell us in the comments below!