simple HIVE JDBC POC

app.java

package com.target.cassandra.hadoop;

import java.sql.Array;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;

import org.apache.hadoop.hive.jdbc.HiveConnection;




/**
 * Hello world!
 *
 */
public class App 
{
    public static void main( String[] args ) throws SQLException 
    {

        
        HiveConnection connection = new HiveConnection("jdbc:hive://localhost:10000/default", null);
//        connection.prepareStatement("select * from retailProduct");
		Statement stmt = connection.createStatement();
		
		ResultSet res;
//		String tableName = "retailProduct ";
		String tableName = "departmentInclusion ";

		setKeyspaceForSession(stmt, "davros");
		
//		dropAndCreateTable(tableName, stmt);
//		showDatabases(stmt);
		listTables(stmt);
		describeTable(stmt, tableName);
//		countRecordsForTable(stmt, tableName);
//		showRecordsForTableWithDpci(stmt,tableName);
		showRecordsForTableWithDepartment(stmt);
//		showRecordsForTableWithDpciLoc(stmt, tableName);
    }
    private static void setKeyspaceForSession(Statement stmt, String keyspace)
			throws SQLException {
		ResultSet res;
		// regular hive query
		String countsql = "Use " + keyspace;
		System.out.println("Running: " + countsql);
		res = stmt.executeQuery(countsql);
		while (res.next()){
			System.out.println(res.getString(1));
		}
	
    	
    }

	private static void countRecordsForTable(Statement stmt, String tableName)
			throws SQLException {
		ResultSet res;
		// regular hive query
		String countsql = "select count(\"isHold\") from " + tableName;
		System.out.println("Running: " + countsql);
		res = stmt.executeQuery(countsql);
		while (res.next()){
			System.out.println(res.getString(1));
		}
	}
	
	private static void showRecordsForTableWithDpciLoc(Statement stmt, String tableName)
			throws SQLException {
		ResultSet res;
		// regular hive query
		String countsql = "select departmentId, classId, itemId, storeId  from " + tableName;
		System.out.println("Running: " + countsql);
		res = stmt.executeQuery(countsql);

		while (res.next()){
			System.out.println(res.getInt("departmentid") + " " + res.getInt("classid") + " " + res.getInt("itemid") + " " + res.getInt("storeid"));
//			System.out.println(res.getInt(1) + " " + res.getInt(2) + " " + res.getInt(3) + " " + res.getInt(4));
//			System.out.println(res.getInt("departmentid"));


		}
	}
	
	private static void showRecordsForTableWithDpci(Statement stmt, String tableName)
			throws SQLException {
		ResultSet res;
		// regular hive query
		String countsql = "select departmentId, classId, itemId, isHold, isHoldMarkets, isShip, isshiptoguestmarkets, isRush, isRushMarkets  from " + tableName;
		System.out.println("Running: " + countsql);
		res = stmt.executeQuery(countsql);

//		while (res.next()){
		while (res.next()){
			printMarkets(res);

//			Array array = res.getArray("isholdmarkets");
//			res.getObject("isholdmarkets");
//			System.out.println(res.getInt("departmentid") + " " + res.getInt("classid") + " " + res.getInt("itemid") + " " + res.getBoolean("ishold") + " "  + res.getBoolean("isship") + " " + res.getBoolean("isrush") );
//			System.out.println(res.getInt(1) + " " + res.getInt(2) + " " + res.getInt(3) + " " + res.getInt(4));
//			System.out.println(res.getInt("departmentid"));


		}
	}
	
	
	private static void showRecordsForTableWithDepartment(Statement stmt)
			throws SQLException {
		ResultSet res;
		// regular hive query
		String countsql = "select departmentId, isHold, isHoldMarkets, isShip, isshipmarkets, isRush, isRushMarkets  from departmentInclusion";
		System.out.println("Running: " + countsql);
		res = stmt.executeQuery(countsql);

//		while (res.next()){
		while (res.next()){
			printMarkets(res);




		}
	}
	
	private static void printMarketsObjectTypes(ResultSet res) throws SQLException {
		Object object = res.getObject("isholdmarkets");
		if (object!=null){
			Object isholdmarkets = res.getObject("isholdmarkets");
			Object isrushmarkets = res.getObject("isrushmarkets");
			Object isshipmarkets = res.getObject("isshipmarkets");

			String isholdmarketsString = "";
			String isshipmarketsString = "";
			String isrushmarketsString = "";
			

			if (isholdmarkets!=null){
				isholdmarketsString = isholdmarkets.getClass().toString();
			}
			
			if (isshipmarkets!=null){
				isshipmarketsString = isshipmarkets.getClass().toString();
			}
			
			if (isrushmarkets!=null){
				isrushmarketsString = isrushmarkets.getClass().toString();
			}


			System.out.println(isholdmarketsString + " : " + isshipmarketsString + " : " + isrushmarketsString);


		}
	}
	
	private static void printMarkets(ResultSet res) throws SQLException {
		Object object = res.getObject("isholdmarkets");
		if (object!=null){
			Object isholdmarkets = res.getObject("isholdmarkets");
			Object isrushmarkets = res.getObject("isrushmarkets");
			Object isshipmarkets = res.getObject("isshipmarkets");

			String isholdmarketsString = "";
			String isshipmarketsString = "";
			String isrushmarketsString = "";
			

			if (isholdmarkets!=null){
				isholdmarketsString = (String)isholdmarkets;
			}
			
			if (isshipmarkets!=null){
				isshipmarketsString = isshipmarkets.toString();
			}
			
			if (isrushmarkets!=null){
				isrushmarketsString = isrushmarkets.toString();
			}


			System.out.println(isholdmarketsString + " : " + isshipmarketsString + " : " + isrushmarketsString);


		}
	}

	private static void showDatabases(Statement stmt) throws SQLException {
		ResultSet res;
		String sql = "show databases";
		System.out.println("Running: " + sql);
		res = stmt.executeQuery(sql);
		while (res.next()) {
			System.out.println(res.getString(1));
		}
	}
	
	private static void listTables(Statement stmt) throws SQLException {
		ResultSet res;
		String sql = "show tables";
		System.out.println("Running: " + sql);
		res = stmt.executeQuery(sql);
		while (res.next()) {
			System.out.println(res.getString(1));
		}
	}

	private static void describeTable(Statement stmt, String tableName)
			throws SQLException {
		ResultSet res;
		String sql;
		// describe table
		sql = "describe " + tableName;
		System.out.println("Running: " + sql);	
		res = stmt.executeQuery(sql);
		while (res.next()) {
			System.out.println(res.getString(1) + "\t" + res.getString(2));
		}
	}

	private static void dropAndCreateTable(String tableName, Statement stmt)
			throws SQLException {
		stmt.executeQuery("drop table " + tableName);
		ResultSet res = stmt.executeQuery("create table " + tableName + " (key int, value string)");
	}
}