Query Cloudera Hadoop Hive using Oracle SQL Developer.

Note: DBeaver free SQL editor is a far better tool than Oracle SQL developer and it connects to 80+ different databases. It is easier to get working with Hive/Impala. Check out the instructions in the link below:

https://plenium.wordpress.com/2019/10/15/connect-dbeaver-sql-tool-to-cloudera-hive-impala-with-kerberos/

Environment:

Oracle SQL Developer Version 18.1.0.095 on Windows 64bit and

Hive on Cloudera Hadoop CDH 5.12.x.

Config steps:

1. First download and install the popular free tool Oracle SQL Developer for Windows from Oracle website.
2. Read this blog for a good idea about connecting Oracle SQL Developer to Hadoop Hive:   https://blogs.oracle.com/bigdataconnectors/move-data-between-apache-hadoop-and-oracle-database-with-sql-developer
3. Note when configuring Cloudera-Hive JDBC drivers use the below website to download the 64bit JDBC driver for windows. https://www.cloudera.com/downloads/connectors/hive/jdbc/2-5-20.html
4. In the Tools->Preferences->Database->Third party JDBC drivers add the .jar files from the JDBC driver download file ClouderaHiveJDBC-2.5.20.1060\ClouderaHiveJDBC41-2.5.20.1060  .  (Note: If the Cloudera_HiveJDBC41 .jar files are not recognized by SQL Developer and the Hive connection is not enabled then try with the ClouderaHiveJDBC4 jars). Make sure to select all 10 or 15 .jar driver files and load into the tool not just the path.
5. Apache Hive Connection setup:
   1. Click on the New Connection in SQL Developer
   2. Click on Hive tab.  If the ‘Hive’ tab does not display next to the ‘Oracle’ tab it is likely that the Hive JDBC drivers did not install correctly. Load the
   3. Tools -> Preferences -> Database -> Third party JDBC drivers again.
   4. Give a connection name, username in Hive or Hue such as hive, Hostname of the hiverserver2, Port default is 10000, Database: default
6. Test the connection if successful then Connect and you will see the Hive tables and will be able to run the SQL queries, import, export etc. on Hive from SQL Developer.

Following additional steps are needed for Kerberos secured Cloudera clusters

See the blogs below:

https://db-blog.web.cern.ch/blog/prasanth-kothuri/2016-02-using-sql-developer-access-apache-hive-kerberos-authentication

http://vijayjt.blogspot.com/2016/02/how-to-connect-to-kerberised-chd-hadoop.html

While testing the connection if you get an error such as:

Status : Failure -Test failed: [Cloudera][HiveJDBCDriver](500164) Error initialized or created transport for authentication: [Cloudera][HiveJDBCDriver](500169) Unable to connect to server: GSS initiate failed.

Then as given in the vijayt’s blog above, you need to copy your krb5.conf file to the folder sqldeveloper\jdk\jre\lib\security. You may need to download Unlimited Java Cryptography Extension files from Oracle Java website and copy (replace) local_policy.jar and US_export_policy.jar files to sqldeveloper\jdk\jre\lib\security directory inside sql developer installation.

Restart SQL Developer and Get a new token for the userid using MIT Kerberos GUI client. Test the Hive JDBC connection.