Table decorators in legacy SQL

Caution: This document describes table decorators in legacy SQL query syntax. The preferred query syntax for BigQuery is standard SQL. Standard SQL does not support table decorators, but the FOR SYSTEM_TIME AS OF clause in standard SQL provides functionality equivalent to snapshot decorators. For range decorators, you can achieve similar semantics in standard SQL by using time-partitioned tables. For more information, see Table decorators in the standard SQL migration guide.

Normally, BigQuery performs a full column scan when running a query. You can use table decorators in legacy SQL to perform a more cost-effective query of a subset of your data. Table decorators can be used whenever a table is read, such as when copying a table, exporting a table, or listing data using tabledata.list.

Note: Range decorators aren't supported in standard SQL. To view the status of this feature request, see the BigQuery feature request tracker. You can click the Vote for this issue and get email notifications icon (the star) to register your support for the feature.

Table decorators support relative and absolute <time> values. Relative values are indicated by a negative number, and absolute values are indicated by a positive number. For example, -3600000 indicates one hour ago in milliseconds, relative to the current time; 3600000 indicates one hour in milliseconds after 1/1/1970.

Snapshot decorators

Syntax

@<time>

• References a snapshot of the table at <time>, in milliseconds since the epoch.
• <time> must be within the last seven days and greater than or equal to the table's creation time, but less than the table's deletion or expiration time.
• @0 is a special case that references the oldest possible snapshot of the table.

You can use snapshot decorators to undelete a table within seven days of table deletion.

Examples

To get a snapshot of the table at one hour ago:

Relative value example

#legacySQL
SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@-3600000]

Absolute value example

1. Get <time> for one hour ago:

   #legacySQL
   SELECT INTEGER(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -1, 'HOUR')/1000)
   

2. Then, replace <time> in the following query:

   #legacySQL
   SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@time]
   

To undelete a table using a snapshot decorator and the bq command-line tool:

Undelete table example

First, determine a timestamp before the table was deleted (in milliseconds since the epoch). Then, copy the table at that timestamp to a new table. The new table must have a different name than the deleted table.

# Create a table "dataset1.table1"
$ bq query --destination_table=dataset1.table1 "select 1"

# Get current timestamp
$ date +%s%3N
1573060348222

# Delete table1
$ bq rm dataset1.table1

# Undelete table
$ bq cp dataset1.table1@1573060348222 dataset1.table1_restore

Range decorators

Syntax

@<time1>-<time2>

• References table data added between <time1> and <time2>, in milliseconds since the epoch.
• <time1> and <time2> must be within the last seven days.
• <time2> is optional and defaults to 'now'.

Examples

Relative value examples

To get table data added between one hour and half an hour ago:

#legacySQL
SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@-3600000--1800000]

To get data from the last 10 minutes:

#legacySQL
SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@-600000-]

Absolute value example

To get table data added between one hour and half an hour ago:

1. Get <time1> for one hour ago:

   #legacySQL
   SELECT INTEGER(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -1, 'HOUR')/1000)
   

2. Get <time2> for a half hour ago:

   #legacySQL
   SELECT INTEGER(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -30, 'MINUTE')/1000)
   

3. Replace <time1> and <time2> in the following query:

   #legacySQL
   SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@time1-time2]