Connect to Apache Hive over ODBC

ImageSource: Unsplash

Introduction

ODBC Drivers — Overview

Configuring the ODBC driver properties :- Linux

#Path to where your driver library has been installed
Driver=/usr/lib/hive/lib/native/Linux-amd64-64/xxxhiveodbc64.so
#Fully Qualified hostname of your AE cluster. Change it for your cluster
Host=<chs-mmm-007-mn001.us-south.ae.appdomain.cloud>
#All endpoints in Analytics Engine are routed through Knox
Port=8443
#HTTPPath - the endpoint for AE is through Knox gateway
HTTPPath=gateway/default/hive
#Database or Schema of your choice
Database/Schema=<default>
#The Thrift Transport mode. Some drivers require you to give a number here
ThriftTransportMode=<HTTP/2>
#Specifies whether to connect directly to HiveServer2 or using Zookeeper. Enter the value for direct mode ie. no zookeeper
ServiceDiscoveryMode=<None/0>
#Analytics Engine uses HiveServer2
HiveServerType=2
#Authentication Mechanism
AuthMech=<User/Password>
#Uses SSL?
SSL=<Yes/1>
#For Analytics Engine, other parameters such as Kerberos ClientCert, TwoWaySSL etc are not applicable. The DSN also can be specified here and later used from isql as shown below

Configuring the ODBC properties :- SPSS Modeler

Configuring the ODBC properties :- Microsoft Excel

Conclusion

Senior Consultant, IBM Cloud. Sharing titbits of epiphanies...