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
Get
<time>
for one hour ago:#legacySQL SELECT INTEGER(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -1, 'HOUR')/1000)
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:
Get
<time1>
for one hour ago:#legacySQL SELECT INTEGER(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -1, 'HOUR')/1000)
Get
<time2>
for a half hour ago:#legacySQL SELECT INTEGER(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -30, 'MINUTE')/1000)
Replace
<time1>
and<time2>
in the following query:#legacySQL SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@time1-time2]