SparkSQL执行UNPIVOT列转行报FOR关键字语法错误排查

在AWS Glue作业中通过spark.sql对Redshift来源表做数据转换，源表结构及样例数据如下：

name   quality DAV  BAV  LAV  KAV
Deli    Good   YES  NO   YES  YES
Beli     ok     NO  YES  NO   YES

期望通过UNPIVOT操作实现列转行，输出结构及样例数据如下：

name  quality Priv_Name Priv_Value
Deli    Good  DAV       YES
Deli    Good  BAV       NO
Deli    Good  LAV       YES
Deli    Good  KAV       YES
Beli     ok   DAV       NO
Beli     ok   BAV       YES
Beli     ok   LAV       NO
Beli     ok   KAV       YES

用户编写的实现代码：

df_Priv_File = spark.sql(f"""SELECT *
    FROM input_file UNPIVOT (Priv_Value FOR Priv_Name IN (DAV,
        BAV, LAV,KAV))
        """)

执行抛出解析异常：

mismatched input 'FOR' expecting {')', ',', '-'}(line 2, pos 47)

pyspark.sql.utils.ParseException: mismatched input 'FOR' expecting {')', ',', '-'}(line 2, pos 47) == SQL == SELECT * FROM input_file UNPIVOT (Priv_Value FOR Priv_Name IN (DAV, -----------------------------------------------^^^ BAV, LAV,KAV))

1. 语法不符合Spark SQL的UNPIVOT规则：Spark要求UNPIVOT子句的IN列表中，必须为每个待转行的列显式指定对应的Priv_Name字符串别名，原写法仅枚举了列名、未做别名映射，SQL解析器会判定括号内仍处于列枚举阶段，读到FOR关键字时不符合语法预期，直接抛错。
2. 版本兼容问题：AWS Glue部分低版本运行环境（对应Spark版本<3.4）本身不支持UNPIVOT关键字，即便语法写对也无法执行。

方案1：UNPIVOT语法修正版（仅Spark 3.4+、Glue高版本可用） #

补全IN列表中每个列的别名映射即可：

df_Priv_File = spark.sql("""
SELECT name, quality, Priv_Name, Priv_Value
FROM input_file 
UNPIVOT (
    Priv_Value FOR Priv_Name IN (
        DAV AS 'DAV',
        BAV AS 'BAV', 
        LAV AS 'LAV',
        KAV AS 'KAV'
    )
)
""")

方案2：STACK函数通用版（全Spark/Glue版本兼容，推荐） #

使用Spark内置的stack函数实现列转行，无版本兼容问题，性能与原生UNPIVOT一致：

df_Priv_File = spark.sql("""
SELECT 
    name,
    quality,
    stack(
        4, -- 数值为待转行的列总数，当前场景共DAV/BAV/LAV/KAV 4列
        'DAV', DAV,
        'BAV', BAV,
        'LAV', LAV,
        'KAV', KAV
    ) AS (Priv_Name, Priv_Value)
FROM input_file
""")

注意：stack第一个参数必须和后续传入的键值对组数匹配，每一组对应一个转行后的行，格式为'Priv_Name取值', 对应列名。

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