Connect to Hive via HiveServer2 JDBC Driver

This post shows you how to connect to HiveServer2 via Hive JDBC driver in Java.

*The way to connect to HiveServer1 is very similar though the driver names are different:

The JDBC connection URL will also be different:

Add dependency

First we need to add dependency in your project pom file (if you are using Maven):

<project xmlns="http://maven.apache.org/POM/4.0.0"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">     <modelVersion>4.0.0</modelVersion>     <groupId>app.kontext.tech</groupId>     <artifactId>examples</artifactId>     <version>0.0.1-SNAPSHOT</version>     <dependencies>         <dependency>             <groupId>org.apache.hive</groupId>             <artifactId>hive-jdbc</artifactId>             <version>3.1.1</version>         </dependency>             <groupId>org.apache.hive</groupId>             <artifactId>hive-jdbc</artifactId>             <version>3.1.1</version>         </dependency>     </dependencies>
</project>

Or you need to directly reference Hive JAR file.

Create a Java class to connect to HiveServer2

Now, we can connect to HiveServer2 as you can do with any other supported databases.

The following is a sample class for your reference:

package examples;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;public class HiveJdbcExample {    /**      * HiveServer2 JDBC driver name      */     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 conn = DriverManager.getConnection("jdbc:hive2://localhost:10000", "hive", "");         Statement stmt = conn.createStatement();         // show tables         String sql = "SHOW databases";         System.out.println("Running: " + sql);         ResultSet rs = stmt.executeQuery(sql);         while (rs.next()) {             System.out.println(rs.getString(1));         }         rs.close();        String sql2 = "select * from test_db.test_table";         System.out.println("Running: " + sql2);         ResultSet rs2 = stmt.executeQuery(sql2);        ResultSetMetaData rsmd = rs2.getMetaData();         int columnsNumber = rsmd.getColumnCount();         while (rs2.next()) {             for (int i = 1; i <= columnsNumber; i++) {                 if (i > 1)                     System.out.print(",  ");                 String columnValue = rs2.getString(i);                 System.out.print(rsmd.getColumnName(i) + " " + columnValue);             }             System.out.println("");         }        rs2.close();        conn.close();     }}

For detailed information about connection URLs (as HiveServer2 service can run in different modes), please refer to the following page published by Apache:

https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-ConnectionURLs

Sample output

https://api.kontext.tech/resource/9b821f50-38e5-5185-8369-df7e7715cfbf

In HiveServer2 Web UI, we can also find the query history:

https://api.kontext.tech/resource/da3cd667-0c18-5d0c-a36b-0f67a7fb69d3

Clicking Drilldown link, you can also view the query detailed information:

https://app.kontext.tech/Images/Uploaded%20Files/DotNetEssential/Open-Live-Writer_5a759b542645_FD02_image_6.png