hive cli
Hive Cli
= hive启动 =
*$ hive –f script.q
*$ hive -e 'SELECT *FROM dummy‘
*$ hive -S -e 'SELECT* FROM dummy‘
*$ hive -hiveconfhive.root.logger=DEBUG,console
= set =
*hive>SET hive.enforce.bucketing=true; //设置值
*hive>SET hive.enforce.bucketing; // 显示值
*hive>set -v; //显示所有的值，包括hadoop的。
*hive> set;//显示跟基本的hadoop不同的配置，原理就是比对当前的所有选项与基本的配置是否不同，如修改过，已经不同了就打印该值。
= dfs 命令 =
dfs 命令可以执行 Shell 环境下的hadoop fs 的所有命令
例如：
列出 HDFS 上的文件：
hive> dfs -ls/user/hive;
= add =
*ADD { FILE[S] |JAR[S] | ARCHIVE[S] } <filepath1> [<filepath2>]*
*hive> add jar/tmp/a.jar;
*hive> add jar/tmp/a.jar /tmp/b.jar;
= delete =
*DELETE { FILE[S] |JAR[S] | ARCHIVE[S] } [<filepath1> <filepath2> ..]
= list =
*LIST { FILE[S] |JAR[S] | ARCHIVE[S] } [<filepath1> <filepath2> ..]
*hive> list jar;
*hive> list jars;
= show =
*hive> showfunctions;
*hive> showtables;
*hive> show tables'*tianzhao*';
*hive> showpartition tablename;
*hive> show tableextended like table_with_partitions partition(ds=101);
= desc =
*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相比，显示更友好
= source =
*hive> source/home/username/sql/test.sql;
= ! =
*hive>!ls; //ls当前目录
= quit =
hive>quit; 或者 hive> exit;
= hiveserver =
* $hive --servicehiveserver
* $hive --servicehelp
Usage ./hive<parameters> --service serviceName <service parameters>
Service List: clihelp 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 aparticular service:
./hive --service serviceName --help
*$hive --servicestart-hive
Starting Hive ThriftServer in Daemon Mode
starting jar, loggingto /home/tianzhao/hive/hadoop-0.19.1/bin/../logs/hadoop-tianzhao-jar-ubuntu.out
*$hive --servicestop-hive
Stopping Hive ThriftServer in Daemon Mode
stopping jar
[http://wiki.apache.org/hadoop/Hive/LanguageManual/Cli官方的配置Wiki]
altertable s_spu set TBLPROPERTIES ('EXTERNAL'='TRUE'); //内部表转外部表
sethive.auto.convert.join=true;
sethive.exec.mode.local.auto=true;
sethive.mapred.local.mem = 200;
sethive.groupby.skewindata=true;
alter serde
数据：
a,b
c,d
e,f
hive> create tabledelim(key string, value string);
hive> load datalocal inpath '/home/tianzhao/Documents/delim' into table delim;
hive> select *from delim;
a,b NULL
c,d NULL
e,f NULL
hive> ALTER TABLEdelim SET SERDEPROPERTIES ('field.delim' = ',');
a b
c d
e f
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-DropPartitions
ALTER TABLE page_viewDROP PARTITION (dt='2008-08-08', country='us');
ALTER TABLE web_logADD IF NOT EXISTS PARTITION (pt='20120325') LOCATION'hdfs://localhost:9000/group/log1/2012/20120325';
ALTER TABLE web_logPARTITION(pt='20120325') SET FILEFORMAT SequenceFile;
ALTER TABLE r_tableSET SERDEPROPERTIES ('serialization.null.format'=);
日志在：/tmp/$USER/hive.log 中
=kill job=
/dhwdata/hadoop/bin/../bin/hadoopjob -Dmapred.job.tracker=hdpjt:9001-kill job_201208241319_843121