如何用BigQuery检查外部GCS表数据是否存在并设置默认值

错误原因 #

你遇到的Error while reading table: matched no files是因为my-project.my-dataset.my-table是外部表，它指向的GCS路径gs://my-bucket/some-dir/*没有任何文件。此时直接执行聚合查询（如MAX()）会触发报错，而非返回NULL，导致你写的IFNULL或表存在判断逻辑失效：

• 第一段SQL：IFNULL仅在查询返回NULL时生效，但无文件时查询直接报错，逻辑无法执行
• 第二段SQL：__TABLES__只判断表是否存在，不检查表内是否有数据。如果表存在但无文件，依然会执行MAX()查询触发报错

解决方案 #

方案1：先判断表是否有数据（推荐） #

用EXISTS判断表是否有数据，避免无数据时执行聚合查询报错：

DECLARE start_date DATETIME;

-- 判断表内是否有数据（而非表是否存在）
IF EXISTS (SELECT 1 FROM `my-project.my-dataset.my-table` LIMIT 1) THEN
  SET start_date = DATETIME_ADD((SELECT MAX(DATETIME(EVENT_DATE)) FROM `my-project.my-dataset.my-table`), INTERVAL 1 HOUR);
ELSE
  -- 无数据时使用raw-data-table的最小日期作为默认值
  SET start_date = (SELECT MIN(DATETIME(EVENT_DATE)) FROM `my-project.my-dataset.raw-data-table`);
END IF;

SELECT start_date;

方案2：用SAFE前缀函数容错 #

使用BigQuery的SAFE.前缀包装聚合函数，让无数据时返回NULL而非报错，再用COALESCE设置默认值：

SELECT COALESCE(
  -- 无数据时SAFE.MAX返回NULL，后续逻辑触发默认值
  DATETIME_ADD(SAFE.MAX(DATETIME(EVENT_DATE)), INTERVAL 1 HOUR),
  (SELECT MIN(DATETIME(EVENT_DATE)) FROM `my-project.my-dataset.raw-data-table`)
) AS start_date
FROM `my-project.my-dataset.my-table`;

补充说明 #

如果你不确定表是否存在（不仅是无数据），可以在EXISTS外层再加一层表存在判断：

IF EXISTS (SELECT 1 FROM `my-project.my-dataset.__TABLES__` WHERE table_id = 'my-table') 
   AND EXISTS (SELECT 1 FROM `my-project.my-dataset.my-table` LIMIT 1) THEN
  -- 表存在且有数据的逻辑
ELSE
  -- 表不存在或无数据的逻辑
END IF;

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