Is there a wait method for Google BigQuery SQL

I’m scheduling a query to run every day that picks up data from a table that get’s written to Google BigQuery automatically. Usually the table is there, but I’d like to be sure before I execute the query depending on this table.

I’m looking into other ways as well, but the simplest way seems to be to use just SQL and integrate the checking and retrying into the scheduled query. I’m able to check if the table exists and retry if it doesn’t. I can’t seem to find a way to not do this immediately and have the query wait for 30 minutes before the next retry. Is there something available, possibly similar to ‘WAITFOR’ that will achieve this?

Current SQL;

DECLARE retry_count INT64;
DECLARE success BOOL;
DECLARE size_bytes INT64;
DECLARE row_count INT64;
SET retry_count = 1;
SET success = FALSE;

WHILE retry_count <=  3 AND success = FALSE DO
BEGIN
  SET row_count = (SELECT row_count FROM [DATASET].__TABLES__ WHERE table_id='[TABLE]');
  IF row_count > 0  THEN
    SELECT 'Table Exists!' as message, retry_count as retries;
    SET success = TRUE;
  ELSE
    SELECT 'Table does not exist' as message, retry_count as retries, row_count;
    SET retry_count = retry_count + 1;
--      WAITFOR DELAY '00:30:00';
  END IF;
END;
END WHILE;

Answer

I hope you found ways to get the time delay added. I too recently came across such a situation and this is how I handled it –

DECLARE retry_count INT64;
DECLARE success BOOL;
DECLARE size_bytes INT64;
DECLARE row_count INT64;
DECLARE DELAY_TIME DATETIME;
DECLARE WAIT STRING;
SET retry_count = 1;
SET success = FALSE;


WHILE retry_count <=  3 AND success = FALSE DO
BEGIN
  SET row_count = (SELECT row_count FROM [DATASET].__TABLES__ WHERE table_id='[TABLE]');
  IF row_count > 0  THEN
    SELECT 'Table Exists!' as message, retry_count as retries;
    SET success = TRUE;
  ELSE
    SELECT 'Table does not exist' as message, retry_count as retries, row_count;
    SET retry_count = retry_count + 1;
--      WAITFOR DELAY '00:30:00';
    SET WAIT = 'TRUE';
    SET DELAY_TIME = DATETIME_ADD(CURRENT_DATETIME,INTERVAL 30 MINUTE);
    WHILE WAIT = 'TRUE' DO
      IF (DELAY_TIME < CURRENT_DATETIME) THEN
         SET WAIT = 'FALSE';
      END IF;
    END WHILE;
  END IF;
END;
END WHILE;

Thanks, Anusha