How do I connect to Hive on EMR through Eclipse?

Question

I am trying to connect to Hive on EMR through Eclipse, but I get an error.

Exception in thread "main" java.sql.SQLException: Could not open client transport with JDBC Uri: jdbc:hive2://localhost:8158: java.net.ConnectException: Connection refused
    at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:215)
    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:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at com.readypulse.sparkanalytics.HiveQLConnector.<init>(HiveQLConnector.java:31)
    at com.readypulse.sparkanalytics.HiveQLConnector.main(HiveQLConnector.java:83)
Caused by: org.apache.thrift.transport.TTransportException: java.net.ConnectException: Connection refused

Answer 1

You would need a SSH Tunnel to poke a hole to EMR master. And then connect via JDBC.

private static String driverName = "org.apache.hive.jdbc.HiveDriver";
private static String hiveConnectionString = "jdbc:hive2://localhost:8158";

//Need port forwarding
SparkPortForwarding.portForwardForSpark();

Class c = Class.forName(driverName);
Connection connection = DriverManager.getConnection(hiveConnectionString,
                "user", "pwd");
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);

You can do port forwarding via shell as:

ssh -i ~/mykey.pem -N -L 8158:<maserhost>:10000 hadoop@<masterhost>

Or You can use the code using the library JSch

public static void portForwardForSpark() {
    try {
        if(session != null && session.isConnected()) {
            return;
        }

        JSch jsch = new JSch();
        jsch.addIdentity(PATH_TO_SSH_KEY_PEM);
        String host = REMOTE_HOST;
        session = jsch.getSession(USER, host, 22);

        // username and password will be given via UserInfo interface.
        UserInfo ui = new MyUserInfo();
        session.setUserInfo(ui);

        session.connect();
        int assingedPort = session.setPortForwardingL(LPORT, RHOST, RPORT);
        System.out.println("Port forwarding done for the post : " + assingedPort);
    } catch (Exception e) {
        System.out.println(e);
    }
}