bigquery合并

f0brbegy 于 2021-07-27 发布在 Java

关注(0)|答案(2)|浏览(375)

我知道bigquery支持合并两个表。目前 INSERT 操作允许在表中插入显式值，例如

INSERT dataset.Inventory (product, quantity)
VALUES('top load washer', 10),
      ('front load washer', 20),
      ('dryer', 30),
      ('refrigerator', 10),
      ('microwave', 20),
      ('dishwasher', 30),
      ('oven', 5)

有没有一种方法可以和 MERGE 手术？例如，类似于：

MERGE dataset.DetailedInventory T
USING('top load washer', 10),
      ('front load washer', 20),
      ('dryer', 30),
      ('refrigerator', 10),
      ('microwave', 20),
      ('dishwasher', 30),
      ('oven', 5)
ON T.appliance = [I don't know what would go here]
WHEN NOT MATCHED THEN [insert]
WHEN MATCHED THEN [update]

我对gbq和sql还比较陌生，如果这是一个简单的问题，我深表歉意。

sql google-bigquery

来源：https://stackoverflow.com/questions/63180227/bigquery-merge-using-explicit-values

2条答案

按热度按时间

ngynwnxp1#

可以使用子查询：

MERGE dataset.DetailedInventory T
USING (SELECT 'top load washer' as col1, 10 as col1 UNION ALL
       SELECT 'front load washer', 20 UNION ALL
       SELECT 'dryer', 30 UNION ALL
       SELECT 'refrigerator', 10 UNION ALL
       SELECT 'microwave', 20 UNION ALL
       SELECT 'dishwasher', 30 UNION ALL
       SELECT 'oven', 5
      ) src
ON T.appliance = src.col1
WHEN NOT MATCHED THEN [insert]
WHEN MATCHED THEN [update]

赞(0）回复(0）举报 2021-07-27

fdbelqdn2#

考虑下面使用bigquery脚本的版本-我认为这是最接近您所要寻找的

CREATE TEMP TABLE inventoryUpdates (product STRING, quantity INT64);

INSERT inventoryUpdates 
VALUES ('top load washer', 11),
  ('front load washer', 20),
  ('dryer', 30),
  ('refrigerator', 11),
  ('microwave', 20),
  ('dishwasher', 30),
  ('oven', 5);

MERGE `dataset.Inventory` T
USING inventoryUpdates U 
ON T.product = U.product
WHEN NOT MATCHED THEN
  INSERT (product, quantity) VALUES(product, quantity)
WHEN MATCHED THEN
  UPDATE SET quantity = T.quantity + U.quantity;

赞(0）回复(0）举报 2021-07-27

我来回答

bigquery合并

2条答案

相关问题

热门标签

最新问答