Big Query table Last Modified Timestamp does not match the last insert time of the table

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

As per 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

      

According to my records, the last insert of the package into this table was actually:

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

      

This is two hours earlier than the indicated time of the last modification. 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 package insert and not the last modified time specified in the metadata:

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

      

returns a zero counter. Whereas the request:

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

returns count: 222,891

      

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

I'm trying to programmatically generate a FROM clause that spans multiple tables with decorators, so I need the exact creation and last modification time for the tables in order to determine when the decorators can be omitted since the time range spans the entire table. However, due to this timing mismatch, I am unable to eliminate the table decorators.

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

+3


source to share


1 answer


Short answer: you are really looking at the correct metadata.

Long answer: The last modification time includes some internal data compression time not related to data modification. Querying your table with a decorator ending with 1431223783125 or 1431216576000 gives the same results as your experiments, but at a later date it improves storage efficiency, which may slightly improve runtime and efficiency. We are reviewing this bug and will update the API shortly to return the most recent user modification time.



In the meantime, there is no harm in including table decorators that are not really needed other than the text of the added query. Neither request cost nor performance will change.

+2


source







All Articles