BigQuery是否有类似Spark unionByName的同名字段合并功能？

直接解决方案：使用UNION ALL BY NAME语法 #

BigQuery已经支持UNION ALL BY NAME，和Spark的unionByName功能完全一致——会自动按字段名匹配合并，两边缺失的字段自动用NULL填充，无需手动补全。

用你的示例改写后的SQL：

WITH
  x AS (SELECT 1 AS a, 2 AS b ),
  y AS (SELECT 3 AS b, 4 AS c )
SELECT * FROM x
UNION ALL BY NAME
SELECT * FROM y

执行后得到预期结果：

a    b    c
1    2    NULL
NULL 3    4

通用动态SQL方案（兼容复杂场景） #

如果需要合并多个表、或者表结构经常变动，可以结合INFORMATION_SCHEMA和动态SQL自动生成查询，不用手动维护字段列表。

比如合并同一数据集下的table_x和table_y：

DECLARE all_columns STRING;

-- 获取两个表的所有字段并去重
SET all_columns = (
  SELECT STRING_AGG(DISTINCT column_name, ', ')
  FROM (
    SELECT column_name FROM `your_dataset.INFORMATION_SCHEMA.COLUMNS` WHERE table_name = 'table_x'
    UNION ALL
    SELECT column_name FROM `your_dataset.INFORMATION_SCHEMA.COLUMNS` WHERE table_name = 'table_y'
  )
);

-- 生成并执行自动补全NULL的合并查询
EXECUTE IMMEDIATE FORMAT("""
  SELECT %s FROM `your_dataset.table_x`
  UNION ALL
  SELECT %s FROM `your_dataset.table_y`
""", 
-- 为table_x生成字段列表，缺失字段用NULL填充
(SELECT STRING_AGG(IF(column_name IN (SELECT column_name FROM `your_dataset.INFORMATION_SCHEMA.COLUMNS` WHERE table_name = 'table_x'), column_name, CONCAT('NULL AS ', column_name)), ', ') 
 FROM (SELECT DISTINCT column_name FROM `your_dataset.INFORMATION_SCHEMA.COLUMNS` WHERE table_name IN ('table_x', 'table_y'))),
-- 为table_y生成字段列表，缺失字段用NULL填充
(SELECT STRING_AGG(IF(column_name IN (SELECT column_name FROM `your_dataset.INFORMATION_SCHEMA.COLUMNS` WHERE table_name = 'table_y'), column_name, CONCAT('NULL AS ', column_name)), ', ') 
 FROM (SELECT DISTINCT column_name FROM `your_dataset.INFORMATION_SCHEMA.COLUMNS` WHERE table_name IN ('table_x', 'table_y')))
);

这个方案会自动识别两个表的所有字段，为每个表补全缺失字段的NULL值，实现通用的字段匹配合并。

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