Spring JDBC with PivotalHD and Hawq

HAWQ enables SQL for Hadoop ensuring we can use something like Spring JDBC as shown below. In this example we use the PivotalHD VM with data from a HAWQ append only table as shown below.

  
gpadmin=# \dt
                             List of relations
   Schema    |            Name             | Type  |  Owner  |   Storage   
-------------+-----------------------------+-------+---------+-------------
 retail_demo | categories_dim_hawq         | table | gpadmin | append only
 retail_demo | customer_addresses_dim_hawq | table | gpadmin | append only
 retail_demo | customers_dim_hawq          | table | gpadmin | append only
 retail_demo | date_dim_hawq               | table | gpadmin | append only
 retail_demo | email_addresses_dim_hawq    | table | gpadmin | append only
 retail_demo | order_lineitems_hawq        | table | gpadmin | append only
 retail_demo | orders_hawq                 | table | gpadmin | append only
 retail_demo | payment_methods_hawq        | table | gpadmin | append only
 retail_demo | products_dim_hawq           | table | gpadmin | append only
(9 rows)

gpadmin=# select * from customers_dim_hawq limit 5;
 customer_id | first_name | last_name | gender 
-------------+------------+-----------+--------
 11371       | Delphine   | Williams  | F
 5480        | Everett    | Johnson   | M
 26030       | Dominique  | Davis     | M
 41922       | Brice      | Martinez  | M
 47265       | Iva        | Wilson    | F
(5 rows)

Time: 57.334 ms 

Code

Customer.java (POJO)

  
package pivotal.au.hawq.beans;

public class Customer {

 public String customerId;
 public String firstName;
 public String lastName;
 public String gender;
 
 public Customer() 
 {
 }

 public Customer(String customerId, String firstName, String lastName,
   String gender) {
  super();
  this.customerId = customerId;
  this.firstName = firstName;
  this.lastName = lastName;
  this.gender = gender;
 }

..... getters/setters etc ....  

DAO : Constants.java

  
package pivotal.au.hawq.dao;

public interface Constants 
{
   public static final String SELECT_CUSTOMER = "select * from retail_demo.customers_dim_hawq where customer_id = ?";
   
   public static final String SELECT_FIRST_FIVE_CUSTOMERS = "select * from retail_demo.customers_dim_hawq limit 5";
   
}  

DAO : CustomerDAO.java

  
package pivotal.au.hawq.dao;

import java.util.List;

import pivotal.au.hawq.beans.Customer;

public interface CustomerDAO 
{
   public Customer selectCustomer (String customerId);
   
   public List<Customer> firstFiveCustomers();
   
}  

DAO : CustomerDAOImpl.java

  
package pivotal.au.hawq.dao;

import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import pivotal.au.hawq.beans.Customer;

public class CustomerDAOImpl implements CustomerDAO 
{
 private JdbcTemplate jdbcTemplate;
 
 public void setDataSource(DataSource dataSource)
 {
     this.jdbcTemplate = new JdbcTemplate(dataSource);
 }

 public Customer selectCustomer(String customerId) 
 {
     return (Customer) jdbcTemplate.queryForObject
            (Constants.SELECT_CUSTOMER, 
              new Object[] { customerId }, 
              new BeanPropertyRowMapper<Customer>( Customer.class));
 }
 
 public List<Customer> firstFiveCustomers() 
 {
  List<Customer> customers = new ArrayList<Customer>();
  
  customers = jdbcTemplate.query(Constants.SELECT_FIRST_FIVE_CUSTOMERS, 
                           new BeanPropertyRowMapper<Customer>( Customer.class)); 
  
  return customers;
  
 }

}  

application-context.xml

  
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:jdbc="http://www.springframework.org/schema/jdbc"
 xmlns:context="http://www.springframework.org/schema/context"
 xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
  http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
  http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd">

 <context:property-placeholder location="classpath:/jdbc.properties"/>
 
 <bean id="pivotalHDDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  <property name="driverClassName" value="${jdbc.driverClassName}" />
  <property name="url" value="${jdbc.url}" />
  <property name="username" value="${jdbc.username}" />
  <property name="password" value="${jdbc.password}" />
 </bean>
 
 <bean id="customerDAOImpl" class="pivotal.au.hawq.dao.CustomerDAOImpl">
      <property name="dataSource" ref="pivotalHDDataSource" />
   </bean>
</beans>  

jdbc.properties

jdbc.driverClassName=org.postgresql.Driver
jdbc.url=jdbc:postgresql://172.16.62.142:5432/gpadmin
jdbc.username=gpadmin
jdbc.password=gpadmin

TestCustomerDAO.java

  
package pivotal.au.hawq.dao.test;

import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import pivotal.au.hawq.beans.Customer;
import pivotal.au.hawq.dao.CustomerDAO;

public class TestCustomerDAO 
{
 private Logger logger = Logger.getLogger(this.getClass().getSimpleName());
 private ApplicationContext context;
 private static final String BEAN_NAME = "customerDAOImpl";
 private CustomerDAO customerDAO;
 
 public TestCustomerDAO() 
 {
     context = new ClassPathXmlApplicationContext("application-context.xml");
     customerDAO = (CustomerDAO) context.getBean(BEAN_NAME);  
     logger.log (Level.INFO, "Obtained customerDAOImpl BEAN...");
 }

 public void run()
 {
  System.out.println("Select single customer from HAWQ -> ");
  Customer customer = customerDAO.selectCustomer("59047");
  System.out.println(customer.toString());
  
  System.out.println("Select five customers from HAWQ -> ");
  
  List<Customer> customers = customerDAO.firstFiveCustomers();
  
  for (Customer cust: customers)
  {
   System.out.println(cust.toString());
  }
  
 }
 
 public static void main(String[] args) 
 {
  // TODO Auto-generated method stub
  TestCustomerDAO test = new TestCustomerDAO();
  test.run();
 }

}  

Output

log4j:WARN No appenders could be found for logger (org.springframework.core.env.StandardEnvironment).

log4j:WARN Please initialize the log4j system properly.

Sep 16, 2013 9:59:09 PM pivotal.au.hawq.dao.test.TestCustomerDAO

INFO: Obtained customerDAOImpl BEAN...

Select single customer from HAWQ -> 

Customer [customerId=59047, firstName=Olivia, lastName=Anderson, gender=F]

Select five customers from HAWQ -> 

Customer [customerId=11371, firstName=Delphine, lastName=Williams, gender=F]

Customer [customerId=5480, firstName=Everett, lastName=Johnson, gender=M]

Customer [customerId=26030, firstName=Dominique, lastName=Davis, gender=M]

Customer [customerId=41922, firstName=Brice, lastName=Martinez, gender=M]

Customer [customerId=47265, firstName=Iva, lastName=Wilson, gender=F]

More Information

Here is the high level page describing the Pivotal HD & HAWQ technology.
http://blog.gopivotal.com/products/pivotal-hd-ga

This page dives deeper into the PHD VM with a walkthrough from data loading, map reduce and SQL queries.
http://pivotalhd.cfapps.io/getting-started/pivotalhd-vm.html

Finally, the following link is the direct download location of the VM discussed above above.
http://bitcast-a.v1.o1.sjc1.bitgravity.com/greenplum/pivotal-sw/pivotalhd_singlenodevm_101_v1.7z