Aws Athena SQL Query is not working in Apache spark

Question

I have query which is working in Athena but not working in spark SQL.

Query-

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)
])

Please help me to create spark SQL query to run in spark .

Expected result:

Date	total	column_name	not_null
2002-1-1	5	col1	4
2002-1-1	4	col2	2

Answer 1

Check below query, Converted to Spark

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)
),
unnest_cte AS (
    SELECT 
        INLINE(
            array(        
                struct(
                    'col1' as column_name,
                    cte.total,
                    cte.col1_not_null AS not_null
                ),
                struct(
                    'col2' as column_name,
                    cte.total,
                    cte.col2_not_null AS not_null
                )
            )
        ) FROM cte
)
SELECT 
    year,
    cte.total,
    not_null,
    column_name,
    not_null 
FROM cte
CROSS JOIN unnest_cte