Community Articles

Find and share helpful community-sourced technical articles.
avatar
Cloudera Employee

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.

download-hive-odbc-driver.png

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

download-hive-odbc-driver.png

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>  
639 Views