累积性快照事实表使用示例

drop table if exists dwd_order_info
create table dwd_order_info(

orderId string,
create_time string,
--工单的业务上的创建时间，用于每天的分时统计
operatelifecycle map<string,string>,--操作进度生命周期

stars map<string,string>,--评价星级map
AUDIT_ORDER_star string,
EVALUATE_ORDER_star string,
RETURN_VISIT_star string,

corcode_f4 string,name_f4 string,
corcode_f3 string,name_f3 string,
corcode_f2 string,name_f2 string,
corcode_f1 string,name_f1 string,

institutionId string,
platfromFiledCode string,
serviceFlowAlias string,
orderSource string,
orderSourceName string,
orderStatus string,
orderStatusName string,

actualhour string,
second_hour string,
third_hour string,
man_hour string,--该工单对应的服务类型下的定义工时
istimeout string,

isurgent string,
isimportance string,
issupervise string,
reworknum string,
isrework string,

deal_user_ids string,--处理人,处理环节的所有的处理人
dealUserOrgIds string,--处理人组织集合

comefrom string
)
partitioned by (dt string) --业务上的创建时间
stored as parquet
location '/warehouse/paascloud/dwd/dwd_order_info'



set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_order_info partition(dt)

select

if(new.orderid is null,old.orderid,new.orderid)  
,if(new.ordercreatetime is null,old.create_time,new.ordercreatetime) 
,if(new.operatelifecycle is null,old.operatelifecycle,new.operatelifecycle)

,if(new.stars is null,old.stars,new.stars) as stars
,if(new.AUDIT_ORDER_star is null,old.AUDIT_ORDER_star,new.AUDIT_ORDER_star) 
,if(new.EVALUATE_ORDER_star is null,old.EVALUATE_ORDER_star,new.EVALUATE_ORDER_star) 
,if(new.RETURN_VISIT_star is null,old.RETURN_VISIT_star,new.RETURN_VISIT_star) 

,if(new.corcode_f4 is null,old.corcode_f4,new.corcode_f4)
,if(new.name_f4 is null,old.name_f4,new.name_f4)
,if(new.corcode_f3 is null,old.corcode_f3,new.corcode_f3)
,if(new.name_f3 is null,old.name_f3,new.name_f3)
,if(new.corcode_f2 is null,old.corcode_f2,new.corcode_f2)
,if(new.name_f2 is null,old.name_f2,new.name_f2)
,if(new.corcode_f1 is null,old.corcode_f1,new.corcode_f1)
,if(new.name_f1 is null,old.name_f1,new.name_f1)

,if(new.institutionId is null,old.institutionId,new.institutionId)
,if(new.platfromFiledCode is null,old.platfromFiledCode,new.platfromFiledCode)
,if(new.serviceFlowAlias is null,old.serviceFlowAlias,new.serviceFlowAlias)
,if(new.orderSource is null,old.orderSource,new.orderSource)
,if(new.orderSourceName is null,old.orderSourceName,new.orderSourceName)
,if(new.orderStatus is null,old.orderStatus,new.orderStatus)
,if(new.orderStatusName is null,old.orderStatusName,new.orderStatusName)

,if(new.actualhour is null,old.actualhour,new.actualhour)
,if(new.second_hour is null,old.second_hour,new.second_hour)
,if(new.third_hour is null,old.third_hour,new.third_hour)
,if(new.man_hour is null,old.man_hour,new.man_hour)
,if(new.istimeout is null,old.istimeout,new.istimeout)

,if(new.isurgent is null,old.isurgent,new.isurgent)
,if(new.isimportance is null,old.isimportance,new.isimportance)
,if(new.issupervise is null,old.issupervise,new.issupervise)
,if(new.reworknum is null,old.reworknum,new.reworknum)
,if(new.isrework is null,old.isrework,new.isrework)

,if(new.deal_user_ids is null,old.deal_user_ids,new.deal_user_ids)
,if(new.dealUserOrgIds is null,old.dealUserOrgIds,new.dealUserOrgIds)

,if(new.comefrom is null,old.comefrom,new.comefrom)
,date_format(if(new.ordercreatetime is null,old.create_time,new.ordercreatetime),'yyyy-MM-dd')
from

(

select

	*
from
	dwd_order_info
where
	dt in (
	select
		date_format(ordercreatetime, 'yyyy-MM-dd')
	from
		wfs_order_list_index
	where
		dt = '2020-05-31' )
		
) old

full outer join
(

select
	info.orderid,
	info.ordercreatetime,
	trace.operatelifecycle,
	trace.stars,
	nvl(trace.stars['AUDIT_ORDER'],'-1') as AUDIT_ORDER_star,
	nvl(trace.stars['RETURN_VISIT'],'-1') as RETURN_VISIT_star,
	nvl(trace.stars['EVALUATE_ORDER'],'-1') as EVALUATE_ORDER_star,
	org.corcode_f4 ,
	org.name_f4 ,
	org.corcode_f3 ,
	org.name_f3 ,
	org.corcode_f2 ,
	org.name_f2 ,
	org.corcode_f1 ,
	org.name_f1,
	
	info.institutionId ,
	info.platfromFiledCode ,
	info.serviceFlowAlias ,
	info.orderSource ,
	info.orderSourceName ,
	info.orderStatus ,
	info.orderStatusName ,
	
	info.actualhour ,
	detail.man_hour as second_hour ,
	detail2.man_hour as third_hour ,
	case
		when orderThirdlyType is not null then detail2.man_hour
		when orderThirdlyType is null
		and orderSecondType is not null then detail.man_hour
	end as man_hour ,
	--该工单对应的服务类型下的定义工时
 	case
		when orderThirdlyType is not null then if(info.actualHour>detail2.man_hour, 1, 0)
		when orderThirdlyType is null
		and orderSecondType is not null then if(info.actualHour>detail.man_hour, 1, 0)
	end as istimeout,
	
	if(info.urgent=1 or info.urgent=2,1,0) as isurgent,
	info.importance as isimportance,
	if(info.superviseNum>0,1,0) as issupervise,
	info.reworknum as reworknum,--该工单返工次数
	if(info.reworkNum>0,1,0) as isrework,
	
	info.dealUserIds as deal_user_ids,
	info.dealUserOrgIds as dealUserOrgIds,
	
	'es' as comefrom
from
	(
	select
		*
	from
		wfs_order_list_index
	where
		dt = '2020-05-31' ) info
join (
	select
		workOrderId 
		,str_to_map(concat_ws(',', collect_set(concat(operateType, '=>', createTime))), ',', '=>') operatelifecycle
		,str_to_map(concat_ws(',', collect_set(concat(operateType, '=>', if(evaluateLevel is not null,evaluateLevel,'-1')))), ',', '=>') stars
	
	from
		wfs_order_track_index
		--- where dt='2020-05-31'

		group by workOrderId ) trace on
	info.orderid = trace.workOrderId
join(
	select
		*
	from
		t_org_flat
	where
		dt = '2020-05-31' )org on
	info.institutionId = org.corcode_f1
	
	join tbworkorder_config_detail detail on
		info.orderSecondType=detail.id
	join tbworkorder_config_detail detail2 on
		info.orderThirdlyType=detail2.id
	
	) new
on old.orderid=new.orderid

累积性快照事实表适用于订单，工单这类生命周期跨越多天的表

数据导入策略：导入计算当前的新增和变化数据new=(createtime and updatetime=dt from ods)，新增的进入新的分区，有变化的更新变化的分区old(dt in (dt1,dt2...))