HiveServer2 JDBC

Using JDBC

You can use JDBC to access data stored in a relational database or other tabular format.

  1. Load the HiveServer2 JDBC driver.

    For example:

    Class.forName("org.apache.hive.jdbc.HiveDriver");
  2. Connect to the database by creating a Connection object with the JDBC driver.

    For example:

    Connection cnct = DriverManager.getConnection("jdbc:hive2://<host>:<port>", "<user>", "<password>");

    The default <port> is 10000. In non-secure configurations, specify a <user> for the query to run as. The <password> field value is ignored in non-secure mode.

    Connection cnct = DriverManager.getConnection("jdbc:hive2://<host>:<port>", "<user>", "");

    In Kerberos secure mode, the user information is based on the Kerberos credentials.

  3. Submit SQL to the database by creating a Statement object and using its executeQuery() method.

    For example:

    Statement stmt = cnct.createStatement(); ResultSet rset = stmt.executeQuery("SELECT foo FROM bar");
  4. Process the result set, if necessary.

JDBC Data Types

The following table lists the data types implemented for HiveServer2 JDBC.

Hive Type Java Type Specification
TINYINT byte signed or unsigned 1-byte integer
SMALLINT short signed 2-byte integer
INT int signed 4-byte integer
BIGINT long signed 8-byte integer
FLOAT double single-precision number (approximately 7 digits)
DOUBLE double double-precision number (approximately 15 digits)
DECIMAL java.math.BigDecimal fixed-precision decimal value
BOOLEAN boolean a single bit (0 or 1)
STRING String character string or variable-length character string
TIMESTAMP java.sql.Timestamp date and time value
BINARY String binary data
Complex Types
ARRAY String -- json encoded values of one data type
MAP String -- json encoded key-value pairs
STRUCT String -- json encoded structured values

The table below indicates which data types are currently implemented for HiveServer2 JDBC.

\ Java Class
-- -- -- -- -- --
Data Type \

PreparedStatement

ResultSet

ResultSetMetaData

JdbcColumn
boolean X X X X
byte X X
tinyint X X
smallint X X
int X X X X
bigint X X
float X X X X
double X X X X
decimal X X X
timestamp X X X X
binary X
string X X X X
Complex Types
array<> X X
map<> X X
struct<> X X

Classes and Supported Methods

This section lists the JDBC classes and methods implemented for HiveServer2.

CallableStatement

CallableStatement implements the java.sql.CallableStatement interface.

There is one supported CallableStatement method:

Supported CallableStatement method in HiveServer2
executeQuery()

In the current release, all other CallableStatement methods are unsupported.

Unsupported CallableStatement methods -- throw SQLException "Method not supported"
(All other CallableStatement methods)

Connection

Connection implements the java.sql.Connection interface.

Supported Connection methods in HiveServer2 Comments
clearWarnings()
close()
createStatement()
getAutoCommit() hard coded to true
getCatalog() hard coded to empty string
getMetaData()
getTransactionIsolation() hard coded to TRANSACTION_NONE
getWarnings()
isClosed()
isReadOnly() hard coded to false
prepareStatement(String sql)
prepareStatement(String sql, int autoGeneratedKeys) second argument is ignored
prepareStatement(String sql, int resultSetType, int resultSetConcurrency) arguments 2 and 3 are ignored
setAutoCommit(boolean autoCommit) throws exception if auto-commit is turned on
setTransactionIsolation(int level) empty implementation

Unsupported Connection methods -- throw SQLException "Method not supported"
commit()
createArrayOf(String typeName, Object[] elements)
createBlob()
createClob()
createNClob()
createSQLXML()
createStatement(int resultSetType, int resultSetConcurrency)
createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
createStruct(String typeName, Object[] attributes)
getClientInfo()
getClientInfo(String name)
getHoldability()
getTypeMap()
isValid(int timeout)
nativeSQL(String sql)
prepareCall(String sql)
prepareCall(String sql, int resultSetType, int resultSetConcurrency)
prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability)
prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability)
prepareStatement(String sql, int[] columnIndexes)
prepareStatement(String sql, String[] columnNames)
releaseSavepoint(Savepoint savepoint)
rollback()
rollback(Savepoint savepoint)
setCatalog(String catalog)
setClientInfo(Properties properties)
setClientInfo(String name, String value)
setHoldability(int holdability)
setReadOnly(boolean readOnly)
setSavepoint()
setSavepoint(String name)
setTypeMap(Map<String,Class<?>> map)

