Please note that the contents of this offline web site may be out of date. To access the most recent documentation visit the online version .
Note that links that point to online resources are green in color and will open in a new window.
We would love it if you could give us feedback about this material by filling this form (You have to be online to fill it)



Table Decorators

Normally, BigQuery performs a full column scan when running a query . You can use table decorators 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.

BigQuery supports the following types of table decorators:

Table decorator type Description Examples
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.

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

Get <time> for one hour ago:

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

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'.

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

Relative value example

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

Absolute value example

Get <time1> for one hour ago:

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

Get <time2> for a half hour ago:

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

Then, replace <time1> and <time2> in the following query:

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

Back to top

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License , and code samples are licensed under the Apache 2.0 License . For details, see our Site Policies .

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.