BigQuery中的优化查询

Question

我试图在BigQuery中运行一个查询，但是我收到了“查询执行期间超出的资源”。

实际上，我有两张桌子：

Table user:
Id  | User   | Latitude  | Longitude
1   | 1      |  50.83    |  4.01
2   | 1      |  50.84    |  4.03
3   | 2      |  48.78    |  2.87
4   | 3      |  47.42    |  8.53
…
Table point_of_interest:
Id  | Latitude  | Longitude | Range   |  Tag  
1   |  50.81    |  3.98     |  0.05   |  a;b;c;d
2   |  50.85    |  4.03     |  0.025  |  a;c;e;f
3   |  40.80    |  3.87     |  0.04   |  a;d;g
4   |  47.42    |  8.57     |  0.08   |  b
…

其目的是加入这些表，用纬度、经度和范围标记所有用户。

为此，我使用了这个查询：

SELECT
  u.User AS id,
  GROUP_CONCAT(poi.Tag) AS tag
FROM (
  SELECT
    u.User,
    poi.Tag,
  FROM
    [user] u
  CROSS JOIN
    [point_of_interest] poi
  WHERE
    u.Latitude BETWEEN poi.Latitude – poi.Range AND poi.Latitude + poi.Range
    AND
    u.Longitude BETWEEN poi.Longitude – poi.Range AND poi.Longitude + poi.Range )
GROUP BY
  id

用户表当前为520 MB，point_of_interest表仅为565 KB，但可能会随着时间的推移而增长。

我想知道是否有更好的方法来实现这一目标，以及实现这一目标的最佳架构。

编辑：

我还尝试使用range LEFT JOIN EACH，但是BigQuery只支持ON关键字之后的相等语句。

Answer 1

您需要拆分表并运行多个较小的查询。

就像这样：

SELECT * FROM table WHERE ABS(HASH(id) % 100) == 0
SELECT * FROM table WHERE ABS(HASH(id) % 100) == 1
SELECT * FROM table WHERE ABS(HASH(id) % 100) == 2
SELECT * FROM table WHERE ABS(HASH(id) % 100) == 3
...
SELECT * FROM table WHERE ABS(HASH(id) % 100) == 99

但是，您需要找到一个相当高的数字，而不是我的示例中的100，并编写一段代码为您自动化这一点。首先，手动尝试一个碎片，并在碎片中保留相当大的记录空间。

您可以将结果WRITE_APPEND到相同的目标表中，并与原始数据分开存储。

Answer 2

对于类似的问题，我通过为每一行生成键来优化它，这些键可以用来避免在整个数据集上交叉连接。

http://googlecloudplatform.blogspot.com/2014/03/geoip-geolocation-with-google-bigquery.html

StackO：How to improve performance of GeoIP query in BigQuery?

顺便问一下，这个问题与后来发布的问题(Tag huge list of elements with lat/long with large list of geolocation data)有关吗？