Gold ⭐️

Hive ODBC Setup with UnixODBC and Debug Logging

October 5, 2026 at 9:28 AM
2 replies
15376 views
Translate All

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/node1.hortonworks.com@HWX.COM # 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