Difference between HiveQL and in SQL.
- No windowing functions. IE, SUM(sales) OVER (PARTITION BY date). Its difficult to do a lot things common to warehousing, like a running sum, without having to write custom mappers/reducers or a UDF.
- No regular UNION, INTERSECT, or MINUS operators.
- Null values are treated differently than empty string, and are exported differently. IE, empty strings are exported as ‘n’ and nulls are exported as nulls. I know this isn’t unique to Hive but still annoying when exporting data from Hive into another system.
- No hierarchical/self referencing querying. I know most distributed computing solutions can’t do this, but it can be very handy.
- No Update or Delete statements.
- Haven’t been able to find any kind of cost-based explain plans. Running explain plans generally just shows the path of accessing data. Useful to some degree but it would be great if it was more advanced in that it could help the user understand which steps are causing the biggest slowdowns.