蜂巢解决方案中的非相等左外部联接 [英] Non equi Left outer join in hive workaround

问题描述

如何在涉及两个以上表的配置单元中实现非等号左外部联接?

How to implement non-equi left outer join in hive involving more than two tables?

使用的查询:

SELECT cs.SID, ins.ID, a.ID, e.id, i.id, cs.dateId, cs.timeId,cs.SSTRT,cs.SEND,cs.VAL,cs.IND,cs.TYP,cs.DTPE,cs.BCDE, cs.IVAL,cs.ICNT,cs.RDT,cs.REJ 
from cs_item_hive cs 
LEFT outer JOIN installation_hive ins ON  (cs.SID=ins.SN)
LEFT OUTER JOIN account_hive a ON (ins.AID=a.ID AND ins.MDID = a.MDID)
LEFT OUTER JOIN equipment_hive e ON ins.GBLSID=e.GSN
LEFT OUTER JOIN item_hive i ON (cs.BCDE=i.ibc AND ins.MDID = i.MDID AND ins.AID = i.AID)
where cs.SEND >= ins.IDAT AND cs.SEND < ins.RDAT;

问题:

与WHERE条件不匹配的记录被跳过. [因此，在使用条件的情况下，Hive不支持非等额联接]

Records that are not matching WHERE condition are skipped. [As, Hive does not support non-equi join, where condition is used]

要求:

与条件不匹配的记录应将ins.ID，a.ID，e.id，i.id替换为 NULL (如等联接).

Records that are not matching the condition should have the ins.ID, a.ID, e.id, i.id substituted as NULL [as like equi-join].

如何在配置单元中实现这样的功能:

How to implement something like this in hive:

SELECT cs.SID, ins.ID, a.ID, e.id, i.id, cs.dateId, cs.timeId,cs.SSTRT,cs.SEND,cs.VAL,cs.IND,cs.TYP,cs.DTPE,cs.BCDE, cs.IVAL,cs.ICNT,cs.RDT,cs.REJ 
from cs_item_hive cs 
LEFT outer JOIN installation_hive ins ON  (cs.SID=ins.SN AND cs.SEND >= ins.IDAT AND cs.SEND < ins.RDAT)
LEFT OUTER JOIN account_hive a ON (ins.AID=a.ID AND ins.MDID = a.MDID)
LEFT OUTER JOIN equipment_hive e ON ins.GBLSID=e.GSN
LEFT OUTER JOIN item_hive i ON (cs.BCDE=i.ibc AND ins.MDID = i.MDID AND ins.AID = i.AID);

请指导我解决此问题.

谢谢.

推荐答案

您可以非常接近:

where (cs.SEND >= ins.IDAT AND cs.SEND < ins.RDAT) or
      (ins.IDAT is null)

或者，我认为您可以使用两个查询，一个用于匹配，一个用于不匹配.

Alternatively, I think you can use two queries, one for matches and one for non-matches.

这篇关于蜂巢解决方案中的非相等左外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！