BigQuery日内表转主表时间预判及动态SQL方案咨询

1. 预判日内表数据同步至主表的时间 #

• 若同步为定时任务（如BigQuery Data Transfer Service、Cloud Scheduler触发的脚本）：
  • 查看任务历史执行记录，统计近期平均完成时间作为预判基准；
  • 通过BigQuery元数据监控同步状态：

    -- 检查主表昨日分区是否已存在（假设按日期字段date_col分区）
    SELECT EXISTS(
      SELECT 1 FROM `your-project.your-dataset.INFORMATION_SCHEMA.PARTITIONS`
      WHERE table_name = 'table_主表'
        AND partition_id = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    ) AS has_yesterday_partition;
    

    -- 查看日内表昨日数据剩余行数
    SELECT COUNT(*) AS yesterday_row_count
    FROM `your-project.your-dataset.table_intraday_`
    WHERE date_col = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
    

    当日内表昨日数据行数降为0时，说明同步已完成。
• 若同步为事件触发（如数据采集完成后自动触发）：无法精准预判时间，建议改为监控日内表的昨日数据状态，当数据被移除时触发取数脚本，而非固定时间执行。

2. 数据同时存在于两个表的情况是否会发生？ #

会发生，这取决于同步逻辑的实现：

• 常规同步流程（先插入主表，再删除日内表）：在插入完成到删除完成的窗口内，两个表都会存在昨日数据，形成短暂重叠；
• 原子操作同步（如分区交换ALTER TABLE ... SWITCH PARTITION、MERGE结合删除）：不会出现数据同时存在的情况，但实现复杂度较高。

动态取数方案的优化建议 #

为避免重复取数或数据不一致，可调整方案：

• 合并双表数据并去重：用UNION ALL获取两个表的昨日数据，再通过业务主键或更新时间去重（如保留最新记录）：

  WITH combined_data AS (
    SELECT *, 'intraday' AS source FROM `your-project.your-dataset.table_intraday_` WHERE date_col = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
    UNION ALL
    SELECT *, 'main' AS source FROM `your-project.your-dataset.table_主表` WHERE date_col = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  )
  SELECT * EXCEPT(source, row_num)
  FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY business_id ORDER BY update_time DESC) AS row_num
    FROM combined_data
  )
  WHERE row_num = 1;
  

• 优先主表+重试逻辑：先检查主表是否有昨日数据，有则取主表；无则取日内表；若两者都无，等待5-10分钟后重试（避免同步过程中出现两边都无数据的间隙）。

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