BigQuery - wait UDF function with JavaScript code

Question

In Bigquery does not exist a native function like DBMS_LOCK.SLEEP (or DBMS_SESSION.SLEEP) from Oracle ("Puts a procedure to sleep for a specific time"). I tried to use an UDF with JavaScript code but it does not work as I expect: it does not stop other than forced. Can you help me understand what's going on? Thanks!

CREATE TEMP FUNCTION JS_Sleep(x FLOAT64)
 RETURNS FLOAT64
LANGUAGE js AS r"""
  function sleep(milliseconds) {
    const date = Date.now();
    let currentDate = null;
    do {
      currentDate = Date.now();
    } while (currentDate - date < milliseconds);
  }
  return sleep(x);

  """;
select JS_Sleep(10000);

busy loops are generally to be avoided, but how about const targetDate= Date.now() + milliseconds; while(Date.now()<targetDate){} - aaaalso function busySleep — hanshenrik
– hanshenrik, Commented May 31, 2022 at 9:22
Thanks, @hanshenrik! I agreed with you about busy loops (I need just loops not with "bussy" :) ). Your code variant behaves the same: it only stops forcibly. — Marius D.
– Marius D., Commented May 31, 2022 at 9:38
in that case, Date.now() is probably broken... or the argument is broken. any chance you can read console.log(milliseconds) output? — hanshenrik
– hanshenrik, Commented May 31, 2022 at 10:37

Jaytiger · Accepted Answer · 2022-05-31 14:29:27Z

1

In BigQuery, CURRENT_TIMESTAMP() similar with Date.now() always returns same value in a single statement or in a single transaction as if time stops regardless how many it appears in a query.

In my experience, this also applies to Date.now(). In your case, JS_Sleep() runs forever cause time stops within your query.

edited May 31, 2022 at 14:29

answered May 31, 2022 at 11:09

Jaytiger

12.3k2 gold badges7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marius D. Over a year ago

I agree with you on BQ and how CURRENT_TIMESTAMP () works. That's why I chose Javascript in BQ - where I didn't expect the same behavior. All my tests prove you right! Thanks for the help!

Collectives™ on Stack Overflow

BigQuery - wait UDF function with JavaScript code

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related