Hive For SQL Developers

Hive SQL is almost, but not exactly, unlike SQL.

The Hive documentation is the ultimate reference and you should add it to your Favourites if you are going to be working with Hive.

Hive will, of course, change over time. New releases come along very fast and you do really need to keep up to date. I retested all the examples below over several releases of the Hortonworks Sandbox (up to 2.5), and then stopped because I saw no progress and because I have more useful things to do. Other distributions should be similar, but don't trust me or anyone else without testing it yourself.

Here are some things that may catch you out:

• Count may give the wrong answer
• Insert is non-standard
• No integrity features
• Over-permissive naming
• Allows single and double quotes interchangeably
• Silent failure on loading data
• Set operators missing
• Date and timestamp issues
• Cannot order by an aggregate expression
• By default does not allow order by column number
• Date extract functions do not work
• The "OpenCSVSerde" changes your table definition
• Finding invisible characters
• Hive does not support the "or" logical operator in a join
• Hive booleans are too confusing to be usable
• How to convert the character encoding when loading data to Hive"

My conclusions about Hive are subject to a Protest Warning.