Community Articles
Find and share helpful community-sourced technical articles
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.
Labels (1)
Super Guru

If you have seen my article on Microservice on Hive, this is the Phoenix version. Phoenix seems to be a better option for REST microservice. I like having HBase as my main data store, it's very performant and highly scalable for application style queries.

7638-phoenixmicroservice.png

See: https://community.hortonworks.com/articles/53629/writing-a-spring-boot-microservices-to-access-hive....

7639-phoenix1.png

This microservices is a Spring Boot REST service on top of the data loaded by this NiFi data flow.

See: https://community.hortonworks.com/content/kbentry/54947/reading-opendata-json-and-storing-into-phoen...

Pom

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>
 <groupId>com.dataflowdeveloper</groupId>
 <artifactId>phoenix</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <packaging>jar</packaging>
 <name>phoenix</name>
 <description>Apache Hbase Phoenix Spring Boot</description>


<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.4.0.RELEASE</version>
</parent>
 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  <java.version>1.8</java.version>
 </properties>


 <dependencies>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-actuator</artifactId>
  </dependency>


   <dependency>
    <groupId>org.apache.phoenix</groupId>
    <artifactId>phoenix-core</artifactId>
<version>4.4.0-HBase-1.0</version>
               <exclusions>
                                <exclusion>
                                        <artifactId>slf4j-log4j12</artifactId>
                                        <groupId>org.slf4j</groupId>
                                </exclusion>
                                <exclusion>
                                        <artifactId>log4j</artifactId>
                                        <groupId>log4j</groupId>
                                </exclusion>
                                <exclusion>
                                        <artifactId>servlet-api</artifactId>
                                        <groupId>javax.servlet</groupId>
                                </exclusion>
    <exclusion>
     <groupId>org.mortbay.jetty</groupId>
     <artifactId>servlet-api-2.5</artifactId>
    </exclusion>
            <exclusion>
                <groupId>org.eclipse.jetty.aggregate</groupId>
                <artifactId>*</artifactId>
            </exclusion>
                        </exclusions>
</dependency>




                <dependency>
                        <groupId>org.springframework.boot</groupId>
                        <artifactId>spring-boot-starter-web</artifactId>
<version>1.4.0.RELEASE</version>
                        <exclusions>
                         <exclusion>
                                        <artifactId>servlet-api</artifactId>
                                        <groupId>javax.servlet</groupId>
                                </exclusion>
                                <exclusion>
                                        <groupId>org.springframework.boot</groupId>
                                        <artifactId>spring-boot-starter-tomcat</artifactId>


                                </exclusion>
                                <exclusion>
                                        <groupId>org.slf4j</groupId>
                                        <artifactId>slf4j-log4j12</artifactId>
                                </exclusion>
                                <exclusion>
                                        <groupId>log4j</groupId>
                                        <artifactId>log4j</artifactId>
                                </exclusion>
      <exclusion>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-logging</artifactId>
            </exclusion>
                        </exclusions>
                </dependency>
                <dependency>
                        <groupId>org.springframework.boot</groupId>
                        <artifactId>spring-boot-starter-jetty</artifactId>
                        <scope>provided</scope>
<version>1.4.0.RELEASE</version>
                        <exclusions>
                                <exclusion>
                                        <groupId>org.slf4j</groupId>
                                        <artifactId>slf4j-log4j12</artifactId>
                                </exclusion>
                                <exclusion>
                                        <groupId>log4j</groupId>
                                        <artifactId>log4j</artifactId>
                                </exclusion>
                        </exclusions>
                </dependency>
  <dependency>
   <groupId>org.apache.hadoop</groupId>
   <artifactId>hadoop-client</artifactId>
   <version>2.7.1</version>
   <type>jar</type>
   <exclusions>
    <exclusion>
     <artifactId>slf4j-log4j12</artifactId>
     <groupId>org.slf4j</groupId>
    </exclusion>
    <exclusion>
     <artifactId>log4j</artifactId>
     <groupId>log4j</groupId>
    </exclusion>
    <exclusion>
     <artifactId>servlet-api</artifactId>
     <groupId>javax.servlet</groupId>
    </exclusion>
                                <exclusion>
                                        <groupId>org.mortbay.jetty</groupId>
                                        <artifactId>servlet-api-2.5</artifactId>
                                </exclusion>
   </exclusions>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-social-twitter</artifactId>
  </dependency>


  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-test</artifactId>
   <scope>test</scope>
  </dependency>


