Hive常用交互命令

Hive常用交互命令
（1）Hive帮助命令

[root@hadoop101 hive-1.2.1]# bin/hive -help

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)
[root@hadoop101 hive-1.2.1]# 

//启动hive服务

[root@hadoop101 hive-1.2.1]# bin/hive

Logging initialized using configuration in jar:file:/usr/local/hadoop/module/hive-1.2.1/lib/hive-common-1.2.1.jar!/hive-log4j.properties

//显示当前数据库与数据表

hive> show databases;
OK
default
Time taken: 1.006 seconds, Fetched: 1 row(s)
hive> 

hive> show tables;
OK
Time taken: 0.069 seconds
hive> 

//创建一张student数据表

hive> create table student(id int,name string,age int)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;


OK
Time taken: 0.137 seconds
hive> 

//查询student数据表
hive> select * from student;

OK
1001	zhangshan	NULL
1002	lishi	NULL
1003	zhaoliu	NULL
Time taken: 0.14 seconds, Fetched: 3 row(s)
hive> 

注意：如元数据有的数据的信息，会直接加载进去

由于datas文件下的student.txt有元数据，所以数据的信息会直接加载到刚创建的同名数据表student表中

常用交互命令

（1）“-e”不进入hive的交互窗口执行sql语句

[root@hadoop101 hive-1.2.1]# bin/hive -e "select id from student;"

Logging initialized using configuration in jar:file:/usr/local/hadoop/module/hive-1.2.1/lib/hive-common-1.2.1.jar!/hive-log4j.properties

OK
1001
1002
1003
Time taken: 2.416 seconds, Fetched: 3 row(s)
[root@hadoop101 hive-1.2.1]# 

（2）“-f”执行脚本中sql语句

//在/usr/local/hadoop/module/datas目录下创建hivef.sql文件
[root@hadoop101 datas]#  touch hivef.sql
[root@hadoop101 datas]# vim hivef.sql 

select * from student;


//执行文件中的sql语句
[root@hadoop101 hive-1.2.1]#  bin/hive -f /usr/local/hadoop/module/datas/hivef.sql 

OK
1001	zhangshan	NULL
1002	lishi	NULL
1003	zhaoliu	NULL
Time taken: 2.598 seconds, Fetched: 3 row(s)
[root@hadoop101 hive-1.2.1]# 

//执行文件中的sql语句并将结果写入文件中
[root@hadoop101 hive-1.2.1]# bin/hive -f /usr/local/hadoop/module/datas/hivef.sql > /usr/local/hadoop/module/hive_result.txt


Logging initialized using configuration in jar:file:/usr/local/hadoop/module/hive-1.2.1/lib/hive-common-1.2.1.jar!/hive-log4j.properties
OK
Time taken: 2.452 seconds, Fetched: 3 row(s)

[root@hadoop101 hive-1.2.1]# 

//查看追加hive_result.txt文件信息
[root@hadoop101 module]# cat hive_result.txt 
1001	zhangshan	NULL
1002	lishi	NULL
1003	zhaoliu	NULL
[root@hadoop101 module]# 

Hive其他命令操作

// 退出hive窗口
hive(default)>exit;
hive(default)>quit;

/**
  在新版的hive中没区别了，在以前的版本是有的：
  exit:先隐性提交数据，再退出；
  quit:不提交数据，退出；
*/

//在hive cli命令窗口中如何查看hdfs文件系统
hive> dfs -ls /;

Found 2 items
drwx-wx-wx   - root supergroup          0 2019-12-29 17:17 /tmp
drwxr-xr-x   - root supergroup          0 2019-12-29 17:17 /user
hive> 

// 在hive cli命令窗口中如何查看本地文件系统
hive> ! ls /usr/local/hadoop/module/datas；

hivef.sql
hive_result.txt
student.txt

//查看在hive中输入的所有历史命令
[root@hadoop101 ~]#  cat .hivehistory

show databases;
use default;
show tables;
create table student(id int, name string);
show tables;
desc student;
insert into student (id,name) values (1,'tom');
select * from student;
quit;
exit
show databases;
exit;
show databases;
show tables;
create table student(id int,name strung)row format delimited fields terminated by '\t'
show databases;
show tables;
create table student(id int,name strung)row format delimited fields terminated by '\t';
 create table sqoop_test(NoViableAltException(26@[])
create table sqoop_test(id int,name string,age int)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
create table hive_test(id int,name string,age int)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
select * from hive_test;
create table student(id int,name string,age int)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
select * from student;
exit;