sql - 在 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，但以后可能会增长。

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

编辑:

我还尝试使用范围 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 到同一个目标表中，并与原始数据分开存储。