hive cli

Hive Command line Options

Usage: hive [-hiveconf x=y]* [<-i filename>]* [<-f filename>|<-e query-string>] [-S]

  -i <filename>             Initialization Sql from file (executed automatically and silently before any other commands)
  -e 'quoted query string'  Sql from command line
  -f <filename>             Sql from file
  -S                        Silent mode in interactive shell where only data is emitted
  -v                        Verbose mode (echo executed SQL to the console)
  -p <port>                 connect to Hive Server on port number
  -hiveconf x=y             Use this to set hive/hadoop configuration variables.
 
   -e and -f cannot be specified together. In the absence of these options, interactive shell is started.  
   However, -i can be used with any other options.  Multiple instances of -i can be used to execute multiple init scripts.

   To see this usage help, run hive -h

root@hadoop1:/opt/hadoop/hive# bin/hive -e 'select * from lpx_partition_test';
Hive history file=/tmp/root/hive_job_log_root_201108021416_2073383027.txt
OK
99001    xxxcompany_name1    2011-06-08    GuangDong
99001    xxxcompany_name1    2011-06-08    ZheJiang
99002    xxxcompany_name1    2011-06-08    ZheJiang
99003    xxxcom2    2011-06-08    ZheJiang
99001    xxxcompany_name1    2011-06-09    GuangDong
99001    xxxcompany_name1    2011-06-09    ZheJiang
99002    xxxcompany_name1    2011-06-09    ZheJiang
99003    xxxcom2    2011-06-09    ZheJiang
99001    xxxcompany_name1    20110608    GuangDong
99001    xxxcompany_name1    20110608    ZheJiang
99002    xxxcompany_name1    20110609    ZheJiang
99003    xxxcom2    20110609    ZheJiang
Time taken: 7.932 seconds

root@hadoop1:/opt/hadoop/hive# bin/hive -e 'select * from lpx_partition_test' -hiveconf hive.exec.scratchdir=/opt/hadoop/mytest/hive_scratch  -hiveconf mapred.reduce.tasks=32
Hive history file=/tmp/root/hive_job_log_root_201108021419_1274184148.txt
OK
99001    xxxcompany_name1    2011-06-08    GuangDong
99001    xxxcompany_name1    2011-06-08    ZheJiang
99002    xxxcompany_name1    2011-06-08    ZheJiang
99003    xxxcom2    2011-06-08    ZheJiang
99001    xxxcompany_name1    2011-06-09    GuangDong
99001    xxxcompany_name1    2011-06-09    ZheJiang
99002    xxxcompany_name1    2011-06-09    ZheJiang
99003    xxxcom2    2011-06-09    ZheJiang
99001    xxxcompany_name1    20110608    GuangDong
99001    xxxcompany_name1    20110608    ZheJiang
99002    xxxcompany_name1    20110609    ZheJiang
99003    xxxcom2    20110609    ZheJiang
Time taken: 4.889 seconds

root@hadoop1:/opt/hadoop/hive# bin/hive -S -e 'select * from lpx_partition_test' > /opt/hadoop/mytest/a.txt
root@hadoop1:/opt/hadoop/hive# cd ../mytest
root@hadoop1:/opt/hadoop/mytest# ls
a.txt  lpx_partition_test.txt  test_file1.txt  test_file2.txt
root@hadoop1:/opt/hadoop/mytest# cat a.txt
99001    xxxcompany_name1    2011-06-08    GuangDong
99001    xxxcompany_name1    2011-06-08    ZheJiang
99002    xxxcompany_name1    2011-06-08    ZheJiang
99003    xxxcom2    2011-06-08    ZheJiang
99001    xxxcompany_name1    2011-06-09    GuangDong
99001    xxxcompany_name1    2011-06-09    ZheJiang
99002    xxxcompany_name1    2011-06-09    ZheJiang
99003    xxxcom2    2011-06-09    ZheJiang
99001    xxxcompany_name1    20110608    GuangDong
99001    xxxcompany_name1    20110608    ZheJiang
99002    xxxcompany_name1    20110609    ZheJiang
99003    xxxcom2    20110609    ZheJiang

root@hadoop1:/opt/hadoop/hive# bin/hive -f /opt/hadoop/mytest/hive-script.sql
Hive history file=/tmp/root/hive_job_log_root_201108021422_1299854372.txt
OK
99001    xxxcompany_name1    2011-06-08    GuangDong
99001    xxxcompany_name1    2011-06-08    ZheJiang
99002    xxxcompany_name1    2011-06-08    ZheJiang
99003    xxxcom2    2011-06-08    ZheJiang
99001    xxxcompany_name1    2011-06-09    GuangDong
99001    xxxcompany_name1    2011-06-09    ZheJiang
99002    xxxcompany_name1    2011-06-09    ZheJiang
99003    xxxcom2    2011-06-09    ZheJiang
99001    xxxcompany_name1    20110608    GuangDong
99001    xxxcompany_name1    20110608    ZheJiang
99002    xxxcompany_name1    20110609    ZheJiang
99003    xxxcom2    20110609    ZheJiang
Time taken: 5.015 seconds

root@hadoop1:/opt/hadoop/hive# bin/hive -i /opt/hadoop/mytest/hive-script.sql
Hive history file=/tmp/root/hive_job_log_root_201108021423_1805178938.txt
99001    xxxcompany_name1    2011-06-08    GuangDong
99001    xxxcompany_name1    2011-06-08    ZheJiang
99002    xxxcompany_name1    2011-06-08    ZheJiang
99003    xxxcom2    2011-06-08    ZheJiang
99001    xxxcompany_name1    2011-06-09    GuangDong
99001    xxxcompany_name1    2011-06-09    ZheJiang
99002    xxxcompany_name1    2011-06-09    ZheJiang
99003    xxxcom2    2011-06-09    ZheJiang
99001    xxxcompany_name1    20110608    GuangDong
99001    xxxcompany_name1    20110608    ZheJiang
99002    xxxcompany_name1    20110609    ZheJiang
99003    xxxcom2    20110609    ZheJiang

Hive interactive Shell Command

  hive> set  mapred.reduce.tasks=32;
  hive> set;
  hive> select a.* from tab1;
  hive> !ls;
  hive> dfs -ls;