Bigquery Custom Schedule Cron Syntax Not Accepted

Question

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 * * *

Answer 1

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:

• End-time intervals
• Start-time intervals
• Custom intervals

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:

• minutes or mins
• hours

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:

• minutes or mins
• hours

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

• Using every specifies a repeating interval:

  • every day 06:00
  • every monday
  • every tuesday
  • ...
  • every sunday

• Specific days can be specified with ordinal numbers (1st, 2nd, 3rd, OR, first, second, third, ..., up to 31st OR thirtyfirst)

  • 1st,3rd tuesday
  • 2nd, third wednesday of month 09:00

Note that the ordinal number and words can be mixed.

INTERVAL_VALUE Valid days are any mix of the following:

• monday or mon
• tuesday or tue
• wednesday or wed
• thursday or thu
• friday or fri
• saturday or sat
• sunday or sun
• day for all days of the week

INTERVAL_SCOPE Can include

• of month [HH:MM]
• of jan,feb,sep,nov [HH:MM] i.e. a comma-separated list of months

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:

• january or jan
• february or feb
• march or mar
• april or apr
• may
• june or jun
• july or jul
• august or aug
• september or sep
• october or oct
• november or nov
• december or dec
• month for all months in the year

Examples

• 2nd monday,thu
• 1,8,15,22 of month 09:00
• 1st mon,wednesday,thu of sep,oct,nov 17:00

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

• second monday,thu -> "Custom Interval"
• third, twentysecond, 30th mon -> "Custom Interval"
• 1 of jan,april,july,oct 00:00 -> "Custom Interval"
• 1st monday of sep,oct,nov 09:00 -> "Custom Interval"
• 1st,third monday of month 04:00 -> "Custom Interval"
• 1,8,15,22 of month 09:00 -> "Custom Interval"
• every monday 09:00 -> "Custom Interval"
• every 5 minutes from 10:00 to 14:00 -> "Start-time Interval"
• every 1 hours from 08:00 to 16:00 -> "Start-time Interval"
• every 2 hours synchronized -> "Start-time Interval"
• every 5 minutes -> "End-time Interval"
• every 1 hours -> "End-time Interval"

IMPORTANT:

• Interval types are chosen implicitly when you enter the schedule
• You can't mix and match the options for the different interval types.
• All specified times are UTC

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.