<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.7.4</version>
    <exclusions>
        <exclusion>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-core</artifactId>
        </exclusion>
        <exclusion>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-annotations</artifactId>
        </exclusion>
    </exclusions>
</dependency>
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-core</artifactId>
    <version>2.7.4</version>
</dependency>
<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-annotations</artifactId>
    <version>2.7.4</version>
</dependency>
          <dependency>
                        <groupId>org.springframework.data</groupId>
                        <artifactId>spring-data-jdbc-core</artifactId>
<version>1.2.1.RELEASE</version>
          <exclusions>
                                <exclusion>
                                        <artifactId>slf4j-log4j12</artifactId>
                                        <groupId>org.slf4j</groupId>
                                </exclusion>
                                <exclusion>
                                        <artifactId>log4j</artifactId>
                                        <groupId>log4j</groupId>
                                </exclusion>
                                <exclusion>
                                        <artifactId>servlet-api</artifactId>
                                        <groupId>javax.servlet</groupId>
                                </exclusion>
                        </exclusions>
                </dependency>




 </dependencies>


 <build>
  <plugins>
   <plugin>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-maven-plugin</artifactId>
   </plugin>
  </plugins>
 </build>
</project>

Follow this Maven build very carefully, it is very specific version.

src/main/resources/application.properties

purl=jdbc:phoenix:serverIP:2181:/hbase-unsecure
pdriver=org.apache.phoenix.jdbc.PhoenixDriver

Java Bean to Hold Philly Crime Data

public class PhillyCrime implements Serializable {
 private String dcDist;
 private String dcKey;
 private String dispatchDate;
 private String dispatchDateTime;
 private String dispatchTime;
 private String hour;
 private String locationBlock;
 private String psa;
 private String textGeneralCode;
 private String ucrGeneral;
}

Application Class to Bootstrap Spring Boot

package com.dataflowdeveloper;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;
import org.springframework.social.twitter.api.Twitter;
import org.springframework.social.twitter.api.impl.TwitterTemplate;

@Configuration
@ComponentScan
@EnableAutoConfiguration
@SpringBootApplication
public class HBaseApplication {


 public static void main(String[] args) {
  SpringApplication.run(HBaseApplication.class, args);
 }


 @Configuration
 @Profile("default")
 static class LocalConfiguration {
  Logger logger = LoggerFactory.getLogger(LocalConfiguration.class);


     @Value("${consumerkey}")

     private String consumerKey;


     @Value("${consumersecret}")
     private String consumerSecret;
     
     @Value("${accesstoken}")
     private String accessToken;
     
     @Value("${accesstokensecret}")
     private String accessTokenSecret;
      
  @Bean
  public Twitter twitter() {
   Twitter twitter = null;
   
   try {
    twitter = new TwitterTemplate(consumerKey, consumerSecret, accessToken, accessTokenSecret);
   } catch (Exception e) {
    logger.error("Error:", e);
   }
   
   return twitter;
  }


     @Value("${purl}")
     private String databaseUri;
          
  @Bean
  public Connection connection() {
          Connection con = null;
    try {
     con = DriverManager.getConnection(databaseUri);
    } catch (SQLException e) {
     e.printStackTrace();
     logger.error("Connection fail: ", e);
    }
 
   //dataSource.setDriverClassName("org.apache.phoenix.jdbc.PhoenixDriver");
   logger.error("Initialized hbase");
   
   return con;
  }
 }
}

Phoenix Query Server does not require a Connection Pool!

DataSourceService

package com.dataflowdeveloper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

@Component("DataSourceService")
public class DataSourceService {


 Logger logger = LoggerFactory.getLogger(DataSourceService.class);


 @Autowired
 public Connection connection;


 // default to empty
 public PhillyCrime defaultValue() {
  return new PhillyCrime();
 }


 // querylimit
 @Value("${querylimit}")
 private String querylimit;


