HANA Live2!: Installing the HIVE ODBC Driver for Connecting to Hadoop from SAP HANA One

[Former Member]

Tahir Hussain “Bob” Babar from the SAP HANA Academy details how to install a HIVE ODBC driver on a SAP HANA One Linux server. Installing HIVE ODBC is an important part of the SAP HANA Live2! course. Note that SAP HANA SPS7 is required for the HANA Live2! course. To view all of the content and links for the course please visit the HANA Live2! Project Overview.

To successfully follow along with this video Unix ODBC 2.2.12 or above must be installed. View that installation process here.

Note: The HIVE server isn't always running. You can continue to follow along the steps using CSVs instead or email Bob at (tahir.hussain.babar@sap.com) to request a time window for the servers to be turn back on.

Installing ODBC Driver:

• First enter the AWS EC2 Management Console, choose running instances, then select Live 2 HANA and then click on connect to launch the Java ssh client. (4:11 – 4:27)
• Next to http://www.simba.com/connectors/apache-hadoop-hive-odbc in a browser and register for a free trail of Hive ODBC. Make sure to select Linux 32& 64 Bit as the operating system. (5:00 – 6:16)
• After agreeing to the terms and clicking download, click on cancel. Then right click on “Click here if your download did not start” and select copy the link location. (6:17 – 6:54)
• Back in the Java ssh client after hanaserver:~# enter cd /_drivers/ enter wget followed by the copied link for the Hive ODBC download. (7:35 -  8:09)
• Next, do a ls command to see that the Simba Hive ODBC has been downloaded and then enter unzip followed by the Hive ODBC file name to unzip it. (8:50 – 8:58)
• Then gunzip the file by entering gunzip followed by the new file name and bracket it using double parenthesis “. (9:12 – 9:25)
• After entering a ls, enter tar xvf followed by the new file name to create the relevant folders. (9:27 – 9:40)
• Next, enter cd simba, then ls, then cd hiveodbc/ then another ls to see all of the installation files. (9:48 – 9:55)
• Then enter cd lib, followed by ls, followed by cd 64 to open up the 64 bit path and pwd. (10:00 – 10:11)

Modifying the Customer sh and ini file.

• Input su – hdbadm press enter and then input vi ~/.customer.sh (10:27 – 10:40)
• Next, hit I to insert and paste in the Hive path between the two existing files. (10:55 – 11:05)
• Log out of hdbadm and enter cd.., then ls, then cd Setup/, then ls, to see the simba.hiveobdc.ini folder. (11:42 – 11:52)
• Log back in as hdbadm and enter cp/_drivers/simba/hiveodbc/Setup/simba.hiveodbc.ini ~/.simba.hiveodbc.ini (11:55 – 12:34)
• Next, enter vi ~/. Simba.hiveodbc.ini to edit the file. (12:52 – 13:00)
• Press I for insert and then change DriverManagerEncoding=UTF-32 to UTF-16 (13:00 – 13:40)
• Then change the next line to read ErrorMessagesPath=_drivers/simba/hiveodbc/ErrorMessages/ (13:41 – 14:02)
• Next, below those lines change it to read:

          # Generic ODBCInstLib

          #  iODBC

          # ODBCInstLib=libiodbcinst.so

          #   SimbaDM / unixODBC

          ODBCInstLib=libodbcinst.so

          Note - all of these steps are outlined in the PDF file that was downloaded

          into the drivers folder. (14:02 – 14:30)

Creating the DSN and Adding the License Key

• After quitting enter vi ~/.odbc.ini and press I to insert a third DSN. (15:05 – 15:25)
• Then enter:

          [HIVE]

          Driver=/_drivers/simba/hiveodbc/lib/64/libsimbahivodbc64.so

          Host=54.83.2.197

          Port=10001

          HiveServerType=2

          AuthMech=2 (15:30 – 17:35)

• Next create a file for the license key by entering vi /usr/sap/HDB/home/SimbaApacheHiveODBCDRiver.lic (18:48 – 19:02)
• Then copy the lic file from the email received after signing up for Simba and paste it in after doing an insert statement and save it by doing an quit command. (19:04 – 19:38)

Correcting the File Dependencies

• Then go to the directory where the drivers are stored by entering cd/_drivers/samba/hiveodbc/lib/64 and then ls. (19:58 – 20:20)
• Next, enter ldd libsimbahiveodbc64.so to see the files that currently don’t have the proper execution permissions for the driver that they are dependent on. (20:24 – 20:50)
• After quitting re-enter the customer sh file by enter vi~/.customer.sh to add the path to the other dependencies. (21:21 – 21:30)
• Insert the three paths from the syntax file above export ODBCINI=$HOME/.odbd.ini The three paths are:

          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/ (21:30 – 21:48)

• Quit, exit, clear, and then log into the HANA One instance with an isql statement to complete the connection. (22:15 – 22:27)

Bob’s hands-on tutorial video demonstrates how to connection an SAP HANA One installation via ODBC to a HIVE Hadoop system.

View other tutorials on the HANA Live2! Project at the SAP HANA Academy.

SAP HANA Academy - over 500 free tutorial technical videos on using SAP HANA.

-Tom Flanagan

SAP HANA Academy

Follow @saphanaacademy