CREATE OR REPLACE TEMP TABLE in a script error: “Exceeded rate limits: too many table update operations for this table.”

Question

This script gives me an error after ~11 steps:

DECLARE steps INT64 DEFAULT 1;
LOOP 
  CREATE OR REPLACE TEMP TABLE countme AS (SELECT steps, 1 x, [1,2,3] y);
  SET steps = steps+1;
  IF steps=30 THEN LEAVE; END IF;
END LOOP;

Exceeded rate limits: too many table update operations for this table. For more information, see https://cloud.google.com/bigquery/troubleshooting-errors

Even if this is a temp table - what can I do instead?

Reported as issuetracker.google.com/issues/146172461, vote and follow for updates — Felipe Hoffa
why would you just simply re-create temp table within the loop w/o any use of it and/or any other actions. if you would add some real actions within the loop you might not get this error. meantime definitely using array variable is the way to go :o) I used this approach in quite number of answers here related to scripting - for example - stackoverflow.com/a/58273872/5221944. Another interesting example - stackoverflow.com/a/58242529/5221944 — Mikhail Berlyant
"Why" is because dealing with a TEMP TABLE makes it easier and shorter than dealing with ARRAY_AGG and UNNEST. Then the lack of other actions is because here I'm only showing a reproducible example. — Felipe Hoffa

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2019-12-12T23:58:33

Instead of using a TEMP TABLE, hold the results on a temp variable with an array. You can even materialize it as the last step:

DECLARE steps INT64 DEFAULT 1;
DECLARE table_holder ARRAY<STRUCT<steps INT64, x INT64, y ARRAY<INT64>>>;

LOOP 
  SET table_holder = (
    SELECT ARRAY_AGG(
      STRUCT(steps, 1 AS x, [1,2,3] AS y))
    FROM (SELECT '')
  );
  SET steps = steps+1;
  IF steps=30 THEN LEAVE; END IF;
END LOOP;

CREATE TABLE temp.results
AS
SELECT *
FROM UNNEST(table_holder)

CREATE OR REPLACE TEMP TABLE in a script error: “Exceeded rate limits: too many table update operations for this table.”

1 Answers