Handling NULL values in Hive
hive, null
Solution
Firstly — I don't think `column1 is not NULL or column1 <> ''` makes very much sense. Maybe you meant to write `column1 is not NULL and column1 <> ''` (`AND` instead of `OR`)?
Secondly — because of Hive's "schema on read" approach to table definitions, invalid values will be converted to `NULL` when you read from them. So, for example, if `table1.column1` is of type `STRING` and `table2.column1` is of type `INT`, then I don't think that `table1.column1 IS NOT NULL` is enough to guarantee that `table2.column1 IS NOT NULL`. (I'm not sure about this, though.)
Problem
I am trying to create a table (table 2) in Hive from another table (table 1). I am trying to exclude certain rows with NULL values and tried the following condition. ``` insert overwrite table table2 partition (date = '2013-06-01') select column1, column 2.... from table1 where column1 is not NULL or column1 <> ''; ``` However, when I try this following query with the new table I get 300+ rows with NULL vaues: ``` select count(*) from table2 where column1 is NULL; ``` Could someone point to what is causing these NULL values? Thank you. Ravi