Beeline command line

Mikhail Serov
Beeline commands

!<SQLLine_command>

Executes an SQLLine command

!delimiter

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

Usage: !delimiter $$

Version: 3.0.0
Beeline properties

fetchsize

The JDBC standard allows 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.

Setting the value to -1 instructs Beeline to use the JDBC driver’s default fetch size (default).

Setting the value to 0 or any positive integer is passed to the JDBC driver for each statement.

Any other negative value will throw an exception.

Usage: !set fetchsize 200

Version: 4.0.0

Beeline Hive commands

Hive-specific commands (same as Hive CLI commands) can 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 Hive commands

reset

Resets the configuration to the default values

reset <key>

Resets the value of a particular configuration variable (key) to the default value If you misspell a key name, Beeline does not show an error.

set <key>=<value>

Sets the value of a particular configuration variable (key) If you misspell a variable name, Beeline does not show an error.

set

Prints a list of configuration variables that are overridden by the user or Hive

set -v

Prints all Hadoop and Hive configuration variables

add FILE[S] <filepath> <filepath>\*

add JAR[S] <filepath> <filepath>\*

add ARCHIVE[S] <filepath> <filepath>\*

Adds one or more files, JARs, or archives to the list of resources in the distributed cache. For more information, see Hive Resources page of the Apache Hive documentation

add FILE[S] <ivyurl> <ivyurl>\*

add JAR[S] <ivyurl> <ivyurl>\*

add ARCHIVE[S] <ivyurl> <ivyurl>\*

As of Hive 1.2.0, adds one or more files, JARs, or archives to the list of resources in the distributed cache using an Ivy URL of the form ivy://group:module:version?query_string. For more information, see Hive Resources page of the Apache Hive documentation

list FILE[S]

list JAR[S]

list ARCHIVE[S]

Lists the resources already added to the distributed cache. For more information, see Hive Resources page of the Apache Hive documentation

list FILE[S] <filepath>\*

list JAR[S] <filepath>\*

list ARCHIVE[S] <filepath>\*

Checks whether the given resources are already added to the distributed cache or not. For more information, see Hive Resources page of the Apache Hive documentation

delete FILE[S] <filepath>\*

delete JAR[S] <filepath>\*

delete ARCHIVE[S] <filepath>\*

Removes the resource(s) from the distributed cache

delete FILE[S] <ivyurl> <ivyurl>\*

delete JAR[S] <ivyurl> <ivyurl>\*

delete ARCHIVE[S] <ivyurl> <ivyurl>\*

As of Hive 1.2.0, removes the resource(s) which were added using the <ivyurl> from the distributed cache. See Hive Resources page of the Apache Hive documentation for more information

reload

As of Hive 0.14.0, makes HiveServer2 aware of any JAR changes in the path specified by the configuration parameter hive.reloadable.aux.jars.path (without having to restart HiveServer2). The changes can be adding, removing, or updating JAR files

dfs <dfs command>

Executes a dfs command

<query string>

Executes a Hive query and prints results to standard output

Beeline command options

The Beeline CLI supports these command line options:

Option Description Usage example

-u <database URL>

The JDBC URL to connect to. Special characters in parameter values should be encoded with URL encoding if needed

 
$ beeline -u db_URL

-r

Reconnects to the last used URL (if you have previously used !connect and !save commands).

Version: 2.1.0

 
$ beeline -r

-n <username>

The username to be used for connection

 
$ beeline -n <valid_user>

-p <password>

The password to be used for connection.

Optional password mode:

Starting from Hive 2.2.0, the argument for -p option is optional.

If the password is not provided after -p, Beeline will prompt for the password while initiating the connection. When password is provided, Beeline uses it to initiate the connection without prompting

 
$ beeline -p <valid_password>

-d <driver class>

The driver class to use

 
$ beeline -d <driver_class>

-e <query>

Specifies the query to be executed. Double or single quotes enclose the query string. This option can be specified multiple times

 
$ beeline -e "query_string"

-f <file>

Specifies the path to a script file to be executed.

Version: 0.12.0

If the script contains tabs, query compilation fails in version 0.12.0. This bug is fixed in version 0.13.0

 
$ beeline -f <path/to/file>

-i <file>

--init <files>

Sets the init file(s) for initialization.

Version: 0.14.0: single file definition introduced.

Version: 2.1.0: multiple files can be specified

 
$ beeline -i <path/to/initfile>

-w <path/to/password_file>

--password-file <path/to/password file>

Specifies the password file to read password from.

Version: 1.2.0

 
$ beeline -u '<JDBC_URL>' -n test_user -w pass

-a <auth type>

--authType <auth_type>

Sets the authentication type passed to the JDBC as an auth property.

Version: 0.13.0

 
$ beeline --authType 'kerberos'

--property-file <file>

Specifies the file to read configuration properties from. Version: 2.2.0

 
$ beeline --property-file <path/to/file>

--hiveconf property=value

Sets a value for the given configuration property. Properties that are listed in hive.conf.restricted.list cannot be reset with hiveconf (see Restricted List and Whitelist page of the Apache Hive documentation). Version: 0.13.0

 
$ beeline --hiveconf prop1=value1

--hivevar name=value