DatabaseMetaData

DatabaseMetaData implements the java.sql.DatabaseMetaData interface.

Supported DatabaseMetaData methods in HiveServer2 Comments
allTablesAreSelectable() hard coded to true
getCatalogs()
getCatalogSeparator() hard coded to CATALOG_SEPARATOR
getCatalogTerm() hard coded to instance
getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
getDatabaseProductName() hard coded to Hive
getDatabaseProductVersion() hard coded to 0.10.0
getDefaultTransactionIsolation() hard coded to TRANSACTION_NONE
getDriverMajorVersion()
getDriverMinorVersion()
getDriverName()
getDriverVersion()
getExtraNameCharacters() returns empty string
getFunctions(String catalog, String schemaPattern, String functionNamePattern)
getImportedKeys(String catalog, String schema, String table)
getJDBCMajorVersion() returns 3
getJDBCMinorVersion() returns 0
getMaxColumnNameLength()
getNumericFunctions() hard coded to the empty string
getPrimaryKeys(String catalog, String schema, String table) returns empty
getProcedureColumns(String catalog, String schemaPattern, String procedureNamePattern, String columnNamePattern) returns empty
getProcedureTerm() returns "UDF" string
getProcedures() returns empty
getSchemas()
getSchemas(String catalog, String schemaPattern)
getSchemaTerm() returns "database"
getSearchStringEscape() returns $SEARCH_STRING_ESCAPE
getSQLStateType() returns sqlStateSQL99
getStringFunctions() hard coded to the empty string
getSystemFunctions() hard coded to the empty string
getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)
getTableTypes()
getTimeDateFunctions() hard coded to the empty string
getTypeInfo()
getUDTs(String catalog, String schemaPattern, String typeNamePattern, int[] types) returns empty
supportsAlterTableWithAddColumn()
supportsAlterTableWithDropColumn()
supportsBatchUpdates()
supportsCatalogsInDataManipulation()
supportsCatalogsInIndexDefinitions()
supportsCatalogsInPrivilegeDefinitions()
supportsCatalogsInProcedureCalls()
supportsCatalogsInTableDefinitions()
supportsColumnAliasing()
supportsGroupBy()
supportsMultipleResultSets()
supportsNonNullableColumns()
supportsOuterJoins()
supportsPositionedDelete()
supportsPositionedUpdate()
supportsResultSetHoldability(int holdability)
supportsResultSetType(int type)
supportsSavepoints()
supportsSchemasInDataManipulation()
supportsSchemasInIndexDefinitions()
supportsSchemasInPrivilegeDefinitions()
supportsSchemasInProcedureCalls()
supportsSchemasInTableDefinitions()
supportsSelectForUpdate()
supportsStoredProcedures()
supportsTransactions()

