HBase Query Engine-phoenix

1. Introduction

Apache Phoenix, an open source project by Saleforce.com, was donated to the Apache Foundation. It is equivalent to a Java middleware that provides a JDBC connection to manipulate hbase data tables, and Phoenix makes HBase support access through JDBC and transforms your SQL queries into HBase scans and corresponding actions.

Address: http://phoenix.apache.org/

Compatibility:

• Phoenix 2.x-hbase 0.94.x
• Phoenix 3.x-hbase 0.94.x
• Phoenix 4.x-hbase 0.98.1+

2. Installation

hadoop2.5.1

HBase0.98.6.1

phoenix-4.4.0-hbase-0.98/

• download and expand our installation tar< /span>
• restart the region servers
• add the Phoenix client jar to the classpath of your HBase client
• download and setup squirrel  As your SQL client so can issue adhoc SQL against your HBase cluster

2.1 Downloads

Official website: http://www.apache.org/dyn/closer.cgi/phoenix/

2.2 Decompression

Upload to the main node under the specified directory to extract:

TAR–ZXVF phoenix-4.4.0-hbase-0.98-bin.tar.gz

2.3 Copy Files

Copy the Phoenix-4.4.0-hbase-0.98-server.jar to the Lib directory of the HBase cluster

Restart the HBase cluster for it to take effect

2.4 Verification

Cd/usr/local/phoenix-4.4.0-hbase-0.98-bin/bin

./sqlline.py 192.168.0.177

Success

3. Creating Tables and views

Phoenix Operation HBase, we have two ways to create a table and create a view.

If you create a table, you can insert, query, and delete HBase by reading and writing.

View, is read-only, generally only can query operation

Although it seems that the functionality of the table is more powerful than the view. But like a relational database such as MySQL, deleting a table operation deletes the table. However, deleting a view operation does not affect the structure of the original table.

Because using Phoenix, the associated mappings are automatically established with HBase when the table is created. When you use Phoenix to delete the relationship between hbase, the table in HBase is deleted as well.

So if you just use the query function, you just need to build the view is OK.

Create a table

CreateHtablename (--this sentence can be directly written, so that the Rowkey in HBase into Phoenix in the primary key, column name is called PK.     --the Rowkey automatically corresponds to the primary key. PkVARCHAR Primary Key NULL,    --Place the field named Col1 under columnfamily named CF, which is written here. "CF". " Col1 "VARCHAR NULL,    --columnfamily named CF, Field named Col2 field, write here ... Here, and so on ."CF". " Col2 "VARCHAR NULL, "Cf2". " Col3 "VARCHAR NULL, "Cf2". " Col4 "VARCHAR NULL)

Create a view of a table that already exists in HBase

 create  view  "Login_log" (PK varchar  primary  key , "login". " usr "varchar ," login "." timestamp  "varchar ," login "." IP "varchar ," login "." Province "varchar ," login "." City "varchar ," login "." District "varchar ," login "." Success "varchar ," login "." Type "varchar ) Default_column_family=  Span style= "color: #ff0000;" > '  

Note:Phoenix is case sensitive

The "Login_log" is enclosed in double quotes because the table name in HBase is Login_log, lowercase, and double quotation marks make the created view lowercase, corresponding to the HBase table.

So without double quotes, the view created is Login_log

The same is true for fields.

4. SQuirreL

First step : Download and install squirrel SQL client:http://www.squirrelsql.org/

Step two : Copy the Phoenix-{version}-client.jar and Phoenix-core-{version}.jar to the Lib folder under the Squirrel installation directory, These two jars can be found in Phoenix's installation directory.

Step three : Open squirrel, click Drivers, create a new driver:

Name: You can customize one of the names

Example URL: This is to allow you to create a new connection as a reminder.

Java Class Path: This needs to select the previously uploaded jar

Class Name:org.apache.phoenix.jdbc.PhoenixDriver

Fourth Step : Create aliases (that is, create an hbase connection):

Name: Can be customized

Driver: Select the previously configured Phoenix Driver

URL: Here The initialization shows the previously configured example URL, you can make specific changes here, 192.168.0.177 modified to ZK address

After the configuration, click Test, enter the server user name, password, test connection, after successful connection to the following page:

To query a view that has been created:

There is only one index, in the query through non-Rowkey query speed will be slow, 300W data will be around 10s, the current Phoenix has supported a two-level index, query through a two-level index to achieve a millisecond response, the next chapter describes the creation of the Phoenix Two index

HBase Query Engine-phoenix