Sets a Hive variable name and value. This is a Hive-specific setting in which variables can be set at the session level and referenced in Hive commands or queries

 
$ beeline --hivevar var1=value1

--color=[true/false]

Controls whether color is used for display. Default is false. Not supported for Separated-Value Output formats

 
$ beeline --color=true

--showHeader=[true/false]

Shows column names in the query results (true) or not (false). Default is true

 
$ beeline --showHeader=false

--headerInterval=ROWS

The interval for re-displaying column headers, in number of rows, when outputformat=table. Default is 100. Not supported for Separated-Value Output formats

 
$ beeline --headerInterval=50

--fastConnect=[true/false]

Controls if the building of a list of all tables and columns for tab-completion of HiveQL statements is skipped (true) or not (false). Default is true

 
$ beeline --fastConnect=false

--autoCommit=[true/false]

Enables/disables automatic transaction commit. Default is false

 
$ beeline --autoCommit=true

--verbose=[true/false]

Shows verbose error messages and debug information (true) or does not show those messages and information (false). Default is false

 
$ beeline --verbose=true

--showWarnings=[true/false]

Displays warnings that are reported on the connection after issuing any HiveQL commands. Default is false

 
$ beeline --showWarnings=true

--showDbInPrompt=[true/false]

Displays the current database name in prompt. Default is false

Version: 2.2.0

 
$ beeline --showDbInPrompt=true

--showNestedErrs=[true/false]

Displays nested errors. Default is false

 
$ beeline --showNestedErrs=true

--numberFormat=[pattern]

Formats numbers using the DecimalFormat pattern

 
$ beeline --numberFormat="#,###,##0.00"

--force=[true/false]

Continues to run the script even after errors (true) or do not continue (false). Default is false

 
$ beeline--force=true

--maxWidth=MAXWIDTH

Shows the maximum width to display before truncating data, in characters, when outputformat=table. Default is to query the terminal for current width, then fall back to 80

 
$ beeline --maxWidth=150

--maxColumnWidth=MAXCOLWIDTH

Shows the maximum column width, in characters, when outputformat=table. Default is 50 in Hive version 2.2.0+ or 15 in earlier versions

 
$ beeline --maxColumnWidth=25

--silent=[true/false]

Reduces the amount of informational messages displayed (true) or not (false). It also stops displaying the log messages for the query from HiveServer2 (Hive 0.14 and later) and the HiveQL commands (Hive 1.2.0 and later). Default is false

 
$ beeline --silent=true

--autosave=[true/false]

Automatically saves preferences (true) or not (false). Default is false

 
$ beeline --autosave=true

--outputformat=[table/vertical/csv/tsv/dsv/csv2/tsv2]

Sets the format mode for the result display. Default is table. See Separated-value output formats for description of recommended options.

Version 0.14.0: added dsv/csv2/tsv2

 
$ beeline --outputformat=tsv

--truncateTable=[true/false]

If set to true, truncates table column in the console when it exceeds console length.

Version: 0.14.0

 
$ beeline --truncateTable=true

--delimiterForDSV=DELIMITER

Sets the delimiter for delimiter-separated values output format. Default is | character.

Version: 0.14.0

 
$ beeline --delimiterForDSV='*'

--isolation=LEVEL

Sets the transaction isolation level to TRANSACTION_READ_COMMITTED or TRANSACTION_SERIALIZABLE. For more information, see Java Connection Interface documentation

 
$ beeline --isolation=TRANSACTION_SERIALIZABLE

--nullemptystring=[true/false]

Sets for use the historic behavior of printing null as empty string (true) or the current behavior of printing null as NULL (false). Default is false.

Version: 0.13.0

 
$ beeline --nullemptystring=false

--incremental=[true/false]

Defaults to true starting from Hive 2.3 onwards, before it defaulted to false. When set to false, the entire result set is fetched and buffered before being displayed, yielding optimal display column sizing. When set to true, result rows are displayed immediately as they are fetched, yielding lower latency and memory usage at the price of extra display column padding. Setting --incremental=true is recommended if you encounter an OutOfMemoryError on the client side (due to the fetched result set size being large)

 
$ beeline --incremental=false

--incrementalBufferRows=NUMROWS

Sets the number of rows to buffer when printing rows on stdout, defaults to 1000; only applicable if --incremental=true and --outputformat=table.

Version: 2.3.0

 
$ beeline --incrementalBufferRows=1000

--maxHistoryRows=NUMROWS

Sets the maximum number of rows to store Beeline history.

Version: 2.3.0

 
$ beeline --maxHistoryRows=1000

--delimiter=;

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

Version: 3.0.0

 
$ beeline beeline --delimiter=$$

--convertBinaryArrayToString=[true/false]

Permits to display binary column data as a string using the platform’s default character set.

Version: 3.0.0:

The default behavior (false) is to display binary data using: Arrays.toString(byte[] columnValue) Displays binary column data as a string using the UTF-8 character set.  
Version: 4.0.0:

The default behavior (false) is to display binary data using Base64 encoding without padding

 
$ beeline --convertBinaryArrayToString=true

--help

Displays usage tips

 
$ beeline --help
Found a mistake? Seleсt text and press Ctrl+Enter to report it