Supplementary Hive Documentation

Overview

HiveServer2

JDBC

Hive Client

Hive Client

HiveQL Language

Hive Security

Authorization

Hive API

Javadocs

HiveServer2 JDBC

HiveServer2 JDBC

Using JDBC
JDBC Data Types
Classes and Supported Methods

Using JDBC

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

Load the HiveServer2 JDBC driver.

For example:

Class.forName("org.apache.hive.jdbc.HiveDriver");
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.
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");
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
Connection
DatabaseMetaData
Driver
PreparedStatement
ResultSet
ResultSetMetaData
Statement

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)

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

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"

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)

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)`