0

I'm trying to use table decorators ranges in one of the AppEngine RequestLog table in BigQuery. According to documentation log entries are objects of type LogEntry https://cloud.google.com/logging/docs/reference/v2/rest/v2/LogEntry.

There are two columns timestamp and receiveTimestamp. The first column description is "The time the event described by the log entry occurred" and for the second one "The time the log entry was received by Stackdriver Logging".

I tried to compare time range and number of records in table querying table using timestamp column and table decorator range.

Query where I'm using timestamp column.

SELECT count(*), MIN( timestamp ), max( timestamp )
FROM [project_id:dataset.appengine_googleapis_com_request_log_20170622]
WHERE timestamp between timestamp('2017-06-22 01:00:00') and 
date_add(timestamp('2017-06-22 01:00:00'), 1, 'hour')

Query result.

1698320 | 2017-06-22 01:00:00 UTC | 2017-06-22 01:59:59 UTC

Query where I'm using table decorator range.

--select timestamp_to_msec(timestamp('2017-06-22 01:00:00')) as time1,
         timestamp_to_msec(date_add(timestamp('2017-06-22 01:00:00'), 1, 'hour')) as time2
  SELECT count(*), min(timestamp), max(timestamp)
  FROM [project_id:dataset.appengine_googleapis_com_request_log_20170622@1498093200000-1498096800000]

Query result.

1534754 | 2017-06-22 00:40:45 UTC | 2017-06-22 01:35:59 UTC

I did not get the same date range and the same number of records. What each of these three timestamps mean? And how table decorators ranges works under the hood? (Does BigQuery make snapshots of tables, when it makes them)

goRunToStack
  • 253
  • 2
  • 9

1 Answers1

1

Table Decorators docenter link description here explains that it uses snapshot.But it "References a snapshot of the table at " -- meaning the time the data was ingested into bigquery. However that's totally unrelated to the timestamp fields in your table, because those fields represent the time the related event happened, not the time the field is ingested into bigquery.

xuejian
  • 195
  • 5