CODEWITHSUNDEEP
google-bigquery
Dylan Russell
Dylan Russell

Reputation: 1098

BigQuery exceeding CPU limits

I keep getting the error

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
  )
);

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 (
     ...
    );

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).

Why is the CPU seconds limit changing and how can I address this issue?

Upvotes: 0

Views: 1270

Answers (1)

Yun Zhang
Yun Zhang

Reputation: 5503

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.

Upvotes: 1

Related Questions