...

% bin/beeline
beeline> !connect jdbc:hive2://<host>:<port>/<db>;auth=noSasl hiveuser pass 

Beeline Commands

Beeline Hive Commands

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

Use ";" (semicolon) to terminate commands. Comments in scripts can be specified using the "--" prefix.

Beeline Command Options

The Beeline CLI supports these command line options:

Output Formats

In Beeline, the result can be displayed in different formats. The format mode can be set with the outputformat option.

The following output formats are supported:

• table82903124
• vertical82903124
• xmlattr82903124
• xmlelements82903124
• separated-value formats (csv, tsv, csv2, tsv2, dsv)

...

nohup beeline --silent=true --showHeader=true --outputformat=dsv -f query.hql </dev/null > /tmp/output.log 2> /tmp/error.log &

JDBC

HiveServer2 has a JDBC driver. It supports both embedded and remote access to HiveServer2. Remote HiveServer2 mode is recommended for production use, as it is more secure and doesn't require direct HDFS/metastore access to be granted for users.

Connection URLs

Connection URL Format

The HiveServer2 URL is a string with the following syntax:

jdbc:hive2://<host1>:<port1>,<host2>:<port2>/dbName;initFile=<file>;sess_var_list?hive_conf_list#hive_var_list

where

• <host1>:<port1>,<host2>:<port2> is a server instance or a comma separated list of server instances to connect to (if dynamic service discovery is enabled). If empty, the embedded server will be used.
• dbName is the name of the initial database.
• <file> is the path of init script file (Hive 2.2.0 and later). This script file is written with SQL statements which will be executed automatically after connection. This option can be empty. 
• sess_var_list is a semicolon separated list of key=value pairs of session variables (e.g., user=foo;password=bar).
• hive_conf_list is a semicolon separated list of key=value pairs of Hive configuration variables for this session
• hive_var_list is a semicolon separated list of key=value pairs of Hive variables for this session.

Special characters in sess_var_list, hive_conf_list, hive_var_list parameter values should be encoded with URL encoding if needed.

Connection URL for Remote or Embedded Mode

The JDBC connection URL format has the prefix jdbc:hive2:// and the Driver class is org.apache.hive.jdbc.HiveDriver. Note that this is different from the old HiveServer.

• For a remote server, the URL format is jdbc:hive2://<host>:<port>/<db>;initFile=<file> (default port for HiveServer2 is 10000).
• For an embedded server, the URL format is jdbc:hive2:///;initFile=<file> (no host or port).

The initFile option is available in Hive 2.2.0 and later releases.

Connection URL When HiveServer2 Is Running in HTTP Mode

JDBC connection URL:  jdbc:hive2://<host>:<port>/<db>;transportMode=http;httpPath=<http_endpoint>, where:

...

Fetch size

In order of precedence, the fetch size for beeline is determined using :

1. If the beeline user does nothing, each query will use the fetch size received from HS2
2. If the beeline user sets the fetchSize in the JDBC connection string, each query will use the fetch size specified there
3. If the user wants to set the fetchSize in the session, they can with the syntax: !set fetchSize xxx
   1. Setting a fetchSize of 0 will direct the driver to use the fetch size provided from HS2
   2. Setting a fetchSize greater than 0 will set the driver fetch size to the specified value
   3. Setting a fetchSize of -1 directs beeline to use the default JDBC default behavior: use the connection string fetchSize and, if none is specified, fallback to the fetch size specified by HS2 (this is the default beeline fetchSize value)
   4. Setting a fetchSize of any other negative integer value is an error

Keeping in mind that whatever the client requests for a fetch size will be overruled on the HiveServer, for every FetchResults request, depending on the configured value for hive.server2.thrift.resultset.max.fetch.size. When a client requests a fetchSize larger than the max, a WARN message is emitted into the HS2 logs for further investigation and to direct clients to adjust their expectations (and configurations).

[1] https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#setFetchSize-int-

JDBC

HiveServer2 has a JDBC driver. It supports both embedded and remote access to HiveServer2. Remote HiveServer2 mode is recommended for production use, as it is more secure and doesn't require direct HDFS/metastore access to be granted for users.

Connection URLs

Connection URL Format

The HiveServer2 URL is a string with the following syntax:

jdbc:hive2://<host1>:<port1>,<host2>:<port2>/dbName;initFile=<file>;sess_var_list?hive_conf_list#hive_var_list

