BigQuery -事实表更新逻辑
我正致力于在性能和成本分析的大查询上构建原型，
Requirements：
为销售业务(激励措施、领导、权利、预测、市场营销、领导等)建立一个DW (星型模式)数据，用于报告和高级分析目的。每天从客户关系管理和其他上游销售/营销系统中批量装载数据。每天的数据量约为5 TB数据，90%的附加数据和10%的数据具有更新的旧指标(直到前7天)。
设计：
- 创建日期分区的大查询事实表--很多查询是 趋势和时间序列类型查询。
- 创建聚合表，用于报告和其他重复查询/仪表板，以限制扫描量和降低成本。
摄食逻辑
- 上游ETL和cron作业，在Google存储中丢弃准备好的文件(已经实现了第三方和其他业务逻辑)。
- 更新的临时表(ODS)，因为分区表不能用DML更新，所以复制持续7天的分区临时表(ODS)来处理更新。(复制操作免费) --每天创建一个新的临时表，以便更容易地复制回事实表。
- 如果源文件中存在记录，则在temp表-- DML操作中更新记录。
- 旧日的新记录，附加到相应的日临时表中。
- 截断并替换(复制)从临时表到DW事实表的最后7天分区。-复制操作，不需要任何费用。
- 将新数据(占总数据的90%)附加到DW事实表-数据加载不需要任何费用。
问题
对于给定的需求，是否有更好的设计方案？
DML操作性能-我需要更新500 TB 1 TB的数据，在BigQuery中使用DML操作对性能有什么影响？
如何评估和预测更新和复制操作性能？
回答 2
Stack Overflow用户
发布于 2019-12-04 17:06:02
关于星型模式，在BigQuery中规范化数据的首选方法是使用嵌套和重复结构。海事组织，您可以看到BigQuery是一个非常强大的数据库，可以用作数据仓库。
对于您的时间序列需要，您可以使用摄入-时间分区表
不用cron作业来查询，您可以在云函数中使用调度查询或GCS触发器。它们可以在GCS中添加新文件时触发。
关于您的摄入逻辑，我建议使用云作曲家，即GCP中气流的集成。
您将能够使用BigQuery算子完成您所说的所有操作。使用这个操作符，您将不需要使用DML来创建表，因为您可以使用BigQueryCreateEmptyTableOperator。使用气流，你就不需要工作了。
关于成本，您可以使用定价计算器
我希望这能帮到你!
Stack Overflow用户
发布于 2019-12-07 10:04:57
这个工作流几乎与我在GCP上为销售/营销/产品数据设置的工作流程相同。
我的工作流程由Python、BigQuery标准SQL、(GCP实例)和组合组成。
我们的工作流是在python类中本地开发的，它处理ETL复制所需的所有步骤。大多数复制的实际步骤只是bigquery中的DML语句，但是我们依赖python类来实现标准化、代码重用和执行顺序/日志记录。
我们将其打包到一个码头容器中，并将其放到Google云注册表中。
我们使用(Airflow) Kubernetes Pod操作符来运行容器中的python文件。这很有帮助，这样我们就不必担心在维护虚拟机时出现的任何开发操作问题。
气流是很好的调度和依赖图。Bigquery中没有一种方法可以轻松地调度查询和维护聚合表的执行顺序。有了气流，一旦ETL管道完成，您就可以轻松地启动这些聚合查询。
我不能百分之百确定您可以期望的成本，但是在BigQuery中存储和处理是相当便宜的。作曲家和库伯奈特斯是花在我们身上的大部分。
虽然这个工作流需要几天的时间才能建立起来，但是当您需要使用销售数据进行预测/规范分析时，它就变得更加强大了。使用python/Airflow，您可以完成所有的etl、运行聚合查询、从R或scikit学到的任何机器学习分析，然后按照您想要的任何时间顺序将结果返回给BigQuery和您的客户关系管理系统。
如果你想要一些代码样本或帮助在GCP网站上快速启动，你可以DM我。
https://stackoverflow.com/questions/39879961
复制
腾讯云开发者
