hive使用技巧（一）自动化动态分配表分区及修改hive表字段名称，使用技巧hive

hive使用技巧（一）自动化动态分配表分区及修改hive表字段名称，使用技巧hive

Author：FuRenjie kwu

1、自动化动态分配表分区

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ods.fund2hundsunlg PARTITION(day)
select distinct fromHostIp ,hundsunNodeIp,concat(substring(requestTime,0,10),' ', substring(requestTime,12,8)) , httpStatus ,responseTimes,urlpath, responseCharts ,postBody, 
concat(substring(requestTime,0,4),substring(requestTime,6,2), substring(requestTime,9,2)) as day
from ods.fund2hundsunlog  ;

说明：

1）set hive.exec.dynamic.partition.mode=nonstrict; 设置表分区可动态加载

2）concat(substring(requestTime,0,4),substring(requestTime,6,2), substring(requestTime,9,2)) as day，根据已有时间的切分来做partition

2、快速修改hive表字段名称

1）  重新创建新表

drop table ods.dratio;
create EXTERNAL table ods.dratio (
dratioId string comment "用户ID:用户ID:860010-2370010130,注册用户ID：860010-2370010131",
cookieId string comment "mcookie",
sex string comment "sex: 1 男， 2 女",
age string comment "age: 1 0-18, 2 19-29, 3 30-39, 4 40以上",
ppt string comment "ppt: 1 高购买力 2 中购买力 3 低购买力",
degree string comment "degree: 1 本科以下 2 本科及其以上",
favor string comment "喜好信息(不定长)", 
commercial string comment "商业价值信息(不定长)"
)
comment "用户行为分析"
partitioned by(day string comment "按天的分区表字段")
STORED AS TEXTFILE 
location '/dw/ods/dratio';

2）重新分配表分区的数据，无须数据移动
alter table ods.dratio add partition(day='20150507') location '/dw/ods/dratio/day=20150507';
alter table ods.dratio add partition(day='20150508') location '/dw/ods/dratio/day=20150508';
alter table ods.dratio add partition(day='20150509') location '/dw/ods/dratio/day=20150509';
alter table ods.dratio add partition(day='20150510') location '/dw/ods/dratio/day=20150510';
alter table ods.dratio add partition(day='20150511') location '/dw/ods/dratio/day=20150511';
alter table ods.dratio add partition(day='20150512') location '/dw/ods/dratio/day=20150512';
alter table ods.dratio add partition(day='20150513') location '/dw/ods/dratio/day=20150513';
alter table ods.dratio add partition(day='20150514') location '/dw/ods/dratio/day=20150514';
alter table ods.dratio add partition(day='20150515') location '/dw/ods/dratio/day=20150515';
alter table ods.dratio add partition(day='20150516') location '/dw/ods/dratio/day=20150516';
alter table ods.dratio add partition(day='20150517') location '/dw/ods/dratio/day=20150517';
alter table ods.dratio add partition(day='20150518') location '/dw/ods/dratio/day=20150518';
alter table ods.dratio add partition(day='20150519') location '/dw/ods/dratio/day=20150519';
alter table ods.dratio add partition(day='20150520') location '/dw/ods/dratio/day=20150520';
alter table ods.dratio add partition(day='20150521') location '/dw/ods/dratio/day=20150521';