首页 文章

在BigQuery中有效地加入IP范围

提问于
浏览 次
1

我有一个IP地址表和一个IP地址范围表（start ip，end ip），我想一起加入 . 我已经能够使用以下查询来完成此工作：

SELECT * FROM `ips` i
JOIN `ranges`  a
ON NET.SAFE_IP_FROM_STRING(i.ip) 
BETWEEN NET.SAFE_IP_FROM_STRING(a.start_ip) 
AND NET.SAFE_IP_FROM_STRING(a.end_ip)

问题是我希望能够为数千万行做到这一点 . （我已经尝试将 NET.SAFE_IP_FROM_STRING 的输出写入范围表，但它只能将速度提高大约10％，并且对缩放没有帮助） .

范围不重叠，因此对于输入表中的每一行，我期望输出表中有0或1行 . 一个 LATERAL JOIN 会让我这样做，几乎肯定会加快速度，但我不认为BigQuery支持它们 . 有没有其他方法可以使这个查询更快和可扩展？

google-bigquery

2 回答

  • 1

    在审阅了与Felipe 's answer I was able to put something together that is incredibly fast and scales really well. As Felipe alluded to, the trick is to do a direct join on a prefix (I went with /16), and then filter with a between. I'中链接的https://cloudplatform.googleblog.com/2014/03/geoip-geolocation-with-google-bigquery.html上的文章之后，预处理范围以将大于/ 16的任何内容拆分为多个块 . 然后我用这个查询覆盖表，这会添加一些额外的字段：

    SELECT *, 
NET.SAFE_IP_FROM_STRING(start_ip) AS start_b, 
NET.SAFE_IP_FROM_STRING(end_ip) AS end_b, 
NET.IP_TRUNC(NET.SAFE_IP_FROM_STRING(start_ip), 16) as prefix

    然后，连接查询看起来像这样：

    SELECT * FROM `ips` i
JOIN `ranges`  a
ON a.prefix = NET.IP_TRUNC(NET.SAFE_IP_FROM_STRING(i.ip), 16)
WHERE NET.SAFE_IP_FROM_STRING(i.ip) BETWEEN a.start_b AND a.end_b

    现在，在计费等级1上，将1000万个IP加入到100万个范围内只需不到30秒！

    回复于
  • 4

    https://stackoverflow.com/a/20156581我做了类似的事情

    我需要更新我对#standardSQL的查询，但基本的秘密是生成一个较小的JOIN区域 .

    如果您可以共享示例数据集，我将很乐意提供新的查询 .

    回复于

相关问题