phoenix--implementing standard SQL statements to HBase

Write in front one:

This paper summarizes the SQL query system based on HBase--salesforce Phoenix

Write in front two:

Environment Description:

first, what is Phoenix

From the official website:

Phoenix is a framework for SQL operations that provides hbase, and Phoenix is a SQL middle tier built on HBase. Phoenix is written entirely in Java, the code is on GitHub, and provides a client-embeddable JDBC driver. For a simple low-latency query, the performance magnitude is milliseconds, and for millions, the performance level is seconds. Phoenix is not like HBase.
Used for Map-reduce job, but to access hbase data in a standardized language.

Phoenix's most notable features:
1, embedded JDBC driver, realizes most java.sql interface, including metadata API
2.Columns can be modeled with multiple row keys or key/value cells
3.Perfect query support, multiple predicates and optimized scan keys can be used
4.DDL support: Add/Remove columns by CREATE TABLE, DROP table, and ALTER TABLE
5. versioned Mode Warehouse: When writing data, the snapshot query uses the appropriate pattern
6. DML support: Upsert VALUES for progressive insertion, Upsert SELECT for mass data transfer between the same or different tables, delete for deleting rows
7. Limited transaction support through client batch processing
8, single table-not yet connected, while the two-level index is also in development
9. Follow the ANSI SQL standard

Ii.principles of Phoenix

The basic principle of Phoenix is to convert a query that is more complex to the HBase client into a series of region Scan, combining coprocessor and custom filter in more than one region Parallel queries on the server, summarizing individual scan results. There are indications that Phoenix should not be an optimized OLAP system, more like an OLTP system for simple single-table queries, filtering, sorting, and retrieval. Phoenix can provide our current big data platform more convenient data operation capability (direct JDBC), performance is good, take care not to use multi-table query.

Third, Phoenix Installation Configuration

1. Download
Phoenix-3.0.0-incubating.tar.gz
Http://phoenix.incubator.apache.org/download.html#Installation
2, copy Phoenix-3.0.0-incubating/common/phoenix-core-3.0.0-incubating.jar to $hbase_home/lib/
3. Restart the HBase cluster

If you are accessing Phoenix via a client, you need to do the following:
Add Phoenix-3.0.0-incubating/hadoop-1/phoenix-3.0.0-incubating-client.jar to the Phoenix client's class path

Iv. visit to Phoenix

4.1, through the command line

Enter Phoenix

Cd/home/yujianxin/hbase/phoenix/phoenix-3.0.0-incubating/bin
./sqlline.py slave3:2181

The following response appears stating that the installation was successful

4.2. Through Java

Class.forName ("Org.apache.phoenix.jdbc.PhoenixDriver");  Connection String:jdbc:phoenix [: <zookeeper quorum> [: <port number>] [: <root node>]]connection Connection = Drivermanager.getconnection ("jdbc:phoenix:slave3:2181");

4.3. Access via SQL Client squirrel

: http://squirrel-sql.sourceforge.net/

Install the configuration again.

v. Use of Phoenix

Test with data from phoenix-3.0.0-incubating.tar.gz

Among them, the contents of Web_stat.sql are as follows:

After executing the above command, look at the tables in Phoenix:

Look at the tables in HBase as follows:

You can send a standard SQL statement to HBase and operate on HBase via Phoenix, the SQL middle tier built on HBase.