BigQuery超过CPU限制 [英] BigQuery exceeding CPU limits

问题描述

我不断收到错误消息

Query exceeded resource limits. 2730.807817954678 CPU seconds were used, and this query must use less than 2500.0 CPU seconds. at [2:3]

起初我正在运行此查询:

At first I was running this query:

create temp table data as
select *
from table
left join othertable
using(caseid);

EXECUTE IMMEDIATE (
  SELECT """
  SELECT caseid, """ || 
    STRING_AGG("""MAX(IF(code = '""" || code || """', 1, 0)) AS _""" || REPLACE(code, '.', '_'), ', ') 
  || """
  FROM data 
  GROUP BY caseid
  """
  FROM (
    SELECT DISTINCT code
    FROM data
    ORDER BY code
  )
);

我以为可能是太多的记录(750万条记录)，所以我将查询范围缩小了:

I thought maybe it was just too many records (7.5 million records), so I narrowed the query to this:

    create temp table data as
    select *
    from table
    left join othertable
    using(caseid)
    where year = "2009";
    
    EXECUTE IMMEDIATE (
     ...
    );

这应该只有大约150,000条记录.这次我仍然遇到相同的错误，这只是减少了CPU秒的最大限制.以前它告诉我它必须使用少于10,000 CPU秒，然后当我减小查询的大小时它告诉我它必须少于2500 CPU秒(如上面的初始错误消息所示).

This should only be about 150,000 records. I still get the same error only this time it just reduces the max limit of CPU seconds. Previously it was telling me it must use less than 10,000 CPU seconds then when I reduced the size of the query it told me it must be less than 2,500 CPU seconds (as shown in the initial error message above).

为什么更改CPU秒数限制，我该如何解决此问题?

推荐答案

这与您正在处理的数据量无关.由于您不是按固定费率计费，因此查询由扫描的数据量来收费(处理).您可以想象在给定的数据量上允许的处理上限(或CPU时间).

It is not about the amount of data that you're processing.Since you're not on flat rate, your query is charged by the amount of data scanned (processed). You can imagine there is an upper limit of the processing (or CPU time) allowed on given amount of data.

人们大部分时间都碰到这种情况，因为他们对数据进行了过多的计算.解决该问题的一种方法是将查询分解为较小的步骤，并实现介于两者之间的中间状态(也许使用临时表).

People bumped into this most of time because they had too much computation over the data. One way to work around is to break up the query into smaller steps and materialize the intermediate state (maybe with temp table) in between.

这篇关于BigQuery超过CPU限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！