Hive JDBC Client Example 4


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.

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
  • Connect to the database by creating a Connection object with theJDBC driver.

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.

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

  • Process the result set, if necessary.

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.

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.

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:

  • 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.

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

 


Profile photo of Siva

About Siva

Senior Hadoop developer with 4 years of experience in designing and architecture solutions for the Big Data domain and has been involved with several complex engagements. Technical strengths include Hadoop, YARN, Mapreduce, Hive, Sqoop, Flume, Pig, HBase, Phoenix, Oozie, Falcon, Kafka, Storm, Spark, MySQL and Java.


Leave a comment

Your email address will not be published. Required fields are marked *

4 thoughts on “Hive JDBC Client Example

  • AJ

    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

  • Dinesh

    Good Tutorial.

    There is a solid Hive SQL Client tool where you can mange hive objects easily. HiveSQLClient tool is build on java and it is standalone(portable) and lightweight. As of now it supports only Kerberos enabled cluster but the future version will support more authentication methods. The most interesting thing about this client tool is it does not require any MIT Kerberos Client to be installed in your machine.

    Hive SQL Client https://hivesqlclient.github.io/site/


Review Comments
default gravatar

I am a plsql developer. Intrested to move into bigdata.

Neetika Singh ITA

.