AWS Athena SQL无法在Apache Spark运行，请求适配改写Spark SQL

有一段可在AWS Athena中正常运行的SQL查询语句，但无法在Spark SQL中执行。原查询语句如下：

With cte as ( select year(date) as year, count(*) as total, count(col1) as col1_not_null,count(col2) as col2_not_null from table group by year(date))
Select cte.year,t.total,t.not_null,t.colum_name,t.not_null from cte
Cross join
UNNEST (array[
cast(row('col1',cte.total,cte.co1_notnull)as row(column_name varchar,total bigint,not_null bigint)),
cast(row('col2',cte.total,cte.col2_notnull)as row(column_name varchar,total bigint,not_null bigint))])a(t)
])

期望得到的结果如下：

需要编写可在Spark中运行的Spark SQL语句。

Spark SQL在结构化对象构造、数组展开的语法上与Athena（基于Presto）存在差异，以下是修正后的可执行语句：

WITH cte AS (
    SELECT 
        year(date) AS year,
        count(*) AS total,
        count(col1) AS col1_not_null,
        count(col2) AS col2_not_null
    FROM `table`
    GROUP BY year(date)
)
SELECT 
    cte.year,
    t.total,
    t.not_null,
    t.column_name
FROM cte
CROSS JOIN explode(
    array(
        named_struct('column_name', 'col1', 'total', cte.total, 'not_null', cte.col1_not_null),
        named_struct('column_name', 'col2', 'total', cte.total, 'not_null', cte.col2_not_null)
    )
) AS t

关键调整说明 #

• 用Spark原生的named_struct替代Athena的row(...)语法来构造结构化对象
• 使用explode函数完成数组展开（Spark也支持UNNEST，但结合数组场景下explode更通用）
• 修正原语句中的拼写错误：co1_notnull改为col1_not_null
• 移除原查询中重复的t.not_null字段选择
• 若表名table是SQL关键字，用反引号包裹避免语法冲突

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