Greenplum external table accessing GemFire region

To complete the Greenplum external table blogs , the last one I was created is an external table querying GemFire region data as shown below.

Previous Blogs

http://theblasfrompas.blogspot.com.au/2013/09/greenplum-command-based-web-external.html

http://theblasfrompas.blogspot.com.au/2013/09/using-oracles-sqlplus-with-greenplum.html

External Table Querying GemFire region

run.sh

cd /Users/gpadmin/demos/gemfire-externaltable

source env_setup.sh

java -cp $CLASSPATH vmware.au.se.gf7.query.QueryGemFireEmps

exttab.sql

  
drop external table emps_from_gemfire;

CREATE EXTERNAL WEB TABLE emps_from_gemfire
(empno int, name character varying(20), job character varying(20), deptno int)
EXECUTE '/Users/gpadmin/demos/gemfire-externaltable/run.sh' on host
FORMAT 'TEXT' (DELIMITER ',');

Output

  
gpadmin=# select * from emps_from_gemfire;
 empno |   name   |    job    | deptno 
-------+----------+-----------+--------
  7369 | SMITH    | CLERK     |     20
  7380 | BLACK    | CLERK     |     40
  7377 | ADAM     | CLERK     |     20
  7371 | APICELLA | SALESMAN  |     10
  7374 | LUCAS    | SALESMAN  |     10
  7381 | BROWN    | SALESMAN  |     40
  7373 | SIENA    | CLERK     |     40
  7376 | ADRIAN   | CLERK     |     20
  7370 | APPLES   | MANAGER   |     10
  7375 | ROB      | CLERK     |     30
  7379 | FRANK    | CLERK     |     10
  7372 | LUCIA    | PRESIDENT |     30
  7378 | SALLY    | MANAGER   |     20
(13 rows)

Time: 1078.169 ms

Java Code

  
package vmware.au.se.gf7.query;

import java.util.Collection;
import java.util.Iterator;

import vmware.au.se.gf7.deptemp.beans.Employee;

import com.gemstone.gemfire.cache.client.ClientCache;
import com.gemstone.gemfire.cache.client.ClientCacheFactory;
import com.gemstone.gemfire.cache.query.FunctionDomainException;
import com.gemstone.gemfire.cache.query.NameResolutionException;
import com.gemstone.gemfire.cache.query.Query;
import com.gemstone.gemfire.cache.query.QueryInvocationTargetException;
import com.gemstone.gemfire.cache.query.QueryService;
import com.gemstone.gemfire.cache.query.SelectResults;
import com.gemstone.gemfire.cache.query.TypeMismatchException;

public class QueryGemFireEmps 
{

 public static final String REGION_NAME = "employees";
 public ClientCache cache = null;
 
 public QueryGemFireEmps() 
 {
    cache = new ClientCacheFactory()
          .set("name", "GreenPlumGemFireClient")
          .set("cache-xml-file", "client.xml")
          .set("log-level", "error")
          .create(); 
 }

 public void run () throws FunctionDomainException, TypeMismatchException, NameResolutionException, QueryInvocationTargetException
 { 
  QueryService queryService = cache.getQueryService();
  Query query = queryService.newQuery("SELECT * FROM /" + REGION_NAME);
  
  Object result = query.execute();
  Collection<?> collection = ((SelectResults<?>)result).asList();
  Iterator<?> iter = collection.iterator();
  
  while (iter.hasNext())
  { 
            Employee emp = (Employee) iter.next();
   System.out.println(emp.toCSVFormat());
  }   
  cache.close();
 }
 
 public static void main(String[] args) throws Exception
 {
  QueryGemFireEmps emps = new QueryGemFireEmps();
  emps.run();
 }

}