This is the documentation for Cloudera Impala 1.2.4.
Documentation for other versions is available at Cloudera Documentation.

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.
  Note: You may need to sign in and accept license agreements before accessing the pages required for downloading ODBC connectors.

Version 1.x of the Cloudera ODBC Connectors uses the original HiveServer1 protocol, corresponding to Impala port 21000.

The newer versions 2.5 and 2.0, currently certified for some but not all BI applications, use the HiveServer2 protocol, corresponding to Impala port 21050. Although the 2.x drivers include authentication through either Kerberos tickets or username/password credentials, Impala only supports Kerberos for authentication.

Connector Download Page Notes

Cloudera ODBC Driver 2.5 for Impala

Cloudera ODBC Driver for Cloudera Impala, Documentation

This is the generic ODBC driver you would use to integrate Impala with any ODBC-based application not listed here. This connector is available for both Linux and Windows systems.

Cloudera ODBC Connector 2.0 for Microstrategy

Cloudera ODBC Connector 2.0 for MicroStrategy, Documentation

This connector is available for both Linux and Windows systems.

Cloudera ODBC Connector 2.5 for Qlikview

Cloudera ODBC Connector 2.5 for Qlikview, Documentation

This connector is available for both Linux and Windows systems.

Cloudera ODBC Connector 1.2 for Tableau

Cloudera ODBC Connector 1.2 for Tableau, Documentation

This connector is available for Windows systems only.

Previous ODBC Connectors

Downloads for Previous ODBC Connectors

This connector is available for both Windows and Linux systems. The latest connector versions have standalone installation guides linked from their respective download pages, while installation for this older connector version is documented in the following section, Configuration Instructions for Cloudera ODBC Connector 1.2.
  Important: If you are using the Cloudera Connector for Tableau, to connect Impala to your Kerberos-secured CDH clusters, contact your Tableau account representative for an updated Tableau Data-connection Customization (TDC) file. The updated TDC file will override the Tableau connection settings to set specific parameters on the connection string that are required for a secure connection.

Configuration Instructions for Cloudera ODBC Connector 1.2

The instructions in this section apply to the older (1.2) version of the Cloudera ODBC driver. Most applications now use the 2.x drivers, which have separate installation instructions, as listed in Configuring Impala to Work with ODBC. For the ODBC driver 1.2 configuration instructions, refer to the following sections depending on your platform:

Configuring MicroStrategy to Use the Cloudera ODBC Driver 1.2 on Linux

You must use ODBC driver version 1.2 or later. Using earlier versions is not supported.

After downloading a connector, configure your systems to work with it.

  Note:

The unixODBC driver manager, which is often used to test drivers, requires odbc.ini be in the home directory. If you are using the unixODBC driver manager, you must prepare your environment by using a command similar to the following example to copy the odbc.ini file:

$ cp /etc/odbc.ini ~/odbc.ini

To configure Microstrategy to use ODBC on Linux:

  1. Use tar to extract and install the ODBC driver from the archive to a location of your choosing. For example, to install the driver to the standard location of /usr, use the following commands:
    tar -zxf ClouderaHiveODBC-v1.20.tar.gz -C /usr

    To install different versions, place the files in alternate locations such as /opt/.

  2. Create a system DSN for Impala by adding values to odbc.ini.
    [ODBC Data Sources]
    IMPALA-SERVER=Hive ODBC Driver
    
    [IMPALA-SERVER]
    Driver=ODBC_DRIVER_LIBS_PATH/libhiveodbc.so.1
    Description=Hive ODBC Driver
    Host=IMPALAD_HOST
    Port=IMPALAD_PORT
    Database=
    FRAMED=0
    Trace=Yes
    TraceFile=/tmp/odbc.log
    Type=Beeswax
      Note: In the preceding example, replace values as follows: ODBC_DRIVER_LIBS_PATH: The full ODBC driver library path. This is often /usr/lib. IMPALAD_HOST: The fully qualified hostname of any node running the impalad process. IMPALAD_PORT: The port number for the impalad process. The default is 21000.
  3. Implement environment variable setting by adding values to the end of ODBC.sh.
    #
    # ODBC Driver for Hive
    #
    HIVE_CONFIG='ODBC_DRIVER_INSTALL_PATH'
    if [ "${HIVE_CONFIG}" != '<HIVE_CONFIG>' ]; then
    export HIVE_CONFIG
    
    mstr_append_path LD_LIBRARY_PATH "${HIVE_CONFIG:?}"/lib
    export LD_LIBRARY_PATH
    
    mstr_append_path PATH "${HIVE_CONFIG:?}"/bin
    export PATH
    fi
      Note: In the preceding example, replace ODBC_DRIVER_INSTALL_PATH with the full path to the parent directory of the driver libraries and include files. This is often /usr.
  4. Add a section for the ODBC driver to odbcinst.ini.
    [ODBC Drivers]
    Hive Driver=Installed
    
    [Hive Driver]
    Driver=ODBC_DRIVER_LIBS_PATH/libhiveodbc.so.1
    Description=Hive Driver
    Setup=ODBC_DRIVER_LIBS_PATH/libhiveodbc.so.1
    APILevel=2
    ConnectFunctions=YYY
    DriverODBCVer=1.0
    FileUsage=0
    SQLLevel=1
      Note: In the preceding example, replace ODBC_DRIVER_LIBS_PATH with the full path to the ODBC driver libraries. This is often /usr/lib.

