the fact that you have other data in the column (like letters) implies that you have the column stored as a string, so use a regex.
SELECT CAST(mycol as BIGINT) WHERE my mycol RLIKE '^-?[0-9.]+$' From: Mohit Durgapal [mailto:durgapalmo...@gmail.com] Sent: Wednesday, September 02, 2015 5:09 AM To: user@hive.apache.org Subject: can we add column type in where clause in a hive query? I would like to query a hive table only for those rows that have coulmn1 as integer only. Due to some data corruption, without this check I am getting a lot of junk data(mix integer & letters), I would like to get rid of that data by applying something like "where column1 is INT" kind of condition, but I couldn't find anything like that in hive. Could anyone suggest how I could do it? ====================================================================== THIS ELECTRONIC MESSAGE, INCLUDING ANY ACCOMPANYING DOCUMENTS, IS CONFIDENTIAL and may contain information that is privileged and exempt from disclosure under applicable law. If you are neither the intended recipient nor responsible for delivering the message to the intended recipient, please note that any dissemination, distribution, copying or the taking of any action in reliance upon the message is strictly prohibited. If you have received this communication in error, please notify the sender immediately. Thank you.
