Connecting to remote Mapr Hive via JDBC

Question

This question is similar, but not the same, as Hive JDBC getConnection does not return . Yet this is about a remote connection. Also the metastore is present in the directory in which the hiveserver2 was started.

We have a running mapr cluster on a remote machine. I would like to connect to Hive on this cluster using Java JDBC.

Hence we started the hive server:

/opt/mapr/hive/hive-0.11/bin/hiveserver2

The output of the server process does not contain any error messages. It listens on port 10000 as reported by netstat.

I try to connect to the server as described in https://cwiki.apache.org/confluence/display/Hive/HiveClient, thereby replacing localhost by the server name where the hiveserver2 is running:

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

Yet the program hangs exactly at this statement. It does not seem to get a connection.

Possibly I need to supply a username and password?

Initially I had used the driver org.apache.hadoop.hive.jdbc.HiveDriver.

Yet it seems like I should be using the driver org.apache.hive.jdbc.HiveDriver if the hive2 server is running. Now I am getting the following Exception:

Exception in thread "main" java.sql.SQLException: Could not establish connection to jdbc:hive2://myserver.example.com:10000/default: Required field 'client_protocol' is unset! Struct:TOpenSessionReq(client_protocol:null)
at org.apache.hive.jdbc.HiveConnection.openSession(HiveConnection.java:246)
at org.apache.hive.jdbc.HiveConnection.<init>(HiveConnection.java:132)
at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:105)
at java.sql.DriverManager.getConnection(DriverManager.java:579)
at java.sql.DriverManager.getConnection(DriverManager.java:221)
at HiveJdbcClient.main(HiveJdbcClient.java:22)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: org.apache.thrift.TApplicationException: Required field 'client_protocol' is unset! Struct:TOpenSessionReq(client_protocol:null)
at org.apache.thrift.TApplicationException.read(TApplicationException.java:108)
at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:71)
at org.apache.hive.service.cli.thrift.TCLIService$Client.recv_OpenSession(TCLIService.java:144)
at org.apache.hive.service.cli.thrift.TCLIService$Client.OpenSession(TCLIService.java:131)
at org.apache.hive.jdbc.HiveConnection.openSession(HiveConnection.java:237)
... 10 more

Answer 1

I had the same problem and was able to get around it by adding the correct dependency to my pom.xml file. I was getting the latest apache release of hive from maven central and switched to using the cdh4 release from the cloudera repo. So, what you are seeing may be a symptom of having the wrong hive-jdbc dependency. Here's the maven snippet I added to my pom file:

<repository>
<id>cloudera</id>
<url>https://repository.cloudera.com/artifactory/cloudera-repos/</url>
</repository>
...
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>0.10.0-cdh4.3.2</version>
</dependency>

Here's a link about the cloudera repo.

Also, adding the ";auth=noSasl" to the URL made my application hang so I removed it.

Answer 2

I think you need to specify the username. Also itshould be hive2 not hive since you are using hiveserver2. Try modifying your connection url:

Connection con = 
  DriverManager.getConnection("jdbc:hive2://myserver.example.com:10000/default", "<user>", "");

Its given in the link Hive2

Hope this helps...!!!

Answer 3

I also had same problem. Please check if server is reachable on the port 10000 from the client (server and port are enabled no firewall is restricting) also check hiveserver is up and running. if yes then it should work. following code work for me for mapr hive.

if you have any query related mapr, please refer answers.mapr.com, this contain most of the information which you might be requiring.

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
import org.apache.log4j.Logger;


import java.io.*;
import org.apache.hadoop.io.SequenceFile;
import org.apache.hadoop.io.SequenceFile.*;
import org.apache.hadoop.io.SequenceFile.Writer;
import org.apache.hadoop.io.*;
import org.apache.hadoop.fs.FileSystem;
import org.apache.hadoop.io.Writable;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.conf.*;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.io.IntWritable;
import org.apache.hadoop.*;

public class HiveJdbcClient {
    //private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
    /**
    * @param args
    * @throws SQLException
    **/
    private static Logger mLogger = Logger.getLogger(HiveJdbcClient.class);
    private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";

    public static void main(String[] args) throws SQLException {
        HiveJdbcClient myJob = new HiveJdbcClient();
        myJob.execute();
    }
    public void execute() throws SQLException {
        //mLogger.info("Start HiveJob");
        System.out.println("Start HiveJob");
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.exit(1);
        }
Connection con = DriverManager.getConnection("jdbc:hive://myserver:10000/default", "", "");
        Statement stmt = con.createStatement();
        String sql = "SHOW TABLES";
        //String tableName = "testHiveDriverTable";
       // ResultSet res1 = stmt.executeQuery("create table " + tableName + " (key int, value string)");
        System.out.println("Running: " + sql);
        ResultSet res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(res.getString(1));
        }

        //mLogger.info("HiveJob executed!");
        System.out.println("HiveJob executed!");
    }
}

Answer 4

You can use the beeline client to connect to hive using JDBC.

It would be some thing like: beeline !connect jdbc:hive2://localhost:10000

check the link: http://dwbitechguru.blogspot.ca/2014/11/how-to-connect-to-hadoop-hive-using.html

Answer 5

In my case adding the: ;auth=noSasl to the JDBC connect string solved the endless waiting for the connection !

jdbc:hive2://server:10000/default;auth=noSasl

Answer 6

You should get hive-service-X.XX.X-cdhX.X.X.jar here :https://repository.cloudera.com/artifactory/cloudera-repos/org/apache/hive/; It works fine to me.