BigQuery中的行级原子MERGE REPLACE

Question

BigQuery中的行级原子MERGE REPLACE

mongodbgoogle-bigqueryetlacid

3

对于我的用例，我正在处理源数据，该数据由唯一键标识，并在分析目的下加载到BigQuery表中，可能会变成n个（非确定性的）目标条目。

构建此ETL以使用Mongo最近的Change Stream功能，我想删除BigQuery中的所有条目，然后以原子方式加载新条目。

通过探索BigQuery DML，我发现支持MERGE操作，但仅支持WHEN MATCHED THEN DELETE或WHEN MATCHED THEN UPDATE。

我对WHEN MATCHED THEN DELETE并紧接着插入操作感兴趣。

在保持尽可能原子或最终一致的情况下，如何在BigQuery中实现这样的ETL以确保数据可用性和正确性。

编辑1：我想提供一个具体的示例来解释。

此数据集的最低独特性是user_id。行不是唯一可识别的。

示例

1.

从mongo更改流接收到的更新用户对象：

user={_id: "3", name="max", registered="2018-07-05" q=["a", "b", "c"]}

2.

当前的 BigQuery.user_q 包含：

| user_id | q |
...
|    3    | a |
|    3    | b |
...

3.

将修改后的用户对象加载到BigQuery.user_q_incoming中的代码进行转换。

| user_id | q |
|    3    | a |
|    3    | b |
|    3    | c |

4.

将user_q和user_q_incoming进行合并：

user_id 3下的user_q中有2行被删除。
user_id 3下的user_q_incoming中有3行被插入。
user_q中的其余数据(...)保持不变。

5.

BigQuery.user_q包含：

| user_id | q |
...
|    3    | a |
|    3    | b |
|    3    | c |
...

例如，用户可能会从他的个人资料中删除一个问题。这将导致剩余的行为q=["a", "c"]。我需要将此转化为BigQuery世界观。

- Maxim Veksler

2个回答

3

BigQuery DML支持INSERT操作。

MERGE语句是一种DML语句，可以将INSERT、UPDATE和DELETE操作组合成一个单独的语句，并原子地执行这些操作。

例如：

MERGE dataset.Inventory T
USING dataset.NewArrivals S
ON FALSE
WHEN NOT MATCHED AND product LIKE '%washer%' THEN
  INSERT (product, quantity) VALUES(product, quantity)
WHEN NOT MATCHED BY SOURCE AND product LIKE '%washer%' THEN
  DELETE

所以，你应该可以顺利完成ETL。

根据问题中添加的更具体细节进行编辑

好的，我明白了 - 我认为在这种情况下MERGE不适用，因为INSERT只能针对NOT MATCH子句执行。有人可能会想出如何欺骗MERGE在这种情况下工作，但同时以下解决方案可以实现你想要实现的目标 - 我认为是这样的:o)

CREATE OR REPLACE TABLE `project.dataset.user_q` (user_id INT64, q STRING) AS
SELECT * FROM `project.dataset.user_q`
WHERE NOT user_id IN (SELECT DISTINCT user_id FROM `project.dataset.user_q_incoming`)
UNION ALL
SELECT * FROM `project.dataset.user_q_incoming`
WHERE user_id IN (SELECT DISTINCT user_id FROM `project.dataset.user_q`)

- Mikhail Berlyant

Mikhail，请查看我描述的合并操作的编辑。 - undefined

我现在明白了，这与我最初阅读你的问题时有点不同。请查看我回答中的更新 - 希望现在对你有用：o) 还要注意，尽管它正式地完成了你的要求 - 但要考虑数据操作的成本。 - undefined

@MaximVeksler - 你有机会尝试了吗？ - undefined

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Yunxiao Ma · Accepted Answer

有一个类似的问题，并且有一种解决方法可以使MERGE工作（https://issuetracker.google.com/issues/35905927#comment9）。

基本上，像以下这样的东西应该可以工作，

MERGE `project.dataset.user_q` T
USING (
  SELECT *, false AS is_insert FROM `project.dataset.user_q_incoming`
UNION ALL
  SELECT *, true AS is_insert FROM `project.dataset.user_q_incoming`
) S
ON T.user_id = S.user_id and NOT is_insert
WHEN MATCHED THEN
  DELETE
WHEN NOT MATCHED AND is_insert THEN
  INSERT(user_id, q) VALUES(user_id, q)

理想情况下，以下是您所需的内容，但目前还不支持。

MERGE `project.dataset.user_q`
USING `project.dataset.user_q_incoming`
ON FALSE
WHEN NOT MATCHED BY TARGET THEN
  INSERT(user_id, q) VALUES(user_id, q)
WHEN NOT MATCHED BY SOURCE AND user_id in (SELECT user_id FROM `project.dataset.user_q_incoming`) THEN
  DELETE