 /**
  * 
  * @param query
  *            - search msg
  * @return List of Twitter2
  */
 public List<PhillyCrime> search(String query) {


  List<PhillyCrime> crimes = new ArrayList<>();
  String sql = "";
  try {
   logger.error("Query: " + query);
   logger.error("Limit:" + querylimit);
   if ( connection == null ) { 
    logger.error("Null connection");
    return crimes;
   }
   if ( query == null || query.trim().length() <= 0 ) { 
    query = "";
    sql = "select * from phillycrime";
   }
   else {
    query = "%" + query.toUpperCase() + "%";
    sql = "select * from phillycrime WHERE upper(text_general_code) like ? LIMIT ?";
   }


   PreparedStatement ps = connection
     .prepareStatement(sql);
   if ( query.length() > 1 ) { 
    ps.setString(1, query);
    ps.setInt(2, Integer.parseInt(querylimit));
   }
   ResultSet res = ps.executeQuery();
   PhillyCrime crime = null;
   while (res.next()) {
    crime = new PhillyCrime();
    crime.setDcKey(res.getString("dc_key"));
    crime.setDcDist(res.getString("dc_dist"));
    crime.setDispatchDate(res.getString("dispatch_date"));
    crime.setDispatchDateTime(res.getString("dispatch_date_time"));
    crime.setDispatchTime(res.getString("dispatch_time"));
    crime.setHour(res.getString("hour"));
    crime.setLocationBlock(res.getString("location_block"));
    crime.setPsa(res.getString("psa"));
    crime.setTextGeneralCode(res.getString("text_general_code"));
    crime.setUcrGeneral(res.getString("ucr_general"));
    crimes.add(crime);
   }


   res.close();
   ps.close();
   connection.close();
   res = null;
   ps = null;
   connection = null;
   crime = null;


   logger.error("Size=" + crimes.size());
  } catch (Exception e) {
   e.printStackTrace();
   logger.error("Error in search", e);
  }


  return crimes;
 }
}


This class does your basic JDBC SQL queries and return.

Spring Boot Rest Controller

import java.util.List;
import javax.servlet.http.HttpServletRequest;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

@RestController
public class DataController {Logger logger = LoggerFactory.getLogger(DataController.class);
 @Autowired
 private DataSourceService dataSourceService;

