I’am new to hive and i want to implement the following query
select a.controlid, b.name as campaign, a.controlactivityid as activitysource, c.code as codemyaprc, c.label_en as label_en, c.label_fr as label_fr from bo_h_control.bridgeactivity a join suvh_econtrol.TMP_GPS_REF b ON a.controlcampaignid = b.ps_cam_id left join srv_h_a8460_fpc.activity c ON a.controlactivityid=c.code and c.date_creation= select max(date_creation) from srv_h_a8460_fpc.activity
It works fine untill the last left join. Nested queries are obviously not authorized in hive.
How can i approach this.
Advertisement
Answer
Use sub-query instead of joined table:
select a.controlid, b.name as campaign, a.controlactivityid as activitysource, c.code as codemyaprc, c.label_en as label_en, c.label_fr as label_fr from bo_h_control.bridgeactivity a join suvh_econtrol.TMP_GPS_REF b ON a.controlcampaignid = b.ps_cam_id left join (select c.*, max(date_creation) over() as max_date_creation from srv_h_a8460_fpc.activity c ) c ON a.controlactivityid=c.code and c.date_creation=c.max_date_creation
Or better move last join condition inside the WHERE clause in the subquery:
select a.controlid, b.name as campaign, a.controlactivityid as activitysource, c.code as codemyaprc, c.label_en as label_en, c.label_fr as label_fr from bo_h_control.bridgeactivity a join suvh_econtrol.TMP_GPS_REF b ON a.controlcampaignid = b.ps_cam_id left join (select * from ( select c.*, max(date_creation) over() as max_date_creation from srv_h_a8460_fpc.activity c )c where max_date_creation=date_creation ) c ON a.controlactivityid=c.code