hive实现树形结构展开

最近遇到了树形结构存储，比如地市，节目目录这种结构。
比如：节目目录展开

drop table aps.tmp_q1;
create table aps.tmp_q1 as 
select
  epg_cate_id alternatekey,
  epg_cata_ttl chinesetitle,
  first_py englishtitle,
  bppc_id bppcid,
  par_id pid,
  epg_cate_id level1_id,
  epg_cata_ttl level1_chinesetitle,
  first_py level1_englishtitle,
  '' level2_id,
  '' level2_chinesetitle,
  '' level2_englishtitle,
  '' level3_id,
  '' level3_chinesetitle,
  '' level3_englishtitle,
  '' level4_id,
  '' level4_chinesetitle,
  '' level4_englishtitle
from test 
where par_id=0
;
drop table aps.tmp_q2;
create table aps.tmp_q2 as
select
  a.epg_cate_id alternatekey,
  a.epg_cata_ttl chinesetitle,
  a.first_py englishtitle,
  a.bppc_id bppcid,
  a.par_id pid,
  b.level1_id level1_id,
  b.level1_chinesetitle level1_chinesetitle,
  b.level1_englishtitle level1_englishtitle,
  a.epg_cate_id level2_id,            
  a.epg_cata_ttl level2_chinesetitle,  
  a.first_py level2_englishtitle,  
  '' level3_id,            
  '' level3_chinesetitle,  
  '' level3_englishtitle,  
  '' level4_id,            
  '' level4_chinesetitle,  
  '' level4_englishtitle   
from test a , aps.tmp_q1 b
where a.par_id=b.level1_id
union all
select
  alternatekey,        
  chinesetitle,        
  englishtitle,        
  bppcid,              
  pid,                 
  level1_id,           
  level1_chinesetitle, 
  level1_englishtitle, 
  level2_id,           
  level2_chinesetitle, 
  level2_englishtitle, 
  level3_id,           
  level3_chinesetitle, 
  level3_englishtitle, 
  level4_id,           
  level4_chinesetitle, 
  level4_englishtitle  
from aps.tmp_q1
;

drop table aps.tmp_q3;
create table aps.tmp_q3 as
select
  a.epg_cate_id alternatekey,
  a.epg_cata_ttl chinesetitle,
  a.first_py englishtitle,
  a.bppc_id bppcid,
  a.par_id pid,
  b.level1_id level1_id,
  b.level1_chinesetitle level1_chinesetitle,
  b.level1_englishtitle level1_englishtitle,
  b.level2_id level2_id,            
  b.level2_chinesetitle level2_chinesetitle,  
  b.level2_englishtitle level2_englishtitle,  
  a.epg_cate_id level3_id,            
  a.epg_cata_ttl level3_chinesetitle,  
  a.first_py level3_englishtitle,  
  '' level4_id,            
  '' level4_chinesetitle,  
  '' level4_englishtitle   
from test a , aps.tmp_q2 b
where a.par_id=b.level2_id
union all
select
  alternatekey,        
  chinesetitle,        
  englishtitle,        
  bppcid,              
  pid,                 
  level1_id,           
  level1_chinesetitle, 
  level1_englishtitle, 
  level2_id,           
  level2_chinesetitle, 
  level2_englishtitle, 
  level3_id,           
  level3_chinesetitle, 
  level3_englishtitle, 
  level4_id,           
  level4_chinesetitle, 
  level4_englishtitle  
from aps.tmp_q2
;
drop table aps.tmp_q4;
create table aps.tmp_q4 as
select
  a.epg_cate_id alternatekey,
  a.epg_cata_ttl chinesetitle,
  a.first_py englishtitle,
  a.bppc_id bppcid,
  a.par_id pid,
  b.level1_id level1_id,
  b.level1_chinesetitle level1_chinesetitle,
  b.level1_englishtitle level1_englishtitle,
  b.level2_id level2_id,            
  b.level2_chinesetitle level2_chinesetitle,  
  b.level2_englishtitle level2_englishtitle,  
  b.level3_id level3_id,            
  b.level3_chinesetitle level3_chinesetitle,  
  b.level3_englishtitle level3_englishtitle,  
  a.epg_cate_id level4_id,            
  a.epg_cata_ttl level4_chinesetitle,  
  a.first_py level4_englishtitle   
from test a , aps.tmp_q3 b
where a.par_id=b.level3_id
union all
select
  alternatekey,        
  chinesetitle,        
  englishtitle,        
  bppcid,              
  pid,                 
  level1_id,           
  level1_chinesetitle, 
  level1_englishtitle, 
  level2_id,           
  level2_chinesetitle, 
  level2_englishtitle, 
  level3_id,           
  level3_chinesetitle, 
  level3_englishtitle, 
  level4_id,           
  level4_chinesetitle, 
  level4_englishtitle  
from aps.tmp_q3
;

insert overwrite table  aps.pptv_epg_catalog_ed 
select 
  alternatekey,        
  chinesetitle,        
  englishtitle,        
  bppcid,              
  pid,                 
  level1_id,           
  level1_chinesetitle, 
  level1_englishtitle, 
  level2_id,           
  level2_chinesetitle, 
  level2_englishtitle, 
  level3_id,           
  level3_chinesetitle, 
  level3_englishtitle, 
  level4_id,           
  level4_chinesetitle, 
  level4_englishtitle 
from aps.tmp_q4
;