Hive Query Column Type Comparison

Hive query is very close to MySQL in many ways, and it has lots of other features that MySQL does not have. I have been playing with it for quite a few months and I quite enjoy using it so far. There are also lots of problems that I have encountered and here is one of them. Because Hive is build from JAVA, which is a strong typed language, when doing query on Hive tables, you should be careful about column types and the value you are comparing with, for example:

CREATE TABLE hive_table (hive_string_column string, hive_int_column int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

-- INSERT SOME DATA HERE

SELECT * FROM hive_table WHERE hive_string_column != 0;
SELECT * FROM hive_table WHERE hive_int_column != '';

Both of the queries above will return no results regardless, simply because the types don’t match. The correct query should be:

SELECT * FROM hive_table WHERE hive_string_column != '' OR hive_string_column != '0';
SELECT * FROM hive_table WHERE hive_int_column != 0;

It is not an easy pickup initially, but now you know!