    @RequestMapping("/query/{query}")
    public List<PhillyCrime> query(
      @PathVariable(value="query") String query) 
    {
     List<PhillyCrime> value = dataSourceService.search(query);
        return value;
    }

To Build

mvn package -DskipTests

To Run

java -Xms512m -Xmx2048m -Dhdp.version=2.4.0.0-169 -Djava.net.preferIPv4Stack=true -jar target/phoenix-0.0.1-SNAPSHOT.jar

To check Your Phoenix Data From the Command Line (2181 is the zookeeper port)

/usr/hdp/current/phoenix-client/bin/sqlline.py server:2181:/hbase

Phoenix Table DDL

CREATE TABLE phillycrime (dc_dist varchar,
dc_key varchar not null primary key,dispatch_date varchar,dispatch_date_time varchar,dispatch_time varchar,hour varchar,location_block varchar,psa varchar,
text_general_code varchar,ucr_general varchar);

Results

2016-09-14 20:09:25.135  INFO 11937 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2016-09-14 20:09:25.150  INFO 11937 --- [           main] o.s.c.support.DefaultLifecycleProcessor  : Starting beans in phase 0
2016-09-14 20:09:25.275  INFO 11937 --- [           main] application                              : Initializing Spring FrameworkServlet 'dispatcherServlet'
2016-09-14 20:09:25.275  INFO 11937 --- [           main] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization started
2016-09-14 20:09:25.294  INFO 11937 --- [           main] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization completed in 19 ms
2016-09-14 20:09:25.322  INFO 11937 --- [           main] org.mortbay.log                          : Logging to Logger[org.mortbay.log] via org.mortbay.log.Slf4jLog
2016-09-14 20:09:25.333  INFO 11937 --- [           main] o.e.jetty.server.AbstractConnector       : Started ServerConnector@7a55af6b{HTTP/1.1,[http/1.1]}{0.0.0.0:9999}
2016-09-14 20:09:25.335  INFO 11937 --- [           main] .s.b.c.e.j.JettyEmbeddedServletContainer : Jetty started on port(s) 9999 (http/1.1)
2016-09-14 20:09:25.339  INFO 11937 --- [           main] com.dataflowdeveloper.HBaseApplication   : Started HBaseApplication in 13.783 seconds (JVM running for 14.405)
2016-09-14 20:09:37.961 ERROR 11937 --- [tp1282287470-17] com.dataflowdeveloper.DataSourceService  : Query: Theft
2016-09-14 20:09:37.961 ERROR 11937 --- [tp1282287470-17] com.dataflowdeveloper.DataSourceService  : Limit:250
2016-09-14 20:09:39.050 ERROR 11937 --- [tp1282287470-17] com.dataflowdeveloper.DataSourceService  : Size=250
2016-09-14 20:09:39.050 ERROR 11937 --- [tp1282287470-17] com.dataflowdeveloper.DataController     : Query:Theft,IP:127.0.0.1 Browser:Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36

Connection Handshake for App

2016-09-14 20:09:18.042  INFO 11937 --- [           main] o.a.h.h.zookeeper.RecoverableZooKeeper   : Process identifier=hconnection-0x4116aac9 connecting to ZooKeeper ensemble=server:2181
2016-09-14 20:09:18.042  INFO 11937 --- [           main] org.apache.zookeeper.ZooKeeper           : Initiating client connection, connectString=server:2181 sessionTimeout=90000 watcher=hconnection-0x4116aac90x0, quorum=server:2181, baseZNode=/hbase-unsecure
2016-09-14 20:09:18.044  INFO 11937 --- [1.1.1.1:2181)] org.apache.zookeeper.ClientCnxn          : Opening socket connection to server 192.11.1.5:2181. Will not attempt to authenticate using SASL (unknown error)
2016-09-14 20:09:18.163  INFO 11937 --- [1.1.1.1:2181)] org.apache.zookeeper.ClientCnxn          : Socket connection established to 192.11.1.:2181, initiating session
2016-09-14 20:09:18.577  INFO 11937 --- [26.195.56:2181)] org.apache.zookeeper.ClientCnxn          : Session establishment complete on server :2181, sessionid = 0x157063034991d14, negotiated timeout = 40000
2016-09-14 20:09:19.953  INFO 11937 --- [           main] nectionManager$HConnectionImplementation : Closing master protocol: MasterService
2016-09-14 20:09:19.953  INFO 11937 --- [           main] nectionManager$HConnectionImplementation : Closing zookeeper sessionid=0x157063034991d14
2016-09-14 20:09:20.040  INFO 11937 --- [           main] org.apache.zookeeper.ZooKeeper           : Session: 0x157063034991d14 closed
2016-09-14 20:09:20.040  INFO 11937 --- [ain-EventThread] org.apache.zookeeper.ClientCnxn          : EventThread shut down
2016-09-14 20:09:20.470  INFO 11937 --- [           main] o.a.p.query.ConnectionQueryServicesImpl  : Found quorum: server.com:2181
2016-09-14 20:09:20.471  INFO 11937 --- [           main] o.a.h.h.zookeeper.RecoverableZooKeeper   : Process identifier=hconnection-0x36b4fe2a connecting to ZooKeeper ensemble=tspanndev10.field.hortonworks.com:2181
2016-09-14 20:09:20.471  INFO 11937 --- [           main] org.apache.zookeeper.ZooKeeper           : Initiating client connection, connectString=tspanndev10.field.hortonworks.com:2181 sessionTimeout=90000 watcher=hconnection-0x36b4fe2a0x0, quorum=tspanndev10.field.hortonworks.com:2181, baseZNode=/hbase-unsecure
2016-09-14 20:09:20.472  INFO 11937 --- [222.2.2.2.2.:2181)] org.apache.zookeeper.ClientCnxn          : Opening socket connection to server 1:2181. Will not attempt to authenticate using SASL (unknown error)
2016-09-14 20:09:20.555  INFO 11937 --- [26:2181)] org.apache.zookeeper.ClientCnxn          : Socket connection established to 172....:2181, initiating session
2016-09-14 20:09:20.641  INFO 11937 --- [22.2.2:2181)] org.apache.zookeeper.ClientCnxn          : Session establishment complete on server 2.2.2/2.2.2:2181, sessionid = 0x157063034991d15, negotiated timeout = 40000

7640-phoenix2.png

7691-phoenix3.png

Reference:

http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.5.0/bk_data-access/content/ch_using-phoenix.html

https://community.hortonworks.com/articles/19016/connect-to-phoenix-hbase-using-dbvisualizer.html

9,036 Views
Comments
Contributor

Do you have any updated version of this program with the latest libraries and also with a secure hbase?

Don't have an account?
Coming from Hortonworks? Activate your account here
Version history
Revision #:
2 of 2
Last update:
‎08-17-2019 10:03 AM
Updated by:
 
Contributors
Top Kudoed Authors