Hive JDBC Client Example 5


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.