Is it possible to access a BigQuery partition in Standard SQL using the '$' decorator?

2
votes

In Google BigQuery, I'm trying to use the $ decorator when querying a partitioned table using Standard SQL. I assume this is supposed to allow me to access partitions and table metadata as it did in Legacy SQL, but it doesn't appear to work in Standard SQL.

Both of the following queries return Error: Table "dataset.partitioned_table$___" cannot include decorator:

1) Accessing a partition directly:

#StandardSQL
SELECT a, b, c
FROM `mydataset.partitioned_table$20161115`

2) Accessing table metadata:

#StandardSQL
SELECT partition_id
FROM `mydataset.partitioned_table$__PARTITIONS_SUMMARY__`;

The obvious workaround for the first query is to use the _PARTITIONTIME pseudocolumn:

#StandardSQL
SELECT a, b, c
FROM mydataset.partitioned_table
WHERE _PARTITIONTIME = '2016-11-15'

However, I haven't been able to find a workaround for the second query, which is useful for retrieving the most recent partition (though using that info to actually query the latest partition seems broken as well. See: How to choose the latest partition in BigQuery table?)

1
google-bigquery
We make use of the feature to study the records in the streaming buffer, since some of our processes depend on it. We recently implemented a BigQuery table with clustering and partitions by hour enabled. This enlarged the streaming buffer, but we were able to update the records in it. This leads to the conclusion that the query with $__UNPARTITIONED decorator in LegacySQL no longer works. We have asked Google to fix it. Please do so too. - Scipio

1 Answers

2
votes

Obtaining the partitions summary using a decorator is currently not supported in StandardSQL. We are planning some work in this area but we don't have an ETA currently on when that might be available. The fastest option right now is to run the query over T$__PARTITIONS_SUMMARY__ using legacy SQL.