Hive JDBC Client Example

In this post, we will discuss about one of common hive clients, JDBC client for both HiveServer1 (Thrift Server) and HiveServer2. Use of HiveServer2 is recommended as HiveServer1 has several concurrency issues and lacks some features available in HiveServer2.

Table of Contents

JDBC Data Types
Hive JDBC Client Example for HiveServer1
- - - - Below is the sample output from eclipse
Hive JDBC Client Example for HiveServer2

JDBC Data Types

The following table lists the data types implemented for HiveServer/HiveServer2 JDBC.

Hive Type	Java Type	Specification
TINYINT	byte	signed or unsigned 1-byte integer
SMALLINT	short	signed 2-byte integer
INT	int	signed 4-byte integer
BIGINT	long	signed 8-byte integer
FLOAT	double	single-precision number (approximately 7 digits)
DOUBLE	double	double-precision number (approximately 15 digits)
DECIMAL	java.math.BigDecimal	fixed-precision decimal value
BOOLEAN	boolean	a single bit (0 or 1)
STRING	String	character string or variable-length character string
TIMESTAMP	java.sql.Timestamp	date and time value
BINARY	String	binary data
Complex Types
ARRAY	String – json encoded	values of one data type
MAP	String – json encoded	key-value pairs
STRUCT	String – json encoded	structured values

Hive JDBC Client Example for HiveServer1

Below is the code snippet for sample Java Client for HiveServer1. The connection URL used here is “jdbc:hive://host:port/dbname” where host and port are determined by where the Hive server is running. Default port number is 10000 and the dbname supported is ‘default’. At this point of time, no other dbnames are supported.

And the HiveDriver class, we use should be org.apache.hadoop.hive.jdbc.HiveDriver , and this class will be present in hive-jdbc-<version>.jar file, so this jar needs to be in classpath for compiling the below code. But for execution we need to make sure that all the jars present under $HIVE_HOME/lib/*.jar files and $HADOOP_HOME/share/hadoop/mapreduce/*.jar files and $HADOOP_HOME/share/hadoop/common/*.jar files need to be added to the build path in eclipse otherwise we get classNotFound exceptions.

In the below code, we are creating one sample empdata table and loading it with three column values per line and retrieving the results back from empdata table.

Load theHiveServerJDBC driver with

Java

Class.forName("org.apache.hadoop.hive.jdbc.HiveDriver");

1
2

Class.forName("org.apache.hadoop.hive.jdbc.HiveDriver");
Connect to the database by creating a Connection object with theJDBC driver.

Java

Connection con = DriverManager.getConnection("jdbc:hive://:/dbname", "", "");

1
2

Connection con = DriverManager.getConnection("jdbc:hive://:/dbname", "", "");

Since, HiveServer 1 runs in non-secure mode, we can create the connection object without username and password parameters in the connections string as shown below.


Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/default", "", "");

1 2	Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/default", "", "");

Submit SQL to the database by creating a Statement object and using its executeQuery() method.


Statement sql = con.createStatement();
ResultSet res = sql.executeQuery("SELECT * FROM tablename");

Statement sql = con.createStatement();

ResultSet res = sql.executeQuery("SELECT * FROM tablename");

Process the result set, if necessary.


package com.test.hiveclient;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class HiveJdbcClientExample {
	/*
	 * 
	 * Before Running this example we should start thrift server. To Start
	 * Thrift server we should run below command in terminal 
	 * hive --service hiveserver &
	 */
	private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";

	public static void main(String[] args) throws SQLException {
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.exit(1);
		}

		Connection con = DriverManager.getConnection(
				"jdbc:hive://localhost:10000/default", "", "");
		Statement stmt = con.createStatement();

		String tableName = "empdata";
		stmt.executeQuery("drop table " + tableName);
		ResultSet res = stmt.executeQuery("create table " + tableName
				+ " (id int, name string, dept string)");

		// 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) + "\t" + res.getString(2) + "\t" + res.getString(2));
	    }
	    
	    // load data into table
	    // NOTE: filepath has to be local to the hive server
	    // NOTE: /home/user/input.txt is a ctrl-A separated file with three fields per line
	    String filepath = "/home/user/input.txt";
	    sql = "load data local inpath '" + filepath + "' into table " + tableName;
	    System.out.println("Running: " + sql);
	    res = stmt.executeQuery(sql);
	    
		sql = "select * from empdata where id='1'";
		res = stmt.executeQuery(sql);
		// show tables
		System.out.println("Running: " + sql);
		res = stmt.executeQuery(sql);
		while (res.next()) {
			System.out.println(res.getString(1));
			System.out.println(res.getString(2));
			System.out.println(res.getString(3));
		}
		res.close();
		stmt.close();
		con.close();
	}
}

