Hive ODBC Setup with UnixODBC and Debug Logging

[#1]

Article

Goals

• Setup Hive ODBC on Ubuntu 14.x.x with/without Kerberos
• Setup Hive ODBC on CentOS 6.x with/without Kerberos
• Enabling Debug logging useful for debugging connectivity issues

Notes/Requirements

• Article assumes that you have a setup of Ubuntu/CentOS server
• Access to Ubuntu/CentOS repository
• Have libsasl2-modules-gssapi-mit installed
• Have "gcc" installed, required for compilation when building from source
• Have "make" installed, required for compilation when building from source
• Verify if you have a working Ubuntu repository by typing "sudo apt-get update" and see if this returns successfully
• Within CentOS as a non superuser you can run "sudo yum repolist" wherein the "base" and "epel" repositories should get listed with thousands as value in "status" field
• Here are the examples of working repository on either systems
• This node should be able to talk to the HiveServer2 via hostname/IP address
• Ensure that firewall is disabled (ufw status for Ubuntu) & (service iptables/ip6tables status on CentOS)
• Hive keytab file copied over to the client machine for getting the kerberos ticket

Ubuntu

root@srai-odbc-ubuntu:~# apt-get update
Get:1 http://security.ubuntu.com trusty-security InRelease [65.9 kB]
....
Ign http://nova.clouds.archive.ubuntu.com trusty/universe Translation-en_US    
Fetched 11.2 MB in 6s (1,623 kB/s)                                             
Reading package lists... Done

CentOS

[centos@node1 ~]$ sudo yum repolist
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
repo id                                                                   repo name                                                                                         status
base                                                                      CentOS-6 - Base                                                                                    6,696
epel                                                                      Extra Packages for Enterprise Linux 6 - x86_64                                                    12,192
extras                                                                    CentOS-6 - Extras                                                                                     62
updates                                                                   CentOS-6 - Updates                                                                                   457

Installing UnixODBC on Ubuntu

• Using existing repository

root@srai-odbc-ubuntu:~# apt-get install unixodbc
Reading package lists... Done
Building dependency tree       
Reading state information... Done
.....
Setting up unixodbc (2.2.14p2-5ubuntu5) ...
Setting up odbcinst (2.2.14p2-5ubuntu5) ...
Processing triggers for libc-bin (2.19-0ubuntu6.4) ...
root@srai-odbc-ubuntu:~# 

• Using source file, which can be downloaded HERE. This is good if you want a custom version of the driver and a custom location for the binaries and libraries to be installed
  • Make a directory where you want to have the files downloaded for e.g., "/opt/odbc/unixodbc"
  • Change path to this directory "cd /opt/odbc"
  • Download the file in this location

  root@srai-odbc-ubuntu:~/ODBC# wget http://www.unixodbc.org/unixODBC-2.3.4.tar.gz
  --2016-08-25 16:34:30--  http://www.unixodbc.org/unixODBC-2.3.4.tar.gz
  Resolving www.unixodbc.org (www.unixodbc.org)... 87.106.19.214
  Connecting to www.unixodbc.org (www.unixodbc.org)|87.106.19.214|:80... connected.
  HTTP request sent, awaiting response... 200 OK
  Length: 1830660 (1.7M) [application/x-gzip]
  Saving to: ‘unixODBC-2.3.4.tar.gz’
  100%[========================================================================================================================================>] 1,830,660    390KB/s   in 5.2s   
  2016-08-25 16:34:35 (346 KB/s) - ‘unixODBC-2.3.4.tar.gz’ saved [1830660/1830660]
  

  • Extract the files from the tarzip file

root@srai-odbc-ubuntu:~/ODBC# tar -xzf unixODBC-2.3.4.tar.gz

• After the tarzip file has been extracted, you should be able to change directory to that folder

cd unixODBC-2.3.4

• By default when you build the source, files are copied within /usr/local/bin, /usr/local and /usr/local/man. Run the following, assuming you want the installation to be performed in a different location, for instance "/opt/odbc/unixodbc"

./configure --prefix=/opt/odbc/unixodbc --exec-prefix=/opt/odbc/unixodbc/oth --bindir=/opt/odbc/unixodbc/bin --sbindir=/opt/odbc/unixodbc/sbin --libexecdir=/opt/odbc/unixodbc/libexec --sysconfdir=/opt/odbc/unixodbc/etc --libdir=/opt/odbc/unixodbc/lib --includedir=/lib --includedir=/lib64
make
make install 

• Once the installation is complete, you should be able to find the "isql" binary

# find /usr/local -name isql
/usr/local/bin/isql

• Execute the command "odbcinst -j", this should return the location of configuration files

# cd /opt/odbc/unixodbc/bin
# ./odbcinst -j
unixODBC 2.3.4
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

NOTE: If you see this error

/usr/local/bin/odbcinst: error while loading shared libraries: libodbcinst.so.2: cannot open shared object file: No such file or directory

..this can be due to libraries not being found, but they might exist on the system. Perform the following steps to verify and fix this

# ldd /opt/odbc/unixodbc/odbcinst 
	linux-vdso.so.1 =>  (0x00007fff33dfe000)
	libodbcinst.so.2 => not found
	libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f7f739e2000)
	libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f7f7361c000)
	libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f7f73418000)
	/lib64/ld-linux-x86-64.so.2 (0x00007f7f73e23000)