Unsupported DatabaseMetaData methods -- throw SQLException "Method not supported"
allProceduresAreCallable()
autoCommitFailureClosesAllResultSets()
dataDefinitionCausesTransactionCommit()
dataDefinitionIgnoredInTransactions()
deletesAreDetected(int type)
doesMaxRowSizeIncludeBlobs()
getAttributes(String catalog, String schemaPattern, String typeNamePattern, String attributeNamePattern)
getBestRowIdentifier(String catalog, String schema, String table, int scope, boolean nullable)
getClientInfoProperties()
getColumnPrivileges(String catalog, String schema, String table, String columnNamePattern)
getConnection()
getCrossReference(String parentCatalog, String parentSchema, String parentTable, String foreignCatalog, String foreignSchema, String foreignTable)
getDatabaseMajorVersion()
getDatabaseMinorVersion()
getExportedKeys(String catalog, String schema, String table)
getFunctionColumns(String catalog, String schemaPattern, String functionNamePattern, String columnNamePattern)
getIdentifierQuoteString()
getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate)
getMaxBinaryLiteralLength()
getMaxCatalogNameLength()
getMaxCharLiteralLength()
getMaxColumnsInGroupBy()
getMaxColumnsInIndex()
getMaxColumnsInOrderBy()
getMaxColumnsInSelect()
getMaxColumnsInTable()
getMaxConnections()
getMaxCursorNameLength()
getMaxIndexLength()
getMaxProcedureNameLength()
getMaxRowSize()
getMaxSchemaNameLength()
getMaxStatementLength()
getMaxStatements()
getMaxTableNameLength()
getMaxTablesInSelect()
getMaxUserNameLength()
getResultSetHoldability()
getRowIdLifetime()
getSearchStringEscape()
getSQLKeywords()
getSuperTables(String catalog, String schemaPattern, String tableNamePattern)
getSuperTypes(String catalog, String schemaPattern, String typeNamePattern)
getTablePrivileges(String catalog, String schemaPattern, String tableNamePattern)
getURL()
getUserName()
getVersionColumns(String catalog, String schema, String table)
insertsAreDetected(int type)
isCatalogAtStart()
isReadOnly()
locatorsUpdateCopy()
nullPlusNonNullIsNull()
nullsAreSortedAtEnd()
nullsAreSortedAtStart()
nullsAreSortedHigh()
nullsAreSortedLow()
othersDeletesAreVisible(int type)
othersInsertsAreVisible(int type)
othersUpdatesAreVisible(int type)
ownDeletesAreVisible(int type)
ownInsertsAreVisible(int type)
ownUpdatesAreVisible(int type)
storesLowerCaseIdentifiers()
storesLowerCaseQuotedIdentifiers()
storesMixedCaseIdentifiers()
storesMixedCaseQuotedIdentifiers()
storesUpperCaseIdentifiers()
storesUpperCaseQuotedIdentifiers()
supportsANSI92EntryLevelSQL()
supportsANSI92FullSQL()
supportsANSI92IntermediateSQL()
supportsConvert()
supportsConvert(int fromType, int toType)
supportsCoreSQLGrammar()
supportsCorrelatedSubqueries()
supportsDataDefinitionAndDataManipulationTransactions()
supportsDataManipulationTransactionsOnly()
supportsDifferentTableCorrelationNames()
supportsExpressionsInOrderBy()
supportsExtendedSQLGrammar()
supportsFullOuterJoins()
supportsGetGeneratedKeys()
supportsGroupByBeyondSelect()
supportsGroupByUnrelated()
supportsIntegrityEnhancementFacility()
supportsLikeEscapeClause()
supportsLimitedOuterJoins()
supportsMinimumSQLGrammar()
supportsMixedCaseIdentifiers()
supportsMixedCaseQuotedIdentifiers()
supportsMultipleOpenResults()
supportsMultipleTransactions()
supportsNamedParameters()
supportsOpenCursorsAcrossCommit()
supportsOpenCursorsAcrossRollback()
supportsOpenStatementsAcrossCommit()
supportsOpenStatementsAcrossRollback()
supportsOrderByUnrelated()
supportsResultSetConcurrency(int type, int concurrency)
supportsStatementPooling()
supportsStoredFunctionsUsingCallSyntax()
supportsSubqueriesInComparisons()
supportsSubqueriesInExists()
supportsSubqueriesInIns()
supportsSubqueriesInQuantifieds()
supportsTableCorrelationNames()
supportsTransactionIsolationLevel(int level)
supportsUnion()
supportsUnionAll()
updatesAreDetected(int type)
usesLocalFilePerTable()
usesLocalFiles()

Driver

Driver implements the java.sql.Driver interface.

Supported Driver methods in HiveServer2
acceptsURL(String url)
connect(String url, Properties info)
getMajorVersion()
getMinorVersion()
getPropertyInfo(String url, Properties info)
jdbcCompliant()

Unsupported Driver methods
(None)

PreparedStatement

PreparedStatement implements the java.sql.PreparedStatement interface.

