1. 修改配置
-- 开启动态分区,默认开启
set hive.exec.dynamic.partition=true;
-- 严格模式要求只少有一个分区字段是指定值的
set hive.exec.dynamic.partition.mode=nonstrict;
-- 一个DML操作可以创建的最大动态分区数,默认1000
set hive.exec.max.dynamic.partitions=100000;
-- 每个mapper和reducer可以创建最大分区数量,默认100
set hive.exec.max.dynamic.partitions.pernode=100000;
2. 建立结果表
create table if not exists operation_log (
user_id bigint,
opt_type string
)
partitioned by (year int, month int, day int)
row format delimited fields terminated by '\001'
stored as textfile;
3. 插入数据
通过 insert into/overwrite
导入数据
insert
into table credit.operation_log
partition(year, month, day)
select user_id,
opt_type,
year(opt_time) as year,
month(opt_time) as month,
day(opt_time) as day
from credit.ods_operation_log;
附:可能遇到的问题
(1)动态分区数量过小导致的问题
- FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
- Fatal error occurred when node tried to create too many dynamic partitions
- mr.ExecMapper (ExecMapper.java:map(178)) - org.apache.hadoop.hive.ql.metadata.HiveExcept
ion: Hive Runtime Error while processing row {“user_id”:26044,“opt_type”:“2”,“opt_time”:“2008-04-11 00:01:00”}
解决方案:增加最大动态分区数量
(2)retry: Resource temporarily unavailable 同时可能伴随着JVM崩溃状况
原因:数据量太大,分区数量太多
解决方案:减少每次进行动态分区的数量,比如在上面的测试例中,手动指定年份分区值,对月和日进行动态分区