Table decorators in legacy SQL

This document details how to use table decorators in legacy SQL query syntax. The preferred query syntax for BigQuery is standard SQL. Table decorators are currently unsupported in standard SQL, but you can achieve table decorator semantics in standard SQL by using a filter on the _TABLE_SUFFIX pseudocolumn. For more information, see Table decorators and wildcard functions 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: Table decorators are not supported in standard SQL. Work is in progress to provide functionality to access data at a point-in-time, similar to snapshot decorators in legacy SQL. To view the details of the feature request, see: 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 7 days and greater than or equal to the table's creation time.
• @0 is a special case that references the oldest possible snapshot of the table: either 7 days in the past, or the table's creation time if the table is less than 7 days old.
• Snapshot decorators are only supported in table copy operations. For query operations, use FOR SYSTEM_TIME AS OF.

You can use snapshot decorators to undelete a table within 7 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 7 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]