Triple H: Hadoop, Hive, HANA on Windows 10’s Bash ...

former_member198951 · ‎02-14-2017

Hello again,

This will be my last post for this series.

I will be showing here the setup of my SAP HXE 2.0 instance installed locally in my desktop.

I will also describe here the steps i have performed in order to access my Hadoop/Hive database installed on my local Linux installation, Windows 10 Bash Shell via SAP Smart Data Access:

Finally, SAP HANA Studio for the virtualization of remote tables of my Hadoop/Hive Database.

The steps here are from SAP HANA Academy channel on YouTube, SAP HANA Express playlist. The assumption here you have SAP HANA 2.0 Express Edition on Virtual Machine up and running. In addition you also have SAP HANA Studio installed and connected with SAP HXE.

Let's get started:

Putty as SSH client

UnixODBC Driver

Simba Apache Hive ODBC/JDBC drives for Linux

SAP HANA Studio - Data Provisioning - SDA

Putty as SSH Client

As you may know, the interface that comes with VMWare Workstation is not that quite user-friendly. In my case, i just use Putty as SSH client to access my SAP HXE instance. Using a very simple settings I am able to connect to my SAP HXE database: Private IP address and SSH port (22):

UnixODBC Driver

To install UnixODBC driver you will need to register your Virtual Machine with SUSE to get the repository updates. Just follow video 22 from the SAP playlist. Then video 27 for the UnixODBC installation.

Update: I had no choice but to update to UnixODBC 2.3.4. This is because "Simba Hive ODBC" was not connecting at all.

To manually install UnixODBC the C++ Compiler is required. Just use "sudo yast" as showed in this SAP HANA academy video. I did not install the VMWare Tools itself, just the C++ Compiler.

I used FileZilla to move the UnixODBC 2.3.4 file. Then gunzip and tar. Finally the installation as follow:

cd /_drivers/unixODBC-2.3.4

sudo ./configure
sudo make
sudo make install

I also had to install "Libsas12" from the SUSE VMWare repository. This is also required for the "Simba Hive ODBC":

now, keep in mind the instance for SAP HXE 2.0 is 90, and not 00. In this case, the Tenant Database port will be 39013. Just in case you decide to test the UnixODBC connection.

Simba Apache Hive ODBC/JDBC drivers for Linux

Download it from Simba Technologies. Registration is needed for a 30-days trial. The license is given by email after the registration.

I downloaded it locally and then moved the ZIP file into my SAP HXE instance:

sudo mkdir /_drivers

sudo chmod -R 777 /_drivers

Then, using FileZilla I uploaded the .ZIP file into into my SAP HXE instance:

The steps here are from SAP HANA Academy "SDA: Configuring ODBC Drivers" YouTube video.

After performing "gunzip" and "tar" steps the new folder "simba" is created in the installation path. The steps are pretty much the same from the SAP HANA Academy video. Only difference it that now "simba" folder has two sub directories: 32 and 64. Do not forget to put the license file into the home directory of the hxeadm user.

I added my Hadoop/Hive IP address into /etc/hosts file as follow:

127.0.0.2 hxehost.localdomain.com hxehost
127.0.0.2 hxehost.localdomain.com hxehost

192.168.2.220 hana
192.168.2.42 hive

Those are my private IP addresses. So no problem showing them here.

Preparing Simba ODBC(64) ini file: no need to copy to the home directory of the hxeadm user:

cd /_drivers/simba/hiveodbc/lib/64

vi simba.hiveodbc.ini

..

DriverManagerEncoding=UTF-16
ErrorMessagesPath=/_drivers/simba/hiveodbc/ErrorMessages/

# Generic ODBCInstLib
# iODBC
#ODBCInstLib=libiodbcinst.so

# SimbaDM / unixODBC
ODBCInstLib=libodbcinst.so

adding Hive DSN configuration into .odbc.ini file:

vi ~/.odbc.ini

[MYSAPHXE]
servernode = hana:39013
driver = /usr/sap/HXE/HDB90/exe/libodbcHDB.so
description = HDB
databasename = systemdb

[MYHIVE]
Driver=/_drivers/simba/hiveodbc/lib/64/libsimbahiveodbc64.so
Host=hive
Port=10000
HiveServerType=2
HS2AuthMech=2

my customer.sh file:

vi ~/.customer.sh

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/_drivers/unixODBC-2.3.4/
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/_drivers/simba/hiveodbc/lib/64/
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib/
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib64/
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/lib64/
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/

export ODBCINI=$HOME/.odbc.ini

Testing my Hadoop/Hive connection using "isql": The troubleshooting and final solution took me 3 days. I almost gave up:

Attempts to connect my Hive system all failed at this:

$isql -v MYHIVE hiveuser password
[01000][unixODBC][Driver Manager]Can't open lib '/_drivers/simba/hiveodbc/lib/64/libsimbahiveodbc64.so' : file not found
[ISQL]ERROR: Could not SQLConnect

This is because something was missing from my installation:

$ ldd /_drivers/simba/hiveodbc/lib/64/libsimbahiveodbc64.so

linux-vdso.so.1 (0x00007ffc3d590000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fcff4310000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fcff40f3000)
libsasl2.so.2 => not found
librt.so.1 => /lib64/librt.so.1 (0x00007fcff3eeb000)
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007fcff3b67000)
libm.so.6 => /lib64/libm.so.6 (0x00007fcff3866000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fcff364f000)
libc.so.6 => /lib64/libc.so.6 (0x00007fcff32a7000)
/lib64/ld-linux-x86-64.so.2 (0x00007fcff6a56000)

This error is misleading. The Simba Hive ODBC Driver "libsimbahiveodbc64.so" was just fine. However the library highlighted above was missing from my SAP HXE instance. So I installed the Libsas12 from the repository using 'sudo yast'. The installation here gives "libsasl2.so.3" only. Simba Hive ODBC needs "libsasl2.so.2"

so, the last step was to create a symbolic link as follow:

cd /usr/lib64
sudo ln -s libsasl2.so.3 libsasl2.so.2

checking "libsimbahiveodbc64.so" again:

ldd /_drivers/simba/hiveodbc/lib/64/libsimbahiveodbc64.so

linux-vdso.so.1 (0x00007ffeb3aec000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f80b2520000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f80b2303000)
libsasl2.so.2 => /usr/lib64/libsasl2.so.2 (0x00007f80b20e6000)
librt.so.1 => /lib64/librt.so.1 (0x00007f80b1ede000)
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007f80b1b5a000)
libm.so.6 => /lib64/libm.so.6 (0x00007f80b1859000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f80b1642000)
libc.so.6 => /lib64/libc.so.6 (0x00007f80b129a000)
/lib64/ld-linux-x86-64.so.2 (0x00007f80b4c66000)

then the connection was successful:

SAP HANA Studio - Data Provisioning - SDA

New Remote Datasource for my HADOOP/HIVE: the DSN here is the one from my .odbc.ini file:

my Hive database:

SQL console script to create the SDA virtual tables:

CREATE VIRTUAL TABLE "LIVE2"."VT_MYHIVE_CONNECTIONS" AT "HIVE"."HIVE"."live2"."connections";

SDA virtual table content:

That's all. SAP Smart Data Access using HADOOP/HIVE ODBC driver from Simba Technologies still very easy to setup.

Cheers,

Fernando