Getting Started with Apache Phoenix

Getting Started with Apache Phoenix Overview

HBase is suitable for storing a large number of NOSQL data that require low relational operations, and is not designed to directly use native PAI to perform conditional judgments and aggregations that are commonly used in relational databases. HBase is excellent, and some teams seek to provide a more general developer-oriented operation on HBase, Apache Phoenix.

Phoenix based HBase provides business-oriented developers with query operations for hbase in a standard SQL manner, and supports most of the features in standard SQL: Conditional operations, grouping, paging, and other advanced query syntax.

Note: Do not introduce the Phoenix architecture as a whole, just use examples to demonstrate how to operate, and how to use them in different scenarios. cluster Environment

zk:host101,host102,host103

hadoop:host102[Main],host103[Preparation],host104,host105,host106

hbase:host102[Preparation],host103[Main],host104,host105,host106

phoenix:host104[Master]

Caveats: The version number of HBase and Hadoop requires a compatible Phoneix installation

1. Download: http://phoenix.apache.org/get apache-phoenix-4.10.0-hbase-1.2-bin.tar.gz

2. Unzip to the host host104/app/path

3. Distribute the Phoenix-4.10.0-hbase-1.2-server.jar file in the installation directory under the Lib directory in the installation directory of all HBase nodes.

4. Restart the HBase cluster for operation in the Linux shell

Executed under the bin subdirectory under the Phoenix installation directory./sqlline.py host101 into the Phoenix shell environment.

1. Create a table

CREATE TABLE Itinfo (ID varchar primary key,name varchar,ageinteger);

2. List tables

!tables;

3. Inserting a piece of data

Upsert into itinfo values (' 1001 ', ' Itxiaofen ', 26);

Modify the data to insert a few more.

4. Query all data, conditional operations, sorting, grouping

SELECT * from Itinfo;

SELECT * FROM Itinfo where age>=27;

SELECT * from Itinfo order BY age Desc;

Select Age,count (1) from Itinfo group by age;

5. Login to HBase Shell to see what's changed

HBase installation directory under Execute Bin/hbase Shell

List all hbase tables execution list, confirmed Phoenix helped us do a lot of things in hbase, the creation of hbase tables, data insertion and other operations. The table with System. Start is the metadata table for Phoenix.

6. To summarize, the last API provided by Phoenix to operate the HBase table in SQL, Phoenix in the cluster parallel to the university's split SQL call HBase API to perform scanning and aggregation operations, performance please refer to the Phoenix official website Introduction With squirrel user-side Operation

A DB client program written by the SQuirreL Java program that allows you to view database content, execute SQL, and more through SQL.

1. Download

Https://jaist.dl.sourceforge.net/project/squirrel-sql/3-snapshots/snapshot-20170703_2250/ Squirrel-sql-snapshot-20170703_2250-standard.jar performing the installation

2. Copy the Phoenix installation path under Phoenix-4.10.0-hbase-1.2-client.jar to the Squirrel installation directory in the Lib directory.

3. Open the Squirrel installation path Squirrel-sql.jar start the app

4. Associated driver, stand-alone dirvers, add-on extension class path will phoenix-4.10.0-hbase-1.2-client.jar join, execute list Drivers scan driver class, OK, as follows

5. Stand-alone aliasses, add the drive name selected in the previous step configuration [blue for driver available]

The jdbc:phoenix:host101 equivalent to the URL host101 in JDBC is the ZK address, the single test is successful, the connection is confirmed.

6. View the table

7. Execute SQL

8. Summary, the client can easily use similar operations Oracle,mysql database way to treat HBase

Java Programming, borrowing force JdbcTemplate

Here, the Springboot project combines jdbctemplate to show how to use it.

1. Create the Springboot project, depending on the following

<dependencies> <!--correction version--<dependency> <groupid>com. Google.guava</groupid> <artifactId>guava</artifactId> <versio

                   N>13.0.1</version> </dependency> <!--required, and version to correspond--<dependency> <groupId>org.apache.phoenix</groupId> &LT;ARTIFACTID&GT;PHOENIX-CORE&L T;/artifactid> <version>4.10.0-HBase-1.1</version> <scope>run

                                     Time</scope> <exclusions> <exclusion> <groupId>jdk.tools</groupId> &LT;ARTIFACTID&GT;JDK.

                         Tools</artifactid> </exclusion> <exclusion>            <groupId>org.slf4j</groupId> &LT;ARTIFACTID&GT;SLF4J-LOG4J12 </artifactId> </exclusion> </exclusions> < /dependency> <!--JDBC--<dependency> &LT;GROUPID&GT;ORG.SPRINGFR Amework.boot</groupid> <artifactId>spring-boot-starter-jdbc</artifactId> & Lt;/dependency> <dependency> <groupid>org.springframework.boot</groupid&gt

                   ;

                   <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactid>spring-boot-co Nfiguration-processor</artifactid> <optional>true</optional> </dependen Cy> </dependencies> 

2. Defining Entity Beans

Publicclass Itinfo {

         private String ID;

         private String name;

         Private String age;

 

         Public String getId () {

                   return ID;

         }

 

         public void SetId (String id) {

                   this.id = ID;

         }

 

         Public String GetName () {

                   return name;

         }

 

         public void SetName (String name) {

                   this.name = name;

         }

 

         Public String Getage () {

                   return age;

         }

 

         public void Setage (String age) {

                   this.age = age;

         }

 

         @Override public

         String toString () {

                   return ' itinfo[id= ' + ID + ", name=" + name + ", age=" + Age + "]";

         }

}

3. APPLICATION.YML, configuring Phoenix Data source Information

Spring:

  DataSource:

    url:jdbc:phoenix:host101,host102,host103:2181/hbase

    Driver-class-name:o Rg.apache.phoenix.jdbc.PhoenixDriver

    Username:

    Password:

4. Main program, query all Iiinfo table objects and encapsulate as list<itinfo> structure

@SpringBootApplication

publicclass phoneixapplication {public

         static void Main (string[] args) throws Exception {

                   Configurableapplicationcontextcontext = Springapplication.run (Phoneixapplication.class, args);

                   JdbcTemplate jdbc =context.getbean (jdbctemplate.class);

                   list<itinfo> Itinfos =jdbc.query ("SELECT * from Itinfo", newbeanpropertyrowmapper<itinfo> (ItInfo.class)) ;

                   System.out.println (Itinfos);

         }

 

         Data source

         @Bean

         @ConfigurationProperties (prefix = "spring.datasource") Public

         DataSource DataSource () {

                   returndatasourcebuilder.create (). Type (Drivermanagerdatasource.class). build ();

         }

 

         JdbcTemplate

         @Bean public

         jdbctemplatejdbctemplate (DataSource DataSource) {

                   return Newjdbctemplate (DataSource);

         }

}

The results of the operation are as follows:

5. Summing up, using the Phoenix operation HBase can use the traditional JDBC approach to data in HBase, many of the excellent frameworks that support standard JDBC are still suitable for Phoenix