如何使用DML语法更新BigQuery中的嵌套记录？ [英] How do I update a nested record in BigQuery using DML syntax?

问题描述

我有以下BigQuery架构，并且正在尝试更新 event_dim.date 字段：

$ b

$ b

  UPDATE`sara-bigquery.examples.app_events_20170113` 
 SET event_dim.date ='20170113'
 WHERE true 
  

但是出现这个错误：

 错误：无法在类型为ARRAY  value STRUCT 的值上访问字段日期;>>，timestamp_micros INT64，...>>在[2:15] 
  

我可以使用此查询选择嵌套字段：

  SELECT x.date FROM`sara-bigquery.examples.app_events_20170113`，
 UNNEST（event_dim）x 
  

但找不到正确的 UPDATE 语法。

解决方案

该查询失败，因为 event_dim 是一个结构数组。这应该是诀窍：

  UPDATE`sara-bigquery.examples.app_events_20170113` 
 SET event_dim = ARRAY（
 SELECT AS STRUCT * REPLACE（'20170113'AS date）FROM UNNEST（event_dim）
）
 WHERE true 
  

查看文档 a>关于如何在标准SQL中处理数组以获取更多详细信息。

I've got the following BigQuery schema, and I'm trying to update the event_dim.date field:

I tried the following query using standard SQL and the new BigQuery DML:

UPDATE `sara-bigquery.examples.app_events_20170113`
SET event_dim.date = '20170113'
WHERE true

But got this error:

Error: Cannot access field date on a value with type ARRAY<STRUCT<name STRING, params ARRAY<STRUCT<key STRING, 
value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, ...>>>, timestamp_micros INT64, ...>> at [2:15]

I'm able to select the nested field with this query:

 SELECT x.date FROM `sara-bigquery.examples.app_events_20170113`,
 UNNEST(event_dim) x

But can't figure out the correct UPDATE syntax.

解决方案

That query failed because event_dim is an array of structs. This should do the trick:

UPDATE `sara-bigquery.examples.app_events_20170113`
SET event_dim = ARRAY(
  SELECT AS STRUCT * REPLACE('20170113' AS date) FROM UNNEST(event_dim)
)
WHERE true

Check out the docs on how arrays are handled in Standard SQL for more details.

这篇关于如何使用DML语法更新BigQuery中的嵌套记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！