BigQuery动态合并表时如何处理STRUCT字段新增列的问题

核心解决方案 #

针对collected_traffic_source这个STRUCT字段，统一构造与目标表bi.production.google_analytics_brandsites完全匹配的结构——旧表中缺失的新增字段用NULL填充，确保无论新旧表的STRUCT结构差异，最终输出的字段数量、类型、顺序都和目标表一致。

修改后的查询 #

DECLARE dataset_name STRING;
DECLARE dataset_counter INT64;

-- 获取所有匹配'analytics_xxx'模式的数据集
CREATE TEMP TABLE datasets AS (
  SELECT ROW_NUMBER() OVER() rownumber, schema_name AS dataset_name
  FROM `google-analytics-prod.INFORMATION_SCHEMATA`
  WHERE schema_name LIKE 'analytics_%'
);

SET dataset_counter = 1;
-- 遍历所有数据集
WHILE dataset_counter <= (SELECT MAX(rownumber) FROM datasets)  DO
  SET dataset_name = (SELECT dataset_name FROM datasets WHERE rownumber = dataset_counter);
  
  EXECUTE IMMEDIATE FORMAT('''
  INSERT INTO `bi.production.google_analytics_brandsites`
  SELECT 
    "%s" AS dataset_brandsite, 
    _TABLE_SUFFIX AS events_tbl_name, 
    PARSE_DATE('%%Y%%m%%d', t.event_date) event_date_formatted,
    t.event_date, 
    t.event_timestamp, 
    t.event_name, 
    t.event_params, 
    t.event_previous_timestamp, 
    t.event_value_in_usd, 
    t.event_bundle_sequence_id, 
    t.event_server_timestamp_offset, 
    t.user_id, 
    t.user_pseudo_id, 
    t.privacy_info, 
    t.user_properties, 
    t.user_first_touch_timestamp, 
    t.user_ltv, 
    t.device, 
    t.geo, 
    t.app_info, 
    t.traffic_source, 
    t.stream_id, 
    t.platform, 
    t.event_dimensions, 
    t.ecommerce, 
    t.items, 
    -- 统一构造collected_traffic_source结构，补全缺失字段
    STRUCT(
      t.collected_traffic_source.manual_campaign_id,
      t.collected_traffic_source.manual_campaign_name,
      t.collected_traffic_source.manual_source,
      -- 替换成实际新增的4个字段名，旧表无此字段时返回NULL
      IFNULL(t.collected_traffic_source.new_field_1, NULL) AS new_field_1,
      IFNULL(t.collected_traffic_source.new_field_2, NULL) AS new_field_2,
      IFNULL(t.collected_traffic_source.new_field_3, NULL) AS new_field_3,
      IFNULL(t.collected_traffic_source.new_field_4, NULL) AS new_field_4
      -- 如果还有其他原有STRUCT字段，务必按目标表顺序补充完整
    ) AS collected_traffic_source,
    t.is_active_user 
  FROM `google-analytics-prod.%s.events_*` AS t
  WHERE NOT EXISTS(
    SELECT 1
    FROM `bi.production.google_analytics_brandsites` AS d
    WHERE t._TABLE_SUFFIX = d.events_tbl_name
    AND "%s" = d.dataset_brandsite
  )
  ''', dataset_name, dataset_name, dataset_name);
  
  SET dataset_counter = dataset_counter + 1;
END WHILE;

关键说明 #

1. STRUCT结构对齐：必须严格按照目标表collected_traffic_source的字段顺序、名称、类型来构造STRUCT，确保和目标表完全一致。
2. 缺失字段补全：用IFNULL处理旧表中不存在的新增字段，当字段不存在时返回NULL，避免因字段缺失导致的列数不匹配错误。
3. 字段名替换：请将示例中的new_field_1~new_field_4替换为实际新增的4个字段名称。

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