package com.test.hiveclient;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class HiveJdbcClientExample {

* Before Running this example we should start thrift server. To Start

* Thrift server we should run below command in terminal

* hive --service hiveserver &

private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";

public static void main(String[] args) throws SQLException {

try {

Class.forName(driverName);

} catch (ClassNotFoundException e) {

e.printStackTrace();

System.exit(1);

}

Connection con = DriverManager.getConnection(

"jdbc:hive://localhost:10000/default", "", "");

Statement stmt = con.createStatement();

String tableName = "empdata";

stmt.executeQuery("drop table " + tableName);

ResultSet res = stmt.executeQuery("create table " + tableName

+ " (id int, name string, dept string)");

// 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) + "\t" + res.getString(2) + "\t" + res.getString(2));

}

// load data into table

// NOTE: filepath has to be local to the hive server

// NOTE: /home/user/input.txt is a ctrl-A separated file with three fields per line

String filepath = "/home/user/input.txt";

sql = "load data local inpath '" + filepath + "' into table " + tableName;

System.out.println("Running: " + sql);

res = stmt.executeQuery(sql);

sql = "select * from empdata where id='1'";

res = stmt.executeQuery(sql);

// show tables

System.out.println("Running: " + sql);

res = stmt.executeQuery(sql);

while (res.next()) {

System.out.println(res.getString(1));

System.out.println(res.getString(2));

System.out.println(res.getString(3));

}

res.close();

stmt.close();

con.close();

}

Below is the sample output from eclipse

For above program run from eclipse by adding all required jar files to Build Path, below is the sample input and output.


123,siva,trad
234,mani,aim
345,ganesh,NW

123,siva,trad

234,mani,aim

345,ganesh,NW


Running: show tables 'empdata'
empdata
Running: describe empdata
id                  	int                 	int                 
name                	string              	string              
dept                	string              	string              
Running: load data local inpath '/home/user/input.txt' into table empdata
Running: select * from empdata
123
siva
trad
234
mani
aim
345
ganesh
NW

Running: show tables 'empdata'

empdata

Running: describe empdata

id int int

name string string

dept string string

Running: load data local inpath '/home/user/input.txt' into table empdata

Running: select * from empdata

123

siva

trad

234

mani

aim

345

ganesh

Hive JDBC Client Example for HiveServer2

The connection URL that needs to be used for HiveServer2 “jdbc:hive2://host:port/dbname” where host and port number are determined by where the Hive server2 is running. default port for HiveServer2 is 10000.

in HiveServer2 also, we follow the same steps as provided above in HiveServer1 but below are the changes that we need to observe.

Non-Secure Mode:

URL format is like “jdbc:hive2://host:port/dbname” in un-secure mode, we can provide the username for query to run as.


Connection con = DriverManager.getConnection("jdbc:hive2://localhost:10000/default", "hiveuser", "");

1 2	Connection con = DriverManager.getConnection("jdbc:hive2://localhost:10000/default", "hiveuser", "");

Secure Mode:

In case of secured cluster with Kerberos , we need to provide additional properties in the connect String.

When connecting to HiveServer2 with Kerberos authentication, the URL format is:


jdbc:hive2://<host>:<port>/<db>;principal=<Server_Principal_of_HiveServer2><br>

1 2	jdbc:hive2://<host>:<port>/<db>;principal=<Server_Principal_of_HiveServer2><br>

Add auth=kerberos and kerberosAuthType=fromSubject JDBC URL properties in addition to having the “principal” url property.
Open the connection in Subject.doAs()

Below is the sample code snippet to creation connection object with Kerberos.


static Connection getConnection( Subject signedOnUserSubject ) throws Exception{
       Connection conn = (Connection) Subject.doAs(signedOnUserSubject, new PrivilegedExceptionAction<Object>()
           {
               public Object run()
               {
                       Connection con = null;
                       String JDBC_DB_URL = "jdbc:hive2://HiveHost:10000/default;" ||
                                              "principal=hive/localhost.localdomain@EXAMPLE.COM;" ||
                                              "auth=kerberos;kerberosAuthType=fromSubject";
                       try {
                               Class.forName(JDBC_DRIVER);
                               con =  DriverManager.getConnection(JDBC_DB_URL);
                       } catch (SQLException e) {
                               e.printStackTrace();
                       } catch (ClassNotFoundException e) {
                               e.printStackTrace();
                       }
                       return con;
               }
           });
       return conn;
}