Supported PreparedStatement methods in HiveServer2
clearParameters()
execute()
executeQuery()
executeUpdate()
getMaxRows()
getResultSet()
getUpdateCount()
getWarnings()
isClosed()
setBoolean(int parameterIndex, boolean x)
setByte(int parameterIndex, byte x)
setDouble(int parameterIndex, double x)
setFloat(int parameterIndex, float x)
setInt(int parameterIndex, int x)
setLong(int parameterIndex, long x)
setShort(int parameterIndex, short x)
setString(int parameterIndex, String x)
setTimestamp(int parameterIndex, Timestamp x)

Unsupported PreparedStatement methods -- throw SQLException "Method not supported"
addBatch()
cancel()
getMetaData()
getParameterMetaData()
setArray(int parameterIndex, Array x)
setAsciiStream(int parameterIndex, InputStream x)
setAsciiStream(int parameterIndex, InputStream x, int length)
setAsciiStream(int parameterIndex, InputStream x, long length)
setBigDecimal(int parameterIndex, BigDecimal x)
setBinaryStream(int parameterIndex, InputStream x)
setBinaryStream(int parameterIndex, InputStream x, int length)
setBinaryStream(int parameterIndex, InputStream x, long length)
setBlob(int parameterIndex, Blob x)
setBlob(int parameterIndex, InputStream inputStream)
setBlob(int parameterIndex, InputStream inputStream, long length)
setBytes(int parameterIndex, byte[] x)
setCharacterStream(int parameterIndex, Reader reader)
setCharacterStream(int parameterIndex, Reader reader, int length)
setCharacterStream(int parameterIndex, Reader reader, long length)
setClob(int parameterIndex, Clob x)
setClob(int parameterIndex, Reader reader)
setClob(int parameterIndex, Reader reader, long length)
setDate(int parameterIndex, Date x)
setDate(int parameterIndex, Date x, Calendar cal)
setNCharacterStream(int parameterIndex, Reader value)
setNCharacterStream(int parameterIndex, Reader value, long length)
setNClob(int parameterIndex, NClob value)
setNClob(int parameterIndex, Reader reader)
setNClob(int parameterIndex, Reader reader, long length)
setNString(int parameterIndex, String value)
setNull(int parameterIndex, int sqlType)
setNull(int parameterIndex, int sqlType, String typeName)
setObject(int parameterIndex, Object x)
setObject(int parameterIndex, Object x, int targetSqlType)
setObject(int parameterIndex, Object x, int targetSqlType, int scaleOrLength)
setQueryTimeout(int seconds)
setRef(int parameterIndex, Ref x)
setRowId(int parameterIndex, RowId x)
setSQLXML(int parameterIndex, SQLXML xmlObject)
setTime(int parameterIndex, Time x)
setTime(int parameterIndex, Time x, Calendar cal)
setTimestamp(int parameterIndex, Timestamp x, Calendar cal)
setUnicodeStream(int parameterIndex, InputStream x, int length)
setURL(int parameterIndex, URL x)

ResultSet

ResultSet extends BaseResultSet, which implements the java.sql.ResultSet interface.

Supported ResultSet methods in HiveServer2
clearWarnings()
close()
findColumn(String columnName)
getBigDecimal(int columnIndex)
getBigDecimal(int columnIndex, int scale)
getBigDecimal(String columnName)
getBigDecimal(String columnName, int scale)
getBoolean(int columnIndex)
getBoolean(String columnName)
getByte(int columnIndex)
getByte(String columnName)
getConcurrency()
getDate(int columnIndex)
getDate(String columnName)
getDouble(int columnIndex)
getDouble(String columnName)
getFetchDirection()
getFetchSize()
getFloat(int columnIndex)
getFloat(String columnName)
getInt(int columnIndex)
getInt(String columnName)
getLong(int columnIndex)
getLong(String columnName)
getMetaData()
getObject(int columnIndex)
getObject(String columnName)
getShort(int columnIndex)
getShort(String columnName)
getString(int columnIndex)
getString(String columnName)
getTimestamp(int columnIndex)
getTimestamp(String columnName)
getType()
getWarnings()
next()
setFetchSize(int rows)
wasNull()

