sql - 如何使用 Hive 查询 3 个大表的相交值？

Question

我有 3 个非常大的 IP 地址表*，我正在尝试计算这 3 个表之间的公共(public) IP 数量。我考虑过使用连接和子查询来查找这 ​​3 个表之间的 IP 交集。如何通过一个查询找到所有 3 个表的交集？

这是不正确的语法，但说明了我正在努力完成的事情:

SELECT COUNT(DISTINCT(a.ip)) FROM a, b, c WHERE a.ip = b.ip = c.ip

我已经看到关于如何连接 3 个表的其他答案，但没有关于 Hive 的内容，也没有关于这种规模的内容。

* 注意事项:

• 表 a:70 亿行
• 表 b:18 亿行
• 表 c:1.68 亿行
• “表”实际上是由 S3 支持的 Hive Metastore。
• 每个表中有很多重复的IP
• 欢迎提出性能建议。
• 如果使用 Spark SQL 查询而不是 Hive 是更好的主意，也可以运行 Spark SQL 查询。

Answer 1

正确的语法是:

SELECT COUNT(DISTINCT a.ip)
FROM a JOIN
     b
     ON a.ip = b.ip JOIN
     c
     ON a.ip  = c.ip;

这可能不会在我们有生之年完成。更好的方法是:

select ip
from (select distinct a.ip, 1 as which from a union all
      select distinct b.ip, 2 as which from b union all
      select distinct c.ip, 3 as which from c
     ) abc
group by ip
having sum(which) = 6;

承认，sum(which) = 6 只是说这三个都存在。由于子查询中的 select distinct，您可以这样做:

having count(*) = 3