where

• <host1>:<port1>,<host2>:<port2> is a server instance or a comma separated list of server instances to connect to (if dynamic service discovery is enabled). If empty, the embedded server will be used.
• dbName is the name of the initial database.
• <file> is the path of init script file (Hive 2.2.0 and later). This script file is written with SQL statements which will be executed automatically after connection. This option can be empty. 
• sess_var_list is a semicolon separated list of key=value pairs of session variables (e.g., user=foo;password=bar).
• hive_conf_list is a semicolon separated list of key=value pairs of Hive configuration variables for this session
• hive_var_list is a semicolon separated list of key=value pairs of Hive variables for this session.

Special characters in sess_var_list, hive_conf_list, hive_var_list parameter values should be encoded with URL encoding if needed.

Connection URL for Remote or Embedded Mode

The JDBC connection URL format has the prefix jdbc:hive2:// and the Driver class is org.apache.hive.jdbc.HiveDriver. Note that this is different from the old HiveServer.

...

Connection URL When SSL Is Enabled in HiveServer2

JDBC connection URL:  jdbc:hive2://<host>:<port>/<db>;ssl=true;sslTrustStore=<trust_store_path>;trustStorePassword=<trust_store_password>, where:

• <trust_store_path> is the path where client's truststore file lives.
• <trust_store_password> is the password to access the truststore.

...

• For a remote server, the URL format is

• jdbc:hive2://<host>:<port>/<db>;

...

• initFile=<file> (default port for HiveServer2 is 10000).
• For an embedded server, the URL format is jdbc:hive2:///;initFile=<file> (no host or port).

The initFile option is available in Hive 2.2.0 and later releases.

Connection URL When HiveServer2 Is Running in HTTP Mode

JDBC connection URL:  jdbc:hive2://<host>:<port>/<db>;transportMode=http;httpPath=<http_endpoint>, where:

• <http_endpoint> is the corresponding HTTP endpoint configured in hive-site.xml. Default value is cliservice.
• Default port for HTTP transport mode is 10001.

  Anchor
  HIVE-6972 HIVE-6972

Connection URL When SSL Is Enabled in HiveServer2

...

For versions earlier than 0.14, see the version note above.

Connection URL When ZooKeeper Service Discovery Is Enabled

ZooKeeper-based service discovery introduced in Hive 0.14.0 (HIVE-7935) enables high availability and rolling upgrade for HiveServer2. A JDBC URL that specifies <zookeeper quorum> needs to be used to make use of these features.

With further changes in Hive 2.0.0 and 1.3.0 (unreleased, HIVE-11581), none of the additional configuration parameters such as authentication mode, transport mode, or SSL parameters need to be specified, as they are retrieved from the ZooKeeper entries along with the hostname.

The JDBC connection URL: jdbc:hive2://<zookeeper quorum>/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2 .

The <zookeeper quorum> is the same as the value of hive.zookeeper.quorum configuration parameter in hive-site.xml/hivserver2-site.xml used by HiveServer2.

Additional runtime parameters needed for querying can be provided within the URL as follows, by appending it as a ?<option> as before.
The JDBC connection URL:  jdbc:hive2://<zookeeper quorum><host>:<port>/<db>;serviceDiscoveryModessl=zooKeeper;zooKeeperNamespace=hiveserver2?tez.queue.name=hive1&hive.server2.thrift.resultset.serialize.in.tasks=true 

Named Connection URLs

As of Hive 2.1.0 (HIVE-13670), Beeline now also supports named URL connect strings via usage of environment variables. If you try to do a !connect to a name that does not look like a URL, then Beeline will attempt to see if there is an environment variable called BEELINE_URL_<name>. For instance, if you specify !connect blue, it will look for BEELINE_URL_BLUE, and use that to connect. This should make it easier for system administrators to specify environment variables for users, and users need not type in the full URL each time to connect.

Reconnecting

Traditionally, !reconnect has worked to refresh a connection that has already been established. It is not able to do a fresh connect after !close has been run. As of Hive 2.1.0 (HIVE-13670), Beeline remembers the last URL successfully connected to in a session, and is able to reconnect even after a !close has been run. In addition, if a user does a !save, then this is saved in the beeline.properties file, which then allows !reconnect to connect to this saved last-connected-to URL across multiple Beeline sessions. This also allows the use of  beeline -r  from the command line to do a reconnect on startup.

