hive常用命令

CREATE TABLE t_hive (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|';

LOAD DATA LOCAL INPATH '/lost+found/test1_hive' OVERWRITE INTO TABLE t_hive ;

show tables;

show tables '*i*';

select * from t_hive;

desc  t_hive;

ALTER TABLE t_hive ADD COLUMNS (d String);

ALTER TABLE t_hive RENAME TO t_wang;

drop table t_wang;

quit,exit:  退出交互式shell
reset: 重置配置为默认值
set <key>=<value> : 修改特定变量的值(如果变量名拼写错误，不会报错)
set:  输出用户覆盖的hive配置变量
set -v : 输出所有Hadoop和Hive的配置变量
add FILE[S] *, add JAR[S] *, add ARCHIVE[S] * : 添加 一个或多个 file, jar, archives到分布式缓存
list FILE[S], list JAR[S], list ARCHIVE[S] : 输出已经添加到分布式缓存的资源
list FILE[S] *, list JAR[S] *,list ARCHIVE[S] * : 检查给定的资源是否添加到分布式缓存
delete FILE[S] *,delete JAR[S] *,delete ARCHIVE[S] * : 从分布式缓存删除指定的资源
! <command> :  从Hive shell执行一个shell命令
dfs <dfs command> :  从Hive shell执行一个dfs命令
<query string> : 执行一个Hive 查询，然后输出结果到标准输出
source FILE <filepath>:  在CLI里执行一个hive脚本文件

hadoop fs -cat /user/hive/warehouse/t_wang/test1_hive

CREATE TABLE t_hive2 (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|';     #创建表
INSERT OVERWRITE TABLE t_hive2 SELECT * FROM t_hive;

CREATE TABLE t_hive3 LIKE t_hive;

hadoop fs -cp /user/hive/warehouse/t_hive /

hadoop fs -ls /t_hive

hadoop fs -cat /t_hive/test1_hive

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/t_hive' SELECT * FROM t_hive;

! cat /tmp/t_hive/000000_0;

create view v_hive as select a,b from t_hive where c>30; 

drop view v_hive;

     000001,092023,9.76
     000002,091947,8.99
     000004,092002,9.79
     000005,091514,2.2
     000001,092008,9.70
     000001,092059,9.45

     000001,092023,9.76
     000002,091947,8.99
     000004,092002,9.79
     000005,091514,2.2
     000001,092008,9.70
     000001,092059,9.45   

     DROP TABLE IF EXISTS t_hft;  

     CREATE TABLE t_hft(
     SecurityID STRING,
     tradeTime STRING,
     PreClosePx DOUBLE
     ) PARTITIONED BY (tradeDate INT)

    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';    

LOAD DATA LOCAL INPATH '/lost+found/t_hft_1' OVERWRITE INTO TABLE t_hft PARTITION (tradeDate=20160220);
LOAD DATA LOCAL INPATH '/lost+found/t_hft_2' OVERWRITE INTO TABLE t_hft PARTITION (tradeDate=20160221);  

SHOW PARTITIONS t_hft;

select * from t_hft where securityid='000001';

select * from t_hft where tradedate=20160220 and preclosepx<9;  

export table dept_count to '/lost+found/hive_dept_count_o';

hive -e 'use test;select * from t_hive';

hive -f test.sql

dfs -ls /;

describe database test;

desc formatted student;