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!