SQL Server转BigQuery代码报错：Scalar subquery返回多元素，求解决

问题背景 #

原有SQL Server的T-SQL代码可遍历指定表所有列，统计各列空值数、非空值数、总行数及占比。移植到BigQuery后触发报错：Query error: Scalar subquery produced more than one element at [25:12]

错误原因 #

原BigQuery代码中，SET sql = (SELECT ... FROM INFORMATION_SCHEMA.COLUMNS) 属于标量子查询，当目标表包含多列时，该查询会返回多行结果，而SET语句仅能接收单个标量值，因此触发报错。这与SQL Server中SELECT @sql += ... 自动将所有行拼接为单个变量的逻辑不同，BigQuery需要显式使用STRING_AGG函数拼接所有列对应的SQL片段。

修正后的BigQuery代码 #

DECLARE TableName STRING DEFAULT 'myTable';
DECLARE sql STRING DEFAULT '';

SET @@dataset_project_id = 'myProject';
SET @@dataset_id = 'myDataset';

CREATE OR REPLACE TEMP TABLE TablePopulation 
(
    tableName STRING,
    columnName STRING,
    columnPosition INT64,
    nullCount NUMERIC(9,2),
    notNullCount NUMERIC(9,2),
    totalRows NUMERIC(9,2),
    percentNull NUMERIC(9,2),
    percentNotNull NUMERIC(9,2)
);

-- 用STRING_AGG拼接所有列对应的INSERT语句
SET sql = (
    SELECT STRING_AGG(
        'INSERT INTO TablePopulation 
         SELECT ''' || table_name || ''' AS tableName,
                ''' || column_name || ''' AS columnName, 
                ' || ordinal_position || ' AS columnPosition, 
                SUM(CASE WHEN `' || column_name || '` IS NULL THEN 1 ELSE 0 END) AS nullCount, 
                COUNT(`' || column_name || '`) AS notNullCount,
                COUNT(*) AS totalRows,
                SAFE_DIVIDE(SUM(CASE WHEN `' || column_name || '` IS NULL THEN 1 ELSE 0 END), COUNT(*)) * 100 AS percentNull,
                SAFE_DIVIDE(SUM(CASE WHEN `' || column_name || '` IS NOT NULL THEN 1 ELSE 0 END), COUNT(*)) * 100 AS percentNotNull
         FROM `' || table_name || '`;',
        '\n' -- 用换行分隔每个INSERT语句
    )
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = TableName
);

-- 执行拼接好的SQL
EXECUTE IMMEDIATE sql;

-- 查询结果
SELECT * 
FROM TablePopulation;

关键修改点 #

• 使用STRING_AGG将每列对应的INSERT语句拼接为完整SQL字符串，解决标量子查询返回多行的问题
• 对列名和表名添加反引号`，避免遇到关键字时触发语法错误
• 用CREATE OR REPLACE TEMP TABLE简化逻辑，无需提前删除临时表（BigQuery临时表会在会话结束后自动销毁）

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