google-bigquery - BigQuery - 事实表更新逻辑

Question

我正在致力于在大查询上构建原型(prototype)以进行性能和成本分析，

要求:

为销售运营(激励、销售线索、权利、预测、营销、销售线索等)数据构建 DW(星型模式)，以用于报告和高级分析目的。每天从 CRM 和其他上游销售/营销系统批量加载数据。数据量约为每天 5 TB 数据，90% 追加，10% 数据更新旧指标(截至前 7 天)。

设计:

• 创建日期分区的 Big Query 事实表 -- 很多查询
  趋势和时间序列类型查询。

• 创建用于报告和其他重复性的汇总表查询/仪表板以限制扫描量并降低成本。

摄取逻辑

1. 上游 ETL 和 cron 作业，用于将加载就绪文件放入 Google Cloud 存储中(已经实现了第 3 方和其他业务逻辑)。

2. 用于更新的临时表(ODS)，由于分区表无法使用DML更新，因此复制最近7天的分区临时表(ODS)来处理更新。 (复制操作无成本)——每天创建一个新的临时表，以便更容易复制回事实表。

3. 如果源文件中存在记录，则更新临时表中的记录——DML操作。

4. 旧日期的新记录，附加到相应日期的临时表中。

5. 截断最后 7 天的分区并将其从临时表替换(复制)到 DW 事实表。 -- 复制操作无成本。

6. 将新数据(占总数据的 90%)附加到 DW 事实表 - 数据加载无成本

问题

针对给定的要求是否有更好的设计选项？

DML 操作性能 - 我需要更新 500MB - 1 TB 数据，在 BigQuery 中使用 DML 操作对性能有何影响以及需要考虑的成本？

如何估计和预测更新和复制操作性能？

Answer 1

关于星型架构，在 BigQuery 中规范化数据的首选方法是使用 nested and repeated structures 。在我看来，您可以将 BigQuery 视为一个非常强大的数据库，可以用作 DW。

对于您的时间序列需求，您可以使用 ingestion-time partitioned tables

您可以使用 schedule queries 而不是 cron 作业来查询或GCS triggers具有云功能。当 GCS 中添加新文件时，它们可以被触发。

关于摄取逻辑，我建议使用 Cloud Composer这是Airflow在GCP中的集成。

您将能够使用 BigQuery operators 执行您所说的所有操作。使用此运算符，您无需使用 DML 来创建表，因为您可以使用 BigQueryCreateEmptyTableOperator。使用 Airflow，您将不需要 cron 作业。

关于费用，您可以使用pricing calculator

希望这会有所帮助!