Connect Oracle SQL Developer to Hive

[#1]

Oracle SQL Developer is one of the most common SQL client tool that is used by Developers, Data Analyst, Data Architects etc for interacting with Oracle and other relational systems. So extending the functionality of SQL developer to connect to hive is very useful for Oracle users.

I found this original article on oracle’s website and made some additions based upon the issues that I ran into. Here is the original link

Oracle SQL Developer support for Hive

Here are the steps that i followed

Step1) For the latest version of SQL developer you would need JDK 1.8 so you would need to install that on your mac and also change the JAVA_HOME path so that it points to JDK 1.8.

Download JDK 1.8

Step2) Download the latest version of Oracle SQL developer for mac, from oracle and unzip it

Oracle SQL Developer Download

Move the SQL Developer file to your application, so that it is available for you. Now when you try to open up Oracle SQL developer on mac, it may not open. For me it showed up in the tray, blinked for a while and then gone. So I had to follow this instruction to fix it

Fix for Mac SQL Developer setup

Once you have this fix then you should be able to open the Oracle SQL developer.

Step3) Need to download JDBC driver for Hive that can work with Oracle SQL Developer. Cloudera has one available and here it he link for it

Link for Hive JDBC Driver for Oracle SQL Developer

Step4) Unzip the downloaded file from step3. There will be another zip file you will find called “Cloudera_HiveJDBC4_2.5.15.1040.zip”. Unzip that file as well and move all the jars to

<your home directory>/.sqldeveloper/

ql.jar

hive_service.jar

hive_metastore.jar

TCLIServiceClient.jar

zookeeper-3.4.6.jar

slf4j-log4j12-1.5.11.jar

slf4j-api-1.5.11.jar

log4j-1.2.14.jar

libthrift-0.9.0.jar

libfb303-0.9.0.jar

HiveJDBC4.jar

Step5) Add these jars to SQL developer

Go “Oracle SQL Developer” --> Preferences

Select Database and then “Third Party JDBC Drivers” and use add entry option to add the jar files mentioned in steps above.

Restart the SQL developer to reflect this change.

Step6) Open SQL developer and right click on connections to add a connection. Select the hive tab, enter your hive server details and Add that connection.

You are all set to browse Hive tables via SQL Developer

[#2]

Good note. Unfortunately it does not recognize the generic Apache Hive JDBC driver. Also if you need to add special properties for ssl, or kerberos or ldap authentication, SQL Developer will not work. Use SQL Workbench J, RazorSQL or Squirrel SQL instead.

[#3]

Right Ancil, these are jar files specifically for Oracle SQL developers connectivity. I thought this article will be useful for folks who have SQL developer as a standard SQL client tool in their company and have no other workaround 🙂

[#4]

Nice writeup, and very timely too. My current client is looking for this info right now - will bring it to them tomorrow.

[#5]

To clarify, are you saying we can install the Cloudera Hive JDBC drivers along with some HDP jar files and use SQL Developer to talk to an HDP Hive database? If so, are there any versions that are support like HDP 2.2.9, HDP 2.3, etc? Or is it your mileage varies?

[#6]

more information:

https://blogs.oracle.com/datawarehousing/entry/oracle_sql_developer_data_modeler

[#7]

Rather than using the Cloudera JDBC driver, HortonWorks provides drivers at http://hortonworks.com/downloads/#data-platform

[#8]

It is not working for me ,I followed above steps.I have 32 bit Windows 7 machine with sql developer.I am not able to see Hive option when I add new connection

[#9]

Hi Chakra,

Thank you for the steps, Have you tried using zk method to connect for sql developer?

I tried the below, but it is not working.

Schema=default;AuthMech=1;KrbRealm=xyz.com;KrbHostFQDN=abcserver.xyz.com,KrbServiceName=hive;ZK=abcserver.xyz.com:2181/hiveserver2,serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2

Thanks,

Saravana

[#10]

Do you now another SQL solution for complex querys in HW?