Spark SQL动态日期范围查询无数据问题求助

问题背景 #

在Databricks Notebook中，使用静态日期条件查询Temp_HISTORICAL_SWIPE_DETAILS表时可正常返回数据：

WHERE DATE_FORMAT(E.EVENT_TIME_UTC,'yyyy-MM-dd') BETWEEN '2019-02-24' AND '2019-03-31'

但通过Python循环生成动态日期参数IterStartLagDatetime和IterEndDatetime后，以下三种写法均无法返回数据：

• BETWEEN CAST('{IterStartLagDatetime}' AS STRING) AND CAST('{IterEndDatetime}' AS STRING)
• BETWEEN to_date('{IterStartLagDatetime}','yyyy-MM-dd') AND to_date('{IterEndDatetime}','yyyy-MM-dd')
• BETWEEN DATE_FORMAT('{IterStartLagDatetime}','yyyy-MM-dd') AND DATE_FORMAT('{IterEndDatetime}','yyyy-MM-dd')

附完整Python循环及SQL执行代码：

%python
BatchInsert_StartYear = 2019
BatchInsert_EndYear = 2019
while (BatchInsert_StartYear <= BatchInsert_EndYear):
    print(BatchInsert_StartYear)
    MonthCount = 1
    while (MonthCount < 13):
        if(MonthCount < 12):  
            IterEndDatetime = right('00'+ str(MonthCount+1),2)+'-01-'+ str(BatchInsert_StartYear)
            IterEndDatetime = datetime.strptime(IterEndDatetime,'%m-%d-%Y')+ timedelta(days=-1)
            IterEndDatetime = IterEndDatetime.strftime("%Y-%m-%d")
            print(IterEndDatetime)
            IterStartDatetime = right('00'+ str(MonthCount),2)+'-01-'+ str(BatchInsert_StartYear)
            IterStartLagDatetime = datetime.strptime(IterStartDatetime,'%m-%d-%Y')+ timedelta(days=-5)
            IterStartDatetime = datetime.strptime(IterStartDatetime,'%m-%d-%Y').strftime("%Y-%m-%d")
            print(IterStartDatetime)
            
            IterStartLagDatetime = IterStartLagDatetime.strftime("%Y-%m-%d")
            print(IterStartLagDatetime)
        else:
            IterEndDatetime = right('00'+ str(1),2)+'-01-'+ str(BatchInsert_StartYear+1)
            IterEndDatetime = datetime.strptime(IterEndDatetime,'%m-%d-%Y')+ timedelta(days=-1)
            IterEndDatetime = IterEndDatetime.strftime("%Y-%m-%d")
            print(IterEndDatetime)
            IterStartDatetime = right('00'+ str(MonthCount),2)+'-01-'+ str(BatchInsert_StartYear)
            IterStartLagDatetime = datetime.strptime(IterStartDatetime,'%m-%d-%Y')+ timedelta(days=-5)
            IterStartDatetime = datetime.strptime(IterStartDatetime,'%m-%d-%Y').strftime("%Y-%m-%d")
            print(IterStartDatetime)
            IterStartLagDatetime = IterStartLagDatetime.strftime("%Y-%m-%d")
            print(IterStartLagDatetime)
        ABC_df = spark.sql('''
        SELECT *
        FROM Temp_HISTORICAL_SWIPE_DETAILS E
        WHERE DATE_FORMAT(E.EVENT_TIME_UTC,'yyyy-MM-dd') BETWEEN DATE_FORMAT('{IterStartLagDatetime}','yyyy-MM-dd') AND DATE_FORMAT('{IterEndDatetime}','yyyy-MM-dd')
        ''')
        ABC_df.show()

问题原因 #

1. 字符串插值失效：原代码中直接在spark.sql()的字符串里写{IterStartLagDatetime}，Python不会自动替换为变量值，导致SQL传入的是字面量{IterStartLagDatetime}而非实际日期字符串。
2. 冗余日期函数错误：IterStartLagDatetime和IterEndDatetime已经是yyyy-MM-dd格式的字符串，SQL中用DATE_FORMAT处理字符串会触发类型错误（DATE_FORMAT要求第一个参数为日期类型）。

可行解决方案 #

方案1：使用Python F-string实现变量替换 #

在SQL字符串前添加f前缀，让Python正确替换变量值，同时去掉SQL中多余的日期转换函数：

ABC_df = spark.sql(f'''
SELECT *
FROM Temp_HISTORICAL_SWIPE_DETAILS E
WHERE DATE_FORMAT(E.EVENT_TIME_UTC,'yyyy-MM-dd') BETWEEN '{IterStartLagDatetime}' AND '{IterEndDatetime}'
''')

方案2：使用Spark SQL参数绑定（更安全） #

通过spark.sql()的参数绑定传递变量，避免SQL注入风险，同时保证类型匹配：

ABC_df = spark.sql('''
SELECT *
FROM Temp_HISTORICAL_SWIPE_DETAILS E
WHERE DATE_FORMAT(E.EVENT_TIME_UTC,'yyyy-MM-dd') BETWEEN ? AND ?
''', (IterStartLagDatetime, IterEndDatetime))

性能优化建议 #

• 若EVENT_TIME_UTC是日期时间类型，建议直接用日期范围比较替代DATE_FORMAT，可利用索引提升查询效率：

  WHERE E.EVENT_TIME_UTC >= to_timestamp('{IterStartLagDatetime}', 'yyyy-MM-dd') 
    AND E.EVENT_TIME_UTC < to_timestamp('{next_day_of_IterEndDatetime}', 'yyyy-MM-dd')
  

• 避免循环内重复调用spark.sql()，可先收集所有日期范围再批量处理。

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