BigQuery Table Decorators with Standard SQL

5
votes

I'm having some trouble using table decorators using Standard SQL. However, the same concept with Legacy SQL syntax works for me. Is this a bug? Here is an example.

(A) The following query works without any issue

SELECT COUNT(*) FROM [some-project-name:some_dataset.some_table_name@<time1>-<time2>]

(B) The following query returns back with an error message Error: Table "some-project-name.some_dataset.some_table_name@<time1>-<time2>" cannot include decorator

SELECT COUNT(*) FROM `some-project-name.some_dataset.some_table_name@<time1>-<time2>`

Notes:

  • Query (A) has Use Legacy SQL box checked.
  • Query (B) has Use Legacy SQL box unchecked.
  • <time1> is absolute and is the creation time in of the table in milliseconds since Unix epoch.
  • <time2> is the current time stamp in milliseconds

UPDATE:

As Mikhail pointed out, this feature is not available for Standard SQL. It has been requested here.

2
google-bigquerysql-standards

2 Answers

10
votes

Good news: it's now implemented.

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax

SELECT *
FROM t
  FOR SYSTEM TIME AS OF '2017-01-01 10:00:00-07:00';
1
votes

Support for table decorators in standard SQL is planned but not yet implemented. 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.

See more at https://cloud.google.com/bigquery/table-decorators