构建BigQuery动态UNION ALL查询时CONCAT函数报错求助

错误原因 #

报错核心是变量类型不匹配：
当使用FOR table_name IN (SELECT * FROM UNNEST(table_names))时，UNNEST输出的数组元素会被包装成一个STRUCT（默认字段名为f0_），而非直接的STRING类型。CONCAT函数要求所有参数都是STRING或BYTES类型，传入STRUCT后就触发了签名不匹配的错误。

解决方案 #

方案1：直接引用STRUCT中的字段 #

修改循环内的table_name为table_name.f0_，明确提取STRING类型的表名字段：

DECLARE query STRING DEFAULT '';
DECLARE table_names ARRAY<STRING>;

SET table_names = (
  SELECT ARRAY_AGG(table_name) AS table_names
  FROM `project.dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_name LIKE '%client'
);

FOR table_name IN (SELECT * FROM UNNEST(table_names)) DO
  SET query = IF(
    query = '',
    CONCAT('SELECT * FROM `project.dataset.', table_name.f0_, '`'),
    CONCAT(query, ' UNION ALL SELECT * FROM `project.dataset.', table_name.f0_, '`')
  );
END FOR;

EXECUTE IMMEDIATE query;

方案2：优化循环遍历方式 #

在UNNEST时直接指定别名，让table_name直接成为STRING类型，代码更直观：

DECLARE query STRING DEFAULT '';
DECLARE table_names ARRAY<STRING>;

SET table_names = (
  SELECT ARRAY_AGG(table_name) AS table_names
  FROM `project.dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_name LIKE '%client'
);

FOR table_name IN (SELECT * FROM UNNEST(table_names) AS table_name) DO
  SET query = IF(
    query = '',
    CONCAT('SELECT * FROM `project.dataset.', table_name, '`'),
    CONCAT(query, ' UNION ALL SELECT * FROM `project.dataset.', table_name, '`')
  );
END FOR;

EXECUTE IMMEDIATE query;

方案3：简化写法（无需FOR循环） #

使用ARRAY_TO_STRING直接拼接所有表的查询语句，代码更简洁高效：

DECLARE query STRING;

SET query = (
  SELECT CONCAT(
    'SELECT * FROM `project.dataset.', 
    ARRAY_TO_STRING(ARRAY_AGG(table_name), '` UNION ALL SELECT * FROM `project.dataset.'),
    '`'
  )
  FROM `project.dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_name LIKE '%client'
);

EXECUTE IMMEDIATE query;

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