Cloudera Community

Community Articles

Find and share helpful community-sourced technical articles.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Advanced Search

Creating a Spring Boot Java 8 Microservice To Read Apache Phoenix Data

Labels (1)
Labels:
avatar
author-rank TimothySpann author-rank
Master Guru

Created on ‎09-14-2016 11:54 PM - edited ‎08-17-2019 10:03 AM

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

17,866 Views
Comments

Re: Creating a Spring Boot Java 8 Microservice To Read Apache Phoenix Data

avatar
author-rank jordan_co_in
Rising Star

Created on ‎03-27-2018 06:06 PM

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

Announcements
What's New @ Cloudera
Cloudera Data Engineering 1.23: Access Spark from Your Favor...
What's New @ Cloudera
HBase REST server scaling support is Generally Available
What's New @ Cloudera
New CLI option in the update-database command
What's New @ Cloudera
New Action menu item in the Cloudera Operational Database UI
What's New @ Cloudera
Get the list of supported instance types in the Cloudera Ope...
View More Announcements
Version history
Last update:
‎08-17-2019 10:03 AM
Updated by:
Master Guru Master Guru
Contributors