Spring JDBC with PivotalHD and Hawq

source: http://oracamp.com/spring-jdbc-pivotalhd-and-hawq

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.

view plainprint?

1.     
2. gpadmin=# \dt  
3.                              List of relations  
4.    Schema    |            Name             | Type  |  Owner  |   Storage     
5. -------------+-----------------------------+-------+---------+-------------  
6.  retail_demo | categories_dim_hawq         | table | gpadmin | append only  
7.  retail_demo | customer_addresses_dim_hawq | table | gpadmin | append only  
8.  retail_demo | customers_dim_hawq          | table | gpadmin | append only  
9.  retail_demo | date_dim_hawq               | table | gpadmin | append only  
10.  retail_demo | email_addresses_dim_hawq    | table | gpadmin | append only  
11.  retail_demo | order_lineitems_hawq        | table | gpadmin | append only  
12.  retail_demo | orders_hawq                 | table | gpadmin | append only  
13.  retail_demo | payment_methods_hawq        | table | gpadmin | append only  
14.  retail_demo | products_dim_hawq           | table | gpadmin | append only  
15. (9 rows)  
16.   
17. gpadmin=# select * from customers_dim_hawq limit 5;  
18.  customer_id | first_name | last_name | gender   
19. -------------+------------+-----------+--------  
20.  11371       | Delphine   | Williams  | F  
21.  5480        | Everett    | Johnson   | M  
22.  26030       | Dominique  | Davis     | M  
23.  41922       | Brice      | Martinez  | M  
24.  47265       | Iva        | Wilson    | F  
25. (5 rows)  
26.   
27. Time: 57.334 ms   

Code

Customer.java (POJO)

view plainprint?

1.     
2. package pivotal.au.hawq.beans;  
3.   
4. public class Customer {  
5.   
6.  public String customerId;  
7.  public String firstName;  
8.  public String lastName;  
9.  public String gender;  
10.    
11.  public Customer()   
12.  {  
13.  }  
14.   
15.  public Customer(String customerId, String firstName, String lastName,  
16.    String gender) {  
17.   super();  
18.   this.customerId = customerId;  
19.   this.firstName = firstName;  
20.   this.lastName = lastName;  
21.   this.gender = gender;  
22.  }  
23.   
24. ..... getters/setters etc ....    

DAO : Constants.java

view plainprint?

1.     
2. package pivotal.au.hawq.dao;  
3.   
4. public interface Constants   
5. {  
6.    public static final String SELECT_CUSTOMER = "select * from retail_demo.customers_dim_hawq where customer_id = ?";  
7.      
8.    public static final String SELECT_FIRST_FIVE_CUSTOMERS = "select * from retail_demo.customers_dim_hawq limit 5";  
9.      
10. }    

DAO : CustomerDAO.java

view plainprint?

1.     
2. package pivotal.au.hawq.dao;  
3.   
4. import java.util.List;  
5.   
6. import pivotal.au.hawq.beans.Customer;  
7.   
8. public interface CustomerDAO   
9. {  
10.    public Customer selectCustomer (String customerId);  
11.      
12.    public List firstFiveCustomers();  
13.      
14. }    

DAO : CustomerDAOImpl.java

view plainprint?

1.     
2. package pivotal.au.hawq.dao;  
3.   
4. import java.util.ArrayList;  
5. import java.util.List;  
6. import javax.sql.DataSource;  
7.   
8. import org.springframework.jdbc.core.BeanPropertyRowMapper;  
9. import org.springframework.jdbc.core.JdbcTemplate;  
10. import pivotal.au.hawq.beans.Customer;  
11.   
12. public class CustomerDAOImpl implements CustomerDAO   
13. {  
14.  private JdbcTemplate jdbcTemplate;  
15.    
16.  public void setDataSource(DataSource dataSource)  
17.  {  
18.      this.jdbcTemplate = new JdbcTemplate(dataSource);  
19.  }  
20.   
21.  public Customer selectCustomer(String customerId)   
22.  {  
23.      return (Customer) jdbcTemplate.queryForObject  
24.             (Constants.SELECT_CUSTOMER,   
25.               new Object[] { customerId },   
26.               new BeanPropertyRowMapper( Customer.class));  
27.  }  
28.    
29.  public List firstFiveCustomers()   
30.  {  
31.   List customers = new ArrayList();  
32.     
33.   customers = jdbcTemplate.query(Constants.SELECT_FIRST_FIVE_CUSTOMERS,   
34.                            new BeanPropertyRowMapper( Customer.class));   
35.     
36.   return customers;  
37.     
38.  }  
39.   
40. }    

application-context.xml

view plainprint?

1.     
2. xml version="1.0" encoding="UTF-8"?>  
3. <beans xmlns="http://www.springframework.org/schema/beans"  
4.  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
5.  xmlns:jdbc="http://www.springframework.org/schema/jdbc"  
6.  xmlns:context="http://www.springframework.org/schema/context"  
7.  xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd  
8.   http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd  
9.   http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd">  
10.   
11.  <context:property-placeholder location="classpath:/jdbc.properties"/>  
12.    
13.  <bean id="pivotalHDDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">  
14.   <property name="driverClassName" value="${jdbc.driverClassName}" />  
15.   <property name="url" value="${jdbc.url}" />  
16.   <property name="username" value="${jdbc.username}" />  
17.   <property name="password" value="${jdbc.password}" />  
18.  </bean>  
19.    
20.  <bean id="customerDAOImpl" class="pivotal.au.hawq.dao.CustomerDAOImpl">  
21.       <property name="dataSource" ref="pivotalHDDataSource" />  
22.    </bean>  
23. </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

view plainprint?

1.     
2. package pivotal.au.hawq.dao.test;  
3.   
4. import java.util.List;  
5. import java.util.logging.Level;  
6. import java.util.logging.Logger;  
7.   
8. import org.springframework.context.ApplicationContext;  
9. import org.springframework.context.support.ClassPathXmlApplicationContext;  
10.   
11. import pivotal.au.hawq.beans.Customer;  
12. import pivotal.au.hawq.dao.CustomerDAO;  
13.   
14. public class TestCustomerDAO   
15. {  
16.  private Logger logger = Logger.getLogger(this.getClass().getSimpleName());  
17.  private ApplicationContext context;  
18.  private static final String BEAN_NAME = "customerDAOImpl";  
19.  private CustomerDAO customerDAO;  
20.    
21.  public TestCustomerDAO()   
22.  {  
23.      context = new ClassPathXmlApplicationContext("application-context.xml");  
24.      customerDAO = (CustomerDAO) context.getBean(BEAN_NAME);    
25.      logger.log (Level.INFO, "Obtained customerDAOImpl BEAN...");  
26.  }  
27.   
28.  public void run()  
29.  {  
30.   System.out.println("Select single customer from HAWQ -> ");  
31.   Customer customer = customerDAO.selectCustomer("59047");  
32.   System.out.println(customer.toString());  
33.     
34.   System.out.println("Select five customers from HAWQ -> ");  
35.     
36.   List customers = customerDAO.firstFiveCustomers();  
37.     
38.   for (Customer cust: customers)  
39.   {  
40.    System.out.println(cust.toString());  
41.   }  
42.     
43.  }  
44.    
45.  public static void main(String[] args)   
46.  {  
47.   // TODO Auto-generated method stub  
48.   TestCustomerDAO test = new TestCustomerDAO();  
49.   test.run();  
50.  }  
51.   
52. }    

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