Bigquery:对记录的频繁更新

Question

我们计划使用bigquery来分析我们的库存系统。由于这是库存，因此存储产品组合的记录将经常更改。就数量而言，总存储产品记录在2亿到4亿之间。预计每天总共有500K个突变。kafka主题的变化即将到来。

从成本的角度来看，最优的解决方案是什么?选择

1. kafka监听器发出一条DML语句。UPDATE inventory SET quantity=? WHERE productid=? AND storeid=?。=比;我对这个选项的评估是，这是所有选项中最简单的，但可能会产生更高的成本，因为bigquery没有主键的概念。搜索索引/聚类等会有帮助吗?

2. 有一个存储每个突变的staging表，然后定期使用MERGE更新主报告表

类似https://cloud.google.com/blog/products/bigquery/performing-large-scale-mutations-in-bigquery(然而这是一篇2018年的文章，事情可能已经改变了很多-例如，我认为这里提到的3小时延迟现在是30分钟)

MERGE dataset.Inventory T
USING dataset.inventory_staging S
ON T.ProductID = S.ProductID and T.storeid = S.storeid
WHEN MATCHED THEN
UPDATE SET quantity = s.quantity
WHEN NOT MATCHED THEN
INSERT (ProductID, quantity) VALUES (ProductID, quantity)

现在是第二个问题，如果我们要采取第二种方法，

将kafka主题下沉到大查询的成本有效方法是什么?卡夫卡——>GCS→BQ比流解决方案更有优势(比如一个样板kafkalistener，它做https://cloud.google.com/bigquery/docs/write-api#write-api-overview)

Answer 1

为每个条目运行一条UPDATE语句将会非常昂贵，您需要拥有阶段表并定期运行merge。

卡夫卡→GCS→BQ是最划算的方式。

作为额外的建议，你可以探索在Pub/Sub中创建一个主题来取代kafka。Pub/Sub也可以直接使用bigquery。

Answer 2

对于第一个问题，单个UPDATE语句是不允许的，计划的merge在数据可用性方面有一些权衡，因此另一个选项是存储所有的突变并对数据进行版本化，以便始终可以恢复最后的值。

然后，您可以调度一个查询，通过从源表中删除旧版本来删除或归档旧版本。这将改善数据扫描。

关于你的第二个问题，最快的解决方法是:

卡夫卡祝辞PubSub sink>Bigquery .

请注意每一步的限制和特定的转换。

https://cloud.google.com/pubsub/docs/connect_kafka convert-to-pubsub

https://cloud.google.com/pubsub/docs/bigquery