Recently, Enkitec received an Oracle Big Data Appliance (BDA) for our server farm in Dallas (Thanks Accenture!). With this new addition to the server farm, I’m excited to see what the BDA can do and how to use it. Since I use Oracle SQL Developer for a lot of things, I figure I better see if I can connect to it…. wait I don’t have access yet, darn! Simple solution, I’ll just use the Oracle Virtual Box VM (Big Data Lite) to make sure my that my SQL Developer can connect when I eventually get access.
The first thing I needed is download the Big Data Lite VM. It can be downloaded from the Oracle Technology Network (here). The second thing I needed to do was download the connectors for HIVE from Cloudera, use the version for the platform you need (here).
After downloading the Cloudera connectors for HIVE, these needed to be unzipped in a location that can be accessed by SQL Developer. Since I’m on a MacBook Pro, I unzipped them in this location:
$ cd ~/Downloads
$ unzip ./Cloudera_HiveJDBC_184.108.40.2066.zip -d /Users/Bobby/Oracle/connectors
$ cd /Users/Bobby/Oracle/connectors
$ ls -ltr
-rw-r–r–@ 1 Bobby staff 5521341 Sep 10 15:16 Cloudera_HiveJDBC4_220.127.116.116.zip
-rw-r–r–@ 1 Bobby staff 5317239 Sep 10 15:16 Cloudera_HiveJDBC3_18.104.22.1686.zip
$ unzip ./Cloudera_HiveJDBC4_22.214.171.1246.zip -d ./Hive
$ cd ./Hive
$ ls -ltr
-r–r–r–@ 1 Bobby staff 1083758 Sep 8 17:28 Cloudera – Simba JDBC Driver for Hive Install Guide.pdf
-rw-r–r–@ 1 Bobby staff 9679 Sep 8 23:28 slf4j-log4j12-1.5.8.jar
-rw-r–r–@ 1 Bobby staff 23445 Sep 8 23:28 slf4j-api-1.5.8.jar
-rw-r–r–@ 1 Bobby staff 367444 Sep 8 23:28 log4j-1.2.14.jar
-rw-r–r–@ 1 Bobby staff 347531 Sep 8 23:28 libthrift-0.9.0.jar
-rw-r–r–@ 1 Bobby staff 275186 Sep 8 23:28 libfb303-0.9.0.jar
-rw-r–r–@ 1 Bobby staff 294796 Sep 8 23:28 ql.jar
-rw-r–r–@ 1 Bobby staff 596600 Sep 8 23:28 hive_service.jar
-rw-r–r–@ 1 Bobby staff 7670596 Sep 8 23:28 hive_metastore.jar
-rw-r–r–@ 1 Bobby staff 2972229 Sep 8 23:28 TCLIServiceClient.jar
-rw-r–r–@ 1 Bobby staff 1656683 Sep 8 23:29 HiveJDBC4.jar
Once the connectors are extracted, SQL Developer needs to know which HIVE connector to use. In this case the JDBC4 connector is required. Unzipped the JDBC4 set of files into a directory, in my case I’m using a directory called Hive.
In order to tell SQL Developer which connector to use, it needs to be specified in the interface by doing the following:
- Start SQL Developer
- Oracle SQL Developer -> Preferences
- Database -> Third Party JDBC -> Add Entry
- Restart SQL Developer
After restarting SQL Developer, we now see an option on the connection screen for Hive.
Now SQL Developer is ready to connect to a Big Data Appliance, oh I mean to my VM for Big Data Lite :), lets setup a connection and see if we can connect. Since I’m connecting to a Virtual Box VM, I need to setup some ports to be used between my MacBook and the VM. In this case, I have setup a SQL port on 15211 which maps to the standard database port of 1521. For the Hive connection I’ve setup 10001 which maps to port 10000.
With the ports put in place, now I can setup SQL Developer to connect to the Hive on the Big Data Lite VM. You will notice that on the username, password, server name and port is needed. The database parameter is optional when connecting to a Bid Data Hive.
Once the connection is configured, I can login to the Hive and review what tables are listed in the Big Data Lite VM.