Table Decorators in Legacy SQL

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().

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.

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

Examples

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

Relative value example

SELECT COUNT(*) FROM [data-sensing-lab:gartner.seattle@-3600000]

Absolute value example

1. Get <time> for one hour ago:

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

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

   SELECT COUNT(*) FROM [data-sensing-lab:gartner.seattle@<time>]

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:

SELECT COUNT(*) FROM [data-sensing-lab:gartner.seattle@-3600000--1800000]

To get data from the last 10 minutes:

SELECT COUNT(*) FROM [data-sensing-lab:gartner.seattle@-600000-]

Absolute value example

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

1. Get <time1> for one hour ago:

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

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

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

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

   SELECT COUNT(*) FROM [data-sensing-lab:gartner.seattle@<time1>-<time2>]