Firebase BigQuery服务器偏移时间 [英] Firebase BigQuery server offset time

问题描述

背景:我正在将Firebase分析数据导出到BigQuery.而且我正在使用cron作业来处理BigQuery中的数据以获取洞察力.

Background: I'm having the Firebase analytics data exported to BigQuery. And I'm using cron jobs to crunch data in BigQuery for getting insight.

问题:为了仅处理增量数据，即自上次执行cron作业以来已到达的数据，我需要一种方法来计算数据到达服务器的时间，因为生成了 event_timestamp 在客户端，可以在发送之前在客户端缓存.

Problem: To be able to only crunch delta data i.e. the data that has arrived since last time I ran my cron job I need a way to figure out the time when the data arrived at server, since the event_timestamp is generated at client and can be cached at client before sent.

见解:我已经尝试过使用 event_server_timestamp_offset (偏移)，我认为我可以将其与 event_timestamp 一起使用.但是我希望补偿量只会是正数，但也可能是负数.当我查看整个导出的Firebase分析数据集中的偏移量的最大值和最小值并将其重新计算为年而不是微秒时，我可以获得超过18年的偏移量.

Insights: I have laborated with event_server_timestamp_offset (offset) which I thought I could use together with event_timestamp. But I was expecting the offset to only be positive but it can also be negative. And when I look at the MAX and MIN for the offset in the entire exported Firebase analytics dataset and re-calculate it to years instead of microseconds I can get more than 18 years offset.

查询:

SELECT
  MAX(event_server_timestamp_offset)/(1000000*60*60*24) max_days, 
  MIN(event_server_timestamp_offset)/(1000000*60*60*24) min_days
FROM
  `analytics_<project_id>.events_*`

结果:max_days = 6784.485790436655，min_days = -106.95833052104166

Result: max_days=6784.485790436655, min_days=-106.95833052104166

问题:

• 如何确定Firebase导出的BigQuery数据的服务器到达时间，以便我可以运行仅处理增量数据的cron作业?
• 我可以将 event_server_timestamp_offset 与 event_timestamp 一起使用吗?如果可以，怎么办?

• How can I figure out the server arrival time for my Firebase exported BigQuery data so I can run cron jobs crunching only delta data?
• Can I use event_server_timestamp_offset together with event_timestamp? If so, how?

最诚挚的问候，丹尼尔

推荐答案

令人惊讶的是，这个问题将近2年没有一个明确的答案，我将Firebase支持团队提供的答案留在这里.格式为-询问问题，然后提供支持人员的答案.

Surprisingly enough, this question not having a clear answer for almost 2 years, I am leaving here the answers I got from the Firebase support team. The format is - question asked followed by the answer of the support staff.

Q1.event_date-记录事件的日期(应用注册时区中的YYYYMMDD格式).这是否意味着该事件是在该日期发生的，或者实际上是在该日期收集的?

Q1. event_date - The date on which the event was logged (YYYYMMDD format in the registered timezone of your app). Does it mean that the event occurred on that date, or that it was actually collected on that date?

A1.根据文档，event_date是指记录/发生事件的日期.请注意，event_date基于Firebase项目的Google Analytics(分析)时区设置.

A1. Per documentation, event_date refers to the date on which the event is logged/occurred. Note that event_date is based on the Analytics timezone setting of your Firebase Project.

Q2.event_timestamp-在客户端上记录事件的时间(以微秒为单位，UTC).是否可以安全地假设这是事件在客户端发生的确切时间戳(当然是在应用时区)?

Q2. event_timestamp - The time (in microseconds, UTC) at which the event was logged on the client. Is it safe to assume that this is the exact timestamp the event occurred on client side (in the app timezone of course)?

A2.是的，这是基于设备时区设置的.但是，如果设备时间不正确，则event_timestamp可能会偏斜.

A2. Yes, this is based on the device timezone setting. However, event_timestamp may be skewed if the device time is incorrect.

Q3.event_server_timestamp_offset-收集时间和上载时间之间的时间戳偏移(以微秒为单位).这是引起所有误解的主要字段-在我们的2020年BigQuery表中，该字段的值介于5天和-2天之间.我的意思是大学时间要提前两天吗?

Q3. event_server_timestamp_offset - Timestamp offset between collection time and upload time in micros. This is the main field that causes all the misunderstandings - in our BigQuery table for the year 2020 this field takes values in a range between 5 days and -2 days. I mean how can the colleciton time be 2 days ahead?

A3.导出架构中的event_server_timestamp_offset字段是事件发生与应用程序将其上传到我们的服务器之间的时间差.换句话说，这是根据我们的服务器，客户的本地时间与实际时间之间的估计差值.该字段的值通常为正，但如果设备时间设置不正确，也可以为负.

A3. The event_server_timestamp_offset field in the export schema is the time difference between when the event took place and the app uploaded it to our server. In other words, this is the estimated difference between the client's local time and the actual time, according to our servers. The values of this field are usually positive, but can be negative as well if the device time setting is incorrect.

Q4.最后一个问题非常重要-我们可以忽略event_server_timestamp_offset字段，仅依靠event_timestamp-作为事件在客户端发生的确切日期和时间(不是收集，未更新等).如果没有，请说明我们如何获得事件发生在客户端的确切日期时间.但是如果是的话请让我知道为什么我们需要event_server_timestamp_offset字段?

Q4. One last question is very important - can we ignore the event_server_timestamp_offset field and just rely on event_timestamp - as the exact date and time the event occurred on the clientside (not collected, not uplaoded, etc). If not- please explain how we can get the exact datetime of the event occuring on the clientside. But if yes please let me know why do we need the event_server_timestamp_offset field?

A4.是的，您实际上可能会忽略它，而仅使用event_timestamp.但是，如前所述，如果设备时间设置不正确，则event_timestamp可能会关闭，但它不会真正影响分析数据的整体情况，因为这种情况通常是一次性的.

A4. Yes, you may actually ignore it and use event_timestamp alone. However, as mentioned earlier, event_timestamp could be off if the device time setting incorrect, but it shouldn't really affect the bigger picture of your analytics data as cases like this are usually one-off.

这篇关于Firebase BigQuery服务器偏移时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！