Connect Hive through Java JDBC

Question

There is a question here connect from java to Hive but mine is different

My hive running on machine1 and I need to pass some queries using Java server running at machine2. As I understand Hive has a JDBC interface for the purpose of receiving remote queries. I took the code from here - HiveServer2 Clients

I installed the dependencies written in the article:

hive-jdbc*.jar
hive-service*.jar
libfb303-0.9.0.jar
libthrift-0.9.0.jar
log4j-1.2.16.jar
slf4j-api-1.6.1.jar
slf4j-log4j12-1.6.1.jar
commons-logging-1.0.4.jar

However I got java.lang.NoClassDefFoundError error at compile time Full Error:

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/hadoop/conf/Configuration
    at org.apache.hive.jdbc.HiveConnection.createBinaryTransport(HiveConnection.java:393)
    at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:187)
    at org.apache.hive.jdbc.HiveConnection.<init>(HiveConnection.java:163)
    at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:105)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:215)
    at com.bidstalk.tools.RawLogsQuerySystem.HiveJdbcClient.main(HiveJdbcClient.java:25)

Another question at StackOverflow recommended to add Hadoop API dependencies in Maven - Hive Error

I don't understand why do I need hadoop API for a client to connect with Hive. Shouldn't JDBC driver be agnostic of the underlying query system? I just need to pass some SQL query?

Edit: I am using Cloudera(5.3.1), I think I need to add CDH dependencies. Cloudera instance is running hadoop 2.5.0 and HiveServer2

But the servers are at machine 1. On machine the code should at least compile and I should have issues at runtime only!

what's the version used of hadoop and hive? – Yosser Abdellatif Goupil Feb 27 '15 at 08:51 — Yosser Abdellatif Goupil, Feb 27 '15 at 08:51

score 9 · Answer 1 · answered Mar 09 '15 at 04:29

9

In case if you didn't still solve this, I have given it a go. And I needed the following dependencies for it to compile and run :

libthrift-0.9.0-cdh5-2.jar
httpclient-4.2.5.jar
httpcore-4.2.5.jar
commons-logging-1.1.3.jar
hive-common.jar
slf4j-api-1.7.5.jar
hive-metastore.jar
hive-service.jar
hadoop-common.jar
hive-jdbc.jar
guava-11.0.2.jar

The hive documentation is probably written against a older version/distribution.

Your exception is due to the missing hadoop-common jar, which has the org.apache.hadoop.conf.Configuration.

Hope this helps.

answered Mar 09 '15 at 04:29

Shyam

516
3
7

Thank You for the reply, I have solved the issue, but I am not too satisfied with the solution so I didn't posted it. – Mangat Rai Modi Mar 09 '15 at 08:59
2

But it is solution to your problem. Kindly satisfy yourself. You were missing hadoop-common.jar – zytham Mar 18 '16 at 10:56

score 7 · Answer 2 · answered Jan 05 '16 at 18:34

Getting the same error when trying to use hive-jdbc 1.2.1 against hive 0.13. Comparing to the long list in other answers. Now we use these two:

hive-jdbc-1.2.1-standalone.jar
hadoop-common-2.7.1.jar

Another side note: you might get 'Required field 'client_protocol' is unset!' when using the latest jdbc against an older Hive. If so, change the jdbc version to 1.1.0:

<dependency>
  <groupId>org.apache.hive</groupId>
  <artifactId>hive-jdbc</artifactId>
  <version>1.1.0</version>
  <classifier>standalone</classifier>
</dependency>

score 4 · Accepted Answer · answered Mar 09 '15 at 09:16

Answering my own question!

With some hit and trial, I have added following dependencies on my pom file and since then I am able to run code on both CHD 5.3.1 and 5.2.1 cluster.

<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-jdbc</artifactId>
    <version>0.13.1-cdh5.3.1</version>
</dependency>
<dependency>
    <groupId>org.apache.thrift</groupId>
    <artifactId>libthrift</artifactId>
    <version>0.9.0</version>
</dependency>
<dependency>
    <groupId>org.apache.thrift</groupId>
    <artifactId>libfb303</artifactId>
    <version>0.9.0</version>
</dependency>
<dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-core</artifactId>
    <version>2.5.0-mr1-cdh5.3.1</version>
</dependency>

<dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-common</artifactId>
    <version>2.5.0-cdh5.3.1</version>
</dependency>
<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>0.13.1-cdh5.3.1</version>
</dependency>
<dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-hdfs</artifactId>
    <version>2.5.0-cdh5.3.1</version>
</dependency>
<dependency>

Please note that some of these dependencies might not be required

score 1 · Answer 4 · answered Nov 02 '15 at 09:59

For others wondering around about what exactly is required to execute remotely a HIVE query using java...

Java code

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

public class Runner
{ 
        private static String driverName = "org.apache.hive.jdbc.HiveDriver";
        public static void main(String[] args) throws SQLException {

                try {
                        // Register driver and create driver instance
                        Class.forName(driverName);
                } catch (ClassNotFoundException ex) {
                      ex.printStackTrace();
                }

                // get connection
                System.out.println("before trying to connect");
                Connection con = DriverManager.getConnection("jdbc:hive2://[HOST IP]:10000/", "hive", "");
                System.out.println("connected");

                // create statement
                Statement stmt = con.createStatement();

                // execute statement
                stmt.executeQuery("show tables");

                con.close();
        }
}

Together with the pom file with the only required dependencies..

<?xml version="1.0" encoding="UTF-8"?>
<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>test-executor</groupId>
    <artifactId>test-executor</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <hadoop.version>2.5.2</hadoop.version>
    </properties>
<dependencies>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>1.2.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-jdbc</artifactId>
        <version>1.2.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-hdfs</artifactId>
        <version>${hadoop.version}</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-common</artifactId>
        <version>${hadoop.version}</version>
    </dependency>
</dependencies>
</project>

How do you get results of the query? – James Wierzba Sep 04 '19 at 18:38 — James Wierzba, Sep 04 '19 at 18:38

score 1 · Answer 5 · answered Apr 25 '16 at 12:48

I have faced the same issue with CDH5.4.1 version. I updated my POM file with the below code and it worked for me.

My Hadoop Version is Hadoop 2.6.0-cdh5.4.1 and Hive version is Hive 1.1.0-cdh5.4.1

<dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>0.13.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-jdbc</artifactId>
        <version>0.13.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.thrift</groupId>
        <artifactId>libthrift</artifactId>
        <version>0.9.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.thrift</groupId>
        <artifactId>libfb303</artifactId>
        <version>0.9.0</version>
    </dependency>
    <dependency>
        <groupId>commons-logging</groupId>
        <artifactId>commons-logging</artifactId>
        <version>1.1.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hadoop</groupId>
        <artifactId>hadoop-client</artifactId>
        <version>2.6.0</version>
    </dependency>

I have resolved with this POM update.

score 1 · Answer 6 · answered May 14 '18 at 23:50

Seems like you are all working with cloudera, I found that the repo in maven looks old because if you go to their site, you can download their jdbc. https://www.cloudera.com/downloads/connectors/hive/jdbc/2-5-20.html The driver seems to support more functionality than the one in hive. I notice that that they have addBatch implemented. I just wish they had these libraries in maven. Maybe someone can find where to get them from using maven.

Connect Hive through Java JDBC

6 Answers6

Linked