Hive command常用

hive 相关命令使用：

1. hive -S -e "select * from table_name limit 10" > /tmp/myquery
   1. -S 开启静默模式，将结果输出到文件
2. show create table table_name;
3. CREATE TABLE table_name(`id` string, ...) STORED AS orc;
4. hive -f insert.sql （执行sql脚本）
5. row format delimited fields terminated by '\001' collection items terminated by '\002' map keys terminated by '\003' lines terminated by '\n' stored as textfile;
6. LOAD DATA LOCAL INPATH '/root/chenc/data/usrGSPPBZ.txt' INTO TABLE usrGSPPBZ;
7. hive -e 'set hive.execution.engine=tez; set hive.cli.print.header=true; set hive.resultset.use.unique.column.names=false; select xwbt, xwnr from jyqyqb.usrzxzb limit 100' | sed 's/,/，/g' | sed 's/\t/,/g' > ./bydata.csv
8. hive -e "set hive.execution.engine=mr; set hive.cli.print.header=true; set hive.resultset.use.unique.column.names=false; select rowkey, id, xxfbrq, xxfbsj, mtcc, mtcccl, xwly, xwlymc, lmfl, mtlm, xwbt, xwfbt, xwzy, substr(xwnr,1,20000) xwnr, zxzz, zxjg, zxjgbh, bmh, bmxx, ljdz, xwnrhc, xxjb, sjybm, sjybmmc, qgzfm, zzs, hfs, djs, gkbz, xgry, xgry2, xgsj, fbsj, shry, jsid, host, msgsourceurl, msgtype, msgsubtype, substr(contentimglinks,1,5000) contentimglinks, contentimglinkslocations, sector, infotype, infoflag from jyqyqb.usrzxzb where fbsj like '2018-12-04%'" | sed 's/,/，/g' | sed 's/\t/,/g' > ./bydata.csv

hive client：

hive --help / hive -H

hive -e 'select a.col from db1.tab1 a'

hive -S -e 'select a.col from db1.tab1 a' > a.txt (-S 静默模式)

hive -f /home/my/hive-script.sql

hive -f hdfs://<namenode>:<port>/hive-script.sql

hive interactive shell commands：

ALTER TABLE table_name ADD COLUMNS (new_col INT);

ALTER TABLE usrzxzb CHANGE id id string;

ALTER TABLE usrzxzb ADD COLUMNS (zxdl int);

ALTER TABLE name DROP [COLUMN] column_name

ALTER TABLE name CHANGE column_name new_name new_type;

ALTER TABLE events RENAME TO 3koobecaf;

INSERT INTO table_name SELECT * FROM ...;

INSERT OVERWRITE TABLE table_name SELECT * FROM ...;