crongoogle-bigqueryscheduled-tasks

Bigquery Custom Schedule Cron Syntax Not Accepted

I am trying to schedule a query to run intraday in Bigquery UI. According to Google's documentation this option uses cron syntax. I have used crontab guru to verify the syntax is correct, although it doesn't matter what syntax you put the scheduler doesn't seem to accept any. Is this a known bug? Below is the cron syntax I'm using to run every 6 hours.

0 */6 * * *

Solution

  • This post attempts to give a more general answer for those that may follow with similar questions.

    A full description of the allowed syntax can be found directly here as well as related materials here under "schedule" field information..

    For full disclosure, I'm going to lift out some parts directly from that documentation here so this answer can "stand alone".

    Generally, the schedule must be of the form:

    [TYPE] [INTERVAL_VALUE] [INTERVAL_SCOPE]

    and you must decide which of the three different kinds of intervals you will use:

    The kind of interval is chosen implicitly by the schedule you provide.

    End-Time Intervals

    These are intervals implemented from when a job finishes.

    TYPE

    Daily intervals must start with the "every" prefix

    INTERVAL_VALUE

    Valid units of time are as follows:

    INTERVAL_SCOPE

    Not applicable for end-time intervals.

    Example

    If "every 5 mins", and the job finishes at 0201, then then next job waits 5 minutes AFTER completion to begin again, and starts at 0206.

    Start-Time Intervals

    A strict schedule for the queries to follow.

    TYPE

    Daily intervals must start with the "every" prefix

    INTERVAL_VALUE

    Some integer amount of the following units of time:

    The units remain the same even using 1 as the amount.

    INTERVAL_SCOPE

    Must be of the form:

    from [HH:MM] to [HH:MM] with HH=00,01,...,23 and MM=00,01,..., 59. OR synchronized

    synchronized repeats a time interval and spreads it evenly across the 24 hour period (e.g. like end-time scheduling but fixing it to a start-time).

    The [INTERVAL_VALUE] given in conjunction with this option must be a factor of 24 (1, 2, 3, 4, 6, 8, 12, or 24), presumably so that each day has the same schedule (otherwise, you would get a "spill" over into the next day).

    Examples

    Example 1: every 5 minutes from 10:00 to 14:00 If the job starts at 1000, and takes 6 minutes, then it will run 1000, 1010, 1020, ..., because the 1005,1015, ..., jobs were skipped because they were still running.

    Direct quote:

    Because the start time of a job is strict, if an instance of a job runs longer than the defined time interval, then the Cron service can skip a job. An individual start time in the interval can be skipped if the prior job has not completed or times out.

    Example 2: every 2 hours synchronized Runs 0000,0200,0400, ..., 2200.

    Custom Intervals

    These specify intervals on the day or month level, and cannot specify sub-daily intervals.

    TYPE

    Note that the ordinal number and words can be mixed.

    INTERVAL_VALUE Valid days are any mix of the following:

    INTERVAL_SCOPE Can include

    Note, a time must be given with any given month, with HH and MM given as above (00-23 and 00-59, respectively). If "of" is excluded, the job runs every month.

    Allowed values:

    Examples

    Note, there is no documentation that could be found describing the time a job runs when the time is not explicitly specified (e.g. 2nd monday,thu).

    General Examples

    IMPORTANT:

    As said in the intro above, this information is essentially ripped from the existing documentation, but I felt that was buried away and that this question deserved a "stand alone" reference text.