Cloudera Community

Community Articles

Find and share helpful community-sourced technical articles.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Advanced Search

Hive ODBC Setup with UnixODBC and Debug Logging

Labels (1)
Labels:
avatar
author-rank srai1
Guru

Created on ‎08-25-2016 09:12 PM

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

12,724 Views
Comments

Re: Hive ODBC Setup with UnixODBC and Debug Logging

avatar
author-rank asirna
Super Guru

Created on ‎01-04-2017 08:18 AM

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.

Re: Hive ODBC Setup with UnixODBC and Debug Logging

avatar
author-rank sappusamy author-rank
Contributor

Created on ‎02-20-2017 09:50 AM

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?

Announcements
What's New @ Cloudera
Cloudera Data Engineering 1.23: Access Spark from Your Favor...
What's New @ Cloudera
HBase REST server scaling support is Generally Available
What's New @ Cloudera
New CLI option in the update-database command
What's New @ Cloudera
New Action menu item in the Cloudera Operational Database UI
What's New @ Cloudera
Get the list of supported instance types in the Cloudera Ope...
View More Announcements
Version history
Last update:
‎08-25-2016 09:12 PM
Updated by:
Guru
Contributors