Monday, November 6, 2017

How to install and configure MapR Hive ODBC driver on Linux

Goal:

How to install and configure MapR Hive ODBC driver on Linux.
This article gives detailed step-by-step instructions as a supplement to this MapR Documentation.

Env:

CentOS 6.5 64bit
MapR 5.2
Hive 2.1
MapRHiveODBC-2.1.8.1005-1.x86_64
libiodbc-3.52.7-1.el6.x86_64

Solution:

1. Download and install MapR Hive ODBC Driver from http://package.mapr.com/tools/MapR-ODBC/MapR_Hive based on the OS version.

For example, this test environment is on CentOS 6.5:
wget http://package.mapr.com/tools/MapR-ODBC/MapR_Hive/MapRHive_odbc_2.1.8.1005/Linux/EL6/MapRHiveODBC-2.1.8.1005-1.el6.x86_64.rpm
rpm -ivh MapRHiveODBC-2.1.8.1005-1.el6.x86_64.rpm
The location after the installation:
  • /opt/mapr/hiveodbc/ErrorMessages – Error messages files directory
  • /opt/mapr/hiveodbc/Setup – Sample configuration files directory
  • /opt/mapr/hiveodbc/lib/64 – 64-bit shared libraries directory
ODBC driver managers use configuration files to define and configure ODBC data sources and drivers. By default, the following configuration files residing in the user’s home directory are used:
  • .odbc.ini – The file used to define ODBC data sources (required)
  • .odbcinst.ini – The file used to define ODBC drivers (optional)
  • .mapr.hiveodbc.ini – The file used to configure the MapR ODBC Driver with SQL Connector for Apache Hive (required)

2. Configure the Environment

Environment variables ODBCINI and ODBCSYSINI can be used to specify different locations for the odbc.ini and odbcinst.ini configuration files.
cp /opt/mapr/hiveodbc/Setup/odbc.ini ~/
cp /opt/mapr/hiveodbc/Setup/odbcinst.ini ~/
export ODBCINI=~/odbc.ini
export ODBCSYSINI=~/odbcinst.ini

For version 2.1.8 and above, you must also set MAPRHIVEINI to point to the mapr.hiveodbc.ini file in the user's home directory:
export MAPRHIVEINI=/etc/.mapr.hiveodbc.ini

Configure LD_LIBRARY_PATH:
export LD_LIBRARY_PATH=/opt/mapr/hiveodbc/lib/64:/usr/lib64/

3. Configure the odbc.ini File

One example is:
[ODBC]
# Specify any global ODBC configuration here such as ODBC tracing.

[ODBC Data Sources]
Sample MapR Hive DSN 64=MapR Hive ODBC Connector 64-bit

[Sample MapR Hive DSN 64]

# Description: DSN Description.
# This key is not necessary and is only to give a description of the data source.
Description=MapR Hive ODBC Connector (64-bit) DSN

# Driver: The location where the ODBC driver is installed to.
Driver=/opt/mapr/hiveodbc/lib/64/libmaprhiveodbc64.so

# When using No Service Discovery, specify the IP address or host name of the Hive server.
# When using ZooKeeper as the Service Discovery Mode, specify a comma-separated list of ZooKeeper
# servers in the following format:
#       <zk_host1:zk_port1>,<zk_host2:zk_port2>,...
HOST=v4.poc.com

# The TCP port Hive server is listening. This is not required when using ZooKeeper as the service
# discovery mode as the port is specified in the HOST connection attribute.
PORT=10000

# The name of the database schema to use when a schema is not explicitly specified in a query.
Schema=default

# Set to 0 to when connecting directory to Hive Server 2 (No Service Discovery).
# Set to 1 to do Hive Server 2 service discovery using ZooKeeper.
# Note service discovery is not support when using Hive Server 1.
ServiceDiscoveryMode=0

# The namespace on ZooKeeper under which Hive Server 2 znodes are added. Required only when doing
# HS2 service discovery with ZooKeeper (ServiceDiscoveryMode=1).
ZKNamespace=

