[improve]: `sleep`: fully in BigQuery SQL (to save cloud run / python costs)

AntoineGiraud commented 1 month ago

Check your idea has not already been reported

[X] I could not find the idea in existing issues

Edit `function_name` and the short idea description in title above

[X] I wrote the correct function name and a short idea description in the title above

Tell us everything

current sleep BigFunctions uses cloud run & python ... therefore generating cost

Here is a all BigQuery SQL solution found on stackoverflow : is-there-a-wait-method-for-google-bigquery-sql

-- procedure in order to wait X seconds (made only with full BigQuery SQL without any cloudrun)
CREATE OR REPLACE PROCEDURE `bigfunctions.eu.sleep(seconds INT64)
BEGIN
  DECLARE i INT64 DEFAULT 0;
  DECLARE seconds_to_iterations_ratio INT64 DEFAULT 75;
  DECLARE num_iterations INT64 DEFAULT seconds * seconds_to_iterations_ratio;
  WHILE i < num_iterations DO
    SET i = i + 1;
  END WHILE;
END;

Shall I do a MR & swap existing sleep function to this SQL logic ?

AntoineGiraud commented 1 month ago

example of use case :

propagating descriptions on columns from an other query ... where you can only do 5 DDL per 10 seconds !

example :

DECLARE i INT64 DEFAULT 5;
FOR sql_alter_descr in (/* {select generating DDL alter table column set options descriptions commands } */)
DO
  execute immediate sql_alter_descr.sql;
  SET i = i + 1;
  IF (i>=5) THEN
    -- sleep of 10s every 5 DDL edits on a table
    CALL bigfunctions.eu.sleep(10);
    SET i = 0;
  END IF;
END FOR;

unytics commented 1 month ago

Great @AntoineGiraud.

We should always prefer sql when it works.

However, I am wondering if the relation between number of iteration and time is really linear. Could you make some tests with seconds and minutes and check it works before opening a PR?

unytics / bigfunctions