Unsupported ResultSet methods -- throw SQLException "Method not supported"
absolute(int row)
afterLast()
beforeFirst()
cancelRowUpdates()
deleteRow()
first()
getArray(int columnIndex)
getArray(String columnName)
getAsciiStream(int columnIndex)
getAsciiStream(String columnName)
getBinaryStream(int columnIndex)
getBinaryStream(String columnName)
getBlob(int columnIndex)
getBlob(String columnName)
getBytes(int columnIndex)
getBytes(String columnName)
getCharacterStream(int columnIndex)
getCharacterStream(String columnName)
getClob(int columnIndex)
getClob(String columnName)
getCursorName()
getDate(int columnIndex, Calendar cal)
getDate(String columnName, Calendar cal)
getHoldability()
getNCharacterStream(int columnIndex)
getNCharacterStream(String columnName)
getNClob(int columnIndex)
getNClob(String columnLabel)
getNString(int columnIndex)
getNString(String columnLabel)
getObject(int columnIndex, Map<String,Class<?>> map)
getObject(String columnName, Map<String,Class<?>> map)
getRef(int columnIndex)
getRef(String columnName)
getRow()
getRowId(int columnIndex)
getRowId(String columnLabel)
getSQLXML(int columnIndex)
getSQLXML(String columnLabel)
getStatement()
getTime(int columnIndex)
getTime(int columnIndex, Calendar cal)
getTime(String columnName)
getTime(String columnName, Calendar cal)
getTimestamp(int columnIndex, Calendar cal)
getTimestamp(String columnName, Calendar cal)
getUnicodeStream(int columnIndex)
getUnicodeStream(String columnName)
getURL(int columnIndex)
getURL(String columnName)
insertRow()
isAfterLast()
isBeforeFirst()
isClosed()
isFirst()
isLast()
last()
moveToCurrentRow()
moveToInsertRow()
previous()
refreshRow()
relative(int rows)
rowDeleted()
rowInserted()
rowUpdated()
setFetchDirection(int direction)
updateArray(int columnIndex, Array x)
updateArray(String columnName, Array x)
updateAsciiStream(int columnIndex, InputStream x)
updateAsciiStream(int columnIndex, InputStream x, int length)
updateAsciiStream(int columnIndex, InputStream x, long length)
updateAsciiStream(String columnLabel, InputStream x)
updateAsciiStream(String columnName, InputStream x, int length)
updateAsciiStream(String columnLabel, InputStream x, long length)
updateBigDecimal(int columnIndex, BigDecimal x)
updateBigDecimal(String columnName, BigDecimal x)
updateBinaryStream(int columnIndex, InputStream x)
updateBinaryStream(int columnIndex, InputStream x, int length)
updateBinaryStream(int columnIndex, InputStream x, long length)
updateBinaryStream(String columnLabel, InputStream x)
updateBinaryStream(String columnName, InputStream x, int length)
updateBinaryStream(String columnLabel, InputStream x, long length)
updateBlob(int columnIndex, Blob x)
updateBlob(int columnIndex, InputStream inputStream)
updateBlob(int columnIndex, InputStream inputStream, long length)
updateBlob(String columnName, Blob x)
updateBlob(String columnLabel, InputStream inputStream)
updateBlob(String columnLabel, InputStream inputStream, long length)
updateBoolean(int columnIndex, boolean x)
updateBoolean(String columnName, boolean x)
updateByte(int columnIndex, byte x)
updateByte(String columnName, byte x)
updateBytes(int columnIndex, byte[] x)
updateBytes(String columnName, byte[] x)
updateCharacterStream(int columnIndex, Reader x)
updateCharacterStream(int columnIndex, Reader x, int length)
updateCharacterStream(int columnIndex, Reader x, long length)
updateCharacterStream(String columnLabel, Reader reader)
updateCharacterStream(String columnName, Reader reader, int length)
updateCharacterStream(String columnLabel, Reader reader, long length)
updateClob(int columnIndex, Clob x)
updateClob(int columnIndex, Reader reader)
updateClob(int columnIndex, Reader reader, long length)
updateClob(String columnName, Clob x)
updateClob(String columnLabel, Reader reader)
updateClob(String columnLabel, Reader reader, long length)
updateDate(int columnIndex, Date x)
updateDate(String columnName, Date x)
updateDouble(int columnIndex, double x)
updateDouble(String columnName, double x)
updateFloat(int columnIndex, float x)
updateFloat(String columnName, float x)
updateInt(int columnIndex, int x)
updateInt(String columnName, int x)
updateLong(int columnIndex, long x)
updateLong(String columnName, long x)
updateNCharacterStream(int columnIndex, Reader x)
updateNCharacterStream(int columnIndex, Reader x, long length)
updateNCharacterStream(String columnLabel, Reader reader)
updateNCharacterStream(String columnLabel, Reader reader, long length)
updateNClob(int columnIndex, NClob nClob)
updateNClob(int columnIndex, Reader reader)
updateNClob(int columnIndex, Reader reader, long length)
updateNClob(String columnLabel, NClob nClob)
updateNClob(String columnLabel, Reader reader)
updateNClob(String columnLabel, Reader reader, long length)
updateNString(int columnIndex, String nString)
updateNString(String columnLabel, String nString)
updateNull(int columnIndex)
updateNull(String columnName)
updateObject(int columnIndex, Object x)
updateObject(int columnIndex, Object x, int scaleOrLength)
updateObject(String columnName, Object x)
updateObject(String columnName, Object x, int scaleOrLength)
updateRef(int columnIndex, Ref x)
updateRef(String columnName, Ref x)
updateRow()
updateRowId(int columnIndex, RowId x)
updateRowId(String columnLabel, RowId x)
updateShort(int columnIndex, short x)
updateShort(String columnName, short x)
updateSQLXML(int columnIndex, SQLXML xmlObject)
updateSQLXML(String columnLabel, SQLXML xmlObject)
updateString(int columnIndex, String x)
updateString(String columnName, String x)
updateTime(int columnIndex, Time x)
updateTime(String columnName, Time x)
updateTimestamp(int columnIndex, Timestamp x)
updateTimestamp(String columnName, Timestamp x)

