How to deal with null and\ N in Hive

This article mainly explains "how to deal with null and\N in Hive". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let Xiaobian take you to learn "Hive null and\N how to deal with it"!

Hive has a false NULL that looks exactly like NULL, but is not NULL. NULL is stored as '\N' by default at the bottom level, and we habitually filter NULL values, usually is null and is not null. Therefore, at this time, you can modify the information in the hive table to ensure that the control is displayed as the specified value, which is convenient for filtering. The solution is as follows:

(1) As follows:

alter table t_dw_ct_author set serdeproperties ('serialization.null.format' = 'NULL');

Then it appears: the underlying data is stored as 'NULL', and the query shows 'NULL'

In this case, if the query is null, the field can be passed through the statement: a is null or a='NULL'

or

alter table t_dw_ct_author set serdeproperties('serialization.null.format' = '\N');

Then it appears: the underlying data is saved as '\N', and the query shows'NULL'

In this case, if the query is null, the field can be passed through the statement: a is null or a='\\N'

(2)'' and length (xx)=0

'' indicates that the field is not null and is an empty string. In this case, a is null cannot query this value. It must be queried by a='' or length(a)=0.

At this point, I believe that everyone has a deeper understanding of "Hive null and\Nhow to deal with", may wish to actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!