Hive history date mapping

浏览数：20 / 时间：2015年06月11日

Hive history table mapping

create table fdl_family as
select * from 
(select ‘acc1‘ as account,‘family1‘ as family,‘2010-01-01‘ as effect_date from nums where num<1
union all
select ‘acc1‘ as account ,‘family2‘ as family,‘2013-01-01‘ as effect_date from nums where num<1
)  a;

create table fdl_family_period as 
select *,effect_date as start_date, coalesce(lead(effect_date) over(partition by account order by effect_date),‘9999-12-31‘) as end_date from fdl_family ;

alter table fdl_family_period change nextdate end_date string;
select * from fdl_family_period ;

--hive --service hiveserver -p 10002;

create table andes_std_trans  as
select * from 
(select ‘acc1‘ as account,‘maker1‘ as maker,100 as amount,‘2012-10-11‘ as trans_date from nums where num<1
union all
select ‘acc1‘ as account ,‘maker1‘ as maker,200 as amount,‘2013-10-11‘ as trans_date from nums where num<1
)  a;

select a.*,b.family from andes_std_trans a join fdl_family_period b on a.account = b.account
where  a.trans_date between b.start_date and b.end_date;

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

Hive history date mapping