Hive Query Column Type Comparison

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

My new Snowflake Blog is now live. I will not be updating this blog anymore but will continue with new contents in the Snowflake world!