Hive Official User's manual-New hive CLI (Beeline CLI)

This text is translated by oneself, original address: https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients

Hive Official User's manual-New hive CLI (Beeline CLI)

This page describes the different clients supported by HiveServer2. Other HiveServer2 documents include: HiveServer2 Overview Setting up HiveServer2 Hive Configuration properties:hiveserver2

Version

Introduced in Hive version 0.11. See HIVE-2935. Beeline–command Line Shell beeline-command Shell

HiveServer2 supports a command line shellbeeline. It is a JDBC client (http://sqlline.sourceforge.net/) based on the Sqlline CLI. Sqlline the details of the document connection, it also applies to Beeline.

Implementing using Beeline instead of the hive CLI

Both embedded mode and remote mode are supported. In embedded mode, it runs an embedded hive (similar to the old hive CLI) However the remote mode is connected via thrift to a separate HiveServer2 process. Starting with the hive 0.1.4 version, Beeline has been used by HiveServer2 and it also prints log information from HiveServer2 to stderr for the queries it executes. The remote HiveServer2 mode is the recommended mode for use with hive products, which is more secure and does not require direct user empowerment of the Hdfs/metastore.

HiveServer2 only Accept authenticated thrift connection requests in remote mode even in HTTP mode, information body class number thrift payload Beeline Example

% bin/beeline 
Hive version 0.11.0-snapshot by Apache
beeline>!connect jdbc:hive2://localhost:10000 Scott Tiger
!connect jdbc:hive2://localhost:10000 Scott Tiger 
connecting to jdbc:hive2://localhost:10000
Connected to:hive (version 0.10.0)
driver:hive (version 0.10.0-snapshot)
Transaction isolation:transaction_ Repeatable_read
0:jdbc:hive2://localhost:10000> show tables;
Show tables;
+-------------------+
|     Tab_name      |
+-------------------+
| primitives        |
| src               |
| src1              |
| Src_json          |
| Src_sequencefile  |
| src_thrift        |
| srcbucket         |
| Srcbucket2        |
| srcpart           |
+-------------------+
9 rows selected (1.079 seconds)

You can also specify connection parameters on the command line. This means that you will be able to find the commands to use when connecting from your UNIX Shell history.

% beeline-u jdbc:hive2://localhost:10000/default-n scott-w password_file
Hive version 0.11.0-snapshot by Apache
  connecting to Jdbc:hive2://localhost:10000/default

Beeline with NOSASL connection

If you prefer to establish a connection through NOSASL mode, you must explicitly specify the authentication mode. ：

% Bin/beeline

Beeline Commands

Command	Description
!<sqlline command>	Sqlline Command List please visit http://sqlline.sourceforge.net/ example : Use!quit to exit the Beeline client.
!delimiter	Sets the delimiter for writing queries in Beeline. Multi-character qualifiers are allowed, but quotes, slashes, and--are not allowed. The default is a semicolon; Use case:!delimiter $$ version:3.0.0 (HIVE-10865)

Beeline Hive Commands

Hvie commands (and the Hive CLI command line) can also be run in Beeline when the HIVEJDBC driver is used.

Use the semicolon ";" To end the command, the script can use "--" as a prefix to identify the command.

Command	Describe
Reset	Resets the configuration to the default value.
Set <key>=<value>	Sets a value for a specific configuration variable (key). Note: If you misspell the variable name, the CLI will not prompt the error.
Set	Prints a list of configuration variables that are overridden by the user or hive.
Set-v	Print all the configuration variables for Hadoop and hive.
Add File[s] <filepath> <filepath>* Add Jar[s] <filepath> <filepath>* Add Archive[s] <filepath> <filepath>*	Add one or more files, jar packages, or compressed packages to the list of resources in the distributed cache. Check the hive resources for more information.
Add File[s] <ivyurl> <ivyurl>* Add Jar[s] <ivyurl> <ivyurl>* Add Archive[s] <ivyurl> <ivyurl>*	From Hive 1.2.0, add one or more files, a jar package, or a compressed package to a distributed cache resource list using a ivy://group:module:version?query_string-like Lvy URL. Check the hive resources for more information.
List File[s] List Jar[s] List Archive[s]	Lists the resources that have been added to the distributed cache. Check the hive resources for more information (as of Hive 0.14.0:hive-7592).
List File[s] <filepath>* List Jar[s] <filepath>* List Archive[s] <filepath>*	Checks whether a given resource has been added to the distributed cache. Check the hive resources for more information.
Delete File[s] <filepath>* Delete Jar[s] <filepath>* Delete Archive[s] <filepath>*	Remove some of the resources in the distributed cache.
Delete File[s] <ivyurl> <ivyurl>* Delete Jar[s] <ivyurl> <ivyurl>* Delete Archive[s] <ivyurl> <ivyurl>*	From Hive 1.2.0, remove the resources that were added to the distributed cache through <ivyurl>. Check the hive resources for more information.
Reload	From Hive 0.14.0, specify the path by configuring the parameter Hive.reloadable.aux.jars.path (without restarting HiveServer2) to make the HiveServer2 aware of any jar package changes. The changes here include adding, deleting, or updating the jar package.
DFS <dfs command>	Executes a DFS command through the hive shell.
<query string>	Executes a hive query statement and outputs standard output.

Beeline command-line Options

The Beeline CLI supports these command-line options:

Options	Describe
- u <database url>	Used for JDBC URL connections. Use case: Beeline-u Db_url
-R	Reconnect to the most recently used URL (if the user has pre-made use, generate the URL with!connect, generate Beeline.properties.file with!save). Use case: Beeline-r version:2.1.0 (HIVE-13670)
- N <username>	The user name to use when connecting. Use case: Beeline-n valid_user
- P <password>	The password to use when connecting. Use case: Beeline-p Valid_password Optional password mode: The start parameter from hive 2.2.0-P option is optional. Use case: beeline-p [Valid_password] If the password is not provided after-p, Beeline will prompt for a password when the connection is initialized. When the password is provided, Beeline uses it to initialize the connection without prompting.
- D <driver class>	Configuring the driver classes used Use case: beeline-d driver_class
- e <query>	The query that should be executed. Single and double quotes are used at both ends of the query statement. This option is used multiple times. Use case: Beeline-e "Query_string" Support for running complex SQL statements, separated by semicolons, in a single statement. (HIVE-9877) Bug fix (Null pointer exception): 0.13.0 (HIVE-5765) Bug Fix (--headerinterval not honored): 0.14.0 (HIVE-7647) Bug fix (running-e in background): 1.3.0 and 2.0.0 (HIVE-6758); Workaround available for earlier versions
- F <file>	The script file that needs to be executed. Use case: Beeline-f filepath version:0.12.0 (HIVE-4268) Note: If the script contains tabs, the query compilation fails in version 0.12.0, this bug has been fixed in version 0.13.0. (HIVE-6359). Bug fix (running-f in background): 1.3.0 and 2.0.0 (HIVE-6758); Workaround available for earlier versions
- I. (OR) --init <file or files>	Initializes the required initial file. Use case: Beeline-i/tmp/initfile Single File: version:0.14.0 (HIVE-6561) Multiple files: version:2.1.0 (HIVE-11336)
- W (OR) --password-file <password file>	Read the password from the file. version:1.2.0 (HIVE-7175)
-A (OR) --authtype <auth type>	The authentication type for JDBC is an identity authentication attribute. version:0.13.0 (HIVE-5155)
--property-file <file>	Read the configuration properties of the file Use case: Beeline--property-file/tmp/a version:2.2.0 (HIVE-13964)
--hiveconf Property=value	Assigns a value to a given configuration property. The properties in the Hive.conf.restricted.list list cannot be reset by hiveconf. (See Restricted List and Whitelist). Use case: Beeline--hiveconf prop1=value1 version:0.13.0 (HIVE-6173)
--hivevar Name=value	The variable name and variable value of hive. This is a hive-specified setting in which variables can be set at the session level and referenced by hive commands and queries. Use case: Beeline--hivevar var1=value1
--color= [True/false]	Controls whether the color is used for display. Default is False Use case: Beeline--color=true (Separated values are not supported for output mode.) See HIVE-9770)
--showheader= [True/false]	Displays whether the column name is in the query results. The default is true. Use case: Beeline--showheader=false
--headerinterval= ROWS	When the output is a table, the interval between them when the column header is re-displayed, calculated by the number of rows. The default value is 100 Use case: Beeline--headerinterval=50 (Separated values are not supported for output mode.) See HIVE-9770)
--fastconnect= [True/false]	When connected, the list of all tables and columns is built by skipping the tab auto-completion function for the HIVEQL syntax, which is true without building it. Use case: Beeline--fastconnect=false