Partner Tools Series: How to connect to CDW Hive using ODBC on your Mac

[#1]

Background:

This is a new article from the Partner Solutions Engineering Team describing how to connect to CDW Hive using ODBC on your Mac. In this series, we will explore client-side activities and Partner integrations that may sometimes be atypical. Our goal is to offer convenient approaches that reduce time and research on your part.

Step 0:

Ensure that homebrew and keytool are installed on your Mac and in your path.
% export PATH=$PATH:/opt/homebrew/bin
% which keytool
/opt/homebrew/opt/openjdk/bin/keytool

Step 1:

Download the CDW certificates for the Hive virtual cluster you want to connect to.

Assuming you downloaded the certificate chain into a file called truststore.jks (can be any name you want), create a PEM file:

% keytool -list -rfc -keystore truststore.jks | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > cdw-certs.pem

You should see multiple certificates in this file. Please also note that the /etc/hosts file on your ODBC client should be 
able to resolve the virtual URLs exposed by CDW.

keytool prompts you for a password. Here, the default password of 'changeit' was used.

Step 2: 

Install the ODBC driver for use on a Mac.
% brew install unixodbc

You should now see the below on your ODBC client machine:

% ls -al /opt/cloudera/hiveodbc
total 1624
drwxr-xr-x  10 root  wheel     320 May 19 10:53 .
drwxr-xr-x   4 root  wheel     128 Dec 21  2021 ..
-rwxrwxrwx   1 root  wheel  700125 Jul  5 2023 Cloudera-ODBC-Driver-for-Apache-Hive-Install-Guide.pdf
-rwxrwxrwx   1 root  wheel   11995 Jul  5  2023 EULA.txt
drwxr-xr-x   3 root  wheel      96 Mar 31  2021 ErrorMessages
-rwxrwxrwx   1 root  wheel   22161 Jul  5  2023 Release-Notes-Hive-ODBC.txt
drwxr-xr-x   4 root  wheel     128 May 19 10:53 Setup
drwxr-xr-x   3 root  wheel      96 May 19 10:53 Tools
drwxr-xr-x   3 root  wheel      96 Mar 31  2021 lib 
-rwxrwxrwx   1 root  wheel   93490 Jul  5  2023 third-party-licenses.txt

Step 3:

Download the Cloudera ODBC Driver for Hive, 
e.g. try https://www.cloudera.com/downloads/connectors/hive/odbc/2-7-0.html

To ensure that the driver was installed, run:

% pkgutil --info cloudera.hiveodbc
package-id: cloudera.hiveodbc
version: 2.7.0

Step 4:

Locate the files used to configured ODBC Data Source Names
% which odbc_config
/opt/homebrew/bin/odbc_config

% odbcinst -j
unixODBC 2.3.12
DRIVERS............: /opt/homebrew/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/homebrew/etc/odbc.ini
FILE DATA SOURCES..: /opt/homebrew/etc/ODBCDataSources
USER DATA SOURCES..: /Users/kdavis/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Step 5:

Configure an ODBC DSN using the paths cited in the previous step above.

% cat /opt/homebrew/etc/odbc.ini
[ODBC Data Sources]
CDW_Hive_DSN=Cloudera Hive ODBC Driver

[CDW_Hive_DSN]
Driver=/opt/cloudera/hiveodbc/lib/universal/libclouderahiveodbc.dylib
Host=hs2-test-hive.apps.cdppvcds.com
Port=443
HiveServerType=2
ServiceDiscoveryMode=0
AuthMech=3
ThriftTransport=2
HttpPath=cliservice
SSL=1
AllowSelfSignedServerCert=1
CAIssuedCertNamesMismatch=1
TwoWaySSL=0
TrustedCerts=/Users/kdavis/Documents/Partners/Protegrity/cluster-build/cdw-certs.pem
Min_TLS=1.2

For a detailed explanation of the settings above, please see an example reference doc at:
https://downloads.cloudera.com/connectors/impala_odbc_2.6.14.1016/Cloudera-ODBC-Connector-for-Impala-Install-Guide.pdf

Next, the odbcinst.ini file should be automatically updated as you install the drivers for 
Hive, Impala, Postgres, etc.

% cat /opt/homebrew/etc/odbcinst.ini
[ODBC Drivers]
PostgreSQL Unicode                   = Installed
Cloudera ODBC Driver for Impala      = Installed
Cloudera ODBC Driver for Apache Hive = Installed

[PostgreSQL Unicode]
Description = PostgreSQL ODBC driver
Driver      = /usr/local/lib/psqlodbcw.so

[Cloudera ODBC Driver for Impala]
Driver = /opt/cloudera/impalaodbc/lib/universal/libclouderaimpalaodbc.dylib

[Cloudera ODBC Driver for Apache Hive]
Driver = /opt/cloudera/hiveodbc/lib/universal/libclouderahiveodbc.dylib

Step 6:

Configure the system paths and environment variables.

Note that the library for the Cloudera Hive Driver is installed at:
/opt/cloudera/hiveodbc/lib/universal/libclouderahiveodbc.dylib

For convenience, use the path /usr/local/lib as a central point to reference the libraries 
needed:

% sudo ln -s /opt/cloudera/hiveodbc/lib/universal/libclouderahiveodbc.dylib 
/usr/local/lib/libclouderahiveodbc.dylib

% sudo ln -s /opt/homebrew/opt/unixodbc/lib/libodbcinst.dylib /usr/local/lib/libodbcinst.dylib

Set the following environment variables:

export DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:/usr/local/lib
export ODBCINI=/opt/homebrew/etc/odbc.ini
export ODBCINSTINI=/opt/homebrew/etc/odbcinst.ini
export CLOUDERAHIVEINI=/opt/cloudera/hiveodbc/lib/universal/cloudera.hiveodbc.ini

Step 7:

Test the ODBC connection to CDW Hive using iSQL or any other desired ODBC tool.

% which isql
/opt/homebrew/bin/isql

% isql -v CDW_Hive_DSN <<username>> <<password>>
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>