Hive CLI

$HIVE_HOME/bin/hive is a shell utility which can be used to run Hive queries in either interactive or batch mode.

Deprecation in favor of Beeline CLI

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 Replacing the Implementation of Hive CLI Using Beeline and Beeline – New Command Line Shell in the HiveServer2 documentation.

Hive Command Line Options

To get help, run "hive -H" or "hive --help".
Usage (as it is in Hive 0.9.0):

usage: hive
 -d,--define <key=value>          Variable substitution to apply to Hive
                                  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)

--database <dbname>      Specify the database to use

Note: The variant "-hiveconf" is supported as well as "--hiveconf".

Examples

See Variable Substitution for examples of using the hiveconf option.

• Example of running a query from the command line

  $HIVE_HOME/bin/hive -e 'select a.col from tab1 a'

• Example of setting Hive configuration variables

  $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

  $HIVE_HOME/bin/hive -S -e 'select a.col from tab1 a' > a.txt

• Example of running a script non-interactively from local disk

  $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)

  $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

  $HIVE_HOME/bin/hive -i /home/my/hive-init.sql

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.

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:

 $HIVE_HOME/bin/hive --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.

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.

Sample Usage:

  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.

Once a resource is added to a session, Hive queries can refer to it by its name (in map/reduce/transform clauses) and the resource is available locally at execution time on the entire Hadoop cluster. Hive uses Hadoop's Distributed Cache to distribute the added resources to all the machines in the cluster at query execution time.

Usage:

   ADD { FILE[S] | JAR[S] | ARCHIVE[S] } <filepath1> [<filepath2>]*
   LIST { FILE[S] | JAR[S] | ARCHIVE[S] } [<filepath1> <filepath2> ..]
   DELETE { FILE[S] | JAR[S] | ARCHIVE[S] } [<filepath1> <filepath2> ..] 

• FILE resources are just added to the distributed cache. Typically, this might be something like a transform script to be executed.
• JAR resources are also added to the Java classpath. This is required in order to reference objects they contain such as UDFs. See Hive Plugins for more information about custom UDFs.
• ARCHIVE resources are automatically unarchived as part of distributing them.

Example:

  hive> add FILE /tmp/tt.py;
  hive> list FILES;
  /tmp/tt.py
  hive> select from networks a 
               MAP a.networkid 
               USING 'python tt.py' as nn where a.ds = '2009-01-04' limit 10;

ADD { FILE[S] | JAR[S] | ARCHIVE[S] } <ivy://org:module:version?key=value&key=value&...> <ivy://org:module:version?key=value&key1=value1&...>*
DELETE { FILE[S] | JAR[S] | ARCHIVE[S] } <ivy://org:module:version> <ivy://org:module:version>*

ADD { FILE[S] | JAR[S] | ARCHIVE[S] } { <ivyurl> | <filepath> } <ivyurl>* <filepath>* 
DELETE { FILE[S] | JAR[S] | ARCHIVE[S] } { <ivyurl> | <filepath> } <ivyurl>* <filepath>*

hive>ADD JAR ivy://org.apache.pig:pig:0.10.0?exclude=org.apache.hadoop:avro;
hive>ADD JAR ivy://org.apache.pig:pig:0.10.0?exclude=org.apache.hadoop:avro&transitive=false;

hive>ADD JAR ivy://org.apache.pig:pig:0.10.0
hive>ADD JAR ivy://org.apache.pig:pig:0.11.1.15
hive>DELETE JAR ivy://org.apache.pig:pig:0.10.0

It is not neccessary to add files to the session if the files used in a transform script are already available on all machines in the Hadoop cluster using the same path name. For example:

• ... MAP a.networkid USING 'wc -l' ...
  Here wc is an executable available on all machines.
• ... MAP a.networkid USING '/home/nfsserv1/hadoopscripts/tt.py' ...
  Here tt.py may be accessible via an NFS mount point that's configured identically on all the cluster nodes.

Note that Hive configuration parameters can also specify jars, files, and archives. See Configuration Variables for more information.

HCatalog CLI

Many (but not all) hcat commands can be issued as hive commands, and vice versa. See the HCatalog Command Line Interface document in the HCatalog manual for more information.