Handle multiple conditions before running a BigQuery job using a scheduled query

Question

I plan to have a scheduled query in BigQuery where it run the some query to check for some conditions first. Then, if all condition passed, it will run the aggregation query.

What I can think of right now is to do something like this:

declare A default (select count(*) from mydataset.mytable);  --SELECT query to get a value
declare B ...
declare C ... 
. . . -- get all of the value into a variable

-- Then use if-else condition e.g.
if A > 100 and B = C+D+E and B = F and ...
then
   -- run an aggregation queries
else
   select "not pass the condition"; -- don't run anything
end if;

This way is workable but I want to make it more visible when some condition is not met. I can view later what condition is not passed. Any idea to improve this when running in scheduled query?

user_13248738 · Answer

Combining the use of the raise and multiple conditional tests to specify the specific error you can use the following:

BEGIN
    DECLARE A DEFAULT (SELECT COUNT(*) FROM `ds.table`);
    DECLARE B DEFAULT (SELECT COUNT(*) FROM `ds.table`);

    DECLARE C1 DEFAULT A>1;
    DECLARE C2 DEFAULT B>A;

    IF C1 AND C2 THEN
        SELECT "Execute Query";
    ELSE 
        IF NOT (C1) THEN raise USING message="Condition 1 failed"; END IF;
        IF NOT (C2) THEN raise USING message="Condition 2 failed"; END IF;
    END IF; 

EXCEPTION WHEN ERROR THEN 
    SELECT @@error.message;
END;

But if you will have multiple faillling conditions, its better to use a select:


    DECLARE A DEFAULT (SELECT COUNT(*) FROM `ds.table`);
    DECLARE B DEFAULT (SELECT COUNT(*) FROM `ds.table`);

    DECLARE C1 DEFAULT A>10;
    DECLARE C2 DEFAULT B>A;

    IF C1 AND C2 THEN
        SELECT "Execute Query";
    ELSE 
        IF NOT (C1) THEN select "Condition 1 failed"; END IF;
        IF NOT (C2) THEN select "Condition 2 failed"; END IF;
    END IF;

Handle multiple conditions before running a BigQuery job using a scheduled query

1 Answers