Unable to connect to kerborised hiveserver2 from jdbc connection

[#1]

Getting error as

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder". SLF4J: Defaulting to no-operation (NOP) logger implementation SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details. Exception in thread "main" java.lang.NullPointerException at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Unknown Source) at org.apache.hadoop.conf.Configuration.getClassByNameOrNull(Configuration.java:2159) at org.apache.hadoop.conf.Configuration.getClassByName(Configuration.java:2124) at org.apache.hadoop.security.SecurityUtil.getCustomAuthPrincipal(SecurityUtil.java:634) at org.apache.hadoop.security.UserGroupInformation.initialize(UserGroupInformation.java:219) at org.apache.hadoop.security.UserGroupInformation.setConfiguration(UserGroupInformation.java:361) at org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge25Sasl.createClientWithConf(HadoopThriftAuthBridge25Sasl.java:72) at org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge25Sasl.createClientWithConf(HadoopThriftAuthBridge25Sasl.java:55) at org.apache.hive.service.auth.KerberosSaslHelper.getKerberosTransport(KerberosSaslHelper.java:54) at org.apache.hive.jdbc.HiveConnection.createBinaryTransport(HiveConnection.java:449) at org.apache.hive.jdbc.HiveConnection.openTransport(HiveConnection.java:202) at org.apache.hive.jdbc.HiveConnection.<init>(HiveConnection.java:177) at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:105) at helloworld.main(helloworld.java:15)

[#2]

Make sure u have following dependencies based on your hdp version.

<dependencies>

<dependency>

<groupId>org.apache.hive</groupId>

<artifactId>hive-exec</artifactId>

<version>1.2.1000.2.5.3.0-37</version>

</dependency>

<dependency>

<groupId>org.apache.hive</groupId>

<artifactId>hive-jdbc</artifactId>

<version>1.2.1000.2.5.3.0-37</version>

</dependency>

<dependency>

<groupId>org.apache.hive</groupId>

<artifactId>hive-metastore</artifactId>

<version>1.2.1000.2.5.3.0-37</version>

</dependency>

<dependency>

<groupId>org.apache.hive</groupId>

<artifactId>hive-service</artifactId>

<version>1.2.1000.2.5.3.0-37</version>

</dependency>

<dependency>

<groupId>org.apache.hadoop</groupId>

<artifactId>hadoop-common</artifactId>

<version>2.7.3.2.5.3.0-37</version>

</dependency>

</dependencies>

following is the example code

org.apache.hadoop.conf.Configuration conf = new org.apache.hadoop.conf.Configuration();

conf.set("hadoop.security.authentication", "Kerberos");

UserGroupInformation.setConfiguration(conf);

UserGroupInformation.loginUserFromKeytab("hive/xx@HDP.COM", "/tmp/hive.service.keytab");

Class.forName("org.apache.hive.jdbc.HiveDriver");

System.out.println("getting connection");

Connection con = DriverManager.getConnection("jdbc:hive2://<host>:10001/;principal=hive/xx@HDP.COM;transportMode=http;httpPath=cliservice");

[#3]

useful for maven based builds...thanks

[#4]

@ashim sinha

Hive CLI is now a legacy tool.HiveServer1 is already deprecated it has been a while that Hive community has been recommending Beeline + HS2 configuration, ideally because of the deprecation Hive CLI, use the below example to achieve the same goal. Assuming you have the correct hive principal replace the below with appropriate values. This is how it works on my cluster

###########################################################
# Access HiveServer2 in kerberized cluster 
# hive CLI has been deprecated in HS2
###########################################################
# su - hive
[hive@london ~]$ beeline
Beeline version 1.2.1000.2.5.3.0-37 by Apache Hive
beeline> ! connect jdbc:hive2://london.uk.com:10000/;principal=hive/london.uk.com@TEST.COM
Connecting to jdbc:hive2://london.uk.com:10000/;principal=hive/london.uk.com@TEST.COM
Enter username for jdbc:hive2://london.uk.com:10000/;principal=hive/london.uk.com@TEST.COM:
Enter password for jdbc:hive2://london.uk.com:10000/;principal=hive/london.uk.com@TEST.COM:
Connected to: Apache Hive (version 1.2.1000.2.5.3.0-37)
Driver: Hive JDBC (version 1.2.1000.2.5.3.0-37)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://london.uk.com:10000/> show databases;
+----------------+--+
| database_name  |
+----------------+--+
| default        |
| moroto         |
| jair           |
+----------------+--+
3 rows selected (2.863 seconds)
0: jdbc:hive2://london.uk.com:10000> use jair;
No rows affected (0.097 seconds)
0: jdbc:hive2://london.uk.com:10000> show tables;
+-----------+--+
| tab_name  |
+-----------+--+
| employee  |
+-----------+--+
1 row selected (0.162 seconds)
0: jdbc:hive2://london.uk.com:10000/> select * from employee;
+--------------+----------------+------------------+-----------------------+--+
| employee.id  | employee.name  | employee.salary  | employee.destination  |
+--------------+----------------+------------------+-----------------------+--+
| 1201         | Gopal          | 45000            | Technical manager     |
| 1202         | Manisha        | 45000            | Proof reader          |
| 1203         | Masthanvali    | 40000            | Technical writer      |
| 1204         | Kiran          | 40000            | Hr Admin              |
| 1205         | Kranthi        | 30000            | Op Admin              |
| 1206         | Geoffrey       | 45000            | DevOPS                |
| 1207         | Salvatore      | 30000            | IT Lead               |
| 1208         | Dave           | 40000            | Cleaner               |
| 1206         | Geoffrey       | 45000            | DevOPS                |
| 1207         | Shelly         | 30000            | IT Lead               |
| 1208         | Dave           | 40000            | Cleaner               |
| 1209         | fid            | 40000            | Cleaner               |
| 1210         | Grid           | 40000            | Clerk                 |
| 1211         | Dred           | 40000            | Masseuse              |
| 1212         | stad           | 40000            | boss                  |
| 1213         | jair           | 40000            | cook                  |
| 1214         | Jenelle        | 40000            | handyman              |
+--------------+----------------+------------------+-----------------------+--+
17 rows selected (7.172 seconds)

Please let me know if you have any issue I will gladly help

[#5]

1. import java.io.*;
2. import java.util.*;
3. import java.sql.*;
4. import org.apache.*;
5. import java.sql.Connection;
6. import java.sql.DriverManager;
7. import java.sql.ResultSet;
8. import java.sql.Statement;
9. import org.apache.hadoop.security.UserGroupInformation;
10. publicclass helloworld {
11. publicstaticvoid main(String[] args)throwsSQLException,ClassNotFoundException,InstantiationException,IllegalAccessException{
12. Driver hiveDriver;
13. Connection con;
14. String connection_string;
15. Properties props =newProperties();
16. connection_string ="jdbc:hive2://****/default;principal=mapr/*****;kerberosAuthType=fromSubject";
17. System.out.println("Full connection string is: "+ connection_string );
18. hiveDriver =(Driver)Class.forName("org.apache.hive.jdbc.HiveDriver").newInstance();
19. System.out.println("BEFORE");
20. con = hiveDriver.connect( connection_string, props );
21. System.out.println("AFTER");
22. Statement stmt = con.createStatement();
23. // See if show tables works
24. ResultSet res;
25. String sql ="show tables";
26. System.out.println("Running: "+ sql);
27. stmt.executeQuery(sql);
28. System.out.println("Done with show tables");
29. }
30. }
31. While executing the code i'm getting kerberos user_name: prompt and while giving the user name and password im getting error as could not open client transport jdbc uri

[#6]

I'm trying to connect from windows to Linux server which has kerborised hiveserver2.through jdbc connectivity.Can you please suggest me on this?

[#7]

import java.io.*;
import java.util.*;
import java.sql.*;
import org.apache.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.apache.hadoop.security.UserGroupInformation;
public class helloworld {
public static void main(String[] args) throws SQLException,ClassNotFoundException,InstantiationException,IllegalAccessException {
Driver hiveDriver;
        Connection con;
        String connection_string;
        Properties props = new Properties();
        connection_string = "jdbc:hive2://****/default;principal=mapr/*****;kerberosAuthType=fromSubject";
        System.out.println( "Full connection string is: " + connection_string );
        hiveDriver = (Driver)Class.forName("org.apache.hive.jdbc.HiveDriver").newInstance();
        System.out.println("BEFORE");
        con = hiveDriver.connect( connection_string, props );
        System.out.println("AFTER");
        Statement stmt = con.createStatement();
        // See if show tables works
        ResultSet res;
        String sql = "show tables" ;
        System.out.println("Running: " + sql);
        stmt.executeQuery(sql);
        System.out.println("Done with show tables");
}
  }
While executing the code i'm getting kerberos user_name: prompt and while giving the user name and password im getting error as could not open client transport jdbc uri 

[#8]

Any suggestion on this?

[#9]

@ashim sinha

Here is a good document that you use as a reference.

Let me know

[#10]

Can you please suggest any other way as we cannot install any software.