How does Hive compare to HBase?

I'm interested in finding out how the recently-released (http://mirror.facebook.com/facebook/hive/hadoop-0.17/) Hive compares to HBase in terms of performance. The SQL-like interface used by Hive is very much preferable to the HBase API we have implemented.

Solution 1:

It's hard to find much about Hive, but I found this snippet on the Hive site that leans heavily in favor of HBase (bold added):

Hive is based on Hadoop which is a batch processing system. Accordingly, this system does not and cannot promise low latencies on queries. The paradigm here is strictly of submitting jobs and being notified when the jobs are completed as opposed to real time queries. As a result it should not be compared with systems like Oracle where analysis is done on a significantly smaller amount of data but the analysis proceeds much more iteratively with the response times between iterations being less than a few minutes. For Hive queries response times for even the smallest jobs can be of the order of 5-10 minutes and for larger jobs this may even run into hours.

Since HBase and HyperTable are all about performance (being modeled on Google's BigTable), they sound like they would certainly be much faster than Hive, at the cost of functionality and a higher learning curve (e.g., they don't have joins or the SQL-like syntax).

Solution 2:

From one perspective, Hive consists of five main components: a SQL-like grammar and parser, a query planner, a query execution engine, a metadata repository, and a columnar storage layout. Its primary focus is data warehouse-style analytical workloads, so low latency retrieval of values by key is not necessary.

HBase has its own metadata repository and columnar storage layout. It is possible to author HiveQL queries over HBase tables, allowing HBase to take advantage of Hive's grammar and parser, query planner, and query execution engine. See http://wiki.apache.org/hadoop/Hive/HBaseIntegration for more details.

Solution 3:

Hive is an analytics tool. Just like pig, it was designed for ad hoc batch processing of potentially enourmous amounts of data by leveraging map reduce. Think terrabytes. Imagine trying to do that in a relational database...

HBase is a column based key value store based on BigTable. You can't do queries per se, though you can run map reduce jobs over HBase. It's primary use case is fetching rows by key, or scanning ranges of rows. A major feature is being able to have data locality when scanning across ranges of row keys for a 'family' of columns.

Solution 4:

To my humble knowledge, Hive is more comparable to Pig. Hive is SQL-like and Pig is script based. Hive seems to be more complicated with query optimization and execution engines as well as requires end user needs to specify schema parameters(partition etc). Both are intend to process text files, or sequenceFiles.

HBase is for key value data store and retrieve...you can scan or filter on those key value pairs(rows). You can not do queries on (key,value) rows.

Solution 5:

Hive and HBase are used for different purpose.

Hive:

Pros:

1. Apache Hive is a data warehouse infrastructure built on top of Hadoop.
2. It allows for querying data stored on HDFS for analysis via HQL, an SQL-like language, which will be converted into series of Map Reduce Jobs
3. It only runs batch processes on Hadoop.
4. it’s JDBC compliant, it also integrates with existing SQL based tools
5. Hive supports partitions
6. It supports analytical querying of data collected over a period of time

Cons:

1. It does not currently support update statements
2. It should be provided with a predefined schema to map files and directories into columns

HBase:

Pros:

1. A scalable, distributed database that supports structured data storage for large tables
2. It provides random, real time read/write access to your Big Data. HBase operations run in real-time on its database rather than MapReduce jobs
3. it supports partitions to tables, and tables are further split into column families
4. Scales horizontally with huge amount of data by using Hadoop
5. Provides key based access to data when storing or retrieving. It supports add or update rows.
6. Supports versoning of data.

Cons:

1. HBase queries are written in a custom language that needs to be learned
2. HBase isn’t fully ACID compliant
3. It can't be used with complicated access patterns (such as joins)
4. It is also not a complete substitute for HDFS when doing large batch MapReduce

Summary:

Hive can be used for analytical queries while HBase for real-time querying. Data can even be read and written from Hive to HBase and back again.