Hadoop World: Rethinking the Data Warehouse with Hadoop and Hive
cloudera.com
cloudera.com
1) Hive shines when your dataset is truly massive (ie > 100mb). Anything less should probably be done in MySQL, unless your backend processing is considerable.
2) Don't come into the project thinking "Oh, I can do SQL on huge datasets and get immediate results." Hive's main advantage is reducing the need to write custom MapReduce scripts--so your processing time is still the same.
3) Hive has its quirks. You have to structure joins right and your where clauses. It's not as forgiving as typical SQL.
I get a lot of mileage out of standard ANSI SQL. Given that Hive isn't quite as forgiving, can you give some specifics on things you lose in Hive that you miss the most?
HiveQL is less forgiving in the sense that sometimes it doesn't return what you'd expect. Sometimes when I run DISTINCTS and GROUPBY's, the output isn't what SQL on MySQL would give. For example, DISTINCTs on strings don't work very well. GROUPBY's lose a little functionality, and you have to read the documentation to figure out what you can do.
Other than that, queries in Hive are not as cheap as they are in a traditional DB. Hive offers great flexibility, but you still have to plan your query carefully because its going to take a while to execute on your large dataset. Even if you specify LIMIT, it doesn't enforce that until after all the MapReduce jobs have run.
BTW, I don't view Hadoop as only being useful for very large datasets. It seems reasonable to build automated Hadoop processing into a new application that has smaller data size requirements. You don't give up much in performance, and the extra development time is reasonable. Then you have lots of flexibility for scaling up. Also, if you only have sporadic needs to process large data sets, using Elastic MapReduce is very cost effective.