Versions Compared

Old Version 7

changes.mady.by.user Shannon Ladymon

Saved on

compared with

New Version 8

changes.mady.by.user Jia Ke

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

No Format
usage: hive
 -d,--define <key=value>          Variable subsitution to apply to hive
                                  commands. e.g. -d A=B or --define A=B
    --database <databasename>     Specify the database to use
 -e <quoted-query-string>         SQL from command line
 -f <filename>                    SQL from files
 -H,--help                        Print help information
    --hiveconf <property=value>   Use value for given property
    --hivevar <key=value>         Variable subsitution to apply to hive
                                  commands. e.g. --hivevar A=B
 -i <filename>                    Initialization SQL file
 -S,--silent                      Silent mode in interactive shell
 -v,--verbose                     Verbose mode (echo executed SQL to the
                                  console)
 

Example

  • Example of running a query from the command line

    No Format
     $HIVE_HOME/bin/hive -e 'select a.foo from pokes a'
 

  • Example of setting Hive configuration variables

    No Format
     $HIVE_HOME/bin/hive -e 'select a.foo from pokes a' --hiveconf hive.exec.scratchdir=/opt/my/hive_scratch --hiveconf mapred.reduce.tasks=1
 

  • Example of dumping data out from a query into a file using silent mode

    No Format
    $HIVE_HOME/bin/hive -S -e 'select a.foo from pokes 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 CLI Interactive Shell Commands Support

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
exit

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).
Note: If you misspell the variable name, the CLI will 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. 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]
list JAR[S]
list ARCHIVE[S]

Lists the resources already added to the distributed cache. See Hive Resources below for more information.

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. See Hive Resources below for more information.

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 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.

Example for source command:

No Format
hive> source /root/test.sql;
hive> show tables;
test1
test2
hive> exit;
hive> quit;
hive> set;
hive> set hive.cli.print.header=true;
hive> set -v;
hive> reset;
hive> add file /opt/a.txt;
Added resources: [/opt/a.txt]
hive> list files;
/opt/a.txt
hive> delete file /opt/a.txt;
hive> add jar /usr/share/vnc/classes/vncviewer.jar;
Added [/usr/share/vnc/classes/vncviewer.jar]to class path
Added resources:[/usr/share/vnc/classes/vncviewer.jar]
hive> list jars;
/usr/share/vnc/classes/vncviewer.jar
hive> delete jar /usr/share/vnc/classes/vncviewer.jar;
hive> !ls;
bin
conf
hive> dfs -ls / ;
numbers_bucketed
test2
testavro2
Found 2 items
drwx-wx-wx  - root supergroup  0   2015-08-12 19:06 /tmp
drwxr-xr-x  - root supergroup  0   2015-08-12 19:43 /user
hive> select * from pokes; 
OK
pokes.foo   pokes.bar
238         val_238
86          val_86
311         val_311
hive>source /opt/s.sql;

Hive CLI Configuration Support

Configuration NameSupported in New Hive CLIDescription
hive.cli.print.headerYesWhether to print the names of the columns in query output.
hive.cli.errors.ignoreYesWhether to force execution a script when errors occurred
hive.cli.promptYes 
hive.cli.pretty.output.num.colsNo 
hive.cli.print.current.dbNoWhether to include the current database in the Hive prompt.

Performance Impacts

Using the JMH to measure the average time cost when retrieving a data set, we have the following results.

...