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

Question

我有两张 table ，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 中分离出来因为形状有时会自己加倍，我们希望将这些线段分开而不是 deduplicating them .
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 不应该处理的任何事情。

我考虑过 the geographic join optimizations由 BigQuery 执行:

Spatial JOINs are joins of two tables with a predicate geographic function in the WHERE clause.

查看。我什至重写了我的 INNER JOIN到上面显示的等效“逗号”连接。

Spatial joins perform better when your geography data is persisted.

查看。两者 shape_geography和 square_geography直接来自现有表。

BigQuery implements optimized spatial JOINs for INNER JOIN and CROSS JOIN operators with the following standard SQL predicate functions: [...] ST_Intersects

查看。就一个ST_Intersect打电话，没有其他条件。

Spatial joins are not optimized: for LEFT, RIGHT or FULL OUTER joins; in cases involving ANTI joins; when the spatial predicate is negated.

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

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

我也考虑过 advice about cross joins :

Avoid joins that generate more outputs than inputs.

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

When a CROSS JOIN is required, pre-aggregate your data.

To avoid performance issues associated with joins that generate more outputs than inputs:

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

查看。我已经预先聚合了按形状分组的排放数据，以便 shapes 中的每个形状表是独一无二的。

• Use a window function. Window functions are often more efficient than using a cross join. For more information, see analytic functions.

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

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

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

Answer 1

我认为squares被倒置，导致几乎完整的地球多边形:

select st_area(square_geography), * from   `open-transport-data.public.squares`

打印结果如 5.1E14 - 这是完整的地球区域。所以任何一条线几乎都与所有的正方形相交。有关详细信息，请参阅 BigQuery 文档: https://cloud.google.com/bigquery/docs/gis-data#polygon_orientation

您可以通过运行 ST_GeogFromText(wkt, FALSE) 来反转它们。 - 选择较小的多边形，忽略多边形方向，这工作得相当快:

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
  `open-transport-data.public.shapes`,
  (select 
       square_key, 
       st_geogfromtext(st_astext(square_geography), FALSE) as square_geography,
     from `open-transport-data.public.squares`) squares
WHERE
  ST_INTERSECTS(shape_geography, square_geography)