BigQuery中FOR循环内IF EXISTS判断恒为True，手动校验却为False

问题根源 #

你的脚本核心问题是动态字段名解析错误：在FOR循环的WHERE var.column_name IS NOT NULL判断中，BigQuery并没有把var.column_name当作数据表的字段名解析，而是将其视为一个字符串字面量（也就是列名本身的文本内容）。因为var.column_name是从INFORMATION_SCHEMA.COLUMNS获取的有效列名字符串，本身永远不为NULL，所以这个判断会始终返回True，导致所有列都被加入数组。

修复方案1：修正循环中的动态SQL判断 #

通过EXECUTE IMMEDIATE执行动态SQL，让BigQuery正确解析var.column_name作为字段名：

DECLARE r ARRAY<STRING>;
SET r = []; 

FOR var IN (SELECT column_name FROM `project-name.dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = "table_name") DO
    DECLARE has_non_null BOOL;
    -- 用format拼接动态SQL，将var.column_name作为字段名传入
    EXECUTE IMMEDIATE format("""
      SELECT EXISTS(SELECT 1 FROM `project-name.dataset.table_name` WHERE %s IS NOT NULL)
    """, var.column_name) INTO has_non_null;
    
    IF has_non_null THEN 
        SET r = ARRAY_CONCAT(r, [var.column_name]);
    END IF;
END FOR;

SELECT r;

更高效的优化方案（推荐） #

原循环方案需要为每一列扫描一次全表（90列就要扫90次4TB数据），成本极高且效率低下。推荐用UNPIVOT+聚合统计的方式，只扫描一次全表即可完成所有列的非空判断：

DECLARE columns_str STRING;
DECLARE query_str STRING;

-- 自动获取目标表的所有列名，拼接成UNPIVOT所需格式
SET columns_str = (
  SELECT STRING_AGG(DISTINCT column_name, ', ')
  FROM `project-name.dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = "table_name"
);

-- 构建动态查询语句
SET query_str = format("""
  WITH unpivoted AS (
    SELECT column_name, value
    FROM `project-name.dataset.table_name`
    UNPIVOT (value FOR column_name IN (%s))
  )
  SELECT ARRAY_AGG(DISTINCT column_name) AS non_null_columns
  FROM unpivoted
  WHERE value IS NOT NULL
""", columns_str);

-- 执行动态查询
EXECUTE IMMEDIATE query_str;

这个方案通过UNPIVOT将所有列转成行数据，再统计每个列名下非空值的存在情况，最后聚合出所有包含非空值的列名数组，仅需一次全表扫描，成本和效率都远优于循环方案。

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