I got the solution.
I have referred https://cwiki.apache.org/confluence/display/Hive/HiveClient and made some changed.
Here's catch.
1. We can use same JDBC url for connecting to Hive/Shark. you only need to change the port.
What I did?
1. I run hive on port 4544 and used below JDBC url in Java class HiveJdbc.java
Connection con = DriverManager.getConnection("jdbc:hive://localhost:4544/default", "", "");
1. I run shark on port 4588 and used below JDBC url in Java class SharkJDBC.java
Connection con = DriverManager.getConnection("jdbc:hive://localhost:4588/default", "", "");
Rest of code is same.
Here's code.
- ---------------------------------- ---------------------------------- -------------------------------------------------------------------
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
public class SharkJdbcClient {
private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
/** * @param args * @throws SQLException */
public static void main(String[] args) throws SQLException
{
try
{
Class.forName(driverName);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.exit(1);
}
Connection con = DriverManager.getConnection("jdbc:hive://localhost:4588/default", "" , "");
Statement stmt = con.createStatement();
String tableName = "bank_tab1_cached";
System.out.println("Droppring the table : " + tableName);
stmt.executeQuery("drop table " + tableName);
ResultSet res = stmt.executeQuery("create table " + tableName+ " (empid int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY " + "\",\"");
// show tables
String sql = "show tables '" + tableName + "'";
System.out.println("Running: " + sql);
res = stmt.executeQuery(sql);
if (res.next())
{ System.out.println(res.getString(1)); }
// describe table
sql = "describe " + tableName;
System.out.println("Running: " + sql);
res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(res.getString(1) + "-------" + res.getString(2)); }
// load data into table
// NOTE: filepath has to be local to the hive server
String filepath = "/home/abhi/Downloads/at_env_jar/emp_data.txt";
sql = "load data local inpath '" + filepath + "' into table " + tableName;
System.out.println("Running: " + sql);
res = stmt.executeQuery(sql);
// select * query
sql = "select * from " + tableName;
System.out.println("Running: " + sql);
res = stmt.executeQuery(sql);
while (res.next()) { System.out.println(String.valueOf(res.getInt(1)) + "\t" + res.getString(2)); }
// regular hive query
sql = "select count(1) from " + tableName;
System.out.println("Running: " + sql);
res = stmt.executeQuery(sql);
while (res.next()) {
System.out.println(res.getString(1));
}
String q1="CREATE TABLE one AS SELECT 1 AS one FROM " + tableName + " LIMIT 1";
int rows=0;
String c1="";
String c2="";
//insert into table emp_tab1 SELECT stack(3 , 1 , "row1" , 2 , "row2", 3 , "row3") AS (empid, name)FROM one;
System.out.println("Inserting records..... " );
String q2 = "insert into table " +tableName + " SELECT stack(3 , 1 ,\"row1\", 2 , \"row2\",3 , \"row3\") AS (empid, name) FROM one";
res = stmt.executeQuery(q2);
System.out.println("Successfully inserted.......... " );
}}
- ---------------------------------- ---------------------------------- -------------------------------------------------------------------
Here's at.sh script used for runnign the code.
---------------------------------- ---------------------------------- -------------------------------------------------------------------
#!/bin/bash
HADOOP_HOME="/usr/lib/hadoop"
HIVE_HOME="/home/abhi/Downloads/hive-0.9.0-bin"
HADOOP_CORE="/home/abhi/Downloads/at_env_jar/Hadoop4.1.1/hadoop-core-0.20.203.0.jar"
CLASSPATH=.:$HADOOP_HOME:$HADOOP_CORE:$HIVE_HOME:$HIVE_HOME/conf
for i in ${HIVE_HOME}/lib/*.jar ; do
CLASSPATH=$CLASSPATH:$i
done
java -cp $CLASSPATH HiveJdbcClient
---------------------------------- ---------------------------------- -------------------------------------------------------------------
Compile your Java code and run the at.sh (with execute permission).
Cheers 🙂
Abhishek
