问查询超时6小时后，如何优化它？

EN

我有两个表，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执行的BigQuery：

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

检查一下。我甚至将我的INNER JOIN重写为上面所示的等效的“逗号”联接。

• 当您的地理数据被持久化时，空间联接表现得更好。

检查一下。shape_geography和square_geography都直接来自现有的表。

• BigQuery使用以下标准SQL谓词函数为内部联接和交叉连接运算符实现优化的空间连接：. ST_Intersects

检查一下。只有一个ST_Intersect调用，没有其他条件。

• 空间连接没有优化:对于左、右或完全外部联接；在涉及反连接的情况下；当空间谓词被否定时。

检查一下。这些情况都不适用。

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

我也考虑过关于交叉连接的建议

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

这个查询肯定会产生比输入更多的输出；这是它的本质，是无法避免的。

• 当需要CROSS JOIN时，预先聚合您的数据。 为了避免与连接相关的性能问题，这些连接会产生比输入更多的输出：

- Use a GROUP BY clause to pre-aggregate the data.

检查一下。我已经预先聚合了按形状分组的排放数据，这样shapes表中的每个形状都是唯一和不同的。

• • 使用窗口函数。窗口函数通常比使用交叉连接更有效。有关更多信息，请参见解析函数。

我认为这个查询不可能使用窗口函数。

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

如何使此查询在合理的时间内运行？