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