Spark 3.5是否支持CLUSTER BY？同代码3.4执行成功3.5失败排查

问题背景 #

在Fabric Spark Notebook环境中，一段包含CLUSTER BY的SQL代码在Spark 3.4版本可正常执行，但在Spark 3.5版本执行相同代码时失败，报错[PARSE_SYNTAX_ERROR]语法错误，错误指向PARTITIONED BY语句。需排查失败原因，并确认Spark 3.5是否支持CLUSTER BY功能。

原SQL代码 #

%%sql
SELECT SalesOrderLineNumber, SalesOrderNumber,'' as CustomerName, SUM(Quantity), SUM(UnitPrice), SUM(TaxAmount)
FROM sales_df
GROUP BY SalesOrderLineNumber, SalesOrderNumber

UNION ALL

SELECT SalesOrderLineNumber,'' as SalesOrderNumber, CustomerName, SUM(Quantity), SUM(UnitPrice), SUM(TaxAmount)
FROM sales_df
GROUP BY SalesOrderLineNumber, CustomerName

UNION ALL

SELECT '' asSalesOrderLineNumber, SalesOrderNumber, CustomerName, SUM(Quantity), SUM(UnitPrice), SUM(TaxAmount)
FROM sales_df
GROUP BY SalesOrderNumber, CustomerName

CLUSTER BY (SalesOrderLineNumber, SalesOrderNumber)

报错信息 #

[PARSE_SYNTAX_ERROR] Syntax error at or near 'PARTITIONED'.(line 17, pos 0)

== SQL ==
SELECT SalesOrderLineNumber, SalesOrderNumber,'' as CustomerName, SUM(Quantity), SUM(UnitPrice), SUM(TaxAmount)
FROM sales_df
GROUP BY SalesOrderLineNumber, SalesOrderNumber

UNION ALL

SELECT SalesOrderLineNumber,'' as SalesOrderNumber, CustomerName, SUM(Quantity), SUM(UnitPrice), SUM(TaxAmount)
FROM sales_df
GROUP BY SalesOrderLineNumber, CustomerName

UNION ALL

SELECT '' asSalesOrderLineNumber, SalesOrderNumber, CustomerName, SUM(Quantity), SUM(UnitPrice), SUM(TaxAmount)
FROM sales_df
GROUP BY SalesOrderNumber, CustomerName

PARTITIONED BY (SalesOrderLineNumber, SalesOrderNumber)
^^^

原因分析与解决方案 #

1. Spark 3.5对CLUSTER BY的支持情况 #

Spark 3.5完全支持CLUSTER BY功能，报错并非因为功能移除，而是语法使用错误。

2. 语法错误根源 #

CLUSTER BY是针对单条SELECT语句的排序/分区指令，不能直接跟在UNION ALL组合的多段查询之后。Spark 3.4的语法校验相对宽松，允许这种不规范写法，但Spark 3.5收紧了语法规则，解析器在处理不符合规范的CLUSTER BY位置时，误将其解析为PARTITIONED BY，从而触发语法错误。

另外原SQL中第三个SELECT语句存在笔误：'' asSalesOrderLineNumber缺少空格，应为'' as SalesOrderLineNumber，这个问题在Spark 3.4可能被忽略，但Spark 3.5的校验更严格，也可能加剧解析失败。

3. 修正后的SQL代码 #

将UNION ALL的整体结果作为子查询，在外层添加CLUSTER BY，同时修正笔误：

%%sql
SELECT * FROM (
    SELECT SalesOrderLineNumber, SalesOrderNumber,'' as CustomerName, SUM(Quantity), SUM(UnitPrice), SUM(TaxAmount)
    FROM sales_df
    GROUP BY SalesOrderLineNumber, SalesOrderNumber

    UNION ALL

    SELECT SalesOrderLineNumber,'' as SalesOrderNumber, CustomerName, SUM(Quantity), SUM(UnitPrice), SUM(TaxAmount)
    FROM sales_df
    GROUP BY SalesOrderLineNumber, CustomerName

    UNION ALL

    SELECT '' as SalesOrderLineNumber, SalesOrderNumber, CustomerName, SUM(Quantity), SUM(UnitPrice), SUM(TaxAmount)
    FROM sales_df
    GROUP BY SalesOrderNumber, CustomerName
)
CLUSTER BY SalesOrderLineNumber, SalesOrderNumber

内容的提问来源于stack exchange，提问作者a-tkim