0

Hive converts Null to empty String in String Column. What is reason for that ?

As per our requirement, we need to see Null in string column instead of empty String otherwise Is Null not working for those columns. So to solve this problem we set below property on Table:

TBLPROPERTIES('serialization.null.format'='')

But Still we see empty string instead of NULL and even SHOW TBLPROPERTIES also not showing this property in result, so i am not sure if this property is set or not.

I tried to set this property in DDL itself and even i tried 

Alter Table <TableName> Set TBLPROPERTIES ('serialization.null.format' = '');
pardeep garg
  • 209
  • 1
  • 9

2 Answers2

0

If needed create another table and store the values of this into that in this format. I mentioned -1 as example you can use anything of your choice.

in your select query 

select
case when <col> is null then -1 else <col> end as <col>
from
table
Ganesh Chandrasekaran
  • 1,578
  • 12
  • 17
  • Thanks Ganesh, My Problem is i am loading Data from Oracle to Hive table after some processing. So Any Null value in Hive is not showing as Null. Hive treat Null values as empty String. So i want to check what I can do that when i select data in Hive , it show me Null instead of empty String. I already tried ('serialization.null.format' = ''); – pardeep garg Mar 17 '20 at 17:03
  • if you use isnull() in Hive, it treats Null as Null. Please check your oracle source whether it returns Null as null or as empty string. – Ganesh Chandrasekaran Mar 17 '20 at 17:05
  • In Oracle, it is Null, When i am inserting that data(Data Type - String) in Hive table. it is showing me empty string not Null – pardeep garg Mar 17 '20 at 17:09
0

How you are moving data? If you are using sqoop then you can try passing below arguments

    --input-null-string '\\N' 
    --input-null-non-string '\\N'
shashank
  • 50
  • 4