Query Cloudera Hadoop Hive using Oracle SQL Developer.

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.

     

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s