Versions Compared

Old Version 190

changes.mady.by.user David Mollitor

Saved on

compared with

New Version 191

changes.mady.by.user David Mollitor

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Updated JDBC Driver information for the JDBC fetch size

...

Command

Description

!<SQLLine command>

List of SQLLine commands available at http://sqlline.sourceforge.net/.

Example: !quit exits the Beeline client.

!delimiter

Set the delimiter for queries written in Beeline. Multi-character delimiters are allowed, but quotation marks, slashes, and -- are not allowed. Defaults to ;

Usage: !delimiter $$

Version: 3.0.0 (HIVE-10865)

Beeline Properties

PropertyDescription
fetchsize

Standard JDBC enables you to specify the number of rows fetched with each database round-trip for a query, and this number is referred to as the fetch size.
Setting the fetch size in Beeline overrides the JDBC driver's default fetch size and affects subsequent statements executed in the current session.

  1. A value of -1 instructs Beeline to use the JDBC driver's default fetch size (default)
  2. A value of zero or more is passed to the JDBC driver for each statement
  3. Any other negative value will throw an Exception

    Usage: !set fetchsize 200

Version: 4.0.0 (HIVE-22853)

Beeline Hive Commands

Hive specific commands (same as Hive CLI commands) can be run from Beeline, when the Hive JDBC driver is used.

...

Code Block
languagejava
static Connection getConnection( Subject signedOnUserSubject ) throws Exception{
       Connection conn = (Connection) Subject.doAs(signedOnUserSubject, new PrivilegedExceptionAction<Object>()
           {
               public Object run()
               {
                       Connection con = null;
                       String JDBC_DB_URL = "jdbc:hive2://HiveHost:10000/default;" ||
                                              "principal=hive/localhost.localdomain@EXAMPLE.COM;" || 
                                              "kerberosAuthType=fromSubject";
                       try {
                               Class.forName(JDBC_DRIVER);
                               con =  DriverManager.getConnection(JDBC_DB_URL);
                       } catch (SQLException e) {
                               e.printStackTrace();
                       } catch (ClassNotFoundException e) {
                               e.printStackTrace();
                       }
                       return con;
               }
           });
       return conn;
}

FetchSize for ResultSets

In the order of precedence, Hive JDBC driver uses the following criteria to determine fetchSize for ResultSet.

  1. Fetch size is set based on what is received from HS2 (hive.server2.thrift.resultset.default.fetch.size) during the client session open sequence
  2. Fetch size is set in the JDBC connection string (not well documented: jdbc:hive2://localhost:10000;fetchSize=100)
  3. Fetch size is set by the application code via JDBC setFetchSize [1]

...

JDBC Fetch Size

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed by the client.  The default value, used for every statement, can be specified through the JDBC connection string.  This default value may subsequently be overwritten, per statement, with the JDBC API.  If no value is specified within the JDBC connection string, then the default fetch size is retrieved from the HiveServer2 instance as part of the session initiation operation.

jdbc:hive2://<host>:<port>/<db>;fetchsize=<value>


Info
titleFetch Size Details

The Hive JDBC driver will receive a preferred fetch size from the instance of HiveServer2 it has connected to.  This value is specified on the server by the hive.server2.thrift.resultset.

...

default.fetch.size

...

 configuration.

The JDBC fetch size is only a hint and the server will attempt to respect the client's requested fetch size though with some limits.  HiveServer2 will cap all requests at a maximum value specified by the hive.server2.thrift.resultset.max.fetch.size configuration value regardless of the client's requested fetch size.

While a larger fetch size may limit the number of round-trips between the client and server, it does so at the expense of additional memory requirements on the client and server.

The default JDBC fetch size value may be overwritten, per statement, with the JDBC API:

  • Setting a value of 0 instructs the driver to use the fetch size value preferred by the server
  • Setting a value greater than zero will instruct the driver to fetch that many rows, though the actual number of rows returned may be capped by the server
  • If no fetch size value is explicitly set on the JDBC driver's statement then the driver's default value is used
    • If the fetch size value is specified within the JDBC connection string, this is the default value
    • If the fetch size value is absent from the JDBC connection string, the server's preferred fetch size is used as the default value

Python Client

A Python client driver is available on github. For installation instructions, see Setting Up HiveServer2: Python Client Driver.

...