Hive history date mapping

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;