# find /opt -name libodbcinst.so.2

/opt/odbc/unixodbc/lib/libodbcinst.so.2

# export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/odbc/unixodbc/lib # ldd /opt/odbc/unixodbc/odbcinst linux-vdso.so.1 => (0x00007fff33dfe000) libodbcinst.so.2 => /opt/odbc/unixodbc/lib/libodbcinst.so.2 (0x00007f7f73c06000) libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f7f739e2000) libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f7f7361c000) libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f7f73418000) /lib64/ld-linux-x86-64.so.2 (0x00007f7f73e23000)

Installing & Configure Hive ODBC on Ubuntu

• Download the driver HERE
• Copy the driver over to any temporary/staging directory or to a directory where you conserve all the binaries, for e.g., "/opt/odbc"
• Install the driver package using the following command

# dpkg -i hive-odbc-native_2.1.2.1002-2_amd64.deb
(Reading database ... 54835 files and directories currently installed.)
Preparing to unpack hive-odbc-native_2.1.2.1002-2_amd64.deb ...
Unpacking hive-odbc-native (2.1.2.1002-2) over (2.1.2.1002-2) ...
Setting up hive-odbc-native (2.1.2.1002-2) ...
#

• Ensure that the libraries installed (if UnixODBC was installed from source files) are included in LD_LIBRARY_PATH

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/lib:/lib64:/opt/odbc/unixodbc/lib

• Locate the files "odbc.ini" and "odbcinst.ini", location based on your method of installation. By default the files are available under /etc/, however, it can also be present in /usr/local/etc. In our case, assuming the custom installation, the files will be available under /opt/odbc/unixodbc/etc

root@srai-odbc-ubuntu:/opt/odbc/unixodbc/etc# ls -lrth /opt/odbc/unixodbc/etc
total 4.0K
-rw-r--r-- 1 root root    0 Aug 25 18:00 odbcinst.ini
-rw-r--r-- 1 root root    0 Aug 25 18:00 odbc.ini
drwxr-xr-x 2 root root 4.0K Aug 25 18:00 ODBCDataSources

• Copy the files from hive installation directory into this location

# cp /usr/lib/hive/lib/native/hiveodbc/Setup/odbc.ini /opt/odbc/unixodbc/etc/odbc.ini
# cp /usr/lib/hive/lib/native/hiveodbc/Setup/odbcinst.ini /opt/odbc/unixodbc/etc/odbcinst.ini

• Edit the file using vi or other editor and configure the following
  • locate the string "[Sample Hortonworks Hive DSN 64]" this is the DSN, short for Data Source Name. You can change it to anything you like, for instance "hivedb"
  • Ensure that the variable HOST has the name for HiveServer2
  • PORT by default should be set to 10000, or as configured otherwise
  • AuthMech should be set to 0 for no authentication OR 1 for Kerberos
  • UID can be set to hive or as needed
  • KrbHostFQDN should be the FQDN of Kerberos Server
  • KrbServiceName should be the principal name configured for hive OR the principal created
  • KrbRealm should be the name of realm, for instance EXAMPLE.COM

Here is the sample configuration output

