where clause won't work in sql transform in glue

We would need more details on what is the issue that you are exactly facing , but check if the below helps you .

In the glue script below note the abc text . The query by itself just uses that literal , whereas in the later section , abc is actually being mapped to the sql table . sparkSqlQuery is a custom function that is written to execute the sql

You can simulate a similar script if you devise just this section of the code in a new visual job.

def sparkSqlQuery(glueContext, query, mapping, transformation_ctx) -> DynamicFrame:
    for alias, frame in mapping.items():
        frame.toDF().createOrReplaceTempView(alias)
    result = spark.sql(query)
    return DynamicFrame.fromDF(result, glueContext, transformation_ctx)



# Script generated for node SQL
SqlQuery0 = """
select 
year(create_time)          as create_time_year,
month(create_time)         as create_time_month,
count(id)                  as count_of_records 
from abc
where 
create_time is not null 
group by year(create_time),month(create_time)
order by year(create_time),month(create_time)

"""
SQL_node = sparkSqlQuery(
    glueContext,
    query              =SqlQuery0,
    mapping            ={"abc": sql_node},
    transformation_ctx ="SQL_node",
)