在Databricks的Spark SQL中执行含WITH子句的SQL Server外键查询报错的解决方案咨询

嘿，我来帮你搞定这个问题！你遇到的报错是因为Spark SQL和SQL Server在元数据结构、语法细节上有不少差异，直接迁移SQL Server的查询肯定会出问题，下面我给你拆解原因和适配方案：

问题背景 #

你原本在SQL Server里用CTE查询能完美列出所有外键信息，但把这段SQL直接放到Databricks的Spark SQL里执行时，触发了ParseException，核心原因是两者的元数据体系、字符串处理逻辑完全不同。

错误原因拆解 #

1. 系统元数据表差异：SQL Server依赖sys.foreign_keys、sys.tables这类私有系统表，但Spark SQL遵循标准SQL的元数据规范，使用information_schema下的视图（比如referential_constraints、key_column_usage）来存储外键等元数据，没有sys开头的这类表。
2. 字符串拼接语法不同：SQL Server用+做字符串拼接，Spark SQL需要用concat()函数（部分版本支持||，但concat兼容性更好）。
3. 多行字符串聚合逻辑不同：SQL Server的FOR XML PATH是私有语法，用来拼接多行结果为单字符串；Spark SQL里可以用collect_list配合concat_ws实现相同效果，更简洁高效。
4. 关键字别名处理：你原查询里用了' = ' as [join]，join是SQL关键字，在Spark SQL里需要用反引号`join`包裹，而且这个字段在最终查询里没用到，完全可以去掉简化代码。

适配后的Spark SQL查询 #

下面是调整后的查询，完全适配Spark SQL，能返回数据库中所有外键的完整信息：

WITH fk_details AS (
    SELECT
        concat(rc.constraint_schema, '.', kcu1.table_name) AS foreign_table,
        concat(rc.unique_constraint_schema, '.', kcu2.table_name) AS primary_table,
        rc.constraint_name AS fk_constraint_name,
        kcu1.column_name AS fk_column_name,
        kcu2.column_name AS pk_column_name
    FROM information_schema.referential_constraints rc
    JOIN information_schema.key_column_usage kcu1
        ON rc.constraint_name = kcu1.constraint_name
    JOIN information_schema.key_column_usage kcu2
        ON rc.unique_constraint_name = kcu2.constraint_name
        AND kcu1.ordinal_position = kcu2.ordinal_position
)
SELECT
    foreign_table,
    primary_table,
    fk_constraint_name,
    concat_ws(',', collect_list(fk_column_name)) AS fk_columns,
    concat_ws(',', collect_list(pk_column_name)) AS pk_columns
FROM fk_details
GROUP BY foreign_table, primary_table, fk_constraint_name
ORDER BY foreign_table, fk_constraint_name

代码说明 #

• 用information_schema的标准视图替代了SQL Server的私有sys表，这是Spark SQL获取元数据的标准方式
• 用concat()进行字符串拼接，替代SQL Server的+语法
• 用collect_list收集同一外键下的所有关联列，再用concat_ws拼接成逗号分隔的字符串，替代FOR XML PATH的私有聚合逻辑
• 去掉了原查询中无意义的冗余字段，简化了查询结构

你可以把这段SQL放到你的Scala代码里执行，比如：

val ch = """上面的Spark SQL查询内容"""
val df = spark.sql(ch)
df.show()

这样就能正常返回所有外键的表格信息啦！

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