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 分析数据集中偏移的 MAX 和 MIN 并将其重新计算为年而不是微秒时，我可以获得超过 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.

第一季度.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 项目的 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.

第 2 季度.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.

第三季度.event_server_timestamp_offset - 以微为单位的收集时间和上传时间之间的时间戳偏移.这是导致所有误解的主要字段 - 在我们 2020 年的 BigQuery 表中，该字段的值范围在 5 天到 -2 天之间.我的意思是收集时间怎么会提前 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.

第四季度.最后一个问题非常重要——我们可以忽略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屋！