hive 动态分区与静态分区,静态分区需要制定分区字段的值插入分区数据,动态分区可以根据分区字段的值自动插入对应分区
静态分区:须指定分区字段的值
insert overwrite table test.tmp_edw_customer_event_detail_di partition(dt='20161218')
select
cid
, event_code
, event_name
, event_channel_type_code
, event_time
, event_user_role_code
, event_extra_data
, event_source_table_name
, event_source_table_code
, event_origin_id
, event_latitude
, event_longitude
, event_trace_request_id
, load_job_number
, load_job_name
, insert_timestamp
, source_system_code
from test.ic_xf_event_from_user_trace_filter_init
where to_date(event_time) <= '2016-12-18';
动态分区:插入字段个数和顺序与目标表一致
-- 设置开启动态分区参数
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table test.tmp_edw_customer_event_detail_di partition(dt)
select
cid
, event_code
, event_name
, event_channel_type_code
, event_time
, event_user_role_code
, event_extra_data
, event_source_table_name
, event_source_table_code
, event_origin_id
, event_latitude
, event_longitude
, event_trace_request_id
, load_job_number
, load_job_name
, insert_timestamp
, source_system_code
, regexp_replace(to_date(event_time), '-', '') as dt
from test.ic_xf_event_from_user_trace_filter_init
where to_date(event_time) > '2016-12-18';