[hivedb]
Driver=/usr/lib/hive/lib/native/Linux-amd64-64/libhortonworkshiveodbc64.so
HOST=node1.hortonworks.com
PORT=10000
HiveServerType=2
AuthMech=1
UID=hive
KrbHostFQDN=kerb.hortonworks.com
KrbServiceName=hive
KrbRealm=HWX.COM

• Export the variables to ensure that only our configured configuration files are being used

export ODBCINI=/opt/odbc/unixodbc/odbc.ini
export ODBCSYSINI=/opt/odbc/unixodbc
export HORTONWORKSHIVEINI=/usr/lib/hive/lib/native/Linux-amd64-64/hortonworks.hiveodbc.ini

• Ensure that hortonworks.hiveodbc.ini (as stated above) is pointing to the correct ODBC library, in here since we are using UnixODBC manager + Hive ODBC driver (Simba), we need to comment out the default "ODBCInstLib=libiodbcinst.so" and uncomment "ODBCInstLib=libodbcinst.so"

# Generic ODBCInstLib
#   iODBC
#ODBCInstLib=libiodbcinst.so  <<<<<<< Comment This
#   SimbaDM / unixODBC
ODBCInstLib=libodbcinst.so <<<<<<< Uncomment

Testing the connectivity

• Get the ticket from Hive and Kerberos using keytab file. In this case, node1.hortonworks.com is the host which serves HiveServer2 connectivity

# kinit -k -t /tmp/hive.service.keytab hive/[email protected]
# klist

• Once the ticket is granted, execute the following command to test connectivity

# /opt/odbc/unixodbc/bin/isql hivedb -v

• You can run some commands to verify if you are able to list the database and access the table data.

# /opt/odbc/unixodbc/bin/isql hivedb 
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show tables;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab_name                                                                                                                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| csvinternal                                                                                                                                                                                                                                                    |
| cvsexternal                                                                                                                                                                                                                                                    |
| sample_07                                                                                                                                                                                                                                                      |
| src                                                                                                                                                                                                                                                            |
| test                                                                                                                                                                                                                                                           |
| testabc                                                                                                                                                                                                                                                        |
| testnormal                                                                                                                                                                                                                                                     |
| tgt                                                                                                                                                                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns -1
8 rows fetched
SQL> select * from test limit 5;
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| col1       | col2                                                                                                                                                                                                                                                           |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1          | one                                                                                                                                                                                                                                                            |
| 1          | one                                                                                                                                                                                                                                                            |
| 10         | ten                                                                                                                                                                                                                                                            |
| 11         | eleven                                                                                                                                                                                                                                                         |
| 10         | ten                                                                                                                                                                                                                                                            |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns -1
5 rows fetched
SQL>

The process of installing UnixODBC on CentOS precisely the same with the exception of how firewalls are turned off. Everything else is virtually the same.

Enabling Debug Logging

• Open the file "hortonworks.hiveodbc.ini", you can use either of the following methods

# vi $HORTONWORKSHIVEINI
# vi /usr/lib/hive/lib/native/Linux-amd64-64/hortonworks.hiveodbc.ini

• The only two parameters that we need to modify are "LogLevel" and "LogPath", here are the examples. Zero is basically no logging.

LogLevel=6
LogPath=/opt/odbc/unixodbc/logs

• Once the variables are set, run a few commands and see that there will be two log files generated within the LogPath specified above. These files can be reviewed for debugging information.

# ls -lrth /opt/odbc/unixodbc/logs/
total 796K
-rw-r--r-- 1 root root 9.5K Aug 25 20:51 HortonworksHiveODBCDriver.log
-rw-r--r-- 1 root root 784K Aug 25 20:51 HortonworksHiveODBCDriver_connection_0.log

• Advisable to turn the values to default i.e., "LogLevel=0" if there are no attempts for debugging required, as it may generate log of data.

Hope this helps!!

[#2]

Thanks a lot for the article.It helped me a lot.

There is a small correction in the above.

1. export ODBCINI=/opt/odbc/unixodbc/etc/odbc.ini
2. export ODBCSYSINI=/opt/odbc/unixodbc/etc

/etc was missing in the path.

[#3]

KrbHostFQDN should be be set to FQDN of the Hive Server or _HOST in case of multiple HS2 servers.

This attribute should not be set to Kerberos server.

Could we please update this article to avoid confusions?