https://community.cloudera.com/t5/Support-Questions/Data-Type-Validation-in-Hive/m-p/143253#M105842 <P>My 2 cents to complement a bit Marco Gaido's answer.</P><P>Doing data validation entirely in Hive is not something uncommon. Basically, some companies use Hive as an ETL solution the same way they used to do it before with traditional databases:</P><P>1) loading the data in a first table ("staging") without doing any validation. The easiest way for that in your case would be to have a (Text file format) table where all the columns are defined as "string", so that you don't get any NULL values when doing a select</P><P>2) create a second table (ideally, ORC format) with the types correctly enforced (int, date, string...)</P><P>3) have a HQL select to fill the second table from the first table. Some basic transformations might be done to have the format that adapt to the corresponding types. In case that some rows don't pass the validation, that field would be marked as NULL or another "ERROR" token. In that query, you might have to write some complex structures/conditions using regex, UDFs etc.</P><P>4) Have another query to just extract all the valid rows from the second table and insert it into a third table (would be the production table). All the lines with NULL or ERROR would go into a fourth table (reporting table).</P><P>Note: there might be some ways to merge the steps 3 &amp; 4 in order to reduce the number of queries, doing some "Hive multi inserts".</P> Thu, 12 May 2016 14:09:59 GMT sluangsay 2016-05-12T14:09:59Z