ResultSetMetaData

ResultSetMetaData implements the java.sql.ResultSetMetaData interface.

Supported ResultSetMetaData methods in HiveServer2 Comments
getColumnCount()
getColumnDisplaySize(int column)
getColumnLabel(int column)
getColumnName(int column)
getColumnType(int column)
getColumnTypeName(int column)
getPrecision(int column)
getScale(int column)
isAutoIncrement(int column) hard coded to false
isCurrency(int column) hard coded to false
isNullable(int column)

Unsupported ResultSetMetaData methods -- throw SQLException "Method not supported"
getCatalogName(int column)
getColumnClassName(int column)
getSchemaName(int column)
getTableName(int column)
isCaseSensitive(int column)
isDefinitelyWritable(int column)
isReadOnly(int column)
isSearchable(int column)
isSigned(int column)
isWritable(int column)

Statement

Statement implements the java.sql.Statement interface.

Supported Statement methods in HiveServer2
clearWarnings()
close()
execute(String sql)
executeQuery(String sql)
executeUpdate(String sql)
getMaxRows()
getResultSet()
getUpdateCount()
getWarnings()
isClosed()
setMaxRows(int max)

Unsupported Statement methods -- throw SQLException "Method not supported"
addBatch(String sql)
cancel()
clearBatch()
execute(String sql, int autoGeneratedKeys)
execute(String sql, int[] columnIndexes)
execute(String sql, String[] columnNames)
executeBatch()
executeUpdate(String sql, int autoGeneratedKeys)
executeUpdate(String sql, int[] columnIndexes)
executeUpdate(String sql, String[] columnNames)
getConnection()
getFetchDirection()
getFetchSize()
getGeneratedKeys()
getMaxFieldSize()
getMoreResults()
getMoreResults(int current)
getQueryTimeout()
getResultSetConcurrency()
getResultSetHoldability()
getResultSetType()
isPoolable()
setCursorName(String name)
setEscapeProcessing(boolean enable)
setFetchDirection(int direction)
setFetchSize(int rows)
setMaxFieldSize(int max)
setPoolable(boolean poolable)
setQueryTimeout(int seconds)