Configuring Impala to Work with ODBC
Third-party products can be designed to integrate with Impala using ODBC. For the best experience, ensure any third-party product you intend to use is supported. Verifying support includes checking that the versions of Impala, ODBC, the operating system, and the third-party product have all been approved for use together. Before configuring your systems to use ODBC, download a connector. You may need to sign in and accept license agreements before accessing the pages required for downloading ODBC connectors.
Downloading the ODBC Driver
See the database drivers section on the Cloudera downloads web page to download and install the driver.
Configuring the ODBC Port
Versions 2.5 and 2.0 of the Cloudera ODBC Connector, currently certified for some but not all BI applications, use the HiveServer2 protocol, corresponding to Impala port 21050. Impala supports Kerberos authentication with all the supported versions of the driver, and requires ODBC 2.05.13 for Impala or higher for LDAP username/password authentication.
Version 1.x of the Cloudera ODBC Connector uses the original HiveServer1 protocol, corresponding to Impala port 21000.
Example of Setting Up an ODBC Application for Impala
To illustrate the outline of the setup process, here is a transcript of a session to set up all required drivers and a business intelligence application that uses the ODBC driver, under Mac OS X. Each .dmg file runs a GUI-based installer, first for the underlying IODBC driver needed for non-Windows systems, then for the Cloudera ODBC Connector, and finally for the BI tool itself.
$ ls -1 Cloudera-ODBC-Driver-for-Impala-Install-Guide.pdf BI_Tool_Installer.dmg iodbc-sdk-3.52.7-macosx-10.5.dmg ClouderaImpalaODBC.dmg $ open iodbc-sdk-3.52.7-macosx-10.dmg Install the IODBC driver using its installer $ open ClouderaImpalaODBC.dmg Install the Cloudera ODBC Connector using its installer $ installer_dir=$(pwd) $ cd /opt/cloudera/impalaodbc $ ls -1 Cloudera ODBC Driver for Impala Install Guide.pdf Readme.txt Setup lib ErrorMessages Release Notes.txt Tools $ cd Setup $ ls odbc.ini odbcinst.ini $ cp odbc.ini ~/.odbc.ini $ vi ~/.odbc.ini $ cat ~/.odbc.ini [ODBC] # Specify any global ODBC configuration here such as ODBC tracing. [ODBC Data Sources] Sample Cloudera Impala DSN=Cloudera ODBC Driver for Impala [Sample Cloudera Impala DSN] # Description: DSN Description. # This key is not necessary and is only to give a description of the data source. Description=Cloudera ODBC Driver for Impala DSN # Driver: The location where the ODBC driver is installed to. Driver=/opt/cloudera/impalaodbc/lib/universal/libclouderaimpalaodbc.dylib # 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 HOST, PORT, KrbFQDN, and KrbServiceName should be set here. # They can also be specified on the connection string. HOST=hostname.sample.example.com PORT=21050 Schema=default # The authentication mechanism. # 0 - No authentication (NOSASL) # 1 - Kerberos authentication (SASL) # 2 - Username authentication (SASL) # 3 - Username/password authentication (SASL) # 4 - Username/password authentication with SSL (SASL) # 5 - No authentication with SSL (NOSASL) # 6 - Username/password authentication (NOSASL) AuthMech=0 # Kerberos related settings. KrbFQDN= KrbRealm= KrbServiceName= # Username/password authentication with SSL settings. UID= PWD CAIssuedCertNamesMismatch=1 TrustedCerts=/opt/cloudera/impalaodbc/lib/universal/cacerts.pem # Specify the proxy user ID to use. #DelegationUID= # General settings TSaslTransportBufSize=1000 RowsFetchedPerBlock=10000 SocketTimeout=0 StringColumnLength=32767 UseNativeQuery=0 $ pwd /opt/cloudera/impalaodbc/Setup $ cd $installer_dir $ open BI_Tool_Installer.dmg Install the BI tool using its installer $ ls /Applications | grep BI_Tool BI_Tool.app $ open -a BI_Tool.app In the BI tool, connect to a data source using port 21050
Notes about JDBC and ODBC Interaction with Impala SQL Features
Most Impala SQL features work equivalently through the impala-shell interpreter of the JDBC or ODBC APIs. The following are some exceptions to keep in mind when switching between the interactive shell and applications using the APIs: