Use SQL Developer to Access Hive Table on Hadoop

There are many UI or command-line tool to access Hive data on Hadoop and I am not going to list them one by one. I use SQL Developer a lot in accessing Oracle database and like this powerful tool. That makes me wondering whether I can use SQL Developer to access Hive table on HDFS. After some researches, I did find a way to configure SQL Developer to access Hive table. Here are the steps:

1. Download the JDBC Driver.
Goto Cloudera website, download Hive JDBC Driver at http://www.cloudera.com/content/cloudera/en/downloads/connectors/hive/jdbc/hive-jdbc-v2-5-4.html.
As for now, the latest version of Hive JDBC Connector is v2.5.4. Unzip the zip file to your target directory.

2. Configure SQL Developer
Open SQL Developer, click Preference -> Database -> Third Party JDBC Driver. Click Add Entry to add all of the jar files just unzipped. Close the SQL Developer, reopen it.
1_sql_developer_hive_jdbc_driver

3. Create a Hive Connection
Create a new connection. The port used is HiveSever2 port or the value for hive.server2.thrift.port. In my cluster, it is 10000. I still use test_oracle hive database created in my previous steps. Click Test, it should show Success.
2_sql_developer_hive_connection

4. Access Hive Tables
After click Connect, I can connect to my Hive databases on my Hadoop cluster.
3_sql_developer_hive_table
You can see the view and look to access Hive is very similar the way accessing regular oracle table. Awesome! Here are a few more other screens.
Properties Tab
4_sql_developer_hive_table_properties
DDL Tab
The DDL tab is nice one. The drawback for DDL is that everything is in one line. It would be nice to format the DDL string in a more readable way. Anyway, it is not a big deal.
5_sql_developer_hive_table_ddl
Detail Tab
6_sql_developer_hive_table_detail
Model Tab
7_sql_developer_hive_table_model

Let me run a query and here is the result, exact the look and feel like I use Oracle.
8_sql_developer_hive_table_query

Advertisements

2 thoughts on “Use SQL Developer to Access Hive Table on Hadoop

  1. Pingback: Use Toad to Access Hadoop Ecosystem | My Big Data World

  2. Pingback: Use Toad to Access Hadoop Ecosystem « Aryan Nava

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s