Connecting a Hortonworks sandbox to HANA via SDA
Yesterday I had some fun with a Hortonworks Sandbox environment. For those of you who do not know what that is, it’s a Hadoop system fully running on a virtual image. I thought it would be nice to run some Lumira analysis on it:
You can find the results of my Saturday night session here.
Getting this to work is fairly simple. Just follow this tutorial and you should be up and running in no time. Note that if you want to run Hortonworks on you Mac via Parallels, you need a conversion tool first called: OVF tool.
Connecting to Lumira is fun, but I decided to take it one step further and connect the virtual environment via smart data access (SDA) to HANA One.
There is a great tutorial on connecting an Amazon AWS image to HANA, but with a little tweaking it also works on the virtual sandbox. I wanted to give you some of my notes in case you want to try yourself!
- First off, follow the tutorial starting at paragraph 6.3
- Note that the wget mentioned will not get your anywhere. Use Winscp and go into your HANA box (via the Suse OS) and copy over the driver manually. You can find the updated Suse 11 driver here. As your tar file has a different name, be sure to update your linux commands to execute the correct instruction
- You can create the mentioned “.hortonworks.hiveodbc.ini” file on the mentioned location, but… there are actually two places where that needs to be done. You will find a second copy at “/usr/lib/hive/lib/native/Linux-amd64-64”. Be sure to update that one aswell!
- The “.odbc.ini” file needs to be updated with the IP addresses of your virtual machine. The way I did it is as follows:
- Open up port 10000 on my router
- Forwarded the port in parallels
- Created a DNS via No-IP. By doing that, whenever my external IP changes, it will automatically update via the DNS
- Open port 10000 on Amazon for your HANA instance
- Paste the mentioned Linux commands from the manual (e.g. Export…) in notepad first and stop just before stopping the HANA database (command su – hdbadm to switch the DB user). If you don’t use notepad, the commands will get garbled up!
- isql did not work for me after doing the install. Here is what I did to get it working:
- Install UnixODB via zypper: zypper in unixODBC
- As an alternative you could install the package manually. As an extra bonus I actually had to install also a compiler as that was not installed yet… : zypper in gcc
- All that is remaining then is to create the SDA connection in HANA via the following command and you’re good to go!
- CREATE REMOTE SOURCE HDP ADAPTER “hiveodbc” CONFIGURATION ‘DSN=HDP’ WITH CREDENTIAL TYPE ‘PASSWORD’ USING ‘user=;password=’;
Right mouse click on your remote table and select “add as virtual table”:
Et voila, you have a working sandbox connection to HANA!
Thank you for reading,