Community Articles
Find and share helpful community-sourced technical articles
Labels (1)



  • 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


  • 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


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


[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) ...
  • 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
    --2016-08-25 16:34:30--
    Resolving (
    Connecting to (||: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 install 
  • Once the installation is complete, you should be able to find the "isql" binary
# find /usr/local -name 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

NOTE: If you see this error

/usr/local/bin/odbcinst: error while loading shared libraries: 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 =>  (0x00007fff33dfe000) => not found => /lib/x86_64-linux-gnu/ (0x00007f7f739e2000) => /lib/x86_64-linux-gnu/ (0x00007f7f7361c000) => /lib/x86_64-linux-gnu/ (0x00007f7f73418000)
	/lib64/ (0x00007f7f73e23000)
# find /opt -name


# export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/odbc/unixodbc/lib # ldd /opt/odbc/unixodbc/odbcinst => (0x00007fff33dfe000) => /opt/odbc/unixodbc/lib/ (0x00007f7f73c06000) => /lib/x86_64-linux-gnu/ (0x00007f7f739e2000) => /lib/x86_64-linux-gnu/ (0x00007f7f7361c000) => /lib/x86_64-linux-gnu/ (0x00007f7f73418000) /lib64/ (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 ( over ( ...
Setting up hive-odbc-native ( ...
  • 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

  • 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 "" and uncomment ""
# Generic ODBCInstLib
#   iODBC  <<<<<<< Comment This
#   SimbaDM / unixODBC <<<<<<< Uncomment

Testing the connectivity

  • Get the ticket from Hive and Kerberos using keytab file. In this case, is the host which serves HiveServer2 connectivity
# kinit -k -t /tmp/hive.service.keytab hive/
# 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

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 /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.
  • 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!!


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.

Cloudera Employee

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?

Don't have an account?
Version history
Revision #:
1 of 1
Last update:
‎08-25-2016 09:12 PM
Updated by:
Top Kudoed Authors