Hive Cli

Hive Cli

[b]= hive启动 =[/b]
*$ hive –f script.q
*$ hive -e 'SELECT * FROM dummy‘
*$ hive -S -e 'SELECT * FROM dummy‘
*$ hive -hiveconf hive.root.logger=DEBUG,console

[b]= set =[/b]
*hive> SET hive.enforce.bucketing=true; //设置值
*hive> SET hive.enforce.bucketing; // 显示值
*hive> set -v; //显示所有的值，包括hadoop的。
*hive> set; //显示跟基本的hadoop不同的配置，原理就是比对当前的所有选项与基本的配置是否不同，如修改过，已经不同了就打印该值。

[b]= dfs 命令 =[/b]
dfs 命令可以执行 Shell 环境下的 hadoop fs 的所有命令
例如：
列出 HDFS 上的文件：
hive> dfs -ls /user/hive;

[b]= add =[/b]
*ADD { FILE[S] | JAR[S] | ARCHIVE[S] } <filepath1> [<filepath2>]*
*hive> add jar /tmp/a.jar;
*hive> add jar /tmp/a.jar /tmp/b.jar;

[b]= delete =[/b]
*DELETE { FILE[S] | JAR[S] | ARCHIVE[S] } [<filepath1> <filepath2> ..]

[b]= list =[/b]
*LIST { FILE[S] | JAR[S] | ARCHIVE[S] } [<filepath1> <filepath2> ..]
*hive> list jar;
*hive> list jars;

[b]= show =[/b]
*hive> show functions;
*hive> show tables;
*hive> show tables '*tianzhao*';
*hive> show partition tablename;
*hive> show table extended like table_with_partitions partition(ds=101);

[b]= desc =[/b]
*hive> desc/describe function length;
*hive> desc/describe tablename;
*hive> desc/describe extended tablename; //显示表的信息
*hive> desc/describe extended tablename partition(ds=1); //显示partition的信息
*hive> desc/describe formatted tablename; //显示表的信息,跟extended相比，显示更友好

[b]= source =[/b]
*hive> source /home/username/sql/test.sql;

[b]= ! =[/b]
*hive> !ls; //ls当前目录

[b]= quit =[/b]
hive> quit; 或者 hive> exit;

[b]= hiveserver =[/b]
* $hive --service hiveserver
* $hive --service help
Usage ./hive <parameters> --service serviceName <service parameters>
Service List: cli help hiveserver hwi jar lineage metastore rcfilecat start-hive stop-hive
Parameters parsed:
--auxpath : Auxillary jars
--config : Hive configuration directory
--service : Starts specific service/component. cli is default
Parameters used:
HADOOP_HOME : Hadoop install directory
HIVE_OPT : Hive options
For help on a particular service:
./hive --service serviceName --help


*$hive --service start-hive
Starting Hive Thrift Server in Daemon Mode
starting jar, logging to /home/tianzhao/hive/hadoop-0.19.1/bin/../logs/hadoop-tianzhao-jar-ubuntu.out
*$hive --service stop-hive
Stopping Hive Thrift Server in Daemon Mode
stopping jar


[http://wiki.apache.org/hadoop/Hive/LanguageManual/Cli 官方的配置Wiki]

alter table s_spu set TBLPROPERTIES ('EXTERNAL'='TRUE'); //内部表转外部表

set hive.auto.convert.join=true;
set hive.exec.mode.local.auto=true;
set hive.mapred.local.mem = 200;
set hive.groupby.skewindata=true;

[b]alter serde[/b]
数据：
a,b
c,d
e,f
hive> create table delim(key string, value string);
hive> load data local inpath '/home/tianzhao/Documents/delim' into table delim;
hive> select * from delim;
a,b NULL
c,d NULL
e,f NULL
hive> ALTER TABLE delim SET SERDEPROPERTIES ('field.delim' = ',');
a b
c d
e f

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-DropPartitions
ALTER TABLE page_view DROP PARTITION (dt='2008-08-08', country='us');

ALTER TABLE web_log ADD IF NOT EXISTS PARTITION (pt='20120325') LOCATION 'hdfs://localhost:9000/group/log1/2012/20120325';
ALTER TABLE web_log PARTITION(pt='20120325') SET FILEFORMAT SequenceFile;
ALTER TABLE r_table SET SERDEPROPERTIES ('serialization.null.format'=);

日志在： /tmp/$USER/hive.log 中