本文介绍了如何使用数组更新bigquery表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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:

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

尝试以下

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表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-05 16:02
查看更多