CODEWITHSUNDEEP
google-bigquery
user1849860
user1849860

Reputation: 237

Big Query Table Last Modified Timestamp does not correspond to time of last table insertion

I have a table, rising-ocean-426:metrics_bucket.metrics_2015_05_09

According to the node js API, retrieving metadata for this table,

Table was created       Sat, 09 May 2015 00:12:36 GMT-Epoch 1431130356251
Table was last modified Sun, 10 May 2015 02:09:43 GMT-Epoch 1431223783125

By my records, the last batch insertion to this table was actually on:

Sun, 10 May 2015 00:09:36 GMT - Epoch 1431216576000.

This is two hours earlier than the reported last modification time. Using table decorators, I can show that no records were inserted into the table after Epoch 1431216576000, proving that no records were inserted in the last two hours between the last batch insertion I made and the last modification time reported in the metadata:

The query: SELECT
count(1) as count
FROM [metrics_bucket.metrics_2015_05_09@1431216577000-1431223783125];

returns zero count. Whereas the query:

SELECT
count(1) as count
FROM [metrics_bucket.metrics_2015_05_09@1431216576000-1431216577000];

returns count: 222,891

Which shows that the correct last modification time was Sun, 10 May 2015 00:09:36 GMT, and not 02:09:43 GMT as the metadata asserts.

I am trying to programmatically generate a FROM clause that spans multiple tables with decorators, so I need accurate creation and last modification times for the tables in order to determine when decorators can be omitted because the time range spans the entire table. However, due to this time discrepancy, I cannot eliminate the table decorators.

The question is, am I looking at the right metadata to obtain correct creation and last modification information?

Upvotes: 3

Views: 3426

Answers (1)

Matthew Wesley
Matthew Wesley

Reputation: 626

Short answer: You are indeed looking at the correct metadata.

Long answer: The last modification time includes the time of some internal compaction of data, unrelated to data change. Executing a query against your table with a decorator ending at either 1431223783125 or 1431216576000 produces the same results, just like your experiments show, but executing at the later time includes our storage efficiency improvements that may slightly improve execution time and efficiency. We consider this a bug, and will soon update the API to return the last user-modification time instead.

In the meantime, there's no harm to including table decorators that aren't really needed, other than the added query text. Neither query cost or performance will change.

Upvotes: 2

Related Questions