hive常用命令，

hive常用命令，

[b]1.把内部表设置成外部表：[/b]
alter table table_name set TBLPROPERTIES ('EXTERNAL'='TRUE');
[b]2.查看Hadoop的hdfs文件[/b]
hadoop fs -text | more
[b]3.查看外部表信息[/b]
describe extended table_name
[b]4.创建外部表[/b]
CREATE EXTERNAL TABLE IF NOT EXISTS table_name(id int,name string)
partitioned by(day string)
row format delimited fields terminated by '|';
[b]5.加载hdfs数据到表中[/b]
alter table telno_app_times ADD IF NOT EXISTS PARTITION(day="20140317") location "/useapptimes/";
[b]6.查看分区[/b]
show partitions tb_test;
[b]添加分区[/b]
alter table telno_app_times ADD IF NOT EXISTS PARTITION(day="20140317")
[b]删除分区[/b]
ALTER TABLE login DROP IF EXISTS PARTITION (dt='2008-08-08');
ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除
[b]7.加载数据到指定分区表[/b]
load data local inpath'/root/hcr/tmp/sample.txt' into table tb_test partition(ds='2013-12-06',ds2='shanghai')
[b]8.修改表名rename to[/b]
alter table tb_records_ctas rename totb_records_2
[b]9.增加新列[/b]
alter table tb_records_2 add columns(new_col int);
[b]10.修改某一列的信息[/b]
ALTER TABLE tb_records_2 CHANGE COLUMN new_col col1 string;
[b]11.数据导出[/b]
[b]导出到本地目录[/b]
insert overwrite local directory'/root/hcr/tmp/ex_abc2.txt' select * from m_t2;
[b]导出到hdfs目录[/b]
insert overwrite directory'/user/houchangren/tmp/m_t2' select * from m_t2;
[b]11.1 hive 导出数据[/b]
bin/hive -e "select * from table" >> res.csv
[b]12.用jdbc连接hive之前一定要启动hiveserver;命令：[/b]
hive -service hiveserver &
hive --service metastore -----------Startup Hive Embedded
bin/hive --service hiveserver-----------Startup Hive Standalone
[b]13.hive部分优化[/b]
[b]hive查询limit优化[/b]
set hive.limit.optimize. enable=true;
[b]hive数据倾斜优化[/b]
set hive.groupby.skewindata=true;
[b]14.采用RCFile 方式压缩历史数据。FackBook全部hive表都用RCFile存数据。[/b]
[b]二、局部压缩方法[/b]
只需要两步：
[b]1.创建表时指定压缩方式，默认不压缩，以下为示例：[/b]
create external table track_hist(
id bigint, url string, referer string, keyword string, type int, gu_idstring,
…/*此处省略中间部分字段*/ …, string,ext_field10 string)
partitioned by (ds string) stored as RCFile location '/data/share/track_histk' ;

[b]2.插入数据是设定立即压缩[/b]
SET hive.exec.compress.output=true;
insert overwrite table track_histpartition(ds='2013-01-01')
select id,url, …/*此处省略中间部分字段*/ …, ext_field10 fromtrackinfo
where ds='2013-01-01';