如何使用数组更新bigquery表? [英] How do I update a bigquery table with an array?

问题描述

我有一个包含日志数据的表，我想用后续查询的结果对其进行更新，该查询将针对过滤后的行插入结果.

I have a table with the log data and I want to update it with the results from the subsequent query which will insert the results against the filtered row.

我想使用全部并集来保留当前值并追加新值，但出现以下错误:

I want to use a union all to keep the current values and append the new ones but I get the following error:

不支持引用其他表的相关子查询，除非可以将它们取消相关，例如将它们转换为有效的JOIN.

Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.

UPDATE LOGGING.table_logs a
SET a.pinged = ARRAY(
      (SELECT AS STRUCT 
      CURRENT_TIMESTAMP() as date,b.size_bytes,timestamp_millis(b.last_modified_time) AS last_modified_time,b.row_count
      FROM  `<DATASETNAME>.__TABLES__` b WHERE table_id = CONCAT("ga_sessions_intraday_",FORMAT_DATE("%Y%m%d", CURRENT_DATE())))

      )

WHERE table_id = CONCAT("ga_sessions_intraday_",FORMAT_DATE("%Y%m%d", CURRENT_DATE()))

推荐答案

以下内容完全未经测试，仅基于[希望]正确地对语法进行改组-从而避免了引用其他表的相关子查询"的问题

Below is not tested at all and is just based on [hopefully] correct shuffling your syntax around - so it avoids issue of "correlated subqueries that reference other tables"

UPDATE LOGGING.table_logs a
SET a.pinged = ARRAY(
  SELECT AS STRUCT 
    CURRENT_TIMESTAMP() AS DATE,
    b.size_bytes,
    TIMESTAMP_MILLIS(b.last_modified_time) AS last_modified_time,
    b.row_count
)
FROM  `<DATASETNAME>.__TABLES__` b 
WHERE a.table_id = b.table_id
AND a.table_id = CONCAT("ga_sessions_intraday_",FORMAT_DATE("%Y%m%d", CURRENT_DATE())) 

请检查并让我知道它是否现在可以使用或仍需要进行一些调整

Please check and let me know if it works now or still some adjustments needed

P.S.显然，以上假设其余逻辑都是正确的

P.S. Obviously, above assumes that the rest of logic is correct

更新:如何保留a.ping中已经存在的内容，并用当前内容和查询结果进行更新?

Update for: How do I retain what's already in a.pinged and update it with what is there currently plus the result of the query?

尝试以下

UPDATE LOGGING.table_logs a
SET a.pinged = ARRAY_CONCAT(a.pinged, ARRAY(
  SELECT AS STRUCT 
    CURRENT_TIMESTAMP() AS DATE,
    b.size_bytes,
    TIMESTAMP_MILLIS(b.last_modified_time) AS last_modified_time,
    b.row_count
))
FROM  `<DATASETNAME>.__TABLES__` b 
WHERE a.table_id = b.table_id
AND a.table_id = CONCAT("ga_sessions_intraday_",FORMAT_DATE("%Y%m%d", CURRENT_DATE()))

这篇关于如何使用数组更新bigquery表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！