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