BigQuery更新GA(UA)导出表UNNEST报错及多表更新咨询

报错原因与单表更新修正 #

错误原因 #

报错核心原因是hits是**ARRAY**类型字段，无法直接通过hits.eventInfo.eventLabel的形式访问数组内嵌套结构体的属性，必须先通过UNNEST将数组拆分为单行元素后才能访问内部字段。
除此之外原脚本还有两个逻辑问题：

• SET逻辑未做值判断，会将所有hit记录的eventLabel全部替换，而非仅替换值为'abc'的条目
• 替换值写死为'null'，不符合替换为'xyz'的需求

修正后的单表更新脚本 #

UPDATE `myProject.ga_sessions_20220403`
SET hits = ARRAY(
  SELECT AS STRUCT * REPLACE (
    IF(
      eventInfo.eventLabel = 'abc',
      (SELECT AS STRUCT * REPLACE ('xyz' AS eventLabel) FROM UNNEST([eventInfo])),
      eventInfo
    ) AS eventInfo
  )
  FROM UNNEST(hits)
)
WHERE EXISTS (
  SELECT 1 FROM UNNEST(hits) AS h
  WHERE h.eventInfo.eventLabel = 'abc'
)

脚本逻辑说明：

• 用EXISTS + UNNEST(hits)判断会话中是否存在符合条件的hit，避免全表扫描更新无匹配记录的行
• 重构hits数组时加IF判断，仅修改满足eventLabel='abc'的hit条目，其余数据保持原样

多日期分片表批量更新方法 #

BigQuery支持用通配符*匹配同数据集下相同结构的日期分片表，更新时需要在WHERE子句中通过_TABLE_SUFFIX指定要更新的表范围，示例脚本如下：

UPDATE `myProject.ga_sessions_*`
SET hits = ARRAY(
  SELECT AS STRUCT * REPLACE (
    IF(
      eventInfo.eventLabel = 'abc',
      (SELECT AS STRUCT * REPLACE ('xyz' AS eventLabel) FROM UNNEST([eventInfo])),
      eventInfo
    ) AS eventInfo
  )
  FROM UNNEST(hits)
)
WHERE 
  -- 连续日期范围用BETWEEN，例如更新2022年4月全月数据
  _TABLE_SUFFIX BETWEEN '20220401' AND '20220430'
  -- 非连续日期可改用枚举：_TABLE_SUFFIX IN ('20220403','20220408','20220421')
  AND EXISTS (
    SELECT 1 FROM UNNEST(hits) AS h
    WHERE h.eventInfo.eventLabel = 'abc'
  )

注意：通配符表更新要求WHERE子句必须包含_TABLE_SUFFIX的过滤条件，否则会执行报错。

新增pagePath过滤条件的写法 #

page.pagePath和eventInfo同属hits数组内STRUCT的同级字段，过滤时需要保证两个条件作用在同一个hit元素上，同时SET逻辑也要同步加上条件避免误改，完整写法如下：

-- 单表场景直接去掉_TABLE_SUFFIX相关条件即可
UPDATE `myProject.ga_sessions_*`
SET hits = ARRAY(
  SELECT AS STRUCT * REPLACE (
    IF(
      eventInfo.eventLabel = 'abc' AND page.pagePath = '12345',
      (SELECT AS STRUCT * REPLACE ('xyz' AS eventLabel) FROM UNNEST([eventInfo])),
      eventInfo
    ) AS eventInfo
  )
  FROM UNNEST(hits)
)
WHERE 
  _TABLE_SUFFIX BETWEEN '20220401' AND '20220430'
  AND EXISTS (
    SELECT 1 FROM UNNEST(hits) AS h
    WHERE 
      h.eventInfo.eventLabel = 'abc'
      AND h.page.pagePath = '12345'
  )

内容的提问来源于stack exchange，提问作者paul