LETclause allows you to create a calculated field that can be used within your query. You can create multiple fields by separating each expression with a comma (e.g.
LET a = b + c, d = e - f).
LETclause can only be used for calculations on raw columns while the second
LETclause can include calculations based on aggregated columns.
Note that since these fields do not exist within the source table, the column types must be provided if you select them as columns within your query.
WHEREclause filters can be added to filter out records based on the raw columns.
Note that including
$commit_time between run_start_time() [ - INTERVAL '<integer>' <time_unit> ] AND run_end_time()in the
WHEREclause is mandatory. If omitted, the query fails to run.
run_end_time()here are based on the
RUN_INTERVALof your job set in the job options.
By default the run interval is 1 minute, meaning that the job executes every minute. This is reflected in the
run_end_time()values shifting by a minute for every execution of the job.
Note that subtracting a time interval is only supported for aggregated outputs; you can, however, still adjust the
run_end_time()by configuring the
For example, if you would like the job to process data from the last hour each time that it executes, you can set
RUN_INTERVAL = 1 HOUR. Just note that this also means that the job will only run once every hour.
When aggregating data, you may want to include data that does not lie between the run time window. In such a case, you should subtract a time interval from
run_start_time()to adjust the time window to include all the desired data within your aggregation.
Subtracting a time interval does not change how frequently the job runs. Thus, if you create a 30 minute window by filtering on
$commit_time between run_start_time() - INTERVAL '29' MINUTE AND run_end_time(), the job still executes every minute by default unless the
If you actually just want the last 30 minutes of data aggregated every 30 minutes, then instead of subtracting a time interval, you can simply just set
RUN_INTERVAL = 30 MINUTES.
Note that all non-aggregate columns selected must be present in the
Additionally, all partition columns should appear within the
GROUP BYclause as mapping an aggregated column to a partition column is not allowed.
HAVINGclause can be used to filter out records based on aggregate columns.
LIMITclause can be used to limit the amount of records written to the target location.
This can be helpful when previewing the output of your job.