Connect Oracle SQL Developer to Hive

As Oracle SQL Developer is one of the most common SQL client tool used by Developers, Data Analyst and Data Architects to interact with Oracle and other relational systems. So extending the functionality of SQL developer to connect to hive is very useful for Oracle users. You can use the SQL Worksheet to query, create and alter Hive tables dynamically accessing data sources defined in the Hive metastore.

Prerequisite:

Hive is already configured and working fine. You can review the post if needed.

Step 1: Check Hiveserver2 is running if not run it from HIVE_HOME/bin
[hdpclient@en01 ~]$ hiveserver2 &
[1] 589

HiveServer2 (HS2) is a service (based on Thrift RPC) that enables clients to execute queries against Hive. HS2 supports multi-client concurrency and authentication. It is designed to provide better support for open API clients like JDBC and ODBC. HS2 is a single process running as a composite service, which includes the Thrift-based Hive service (TCP or HTTP) and a Jetty web server for web UI.

Before running HS2 set the below property in HIVE_HOME/conf/hive-site.xml

<property>
<name>hive.server2.enable.doAs</name>
<value>false</value>
<description> Setting this property to true will have HiveServer2 execute Hive operations as the user making the calls to it. </description>
</property>
Check the web UI also

http://en01:10002/hiveserver2.jsp

Step 2: Check connectivity with your hive database using some Hiveserver2 client eg; HIVE_HOME/bin/beeline

Beeline is a command shell tool that works with HiveServer2. It's a JDBC client that is based on the SQLLine CLI. It works in both embedded mode as well as remote mode.

[hdpclient@en01 ~]$ beeline

which: no hbase in (/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/hdpclient/.local/bin:/home/hdpclient/bin:/usr/java/default/bin:/usr/hadoopsw/hadoop-2.7.3/sbin:/usr/hadoopsw/hadoop-2.7.3/bin:/usr/hadoopsw/apache-hive-2.1.1-bin/bin:/usr/hadoopsw/db-derby-10.13.1.1-bin/bin)

Beeline version 2.1.1 by Apache Hive

beeline>

Connect with beeline

beeline> !connect jdbc:hive2://en01:10000/scott

Connecting to jdbc:hive2://en01:10000/scott

Enter username for jdbc:hive2://en01:10000/scott:

Enter password for jdbc:hive2://en01:10000/scott:

Connected to: Apache Hive (version 2.1.1)

Driver: Hive JDBC (version 2.1.1)

17/05/14 12:49:44 [main]: WARN jdbc.HiveConnection: Request to set autoCommit to false; Hive does not support autoCommit=false.

Transaction isolation: TRANSACTION_REPEATABLE_READ

0: jdbc:hive2://en01:10000/scott>

0: jdbc:hive2://en01:10000/scott> show tables;

+--------------------+--+

| tab_name |

+--------------------+--+

| access_logs |

| alog |

| dept |

| dual |

| emp |

+--------------------+--+

5 rows selected (0.097 seconds)

Exit from beeline

0: jdbc:hive2://en01:10000/scott> !q

Closing: 0: jdbc:hive2://en01:10000/scott

Step 3: Download/install latest SQL Developer from Oracle.

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Step 4: Download JDBC driver for Hive that can work with Oracle SQL Developer. Cloudera has one available and here it he link for it

https://www.cloudera.com/downloads/connectors/hive/jdbc/2-5-15.html

Download the Hive Drivers and unzip the archive. Three zip files are contained within the archive. Unzip the JDBC4 archive to a target directory that is accessible to SQL Developer. You will get an error when attempting to open a Hive connection in SQL Developer if you use a different JDBC version. Ensure you use JDBC4 and not JDBC41.

Step 5: After JDBC drivers have been extracted, update SQL Developer to use the new drivers. Update the preferences in SQL Developer to leverage the new drivers.

Start SQL Developer
Go to Tools -> Preferences
Navigate to Database -> Third Party JDBC Drivers
Add all of the jar files contained in the zip to the Third-party JDBC Driver Path. It should look like the picture below:

Restart SQL Developer

Step 6: Now that SQL Developer is configured to access Hive, let's create a connection to Hiveserver2. Click the New Connection button in the SQL Developer toolbar.

Step 7: The Hive connection is now treated like any other connection in SQL Developer. The tables are organized into Hive databases; you can review the tables' data, properties, partitions, indexes, details and DDL: