BigQuery定时查询无数据插入？调用存储函数未新增行问题

核心问题根源 #

执行后新增0行的本质是内层查询未返回任何数据，主要集中在时区不匹配、数据延迟、逻辑冗余这几个关键点：

1. 时区不一致导致小时筛选失效 #

代码中event_hour基于America/New_York时区提取，但WHERE条件里的EXTRACT(HOUR FROM CURRENT_TIMESTAMP())用的是BigQuery默认时区（通常为UTC），两者小时数不匹配，直接过滤掉所有数据。

修复：将当前时间的小时提取也指定为纽约时区：

AND EXTRACT(HOUR FROM TIMESTAMP_MICROS(event_timestamp) AT TIME ZONE 'America/New_York') = EXTRACT(HOUR FROM CURRENT_TIMESTAMP() AT TIME ZONE 'America/New_York')

2. 冗余的双层GROUP BY #

内层查询已经按event_date, event_hour, medium完成分组聚合，外层再次执行相同分组的SUM操作完全多余，不仅浪费资源，还可能掩盖数据缺失的问题。

修复：去掉外层GROUP BY和SUM，直接用内层查询结果插入：

EXECUTE IMMEDIATE FORMAT("""
  INSERT INTO newdataset.newtable
  (event_date, event_hour, medium, total_sessions, total_conversions, total_purchase_revenue)
  SELECT
    event_date,
    event_hour,
    medium,
    sessions AS total_sessions,
    conversions AS total_conversions,
    purchase_revenue AS total_purchase_revenue
  FROM (
    SELECT
      DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
      EXTRACT(HOUR FROM TIMESTAMP_MICROS(event_timestamp) AT TIME ZONE 'America/New_York') AS event_hour,
      traffic_source.medium AS medium,
      COUNT(user_id) AS sessions,
      SUM(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS conversions,
      SUM(CASE WHEN event_name = 'purchase' THEN ecommerce.purchase_revenue_in_usd ELSE 0 END) AS purchase_revenue
    FROM %s
    WHERE DATE(TIMESTAMP_MICROS(event_timestamp)) = CURRENT_DATE('America/New_York')
      AND EXTRACT(HOUR FROM TIMESTAMP_MICROS(event_timestamp) AT TIME ZONE 'America/New_York') = EXTRACT(HOUR FROM CURRENT_TIMESTAMP() AT TIME ZONE 'America/New_York')
    GROUP BY event_date, event_hour, medium
  )
  ORDER BY event_date, event_hour, medium;
""", tblName);

3. GA Intraday数据延迟问题 #

BigQuery的GA intraday表数据并非实时同步，通常有5-15分钟的延迟。如果定时任务设置为整点立即执行，对应小时的数据还未同步到BigQuery，会导致查询无结果。

修复：将定时任务执行时间调整为整点后10-15分钟（比如每小时的10分、25分），确保数据同步完成。

4. 动态表名验证 #

先手动执行以下语句，确认生成的表名是否存在且有对应日期的数据：

SELECT CONCAT('`myga-project.mytable.events_intraday_', FORMAT_DATE('%Y%m%d', CURRENT_DATE('America/New_York')), '`') AS target_table;

若表名不存在，检查日期格式是否与GA intraday表的命名规则（events_intraday_YYYYMMDD）一致。

调试步骤 #

修复前，先手动替换动态表名执行内层查询，确认是否有数据返回：

SELECT
  DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
  EXTRACT(HOUR FROM TIMESTAMP_MICROS(event_timestamp) AT TIME ZONE 'America/New_York') AS event_hour,
  traffic_source.medium AS medium,
  COUNT(user_id) AS sessions,
  SUM(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS conversions,
  SUM(CASE WHEN event_name = 'purchase' THEN ecommerce.purchase_revenue_in_usd ELSE 0 END) AS purchase_revenue
FROM `myga-project.mytable.events_intraday_20240520` -- 替换为实际日期的表名
WHERE DATE(TIMESTAMP_MICROS(event_timestamp)) = CURRENT_DATE('America/New_York')
  AND EXTRACT(HOUR FROM TIMESTAMP_MICROS(event_timestamp) AT TIME ZONE 'America/New_York') = EXTRACT(HOUR FROM CURRENT_TIMESTAMP() AT TIME ZONE 'America/New_York')
GROUP BY event_date, event_hour, medium;

如果该查询返回数据，问题出在定时任务的时区或执行时间设置；如果无数据，说明GA intraday表暂未同步对应小时的数据。

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