Google BigQuery循环执行导出任务时如何实现异常处理？

问题背景 #

需要每月通过EXPORT DATA OPTIONS命令，将多个数据集内的多张表导出至GCS进行备份，待备份表的信息维护在一张表中，结构如下：

编写的动态查询脚本在遇到Date_filter字段为STRING类型时会报错中断，无法继续处理剩余表。需要实现类似try-catch的异常处理机制，让执行不中断，同时能查看已导出和导出失败的表列表。

原脚本如下：

DECLARE folder_name DEFAULT CAST(DATE(CURRENT_DATE()) AS STRING);

DECLARE i INT64 DEFAULT 0;

DECLARE dynamic_query ARRAY <STRING>;

SET dynamic_query = (SELECT ARRAY_AGG(CONCAT("EXPORT DATA OPTIONS (uri = 'gs://data_backup/",folder_name,'/',Table_Name,"_*',\n format = 'CSV',","\n overwrite = TRUE,","\n header = TRUE,","\n field_delimiter = ',') AS","\n\n SELECT * FROM \n","   `",Table_Name,"`\n","WHERE \n DATE(",Date_filter,") BETWEEN DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 month), month) AND LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 month), month)","\n LIMIT \n 1000000",";","\n")) FROM `google_sheets.backup_tables`);

LOOP
  SET i = i + 1;
  IF i > ARRAY_LENGTH(dynamic_query) THEN 
    SET boolean = FALSE;
    LEAVE; 
  END IF;
  EXECUTE IMMEDIATE dynamic_query[ORDINAL(i)];
END LOOP; 

SELECT dynamic_query;

改进后的脚本（带异常处理） #

利用BigQuery的BEGIN...EXCEPTION...END块实现异常捕获，同时维护临时表记录每个表的导出状态：

DECLARE folder_name DEFAULT CAST(DATE(CURRENT_DATE()) AS STRING);
DECLARE i INT64 DEFAULT 0;
DECLARE total_tables INT64;
DECLARE current_table STRING;
DECLARE current_query STRING;
DECLARE backup_list ARRAY<STRUCT<Table_Name STRING, export_query STRING>>;

-- 创建临时表存储导出结果
CREATE TEMP TABLE export_results (
  table_name STRING,
  status STRING,
  error_message STRING,
  export_timestamp TIMESTAMP
);

-- 获取待备份表的详细信息并生成导出查询
WITH backup_tables AS (
  SELECT 
    Table_Name, 
    Date_filter,
    CONCAT(
      "EXPORT DATA OPTIONS (",
      "uri = 'gs://data_backup/",folder_name,"/",Table_Name,"_*',",
      "format = 'CSV',",
      "overwrite = TRUE,",
      "header = TRUE,",
      "field_delimiter = ','",
      ") AS ",
      "\nSELECT * FROM `",Table_Name,"` ",
      "\nWHERE DATE(", 
        -- 兼容STRING类型的Date_filter：如果是字段名则转换为STRING，否则直接使用
        IF(REGEXP_CONTAINS(Date_filter, r"^'.+'$"), Date_filter, CONCAT("CAST(", Date_filter, " AS STRING)")),
      ") BETWEEN DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH) AND LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH)",
      "\nLIMIT 1000000;"
    ) AS export_query
  FROM `google_sheets.backup_tables`
  WHERE Timeline = 'Monthly'
)
SELECT ARRAY_AGG(STRUCT(Table_Name, export_query)) INTO backup_list FROM backup_tables;

SET total_tables = ARRAY_LENGTH(backup_list);

-- 循环执行导出并捕获异常
LOOP
  SET i = i + 1;
  IF i > total_tables THEN
    LEAVE;
  END IF;
  
  SET current_table = backup_list[ORDINAL(i)].Table_Name;
  SET current_query = backup_list[ORDINAL(i)].export_query;
  
  BEGIN
    EXECUTE IMMEDIATE current_query;
    -- 记录成功状态
    INSERT INTO export_results (table_name, status, export_timestamp)
    VALUES (current_table, 'SUCCESS', CURRENT_TIMESTAMP());
  EXCEPTION WHEN ERROR THEN
    -- 记录失败状态和错误信息
    INSERT INTO export_results (table_name, status, error_message, export_timestamp)
    VALUES (current_table, 'FAILED', @@error.message, CURRENT_TIMESTAMP());
  END;
END LOOP;

-- 输出最终导出结果
SELECT * FROM export_results ORDER BY status;

关键改进说明 #

• 异常捕获机制：通过BEGIN...EXCEPTION...END块包裹导出执行逻辑，捕获所有执行错误，保证脚本不会中断，剩余表能继续处理。
• STRING类型Date_filter兼容：自动判断Date_filter是字段名还是字符串常量，对字段名自动转换为STRING类型，避免DATE()函数类型不匹配报错。
• 结果追踪：创建临时表export_results，记录每个表的导出状态、错误信息和时间戳，执行完成后可直接查看所有表的备份情况。
• 结构化查询存储：用STRUCT数组存储表名和对应导出查询，比纯字符串数组更易维护，方便关联表名和执行结果。

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