hadoop - Hive - 或条件与左外连接

Question

我已经将所有关于类似案例的查询都提到了。尽管错误可能很常见，但我正在寻找针对特定情况的解决方案。请不要将问题标记为重复，除非您使用已接受的解决方案得到完全相同的场景。

---

我有两张 table

Main table:

c1  c2  c3  c4  c5
1   2   3   4   A

Other table

c1  c2  c3  c4  c5
1   8   5   6   B
8   2   8   9   C
8   7   3   9   C
8   7   9   4   C
5   6   7   8   D

现在，从另一个表中，我应该只能在所有列中选择唯一的记录。例如仅最后一行 (5,6,7,8, D)。

其他表的第 1 行被拒绝，因为 c1 值 (1) 与主表中的 c1 值 (1) 相同，第 2 行被拒绝，因为其他表和主表的 c2 值匹配，同样...

简而言之，在查询输出的主表中，其他表中的任何列都不应具有相同的值(在相应列中)。

---

我尝试创建以下查询

select t1.* from otherTable t1
LEFT OUTER JOIN mainTable t2
ON ( t1.c1 = t2.c1 OR t1.c2 = t2.c2 OR t1.c3 = t2.c3 OR t1.c4 = t2.c4 )
Where t2.c5 is null;

但是，hive 抛出以下异常

OR not supported in JOIN currently

我了解配置单元的局限性，很多时候我都使用 UNION (ALL | DISTINCT) 和 inner join 来克服这个局限性；但无法对此使用相同的策略。

请帮忙。

编辑 1:我有配置单元版本限制 - 只能使用版本 1.2.0

Answer 1

您可以进行笛卡尔积连接(无条件的内部连接):

select t1.* from otherTable t1
,mainTable t2
WHERE  t1.c1 != t2.c1 AND t1.c2 != t2.c2 
       AND t1.c3 != t2.c3 AND t1.c4 != t2.c4 AND t1.c5 !=  t2.c5;

假设您在 mainTable 中有一行，此查询应该与使用 OUTER JOIN

的查询一样高效

另一种选择是将您提出的查询分成 5 个不同的LEFT OUTER JOIN 子查询:

select t1.* from (
  select t1.* from (
    select t1.* from (
      select t1.* from (
        select t1.* from otherTable t1
        LEFT OUTER JOIN (select distinct c1 from mainTable) t2
        ON ( t1.c1 = t2.c1) Where t2.c1 is null ) t1
      LEFT OUTER JOIN (select distinct c2 from mainTable) t2
      ON ( t1.c2 = t2.c2) Where t2.c2 is null ) t1
    LEFT OUTER JOIN (select distinct c3 from mainTable) t2
    ON ( t1.c3 = t2.c3) Where t2.c3 is null ) t1
  LEFT OUTER JOIN (select distinct c4 from mainTable) t2
  ON ( t1.c4 = t2.c4) Where t2.c4 is null ) t1
LEFT OUTER JOIN (select distinct c5 from mainTable) t2
ON ( t1.c5 = t2.c5) Where t2.c5 is null
;

在这里，对于每一列，我首先从 mainTable 中获取不同的列，然后将其与 otherTable 的剩余部分连接起来。缺点是我在 mainTable 上传递了 5 次 - 每列一次。如果主表中的值是唯一的，您可以从子查询中删除 distinct。