...
HiveServer2 (introduced in Hive 0.11) has its own CLI called Beeline, which is a JDBC client based on SQLLine. Due to new development being focused on HiveServer2, Hive CLI will soon be deprecated in favor of Beeline (HIVE-10511).
See See Replacing the Implementation of Hive CLI Using Beeline and Beeline – New Command Line Shell in the HiveServer2 documentation.
...
No Format |
---|
usage: hive -d,--define <key=value> Variable substitution to apply to hiveHive commands. e.g. -d A=B or --define A=B -e <quoted-query-string> SQL from command line -f <filename> SQL from files -H,--help Print help information -h <hostname> Connecting to Hive Server on remote host --hiveconf <property=value> Use value for given property --hivevar <key=value> Variable substitution to apply to hive commands. e.g. --hivevar A=B -i <filename> Initialization SQL file -p <port> Connecting to Hive Server on port number -S,--silent Silent mode in interactive shell -v,--verbose Verbose mode (echo executed SQL to the console) |
...
Example of running a query from the command line
No Format $HIVE_HOME/bin/hive -e 'select a.col from tab1 a'
Example of setting Hive configuration variables
No Format $HIVE_HOME/bin/hive -e 'select a.col from tab1 a' --hiveconf hive.exec.scratchdir=/home/my/hive_scratch --hiveconf mapred.reduce.tasks=32
Example of dumping data out from a query into a file using silent mode
No Format $HIVE_HOME/bin/hive -S -e 'select a.col from tab1 a' > a.txt
Example of running a script non-interactively from local disk
No Format $HIVE_HOME/bin/hive -f /home/my/hive-script.sql
Example of running a script non-interactively from a Hadoop supported filesystem (starting in Hive 0.14)
No Format $HIVE_HOME/bin/hive -f hdfs://<namenode>:<port>/hive-script.sql $HIVE_HOME/bin/hive -f s3://mys3bucket/s3-script.sql
Example of running an initialization script before entering interactive mode
No Format $HIVE_HOME/bin/hive -i /home/my/hive-init.sql
The The hiverc File
The CLI when invoked without the -i
option will attempt to load $HIVE_HOME/bin/.hiverc and $HOME/.hiverc as initialization files.
Hive Batch Mode Commands
When $HIVE_HOME/bin/hive
is run with the -e
or -f
option, it executes SQL commands in batch mode.
hive -e '<query-string>'
executes the query string.hive -f <filepath>
executes one or more SQL queries from a file.
Logging
Hive uses log4j for logging. These logs are not emitted to the standard output by default but are instead captured to a log file specified by Hive's log4j properties file. By default Hive will use hive-log4j.default
in the conf/
directory of the Hive installation which writes out logs to /tmp/<userid>/hive.log
and uses the WARN
level.
It is often desirable to emit the logs to the standard output and/or change the logging level for debugging purposes. These can be done from the command line as follows:
No Format | ||
---|---|---|
| ||
Info | ||
| ||
As of Hive 0.14, <filepath> can be from one of the Hadoop supported filesystems (HDFS, S3, etc.) as well. $HIVE_HOME/bin/hive -f hdfs://<namenode>:<port>/hive-script.sql $HIVE_HOME/bin/hive -f s3://mys3bucket/s3-script.sqlSee HIVE-7136 for more details. |
Hive Interactive Shell Commands
When $HIVE_HOME/bin/hive
is run without either the -e
or -f
option, it enters interactive shell mode.
Use ";" (semicolon) to terminate commands. Comments in scripts can be specified using the "--" prefix.
--hiveconf hive.root.logger=INFO,console
|
hive.root.logger
specifies the logging level as well as the log destination. Specifying console
as the target sends the logs to the standard error (instead of the log file).
See Hive Logging in Getting Started for more information.
Tool to Clear Dangling Scratch Directories
See Scratch Directory Management in Setting Up HiveServer2 for information about scratch directories and a command-line tool for removing dangling scratch directories that can be used in the Hive CLI as well as HiveServer2.
Hive Batch Mode Commands
When $HIVE_HOME/bin/hive
is run with the -e
or -f
option, it executes SQL commands in batch mode.
hive -e '<query-string>'
executes the query string.hive -f <filepath>
executes one or more SQL queries from a file.
Info | ||
---|---|---|
| ||
As of Hive 0.14, <filepath> can be from one of the Hadoop supported filesystems (HDFS, S3, etc.) as well.
See HIVE-7136 for more details. |
Hive Interactive Shell Commands
When $HIVE_HOME/bin/hive
is run without either the -e
or -f
option, it enters interactive shell mode.
Use ";" (semicolon) to terminate commands. Comments in scripts can be specified using the "--" prefix.
Command | Description |
---|---|
quit | Use quit or exit to leave the interactive shell. |
reset | Resets the configuration to the default values (as of Hive 0.10: see HIVE-3202). |
set <key>=<value> | Sets the value of a particular configuration variable (key). |
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>* | Adds one or more files, jars, or archives to the list of resources in the distributed cache. See Hive Resources below for more information. |
add | |
Command | Description |
quit | Use quit or exit to leave the interactive shell. |
reset | Resets the configuration to the default values (as of Hive 0.10: see HIVE-3202). |
set <key>=<value> | Sets the value of a particular configuration variable (key). |
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>* | Adds one or more files, jars, or archives to the list of resources in the distributed cache. See Hive Resources below for more information. |
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. See Hive Resources below for more information. |
list FILE[S] | Lists the resources already added to the distributed cache. See Hive Resources below for more information. |
list FILE[S] <filepath>* | Checks whether the given resources are already added to the distributed cache or not. See Hive Resources below for more information. |
delete FILE[S] <filepath>* | Removes the resource(s) from the distributed cache. |
delete FILE[S] <ivyurl> <ivyurl>* delete add JAR[S] <ivyurl> <ivyurl>* delete add ARCHIVE[S] <ivyurl> <ivyurl>* | As of Hive 1.2.0, removes the resource(s) which were added using the <ivyurl> from the distributed cacheadds 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. See Hive Resources below for more information. |
! <command> | Executes a shell command from the Hive shell. |
dfs <dfs command> | Executes a dfs command from the Hive shell. |
<query string> | Executes a Hive query and prints results to standard output. |
source FILE <filepath> | Executes a script file inside the CLI. |
Sample Usage:
No Format |
---|
hive> set mapred.reduce.tasks=32;
hive> set;
hive> select a.* from tab1;
hive> !ls;
hive> dfs -ls;
|
Logging
Hive uses log4j for logging. These logs are not emitted to the standard output by default but are instead captured to a log file specified by Hive's log4j properties file. By default Hive will use hive-log4j.default
in the conf/
directory of the Hive installation which writes out logs to /tmp/<userid>/hive.log
and uses the WARN
level.
It is often desirable to emit the logs to the standard output and/or change the logging level for debugging purposes. These can be done from the command line as follows:
No Format |
---|
$HIVE_HOME/bin/hive --hiveconf hive.root.logger=INFO,console
|
...
list FILE[S] | Lists the resources already added to the distributed cache. See Hive Resources below for more information. |
list FILE[S] <filepath>* | Checks whether the given resources are already added to the distributed cache or not. See Hive Resources below for more information. |
delete FILE[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 below for more information. |
! <command> | Executes a shell command from the Hive shell. |
dfs <dfs command> | Executes a dfs command from the Hive shell. |
<query string> | Executes a Hive query and prints results to standard output. |
source <filepath> | Executes a script file inside the CLI. |
Sample Usage:
No Format |
---|
hive> set mapred.reduce.tasks=32;
hive> set;
hive> select a.* from tab1;
hive> !ls;
hive> dfs -ls;
|
Hive Resources
Hive can manage the addition of resources to a session where those resources need to be made available at query execution time. The resources can be files, jars, or archives. Any locally accessible file can be added to the session.
...
Info | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
As of Hive 1.2.0, resources can be added and deleted using ivy url Ivy URLs of the form ivy://group:module:version?query_string.
Various parameters can be passed in the query_string to configure how and which jars are added to the artifactory. The parameters are in the form of key value pairs separated by '&'. Usage:
Also, we can mix <ivyurl> and <filepath> in the same ADD and DELETE commands.
The different parameters that can be passed are:
Examples:
The DELETE command will delete the resource and all its transitive dependencies unless some dependencies are shared by other resources. If two resources share a set of transitive dependencies and one of the resource resources is deleted using the delete DELETE syntax, then all the transitive dependencies will be deleted for the resource except the ones which are shared. Examples:
if If A is the set containing the transitive dependencies of pig-0.10.0 and B is the set containing the transitive dependencies of pig-0.11.1.15, then after executing the above commands, A-(A intersection B) will be deleted. See HIVE-9664 for more details. |
...