Configuring MicroStrategy and Qlikview to Use the Cloudera ODBC Driver 1.2 on Windows

After downloading a connector, configure your systems to work with it.

To configure Microstrategy, Qlikview, or other tools except Tableau to use ODBC on Windows:

  1. Run the downloaded .exe file to invoke the installation wizard. You will need to accept the license agreement, and accept or modify the installation location. The driver currently comes only in a 32-bit version, but can also run on a 64-bit system.
      Note: You can perform a silent (non-interactive) installation by running the install executable as <Install_exe>/S /D="<Dir_to_install>". For example: ClouderaHiveODBCSetup_v1_20.exe /S /D="C:\Program Files (x86)\Hive ODBC" will install the driver and the uninstall script in the C:\Program Files (x86)\Hive ODBC directory. The license is accepted automatically in this mode.
  2. Open the ODBC Data Source Administrator. You must use the 32-bit version.
  3. Click System DSN tab, then click Add.
  4. Select Cloudera ODBC Driver for Apache Hive and click Finish.
  5. Enter values for the following fields:
    • Data Source Name – Name for this DSN.
    • Host – Fully qualified hostname for a node running the impalad process.
    • Port – Port number for the impalad process. The default is 21000.
    • Type – Beeswax
  6. Click OK.

Steps for Tableau on Windows

If you would like to use Tableau with the Cloudera Connector for Tableau, use the same Tableau documentation as Hive. Tableau does not use the system DSN configuration described here, but instead requires configuring the connection within Tableau itself.

Known Issues and Limitations of the Cloudera ODBC Connector Version 1.2

Users of this ODBC driver should take the time to familiarize themselves with Impala and the limitations when using it with the Cloudera ODBC Connector.

Impala is not a relational database, and does not support many of the features that are typically found in relational database systems. Missing features include the following:

  • No support for transactions.
  • No support for cursors or scrollable cursors.
  • No support for row level inserts, updates, or deletes.
  • No explicit support for canceling a running query.
  • No support for prepared statements.

Users should be aware of the SQL functionality supported by Impala. Users are encouraged to read the Language Reference and check with the application developer to confirm it is supported on Impala before attempting to use this driver.

These limitations affect the driver in two ways:

  • Many types of SQL statements can not be executed through the driver because the underlying system does not support them.
  • Many functions in the ODBC API can not be supported on top of Hive because Hive does not provide the required infrastructure.

The following ODBC functions are not supported by the driver:

  • SQLBindParameter
  • SQLBrowseConnect
  • SQLBulkOperations
  • SQLCancel
  • SQLCloseCursor
  • SQLColumnPrivileges
  • SQLCopyDesc
  • SQLDataSources
  • SQLDrivers
  • SQLEndTrans
  • SQLForeignKeys
  • SQLGetCursorName
  • SQLGetDescField
  • SQLGetDescRec
  • SQLGetTypeInfo
  • SQLMoreResults
  • SQLNativeSQL
  • SQLParamOptions
  • SQLPrepare
  • SQLPrimaryKeys
  • SQLProcedureColumns
  • SQLProcedures
  • SQLSetCursorName
  • SQLSetDescField
  • SQLSetDescRec
  • SQLSetPos
  • SQLSetScrollOptions
  • SQLSpecialColumns
  • SQLStatistics
  • SQLTablePrivileges
  • SQLTransact

The driver provides support for the following ODBC functions, though bugs may be present:

  • SQLAllocConnect
  • SQLAllocEnv
  • SQLAllocHandle
  • SQLAllocStmt
  • SQLBindCol
  • SQLColAttribute
  • SQLColAttributes
  • SQLColumns
  • SQLConnect
  • SQLDescribeCol
  • SQLDescribeParam
  • SQLDisconnect
  • SQLDriverConnect
  • SQLError
  • SQLExecDirect
  • SQLExecute
  • SQLFetch
  • SQLFreeConnect
  • SQLFreeEnv
  • SQLFreeHandle
  • SQLFreeStmt
  • SQLGetConnectAttr
  • SQLGetConnectOption
  • SQLGetConnectionOption
  • SQLGetData
  • SQLGetDiagField
  • SQLGetDiagRec
  • SQLGetEnvAttr
  • SQLGetFunctions
  • SQLGetInfo
  • SQLGetStmtAttr
  • SQLGetStmtOption
  • SQLNumParams
  • SQLNumResultCols
  • SQLParamData
  • SQLPutData
  • SQLRowCount (Always returns -1)
  • SQLSetConnectAttr
  • SQLSetEnvAttr
  • SQLSetParam
  • SQLSetStmtAttr
  • SQLSetStmtOption
  • SQLTables