Enable Hive Metastore NULL value as empty string

Let’s discuss on enabling Hive Metastore NULL result for an empty string.

In Hive, while inserting values, if some columns have empty strings and you want to display it as  NULL when queried the table.

From the below image, you can see the empty string are displayed just like that. And this property applies to only String column type.

 

To display it as a NULL value, like the below image, you have to set 2 properties.

1. Hive Metastore Server (Hive Metastore Server Advanced Configuration Snippet (Safety Valve) for hive-site.xml) from Cloudera manager
2. Table properties

 

1. Hive Metastore Property

Cloudera manager -> Hive -> Configuration -> Hive Metastore Server Advanced Configuration Snippet (Safety Valve) for hive-site.xml

add:

<property>
<name>hive.metastore.orm.retrieveMapNullsAsEmptyStrings</name>
<value>true</value>
</property>

In Cloudera Manager 5.10, you have good UI to set those values, see below image.

 

If you set those value to False, this will not work.

After setting NULLAsEmptyStrings, property, you have to restart Hive service to take effect.

 

2. Once you have enabled property in Hive Metastore. Let’s create a table.

2.1 Table creation:

CREATE TABLE display_null (first_name string, second_name string);

Here we are creating the table with 2 string column. 

2.2 Insert Values:

INSERT INTO TABLE display_null VALUES (“Boopathi”, “”), (“”, “Boopathi”);

we have insterted 2 rows with empty string on each.

2.3 Display as an empty string:

 

2.4 TABLE PROPERTIES:

ALTER TABLE display_null SET TBLPROPERTIES(‘serialization.null.format’=”); 

setting “serialization.null.format“

 

2.5 Display result as NULL :

 

2.6 Table description:

DESCRIBE FORMATTED display_null;

you will see the properties in the below image.