Using ODBC to Connect to HiveServer2

This section contains details about setting up and using the ODBC Connector for Hive.

Before You Begin

The Data Fabric Hive ODBC Connector is an ODBC driver for Apache Hive 0.7.0 and later that complies with the ODBC 3.52 specification. You can download the Hive ODBC connector from https://package.ezmeral.hpe.com/tools/MapR-ODBC/MapR_Hive/. To access the repository, see Using the HPE Ezmeral Token-Authenticated Internet Repository.

After downloading the driver, refer to the documentation for Hive ODBC Driver to install and configure the driver. The Hive ODBC Driver supports the following Advanced Options:

  • Enable Auto Reconnect
  • Driver Config Take Precedence
  • Fast SQL Prepare
  • Get Tables With Query
  • Invalid Session Auto Recover
  • Show System Table
  • Socket Timeout
  • Default String Column Length
  • Rows Fetched Per Block
  • Use Native Query

To use the ODBC driver, configure a Data Source Name (DSN), a definition that specifies how to connect to Hive. DSNs are typically managed by the operating system and may be used by multiple applications. Some applications do not use DSNs. You will need to refer to your particular application’s documentation to understand how it connects using ODBC.

The standard query language for ODBC is SQL. HiveQL, the standard query language for Hive, includes a subset of ANSI SQL-92. Applications that connect to Hive using ODBC may need queries altered if the queries use SQL features that are not present in Hive. Applications that use SQL will recognize HiveQL, but might not provide access to HiveQL-specific features such as multi-table insert.

Please refer to the Hive Language Manual for up-to-date information on HiveQL.

The SQL Connector

The SQL Connector feature translates standard SQL-92 queries into equivalent HiveQL queries. The SQL Connector performs syntactical translations and structural transformations. For example:

  • Quoted Identifiers: When quoting identifiers, HiveQL uses back quotes (`), while SQL uses double quotes ("). Even when a driver reports the back quote as the quote character, some applications still generate double-quoted identifiers.
  • Table Aliases: HiveQL does not support the AS keyword between a table reference and its alias.
  • The JOIN, INNER JOIN, and CROSS JOIN SQL syntaxes are translated to the HiveQL JOIN syntax.
  • SQL TOP N queries are transformed to HiveQL LIMIT queries.