Connecting DRILL using ODBC in Linux - Using unixODBC Driver Manager

System requirements can be found in https://drill.apache.org/docs/installing-the-driver-on-linux/

My environment :  cat /etc/redhat-release

CentOS release 6.6 (Final)

NOTE : Make sure the hostname and IPs of drill bit nodes are specified in '/etc/hosts'

Step 1 : Installing unixODBC

yum install unixODBC

Step 2 : Download MapR Drill ODBC Driver

cd /home/alwin

wget http://package.mapr.com/tools/MapR-ODBC/MapR_Drill/MapRDrill_odbc_v1.2.1.1000/MapRDrillODBC-1.2.1.x86_64.rpm

Step 3 : Install MapR Drill ODBC Driver

cd /home/alwin

yum localinstall --nogpgcheck MapRDrillODBC-1.2.1.x86_64.rpm

Step 4 : Test the installation

[a] rpm -qa | grep -i mapr

MapRDrillODBC-1.2.1-1.x86_64

[b] rpm -qa | grep -i unixodbc

unixODBC-2.2.14-14.el6.x86_64

Step 5 : Copy the following files in '/opt/mapr/drillodbc/Setup' to HOME directory

·        mapr.drillodbc.ini

·        odbc.ini

·        odbcinst.ini

echo $HOME

/root

cd /opt/mapr/drillodbc/Setup

cp * /root/

Step 6 : Rename the files as hidden files

cd /root

mv mapr.drillodbc.ini .mapr.drillodbc.ini

mv odbc.ini .odbc.ini

mv odbcinst.ini .odbcinst.ini

Step 7 : Set the environment variables

export ODBCINI=~/.odbc.ini

export MAPRDRILLINI=~/.mapr.drillodbc.ini

export LD_LIBRARY_PATH=/usr/local/lib:/opt/mapr/drillodbc/lib/64

Step 8 : Define the ODBC sources in .odbc.ini

Sample used by me is given below:

[ODBC]

Trace=no

[ODBC Data Sources]

Sample MapR Drill DSN 64=MapR Drill ODBC Driver 64-bit

[Sample MapR Drill DSN 64]

# This key is not necessary and is only to give a description of the data source.

Description=MapR Drill ODBC Driver (64-bit) DSN

# Driver: The location where the ODBC driver is installed to.

Driver=/opt/mapr/drillodbc/lib/64/libmaprdrillodbc64.so

# The DriverUnicodeEncoding setting is only used for SimbaDM

# When set to 1, SimbaDM runs in UTF-16 mode.

# When set to 2, SimbaDM runs in UTF-8 mode.

#DriverUnicodeEncoding=2

# Values for ConnectionType, AdvancedProperties, Catalog, Schema should be set here.

# If ConnectionType is Direct, include Host and Port. If ConnectionType is ZooKeeper, include ZKQuorum and ZKClusterID

# They can also be specified on the connection string.

# AuthenticationType: No authentication; Basic Authentication

ConnectionType=Zookeeper

#HOST=[HOST]

#PORT=[PORT]

ZKQuorum=<hostname>:5181

ZKClusterID=ajames-drillbits

AuthenticationType=No Authentication

UID=[USERNAME]

PWD=[PASSWORD]

AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5;

Catalog=DRILL

Schema=

Make sure the following:

[1] /opt/mapr/drillodbc/lib/64/libmaprdrillodbc64.so is present

[2] I used Zookeeper mode of conection, please make sure

               a. ConnectionType=Zookeeper

               b. ZKQuorum=<same as value specified for 'zk.connect' in /opt/mapr/drill/drill-                                       <version>/conf/drill-override.conf file>

               c. ZKClusterID==<same as value specified for 'cluster-id' in /opt/mapr/drill/drill-                                    <version>/conf/drill-override.conf file>

NOTE: I connected to drill-bit which is not authenticated

For authentication, set the following:

               AuthenticationType=Basic Authentication

               UID=[<userid>]

               PWD=[<password>]

Step 9 : Configure the MapR Drill ODBC Driver

Sample of my .mapr.drillodbc.ini is given below

## - Note that this default DriverManagerEncoding of UTF-32 is for iODBC.

## - unixODBC uses UTF-16 by default.

## - If unixODBC was compiled with -DSQL_WCHART_CONVERT, then UTF-32 is the correct value.

##   Execute 'odbc_config --cflags' to determine if you need UTF-32 or UTF-16 on unixODBC

## - SimbaDM can be used with UTF-8 or UTF-16.

##   The DriverUnicodeEncoding setting will cause SimbaDM to run in UTF-8 when set to 2 or UTF-16 when set to 1.

[Driver]

DisableAsync=0

DriverManagerEncoding=UTF-32

ErrorMessagesPath=/opt/mapr/drillodbc/ErrorMessages

LogLevel=0

LogPath=[LogPath]

SwapFilePath=/tmp

## - Uncomment the ODBCInstLib corresponding to the Driver Manager being used.

## - Note that the path to your ODBC Driver Manager must be specified in LD_LIBRARY_PATH.

# Generic ODBCInstLib

#   iODBC

# ODBCInstLib=libiodbcinst.so

#   SimbaDM / unixODBC

ODBCInstLib=libodbcinst.so

# AIX specific ODBCInstLib

#   iODBC

#ODBCInstLib=libiodbcinst.a(libiodbcinst.so.2)

#   SimbaDM

#ODBCInstLib=libodbcinst.a(odbcinst.so)

#   unixODBC

#ODBCInstLib=libodbcinst.a(libodbcinst.so.1)

NOTE : Since we are using unixODBC, comment out

# Generic ODBCInstLib

#   iODBC

ODBCInstLib=libiodbcinst.so

and uncomment

#   SimbaDM / unixODBC

ODBCInstLib=libodbcinst.so

Step 10 : Testing the connection

We can use 'isql' to test the connection

isql "<DSN>"

From my system 

isql "Sample MapR Drill DSN 64"

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>

For drill bit node with authentication use:

isql "<DSN>" "<username>" "<password>"