2

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?)

Community
  • 1
  • 1
R.M.
  • 175
  • 2
  • 5
  • 1
    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 Mar 09 '21 at 07:35

1 Answers1

2

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.

Pavan Edara
  • 2,285
  • 1
  • 12
  • 12
  • Will there be support for accessing partitions directly using decorators in Standard SQL? The query is validated by the BQ query validator, but running it produces the error. – R.M. Nov 16 '16 at 22:54
  • Currently, we don't have any plans for standard SQL to support the decorator syntax with partitions for accessing one partition. However, using predicate such as _PARTITIONTIME = TIMESTAMP("2016-11-15") has the exact same behavior. We'll be dealing with `__PARTITIONS_SUMMARY__` in a more general way, most likely, without having to use the decorator syntax. – Pavan Edara Nov 17 '16 at 20:18
  • Will there be support for using a subquery to filter on `_PARTITIONTIME`? Right now, you can do it, but the query will process the entire table across all partitions, rather than just the partitions that match the `where` condition, which is obviously not ideal. – R.M. Dec 13 '16 at 03:20
  • We plan to add support for using a subquery for partitiontime filtering, but we don't have an ETA at this point. Thanks. – Pavan Edara Dec 13 '16 at 03:22
  • @PavanEdara Any updates on this? Is there some progress being made? – Stijn de Witt Sep 18 '17 at 14:03