UPSERT BigQuery

Question

我有一个40列的Bigqury表。我想使用PHP代码向它插入数据。我在数组中插入的数据如下所示。

[
  0 => array:2 [
    "id" => 1
    "name" => "updated name 1 "
  ]
  1 => array:2 [
    "id" => 3
    "name" => "new name to insert"
  ]
]

我在这个帖子中发现了一个MERGE命令。我试过这样的方法。

MERGE dataset.table c
USING (SELECT * FROM UNNEST([(1, 'updated name 1'),
                             (3, 'new name to insert')])) d
ON c.id = d.id
WHEN NOT MATCHED THEN
  INSERT ROW
WHEN MATCHED THEN
  UPDATE SET c.name = d.name;

这失败了，我如何动态地将数据填充到d？数组可能没有多少所需的列数据。

我也提到了这。

Answer 1

您可以使用部分列(只要这些“忽略”的列是NULLABLE，这是默认的)来执行UPSERT，请参见下面使用temp表演示此思想的示例：

CREATE TEMP TABLE c AS 
  SELECT 1 id, "old name" name, NULL col1, NULL col2, NULL col3, NULL col4;

SELECT * FROM c; -- output before

MERGE c
USING UNNEST([struct<id INT64, name STRING>
              (1, 'updated name 1'),
              (3, 'new name to insert')]) d
ON c.id = d.id
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (d.id, d.name)
WHEN MATCHED THEN
  UPDATE SET c.name = d.name;

SELECT * FROM c; -- output after

合并前输出

+----+----------+------+------+------+------+
| id |   name   | col1 | col2 | col3 | col4 |
+----+----------+------+------+------+------+
|  1 | old name | NULL | NULL | NULL | NULL |
+----+----------+------+------+------+------+

合并后输出

+----+--------------------+------+------+------+------+
| id |        name        | col1 | col2 | col3 | col4 |
+----+--------------------+------+------+------+------+
|  3 | new name to insert | NULL | NULL | NULL | NULL |
|  1 | updated name 1     | NULL | NULL | NULL | NULL |
+----+--------------------+------+------+------+------+

警告

您的查询受长度限制。因此，不要在一个查询中使用UPSERT太多行。

最大未解析标准SQL查询长度-1MB

我还建议您使用查询参数来传递数据，而不是将它们放在查询中。

可伸缩性

如果每日更新量超过200 it，可能会将数据分解为过多的合并，使其更接近DML的每日限制。

每个表每天最多的合并插入、更新、删除和合并语句数- 1,000条

您应该考虑其他方法来完成UPSERT。其中一种方法是将csv/json/parquet文件上载到GCS和加载到BigQuery表中，然后从表中执行类似的合并语句。