# Set to 1 if you are connecting to Hive Server 1. Set to 2 if you are connecting to Hive Server 2.
HiveServerType=2

# The authentication mechanism to use for the connection.
#   Set to 0 for No Authentication
#   Set to 1 for Kerberos
#   Set to 2 for User Name
#   Set to 3 for User Name and Password
# Note only No Authentication is supported when connecting to Hive Server 1.
AuthMech=2

# The Thrift transport to use for the connection.
# Set to 0 for Binary
# Set to 1 for SASL
# Set to 2 for HTTP
# Note for Hive Server 1 only Binary can be used.
ThriftTransport=1

# When this option is enabled (1), the driver does not transform the queries emitted by an
# application, so the native query is used.
# When this option is disabled (0), the driver transforms the queries emitted by an application and
# converts them into an equivalent from in HiveQL.
UseNativeQuery=0

# Set the UID with the user name to use to access Hive when using AuthMech 2 to 8.
UID=mapr

# The following is settings used when using Kerberos authentication (AuthMech 1 and 10)

# The fully qualified host name part of the of the Hive Server 2 Kerberos service principal.
# For example if the service principal name of you Hive Server 2 is:
#   hive/myhs2.mydomain.com@EXAMPLE.COM
# Then set KrbHostFQDN to myhs2.mydomain.com
# KrbHostFQDN=[Hive Server 2 Host FQDN]

# The service name part of the of the Hive Server 2 Kerberos service principal.
# For example if the service principal name of you Hive Server 2 is:
#   hive/myhs2.mydomain.com@EXAMPLE.COM
# Then set KrbServiceName to hive
# KrbServiceName=[Hive Server 2 Kerberos service name]

# The realm part of the of the Hive Server 2 Kerberos service principal.
# For example if the service principal name of you Hive Server 2 is:
#   hive/myhs2.mydomain.com@EXAMPLE.COM
# Then set KrbRealm to EXAMPLE.COM
# KrbRealm=[Hive Server 2 Kerberos realm]

# Set to 1 to enable SSL. Set to 0 to disable.
SSL=0

# Set to 1 to enable two-way SSL. Set to 0 to disable. You must enable SSL in order to
# use two-way SSL.
TwoWaySSL=0

# The file containing the client certificate in PEM format. This is required when using two-way SSL.
ClientCert=

# The client private key. This is used for two-way SSL authentication.
ClientPrivateKey=

# The password for the client private key. Password is only required for password protected
# client private key.
ClientPrivateKeyPassword=

4. Configure the odbcinst.ini File

One example is:
[ODBC Drivers]
MapR Hive ODBC Connector 64-bit=Installed

[MapR Hive ODBC Connector 64-bit]
Description=MapR Hive ODBC Connector (64-bit)
Driver=/opt/mapr/hiveodbc/lib/64/libmaprhiveodbc64.so

## The option below is for using unixODBC when compiled with -DSQL_WCHART_CONVERT.
## Execute 'odbc_config --cflags' to determine if you need to uncomment it.
# IconvEncoding=UCS-4LE

5. Configure /etc/.mapr.hiveodbc.ini

cp /opt/mapr/hiveodbc/lib/64/mapr.hiveodbc.ini /etc/.mapr.hiveodbc.ini
Modify it from:
# Generic ODBCInstLib
#   iODBC
ODBCInstLib=libiodbcinst.so
to:
# Generic ODBCInstLib
#   iODBC
ODBCInstLib=libiodbcinst.so.2

6. Test using libiodbc Driver Manager

yum install libiodbc
Test using iodbctest command:
[root@v4 ~]# iodbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0709.0909

Enter ODBC connect string (? shows list): ?

DSN                              | Driver
------------------------------------------------------------------------------
Sample MapR Hive DSN 64          | MapR Hive ODBC Connector 64-bit

Enter ODBC connect string (? shows list): DSN=Sample MapR Hive DSN 64
Driver: 2.1.8.1005 (MapR Hive ODBC Connector)

SQL>show databases;

database_name
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
default

result set 1 returned 1 rows.


No comments:

Post a Comment

Popular Posts