static Connection getConnection( Subject signedOnUserSubject ) throws Exception{

Connection conn = (Connection) Subject.doAs(signedOnUserSubject, new PrivilegedExceptionAction<Object>()

{

public Object run()

{

Connection con = null;

String JDBC_DB_URL = "jdbc:hive2://HiveHost:10000/default;" ||

"principal=hive/localhost.localdomain@EXAMPLE.COM;" ||

"auth=kerberos;kerberosAuthType=fromSubject";

try {

Class.forName(JDBC_DRIVER);

con = DriverManager.getConnection(JDBC_DB_URL);

} catch (SQLException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

return con;

}

});

return conn;

}

Below is the sample code snippet to creation connection object with Kerberos.


package com.test.hiveclient;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class HiveJdbcClientExample {
	/*
	 * 
	 * Before Running this example we should start thrift server. To Start
	 * Thrift server we should run below command in terminal 
	 * hive --service hiveserver &
	 */
	private static String driverName = "org.apache.hive.jdbc.HiveDriver";

	public static void main(String[] args) throws SQLException {
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.exit(1);
		}

		Connection con = DriverManager.getConnection(
				"jdbc:hive2://localhost:10000/default", "hiveuser", "");
		Statement stmt = con.createStatement();

		String tableName = "empdata";
		stmt.executeQuery("drop table " + tableName);
		ResultSet res = stmt.executeQuery("create table " + tableName
				+ " (id int, name string, dept string)");

		// 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) + "\t" + res.getString(2) + "\t" + res.getString(2));
	    }
	    
	    // load data into table
	    // NOTE: filepath has to be local to the hive server
	    // NOTE: /home/user/input.txt is a ctrl-A separated file with three fields per line
	    String filepath = "/home/user/input.txt";
	    sql = "load data local inpath '" + filepath + "' into table " + tableName;
	    System.out.println("Running: " + sql);
	    res = stmt.executeQuery(sql);
	    
		sql = "select * from empdata where id='1'";
		res = stmt.executeQuery(sql);
		// show tables
		System.out.println("Running: " + sql);
		res = stmt.executeQuery(sql);
		while (res.next()) {
			System.out.println(res.getString(1));
			System.out.println(res.getString(2));
			System.out.println(res.getString(3));
		}
		res.close();
		stmt.close();
		con.close();
	}
}

package com.test.hiveclient;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class HiveJdbcClientExample {

* Before Running this example we should start thrift server. To Start

* Thrift server we should run below command in terminal

* hive --service hiveserver &

private static String driverName = "org.apache.hive.jdbc.HiveDriver";

public static void main(String[] args) throws SQLException {

try {

Class.forName(driverName);

} catch (ClassNotFoundException e) {

e.printStackTrace();

System.exit(1);

}

Connection con = DriverManager.getConnection(

"jdbc:hive2://localhost:10000/default", "hiveuser", "");

Statement stmt = con.createStatement();

String tableName = "empdata";

stmt.executeQuery("drop table " + tableName);

ResultSet res = stmt.executeQuery("create table " + tableName

+ " (id int, name string, dept string)");

// 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) + "\t" + res.getString(2) + "\t" + res.getString(2));

}

// load data into table

// NOTE: filepath has to be local to the hive server

// NOTE: /home/user/input.txt is a ctrl-A separated file with three fields per line

String filepath = "/home/user/input.txt";

sql = "load data local inpath '" + filepath + "' into table " + tableName;

System.out.println("Running: " + sql);

res = stmt.executeQuery(sql);

sql = "select * from empdata where id='1'";

res = stmt.executeQuery(sql);

// show tables

System.out.println("Running: " + sql);

res = stmt.executeQuery(sql);

while (res.next()) {

System.out.println(res.getString(1));

System.out.println(res.getString(2));

System.out.println(res.getString(3));

}

res.close();

stmt.close();

con.close();

}

4 thoughts on “Hive JDBC Client Example”

Reply ↓

vinu December 1, 2015 at 9:53 pm

Hi Siva

thanks for tutorial

can you please let me know how we can retrieve a complex types via jdbc ? Is there any guide to this ?

thanks

Vinu
- Reply ↓
  
  Siva Post author December 4, 2015 at 2:35 pm
  
  yes you can
Reply ↓

AJ March 13, 2016 at 1:46 pm

I am trying to connect hive using java code. I have started the hive sever2.
But after running the code given by you its showing the following error:

Exception in thread “main” java.sql.SQLException: The query did not generate a result set!
at org.apache.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:393)
at hivejdbcclient.HiveJdbcClient.main(HiveJdbcClient.java:42)
/home/aj/.cache/netbeans/8.1/executor-snippets/run.xml:53: Java returned: 1
Pingback: JDBC HIVE – Formation Big Data

Hadoop Online Tutorials

Hive JDBC Client Example 4

JDBC Data Types

Hive JDBC Client Example for HiveServer1

Below is the sample output from eclipse

Hive JDBC Client Example for HiveServer2

Non-Secure Mode:

Secure Mode:

Leave a Reply to Siva Cancel reply

4 thoughts on “Hive JDBC Client Example”

JDBC Data Types

Hive JDBC Client Example for HiveServer1

Below is the sample output from eclipse

Hive JDBC Client Example for HiveServer2

Non-Secure Mode:

Secure Mode:

Share this:

Leave a Reply to Siva Cancel reply

4 thoughts on “Hive JDBC Client Example”