Using hive-site.xml to automatically connect to HiveServer2

true;sslTrustStore=<trust_store_path>;trustStorePassword=<trust_store_password>, where:

• <trust_store_path> is the path where client's truststore file lives.
• <trust_store_password> is the password to access the truststore.

In HTTP mode:  jdbc:hive2://<host>:<port>/<db>;ssl=true;sslTrustStore=<trust_store_path>;trustStorePassword=<trust_store_password>;transportMode=http;httpPath=<http_endpoint>.

For versions earlier than 0.14, see the version note above.

Connection URL When ZooKeeper Service Discovery Is Enabled

ZooKeeper-based service discovery introduced in Hive 0.14.0 (HIVE-7935) enables high availability and rolling upgrade for HiveServer2. A JDBC URL that specifies <zookeeper quorum> needs to be used to make use of these features.

With further changes in Hive 2.0.0 and 1.3.0 (unreleased, HIVE-11581), none of the additional configuration parameters such as authentication mode, transport mode, or SSL parameters need to be specified, as they are retrieved from the ZooKeeper entries along with the hostname.

The JDBC connection URL: jdbc:hive2://<zookeeper quorum>/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2 .

The <zookeeper quorum> is the same as the value of hive.zookeeper.quorum configuration parameter in hive-site.xml/hivserver2-site.xml used by HiveServer2.

Additional runtime parameters needed for querying can be provided within the URL as follows, by appending it as a ?<option> as before.

The JDBC connection URL: jdbc:hive2://<zookeeper quorum>/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2?tez.queue.name=hive1&hive.server2.thrift.resultset.serialize.in.tasks=true 

Named Connection URLs

As of Hive 2.1.0 (HIVE-13670), Beeline now also supports named URL connect strings via usage of environment variables. If you try to do a !connect to a name that does not look like a URL, then Beeline will attempt to see if there is an environment variable called BEELINE_URL_<name>. For instance, if you specify !connect blue, it will look for BEELINE_URL_BLUE, and use that to connect. This should make it easier for system administrators to specify environment variables for users, and users need not type in the full URL each time to connect.

Reconnecting

Traditionally, !reconnect has worked to refresh a connection that has already been established. It is not able to do a fresh connect after !close has been run. As of Hive 2.1.0 (HIVE-13670), Beeline remembers the last URL successfully connected to in a session, and is able to reconnect even after a !close has been run. In addition, if a user does a !save, then this is saved in the beeline.properties file, which then allows !reconnect to connect to this saved last-connected-to URL across multiple Beeline sessions. This also allows the use of  beeline -r  from the command line to do a reconnect on startup.

Using hive-site.xml to automatically connect to HiveServer2

As of Hive 2.2.0 (HIVE-14063), Beeline adds support to use the hive-site.xml As of Hive 2.2.0 (HIVE-14063), Beeline adds support to use the hive-site.xml present in the classpath to automatically generate a connection URL based on the configuration properties in hive-site.xml and an additional user configuration file. Not all the URL properties can be derived from hive-site.xml and hence in order to use this feature user must create a configuration file called “beeline-hs2-connection.xml” which is a Hadoop XML format file. This file is used to provide user-specific connection properties for the connection URL. Beeline looks for this configuration file in ${user.home}/.beeline/ (Unix based OS) or ${user.home}\beeline\ directory (in case of Windows). If the file is not found in the above locations Beeline looks for it in ${HIVE_CONF_DIR} location and /etc/hive/conf (check HIVE-16335 which fixes this location from /etc/conf/hive in Hive 2.2.0) in that order. Once the file is found, Beeline uses beeline-hs2-connection.xml in conjunction with the hive-site.xml in the class path to determine the connection URL.

...

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

JDBC Client Setup for a Secure Cluster

...

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]

Keeping in mind that whatever the client requests for a fetch size will be overruled on the HiveServer, for every FetchResults request, depending on the configured value for hive.server2.thrift.resultset.max.fetch.size. When a client requests a fetchSize larger than the max, a WARN message is emitted into the HS2 logs for further investigation and to direct clients to adjust their expectations (and configurations).

[1] https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#setFetchSize-int-

Python Client

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

...

For versions earlier than 0.14, see the version note above.

Passing HTTP Header Key/Value Pairs via JDBC Driver

...

For versions earlier than 0.14, see the version note above. 

Passing Custom HTTP Cookie Key/Value Pairs via JDBC Driver

...