Community Articles
Find and share helpful community-sourced technical articles
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.
Labels (1)
Guru

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

5,917 Views
Comments

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?
Coming from Hortonworks? Activate your account here
Version history
Revision #:
1 of 1
Last update:
‎08-25-2016 09:12 PM
Updated by:
 
Contributors
Top Kudoed Authors