Hacker News Reader

Comment by physcab | Hacker News Reader

I've been using Hive for the past month or so. It has significantly reduced my development time in doing analytics. If you're interested in this tool, let me give some advice:

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.

View on HN

I'm assuming you meant 100GB? not MB?

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?

No I meant 100MB. It really depends on how strained your databases are. For example, we have about 6 people constantly running jobs on MySQL and running it into the ground. So if I ever wanted to do some data manipulation I have to pretty much wait until 11pm. But yes, if your situation isn't as bad, you can do a lot with MySQL.

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.

If you have bucketed tables, you may find the TABLESAMPLE operator useful to iteratively construct complex queries and get query results back sooner: http://wiki.apache.org/hadoop/Hive/LanguageManual/Sampling. Agreed that Hive, and databases in general, could be better in this regard.

I've been using a combination of hive and cascading and it's worked really well so far. Cascading for the stuff I can't do in hive.

Reply on news.ycombinator.com