AWS Glue作业Spark SQL中Timestamp提取日期转换异常问题咨询

问题根因 #

AWS Glue作业底层使用Spark SQL引擎，其语法规则与常规关系型数据库（如MySQL）存在差异：Spark SQL中执行CAST(server_time AS VARCHAR(10))时，不会自动截断字符串取前10位，仅会对字符串长度做校验，超出长度要么返回异常要么保留完整字符串，因此你得到的仍然是完整的timestamp格式结果。

修复方案 #

有两种常用的实现方式可以提取timestamp的日期部分：

• 直接转换为日期类型：使用DATE(server_time)直接将timestamp字段转换为日期类型，性能更高，同时支持日期分区裁剪，适合筛选、分组等场景
• 转换为指定格式字符串：使用DATE_FORMAT(server_time, 'yyyy-MM-dd')精准输出10位长度的日期字符串，适合需要字符串类型输出的场景

修正后查询代码 #

spark.sql("""SELECT 
               count(distinct b.user_id) AS count
             FROM matomo_matomo_log_visit b
             LEFT JOIN matomo_matomo_log_link_visit_action a ON b.idvisit = a.idvisit
             WHERE DATE(server_time) BETWEEN '2021-06-01' AND '2021-06-30'
             GROUP BY DATE(server_time), b.user_id
           """)

如果需要分组后返回字符串格式的日期，可以使用如下写法：

spark.sql("""SELECT 
               DATE_FORMAT(server_time, 'yyyy-MM-dd') AS event_date,
               count(distinct b.user_id) AS count
             FROM matomo_matomo_log_visit b
             LEFT JOIN matomo_matomo_log_link_visit_action a ON b.idvisit = a.idvisit
             WHERE DATE_FORMAT(server_time, 'yyyy-MM') = '2021-06'
             GROUP BY DATE_FORMAT(server_time, 'yyyy-MM-dd'), b.user_id
           """)

额外优化建议 #

WHERE条件避免使用字符串模糊匹配，直接使用日期范围过滤，或者匹配DATE_FORMAT(server_time, 'yyyy-MM')的结果，执行效率远高于LIKE模糊匹配。

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