google-bigquery - 6小时后查询超时，如何优化？

问题描述

我有两个表，shapes并且squares，我基于GEOGRAHPY列的交叉点加入。

该shapes表包含车辆的行驶路线：

shape_key        STRING            identifier for the shape
shape_lines      ARRAY<GEOGRAPHY>  consecutive line segments making up the shape
shape_geography  GEOGRAPHY         the union of all shape_lines
shape_length_km  FLOAT64           length of the shape in kilometers

Rows: 65k
Size: 718 MB

我们保持shape_lines分离，ARRAY因为形状有时会在自身上重复，并且我们希望将这些线段保持分离而不是重复数据删除。

该squares表包含一个 1×1 平方千米的网格：

square_key        INT64      identifier of the grid square
square_geography  GEOGRAPHY  four-cornered polygon describing the grid square

Rows: 102k
Size: 15 MB

这些形状代表车辆的行驶路线。对于每种形状，我们在单独的表格中计算了有害物质的排放量。目的是计算每个方格的排放量，假设它们沿路线均匀分布。为此，我们需要知道路线形状的哪一部分与每个网格单元相交。

这是计算它的查询：

SELECT
  shape_key,
  square_key,
  SAFE_DIVIDE(
      (
        SELECT SUM(ST_LENGTH(ST_INTERSECTION(line, square_geography))) / 1000
        FROM UNNEST(shape_lines) AS line
      ),
      shape_length_km)
    AS square_portion
FROM
  shapes,
  squares
WHERE
  ST_INTERSECTS(shape_geography, square_geography)

遗憾的是，此查询在 6 小时后超时，而不是产生有用的结果。

在最坏的情况下，查询可以产生 66 亿行，但实际上不会发生这种情况。我估计每个形状通常与 50 个网格正方形相交，因此输出应该是大约 65k * 50 = 3.3M 行；BigQuery 不应该处理任何事情。

我考虑了BigQuery 执行的地理连接优化：

空间连接是两个表的连接，WHERE子句中带有谓词地理函数。

查看。我什INNER JOIN至将我的改写为上面显示的等效“逗号”连接。
当您的地理数据被持久化时，空间连接的性能会更好。

查看。两者shape_geography都square_geography直接来自现有表。
BigQuery 使用以下标准 SQL 谓词函数为 INNER JOIN 和 CROSS JOIN 运算符实现优化的空间 JOIN：[...]ST_Intersects

查看。只需一个ST_Intersect电话，没有其他条件。
空间连接未优化：对于 LEFT、RIGHT 或 FULL OUTER 连接；在涉及 ANTI 连接的情况下；当空间谓词被否定时。

查看。这些情况均不适用。

所以我认为 BigQuery 应该能够使用它使用的任何空间索引数据结构来优化这个连接。

我还考虑了有关交叉连接的建议：

避免产生比输入更多的输出的连接。

这个查询肯定会产生比输入更多的输出；这是它的本质，无法避免。
当需要 aCROSS JOIN时，预先聚合您的数据。

为避免与生成的输出多于输入的连接相关的性能问题：
- 使用 GROUP BY 子句预先聚合数据。
查看。我已经预先汇总了按形状分组的排放数据，因此shapes表格中的每个形状都是独一无二的。
- 使用窗口函数。窗口函数通常比使用交叉连接更有效。有关详细信息，请参阅分析函数。
我认为这个查询不可能使用窗口函数。

我怀疑 BigQuery 根据输入行数分配资源，而不是根据中间表或输出的大小。这可以解释我所看到的病态行为。

我怎样才能使这个查询在合理的时间内运行？

标签： google-bigquerygiscartesian-product

google-bigquery - 6小时后查询超时，如何优化？

问题描述

解决方案

推荐阅读