Apache Phoenix

Apache Phoenix is an efficient SQL skin for Apache HBase that has created a lot of buzz. Many companies are successfully using this technology, including Salesforce.com, where Phoenix first started.

Internally, Phoenix takes your SQL query, compiles it into a series of native HBase API calls, and pushes as much work as possible onto the cluster for parallel execution. It automatically creates a metadata repository that provides typed access to data stored in HBase tables. Phoenix’s direct use of the HBase API, along with coprocessors and custom filters, results in performance on the order of milliseconds for small queries, or seconds for tens of millions of rows.

Regardless of these helpful features, Phoenix is not a drop-in RDBMS replacement. There are some limitations:

1. Phoenix doesn’t support cross-row transactions yet.
2. Itz query optimizer and join mechanisms are less sophisticated than most COTS DBMSs.
3. As secondary indexes are implemented using a separate index table, they can get out of sync with the primary table (although perhaps only for very short periods.) These indexes are therefore not fully-ACID compliant.
4. Multi-tenancy is constrained—internally, Phoenix uses a single HBase table.

Unlike Impala, however, Phoenix is intended to operate exclusively on HBase data; its design and implementation are heavily customized to leverage HBase features including coprocessors and skip scans.

Top-3 differences between Impala and Phoenix

1. The main goal of Phoenix is to provide a high-performance relational database layer over HBase for low-latency applications. Impala’s primary focus is to enable interactive exploration of large data sets by providing high-performance, low-latency SQL queries on data stored in popular Hadoop file formats. Hive is mainly concerned with providing data warehouse infrastructure, especially for long-running batch-oriented tasks.
2. Phoenix is a good choice, for example, in CRUD applications where you need the scalability of HBase along with the facility of SQL access. In contrast, Impala is a better option for strictly analytic workloads and Hive is well suited for batch-oriented tasks like ETL.
3. Phoenix is comparatively lightweight since it doesn’t need an additional server.

Next Cloudera release CDH 5.5 will be packaged with Apache Phoenix to leverage. I'm waiting !!!