GCP Bigquery中的动态合并

Question

我在GCP Bigquery中有一个主表和一个staging表。主表将包含快照数据，staging表将是一个流表。我想运行合并查询，并以一定的间隔更新主表中的数据。

由于我将使用MongoDB Debezium连接器，因此我将只接收一行的更新列，其余列将为空。

正常的合并查询将更新整个行，这是不希望的。

我需要一种方法来合并表中仅针对那些特定列的行，并保持其余列不变。每行要更新的列可以不同。

例如:

Main table data:
id  status  login_id    task_id user_id
71  CLAIMED 13          4373737 2191

Staging table data:
id  status  login_id    task_id user_id
71  null    null        4636282 null

我想要这样的结果:

id  status  login_id    task_id user_id
71  CLAIMED 13          4636282 2191

查询必须为新键插入一条记录，并为现有键更新特定列.

有可能吗?有人能帮我吗?

Answer 1

下面的查询应该工作。您需要为每列创建一个匹配子句，而不是用单个子句来更新WHEN MATCHED上的整行。WHEN NOT MATCHED BY TARGET部分保持不变以插入新表项。

merge main T 
using staging S
on T.id = S.id
when matched and S.status is not null then update set T.status = cast(S.status as string)
when matched and S.login_id is not null then update set T.login_id = S.login_id
when matched and S.task_id is not null then update set T.task_id = S.task_id
when matched and S.user_id is not null then update set T.user_id = S.user_id
when not matched by target then insert values (S.id, cast(S.status as string), S.login_id, S.task_id, S.user_id)

我测试了它，它工作。您可能需要强制转换一些列，因为如果S的值为null而T的值为int, bigquery将会报错。