BigQuery, date partitioned tables and decorator

Question

I am familiar with using table decorators to query a table, for example, as it was a week ago or for data inserted over a certain date range.

Introducing date-partitioned tables revealed a pseudo column called _PARTITIONTIME. Using a date decorator syntax, you can add records to a certain partition in the table.

I was wondering if the pseudo column _PARTITIONTIME is also used, behind the scene, to support table decorators or something that straightforward.

If yes, can it be accessed/changed, as we do with the pseudo column of partitioned tables?

Is it called _PARTITIONTIME or _INSERTIONTIME? Of course, both didn't work. :)

Pentium10 Pentium10 · Accepted Answer · 2017-01-19T07:37:31

First check if indeed the table is partitioned by reading out partitions

 SELECT TIMESTAMP(partition_id)
  FROM [dataset.partitioned_table$__PARTITIONS_SUMMARY__]

In case not you will get error: Cannot read partition information from a table that is not partitioned

then another important step: To select the value of _PARTITIONTIME, you must use an alias.

SELECT
  _PARTITIONTIME AS pt,
  field1
FROM
  mydataset.table1

but when you use in WHERE it's not mandatory, only when it's in select.

#legacySQL
SELECT
  field1
FROM
  mydataset.table1
WHERE
  _PARTITIONTIME > DATE_ADD(TIMESTAMP('2016-04-15'), -5, "DAY")

you can always reference one partitioned table with the decorator: mydataset.table$20160519

BigQuery, date partitioned tables and decorator

1 Answers