修复BigQuery中的“超出资源”并使其运行得更快

Question

GDELT的Kalev遇到了这个问题--当分析了整整一个月时，下面的查询将在BigQuery中运行，但在一整年的时间内不会运行。

SELECT Source, Target, count, RATIO_TO_REPORT(count) OVER() Weight
FROM (
  SELECT a.name Source, b.name Target, COUNT(*) AS COUNT
  FROM (FLATTEN(
      SELECT
        GKGRECORDID, CONCAT( STRING(ROUND(FLOAT(IFNULL(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#(.*?)#'), '0')), 3)), '#', STRING(ROUND(FLOAT(IFNULL(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#.*?#(.*?)#'), '0')), 3)) ) AS name
      FROM [gdelt-bq:gdeltv2.gkg]
      WHERE DATE>20150100000000 and DATE<20151299999999, name)) a
  JOIN EACH (
    SELECT 
      GKGRECORDID, CONCAT( STRING(ROUND(FLOAT(IFNULL(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#(.*?)#'), '0')), 3)), '#', STRING(ROUND(FLOAT(IFNULL(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#.*?#(.*?)#'), '0')), 3)) ) AS name
    FROM [gdelt-bq:gdeltv2.gkg]
    WHERE DATE>20150100000000 and DATE<20151299999999 ) b
  ON a.GKGRECORDID=b.GKGRECORDID
  WHERE a.name<b.name
    AND a.name != '0.000000#0.000000'
    AND b.name != '0.000000#0.000000'
  GROUP EACH BY 1, 2
  ORDER BY 3 DESC )
WHERE count > 50
LIMIT 500000

“在查询执行期间超出了资源。”

我们怎么才能解决这个问题？

Answer 1

首先是关于成本优化的注意事项:每列扫描一次BigQuery费用，此查询将超过72 go。GDELT表将其全部存储在一个表中--我们可以通过创建年度表而不是单个表来优化成本。

现在，我们如何才能修复这个查询，使其运行一整年呢？“查询执行期间超出的资源”通常来自不可伸缩的函数。例如：

• RATIO_TO_REPORT(COUNT) OVER()不会缩放: OVER()函数运行于整个结果集，允许我们计算总计以及每一行贡献了多少-但是要运行这个函数，我们需要整个结果集来适应一个VM。好消息是，OVER()能够在分区数据时进行扩展，例如，有一个OVER(按月划分)，然后我们只需要每个分区就可以适应VM。对于这个查询，为了简单起见，我们将移除这个结果列。
• ORDER不会缩放:要排序结果，我们也需要所有的结果来适应一个VM。这就是为什么“--允许--大--结果”不允许一步一步地运行订单，因为每个VM将并行地处理和输出结果。

在这个查询中，我们有一种通过可伸缩性处理顺序的简单方法--我们将把后面的过滤器"WHERE > 50“移到前面的进程中。与其对所有结果进行排序，并过滤具有COUNT>50的结果，我们还将将其移动并将其更改为HAVING，因此它在订单执行之前运行如下：

SELECT Source, Target, count
FROM (
  SELECT a.name Source, b.name Target, COUNT(*) AS COUNT
  FROM (FLATTEN(
      SELECT
        GKGRECORDID, CONCAT( STRING(ROUND(FLOAT(IFNULL(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#(.*?)#'), '0')), 3)), '#', STRING(ROUND(FLOAT(IFNULL(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#.*?#(.*?)#'), '0')), 3)) ) AS name
      FROM [gdelt-bq:gdeltv2.gkg]
      WHERE DATE>20150100000000 and DATE<20151299999999,name)) a
  JOIN EACH (
    SELECT 
      GKGRECORDID, CONCAT( STRING(ROUND(FLOAT(IFNULL(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#(.*?)#'), '0')), 3)), '#', STRING(ROUND(FLOAT(IFNULL(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#.*?#(.*?)#'), '0')), 3)) ) AS name
    FROM [gdelt-bq:gdeltv2.gkg]
    WHERE DATE>20150100000000 and DATE<20151299999999 ) b
  ON a.GKGRECORDID=b.GKGRECORDID
  WHERE a.name<b.name
    AND a.name != '0.000000#0.000000'
    AND b.name != '0.000000#0.000000'
  GROUP EACH BY 1, 2
  HAVING count>50
  ORDER BY 3 DESC )
LIMIT 500000

现在，查询运行了整整一年的数据！

让我们看一下解释统计：

​

​

我们可以看到，1.88亿行表被读取了两次:第一个子查询生成15亿行(给定“平面”)，第二个子查询过滤掉不是在2015年的行(请注意，该表在2015年年初开始存储数据)。

第三阶段很有趣:连接这两个子查询产生了30亿行！通过过滤器和聚合步骤，这些用户减少到5亿人：

​

​

我们能做得更好吗？

是!让我们将2 WHERE a.name != '....'移到前面的“具有”：

SELECT Source, Target, count
FROM (
  SELECT a.name Source, b.name Target, COUNT(*) AS COUNT
  FROM (FLATTEN(
      SELECT
        GKGRECORDID, CONCAT( STRING(ROUND(FLOAT(IFNULL(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#(.*?)#'), '0')), 3)), '#', STRING(ROUND(FLOAT(IFNULL(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#.*?#(.*?)#'), '0')), 3)) ) AS name
      FROM [gdelt-bq:gdeltv2.gkg]
      WHERE DATE>20150100000000 and DATE<20151299999999
      HAVING name != '0.000000#0.000000',name)) a
  JOIN EACH (
    SELECT 
      GKGRECORDID, CONCAT( STRING(ROUND(FLOAT(IFNULL(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#(.*?)#'), '0')), 3)), '#', STRING(ROUND(FLOAT(IFNULL(REGEXP_EXTRACT(SPLIT(V2Locations,';'),r'^[2-5]#.*?#.*?#.*?#.*?#.*?#(.*?)#'), '0')), 3)) ) AS name
    FROM [gdelt-bq:gdeltv2.gkg]
    WHERE DATE>20150100000000 and DATE<20151299999999 
    HAVING name != '0.000000#0.000000') b
  ON a.GKGRECORDID=b.GKGRECORDID
  WHERE a.name<b.name
  GROUP EACH BY 1, 2
  HAVING count>50
  ORDER BY 3 DESC )
LIMIT 500000

这个跑得更快了！

让我们看一下解释统计：

​

​

看见?通过在加入前将过滤移到一个步骤，第3阶段只需要经过10亿行，而不是30亿行。速度要快得多(即使对于BigQuery，您可以自己检查，它能够在短时间内处理一个联接生成的超过30亿行)。

这个查询是为了什么？

看看这里漂亮的结果：http://blog.gdeltproject.org/a-city-level-network-diagram-of-2015-in-one-line-of-sql/

​

​