A loop, and BEGIN ... EXCEPTION
Some jobs repeat: the same steps for each make, each month, each table. A script can repeat statements in a loop and carry on when one pass goes wrong, though one GROUP BY query beats a loop over data almost every time.
How it works
LOOP runs its statements again and again until a LEAVE ends it. A counter variable says which item the pass is on, and a temp table keeps what each pass found, so the last SELECT can return all of it. BEGIN ... EXCEPTION WHEN ERROR THEN ... END is a block with a safety net. If a statement inside it fails, BigQuery stops that block and runs the handler instead, and the script carries on after END.
LOOP ... END LOOP;repeats the statements between them. Only aLEAVEends it, usually inside anIF.BREAKdoes the same job asLEAVE. If nothing in the loop moves the value that theIFchecks, the loop never ends.- To go through a list, keep a counter:
DECLARE i INT64 DEFAULT 0;, read the item withmakes[OFFSET(i)](the first item isOFFSET(0)),LEAVEwheni >= ARRAY_LENGTH(makes), andSET i = i + 1;at the end of each pass. AFOR rec IN (SELECT ...) DO ... END FOR;loop walks the rows of a query without a counter. This lesson usesLOOPso that you can see the counter and the exit. CONTINUEskips the rest of the pass and starts the next one, andITERATEmeans the same.WHILE condition DO ... END WHILE;checks its condition before every pass, so it needs noLEAVEto stop.CREATE TEMP TABLE pass_rates (make STRING, pass_rate FLOAT64);makes a table that lasts as long as the script. Each pass canINSERTinto it, and the last statement canSELECTfrom it. A temp table made inside aBEGINblock is still there afterEND.CREATE TEMP TABLEandINSERTare borrowed from the Writing data track, which teaches them in full. Here they only keep what each pass found.BEGIN ... EXCEPTION WHEN ERROR THEN ... END;runs the statements beforeEXCEPTION. If one fails, the rest of the block is skipped, the statements afterTHENrun, and the script carries on afterEND. If nothing fails, the handler is skipped.- A handler undoes nothing. Statements that ran before the failing one have taken effect: a
SEThas changed its variable and anINSERThas added its row. The failing statement itself adds nothing, and a variable it was meant to set keeps its old value. In a loop that is the value from the previous pass, so a handler that reads it can record a stale number. The handler can use what the block already did. - As you saw,
DECLAREgoes first in a script or first in aBEGINblock. A variable declared inside a block exists only inside that block, and its handler cannot see it: the handler fails withUnrecognized name. A variable declared before the block can be set inside the block and read in the handler. - A handler catches errors that happen when a statement runs. Examples are a division by zero, a failed
ASSERT, a missing table or column, and a query without the date filter thatfact_mot_testneeds. ASSERT condition AS 'message';raises an error when the condition is not true. A handler cannot catch a syntax error, because the whole script is read before anything runs. A handler does not catch an error raised inside the handler itself. While you write a handler,@@error.messageholds the error text.SAFE_DIVIDEalready avoids the error of a division by zero, and it is the better tool for that one error. For a make with no tests it givesNULLand no error, and anINSERT ... SELECTstill adds one row for that make, because a query withCOUNT(*)and noGROUP BYreturns one row even when nothing matches. A handler is the general tool for errors that aSAFE_function cannot head off, such as a failedASSERTor a missing table. Every loop in this lesson could also be oneGROUP BYquery. A loop earns its place when each pass is a statement that can fail on its own, or when stopping early means you never read the rest of the data.
Common mistake
WHEN ERROR catches every error, including your own mistakes. A typo in a column name of the block's INSERT is an error too, so the handler runs on every pass and the script still finishes, with every make marked as failed. Keep the block small, and make the handler record something you can see, such as a failed flag or a skipped status.
Worked example
DECLARE makes ARRAY<STRING> DEFAULT ['HONDA', 'MAZDA', 'MAZDAA'];
DECLARE i INT64 DEFAULT 0;
CREATE TEMP TABLE audit (make STRING, step_no INT64, step STRING);
LOOP
IF i >= ARRAY_LENGTH(makes) THEN
LEAVE;
END IF;
BEGIN
DECLARE target_make STRING DEFAULT makes[OFFSET(i)];
INSERT INTO audit (make, step_no, step) VALUES (target_make, 1, 'started');
INSERT INTO audit (make, step_no, step)
SELECT
target_make,
2,
CONCAT('finished: ', CAST(ROUND(COUNTIF(test_result = 'PASSED') / COUNT(*), 3) AS STRING))
FROM `learn.mot.fact_mot_test`
WHERE make = target_make
AND completed_date BETWEEN '2024-01-01' AND '2024-12-31';
EXCEPTION WHEN ERROR THEN
INSERT INTO audit (make, step_no, step) VALUES (makes[OFFSET(i)], 3, 'failed');
END;
SET i = i + 1;
END LOOP;
SELECT make, step_no, step FROM audit ORDER BY make, step_no;The list holds three makes. MAZDAA is a typo for MAZDA, so it has no tests in 2024. i counts the passes, and the LEAVE ends the loop once i has gone past the last make.
Each pass is a BEGIN ... END block. Its DECLARE comes first in the block, and target_make exists only for that pass.
The block writes a started row, then tries to write a finished row with the make's pass rate. HONDA and MAZDA get both rows.
For MAZDAA the second INSERT fails with a division by zero. The handler writes a failed row, and the loop goes on. The started row is still there, because a handler undoes nothing. The handler cannot use target_make, so it reads the make from the list again.
You do not need a script to count tests per make, because one GROUP BY query does that. The script helps because each pass can fail on its own, and the handler records that pass and lets the other passes finish.