BigQuery如何查找GA360导出表序列中缺失的日期记录

• 已配置GA360数据实时直连导出至BigQuery，按规则每日GA360导出任务会在对应视图下生成1张独立表，累计存储400天数据时对应应存在400张表。
• 排查时发现部分视图下表总数和预期天数不匹配，存在数据缺失风险，需要定位缺失日期对应的表。
• 当前已有基础查询可返回数据集与解析为日期格式的表ID，基础查询逻辑如下：

SELECT 'xxx' as dataset_id, 
PARSE_DATE('%Y%m%d',RIGHT(table_id,8)) as date 
FROM `project.xxx.__TABLES__` where table_id like 'ga_sessions_2%'

查询输出示例 #

核心需求 #

修改上述查询，返回指定时间区间（示例区间为2022-04-12至2022-06-01）内每一个缺失日期对应的记录。

实现逻辑为先生成指定排查区间内的全量连续日期序列，再和已存在的表日期做左连接，连接后匹配不到已有表的日期即为缺失日期。
可直接在BigQuery中运行以下修改后的SQL：

-- 配置排查时间范围，可按需修改
DECLARE start_date DATE DEFAULT '2022-04-12';
DECLARE end_date DATE DEFAULT '2022-06-01';

WITH existing_tables AS (
  SELECT 
    'xxx' AS dataset_id, 
    PARSE_DATE('%Y%m%d', RIGHT(table_id, 8)) AS date 
  FROM `project.xxx.__TABLES__` 
  WHERE table_id LIKE 'ga_sessions_2%'
    AND PARSE_DATE('%Y%m%d', RIGHT(table_id, 8)) BETWEEN start_date AND end_date
),
all_expected_dates AS (
  SELECT
    dataset_id,
    expected_date
  FROM UNNEST(GENERATE_DATE_ARRAY(start_date, end_date, INTERVAL 1 DAY)) AS expected_date
  CROSS JOIN (SELECT DISTINCT dataset_id FROM existing_tables)
)
SELECT
  a.dataset_id,
  a.expected_date AS missing_date
FROM all_expected_dates a
LEFT JOIN existing_tables e
  ON a.dataset_id = e.dataset_id
  AND a.expected_date = e.date
WHERE e.date IS NULL
ORDER BY a.dataset_id, a.expected_date;

结果说明 #

针对给出的示例数据，上述查询会返回2022-04-13至2022-05-31区间内的所有日期，即为该时间段内缺失的GA360导出表对应日期。

调整说明 #

• 如需修改排查时间范围，直接修改开头start_date、end_date两个变量值即可。例如要排查最近400天的缺失数据，可将start_date设为DATE_SUB(CURRENT_DATE(), INTERVAL 400 DAY)，end_date设为CURRENT_DATE()
• 如需批量排查多个数据集，可将existing_tables中的固定dataset_id逻辑替换为从对应元表读取的真实dataset_id，SQL无需其他改动即可适配多数据集场景
• 运行查询前需确保账号拥有对应数据集__TABLES__元表的读取权限，否则会触发权限报错

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