CODEWITHSUNDEEP
google-bigquerypartitioning
Scicrazed
Scicrazed

Reputation: 602

Querying _PARTITIONTIME only yields a date

I attempted to query _PARTITIONTIME in my ingestion time partitioned table. Upon doing so, I realized that each row produced an identical incorrect timestamp: 2019-02-25 00:00:00 UTC. I attempted to extract specific 'parts' of _PARTITIONTIME, but all of them excluding the date yielded 0.

Although data is streamed into the table, it is actually inserted in batches every 10 minutes or so(as far as I understand). As a followup question, when is _PARTITIONTIME calculated? I saw this post, but I only understood the flow, not when it's actually calculated.

Upvotes: 2

Views: 1794

Answers (2)

Nishanth Pavinkurve
Nishanth Pavinkurve

Reputation: 3

As of September 21, 2020, the hourly time partitioning feature is GA. For tables partitioned by hour, BQ insertion writes to the partition hour 00 by default, unless the partition to write to is explicitly specified at insertion/ingestion time. The partition can be specified using $YYYYMMDDHH or along the lines of the SQL statement:

INSERT INTO
  project_id.dataset.mytable (
    _PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:00+00", HOUR, "UTC"),
  1,
  "one"

Upvotes: 0

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

_PARTITIONTIME is more like a _PARTITIONDATE - you shouldn't expect it to contain the exact time when the record was streamed, but the date that partition belongs to.

Ingestion-time partitioned tables include a pseudo column named _PARTITIONTIME that contains a date-based timestamp for data that is loaded into the table. https://cloud.google.com/bigquery/docs/partitioned-tables

Upvotes: 1

Related Questions