NULL data converted into '\N' for numeric columns in hive.?

Question

I created a hive table which has numeric columns such as double and string columns.My file contains some NULL values for both numeric and string columns. When I try to load a file into this table, NULL values for the numeric columns is replaced by '\N' in file.I know this is hive property that handle null values for numeric type columns but i want to prevent it or Is there any way that i can change NULL into something else instead of '\N'.

Answer 1

By default NULL values are written in the data files as \N and \Nin the data files are being interpreted as NULL when querying the data.
This can be overridden by using TBLPROPERTIES('serialization.null.format'=...)

E.g.
TBLPROPERTIES('serialization.null.format'='') means the following:

• An empty field in the data files will be treated as NULL when you query the table
• When inserting rows to the table, NULL values will be written to the data files as empty fields

---

This property can be declared as part of the table creation

create table mytable (...)
tblproperties('serialization.null.format'='')
;

and can be changed later on

alter table mytable set tblproperties('serialization.null.format'='')
;