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