hive shell命令

Hive的Shell有命令行模式和交互模式。
命令行模式用起来如同“hadoop fs -ls /”这种；交互模式如同“hbase shell”进入的模式，和hbase shell不同的是hive的交互模式里每个命令后都要用分号结束。

Hive 命令行参数：
-i 从文件执行初始化HQL后进入交互模式。
-e 从命令行执行指定的HQL语句。
-f 执行HQL脚本文件。
-v 输出执行的HQL语句到控制台。
-p connect to Hive Server on port number -hiveconf x=y Use this to set hive/hadoop configuration variables.

1）从命令行执行指定的sql语句

root@debian:~# hive -e ‘create table tb1(id int, name string, age int);’
OK ########## 执行成功
Time taken: 1.915 seconds
root@debian:~# hive -v -e ‘create table tb1(id int, name string, age int);’
create table tb1(id int, name string, age int)
OK ########## 打印sql语句。执行成功
Time taken: 2.987 seconds
root@debian:~# hive -e ‘create table tb1(id int, name string, age int);’ -v >> /home/hive-1.1.0/a.txt
OK ########## 执行成功。在指定目录创建文件，保存执行的sql语句
Time taken: 3.411 seconds
root@debian:~# hive -e ‘show tables;’
OK ########## 执行成功。打印结果
tb1
Time taken: 1.801 seconds, Fetched: 2 row(s)
root@debian:~# hive -e ‘show tables;’ -v >> /home/hive-1.1.0/a.txt
OK ########## 执行成功。在指定文件保存执行的sql语句和结果
Time taken: 1.106 seconds, Fetched: 1 row(s)
root@debian:~#

2）在进入交互模式之前，执行初始化的sql语句文件
在/home/hive-1.1.0里准备一个sql文件init.sql，写入sql语句：

create table tb2(id int, name string, age int);
show tables;

然后执行：

root@debian:~# hive -v -i /home/hive-1.1.0/init.sql
create table tb2(id int, name string, age int) #这一行是因-v参数才打印的
show tables #这一行是因-v参数才打印的
tb1 #这一行是因为sql文件中的“show tables;”才打印的

3）以非交互式模式执行sql文件sql语句（未测试）
root@debian:~# hive -f /home/hive-1.1.0/script.sql
root@debian:~# hive -f -v /home/hive-1.1.0/script.sql
root@debian:~# hive -f -v /home/hive-1.1.0/script.sql >> /home/hive-1.1.0/a.txt

4）以指定的hive环境变量执行指定的sql语句（未测试）
root@debian:~# hive -e ‘select a.col from tb2 a’
-hiveconf hive.exec.scratchdir=/home/hive-1.1.0/hive_scratch
-hiveconf mapred.reduce.tasks=32

如果已经在一个终端进入hive的交互模式，在另一个终端执行hive的命令行模式会报错：
root@debian:~# hive -e ‘show tables;’
Exception in thread “main” java.lang.RuntimeException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
Caused by: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
Caused by: java.lang.reflect.InvocationTargetException
Caused by: javax.jdo.JDOFatalDataStoreException: Unable to open a test connection to the given database. JDBC url = jdbc:derby:;databaseName=metastore_db;create=true, username = App. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ——
java.sql.SQLException: Failed to start database ‘metastore_db’ with class loader sun.misc.LauncherAppClassLoader@3ad6a0e0,seethenextexceptionfordetails.Causedby:ERRORXJ040:Failedtostartdatabase‘metastoredb′withclassloadersun.misc.LauncherAppClassLoader@3ad6a0e0, see the next exception for details.
Caused by: ERROR XSDB6: Another instance of Derby may have already booted the database /root/metastore_db.
NestedThrowables:
java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:derby:;databaseName=metastore_db;create=true, username = App. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ——
java.sql.SQLException: Failed to start database ‘metastore_db’ with class loader sun.misc.LauncherAppClassLoader@3ad6a0e0,seethenextexceptionfordetails.Causedby:ERRORXJ040:Failedtostartdatabase‘metastoredb′withclassloadersun.misc.LauncherAppClassLoader@3ad6a0e0, see the next exception for details.
Caused by: ERROR XSDB6: Another instance of Derby may have already booted the database /root/metastore_db.
Caused by: java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:derby:;databaseName=metastore_db;create=true, username = App. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ——
java.sql.SQLException: Failed to start database ‘metastore_db’ with class loader sun.misc.LauncherAppClassLoader@3ad6a0e0,seethenextexceptionfordetails.Causedby:ERRORXJ040:Failedtostartdatabase‘metastoredb′withclassloadersun.misc.LauncherAppClassLoader@3ad6a0e0, see the next exception for details.
Caused by: ERROR XSDB6: Another instance of Derby may have already booted the database /root/metastore_db.
Caused by: java.sql.SQLException: Failed to start database ‘metastore_db’ with class loader sun.misc.LauncherAppClassLoader@3ad6a0e0,seethenextexceptionfordetails.Causedby:ERRORXJ040:Failedtostartdatabase‘metastoredb′withclassloadersun.misc.LauncherAppClassLoader@3ad6a0e0, see the next exception for details.
Caused by: ERROR XSDB6: Another instance of Derby may have already booted the database /root/metastore_db.