AWS Athena中CAST(string as date)函数无法正常使用问题求助

问题描述 #

我创建了一个外部表，将unformattedDate字段设为string类型，但无法使用CAST(string as date)函数转换日期。

建表语句：

CREATE EXTERNAL TABLE IF NOT EXISTS tempTable
(unformattedDate string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
STORED AS TEXTFILE
LOCATION 's3://...'
TBLPROPERTIES ('skip.header.line.count'='1')

（注：原语句中unformattedDate as string为语法错误，正确写法应为unformattedDate string）

执行查询提取日期部分：

Select unformattedDate, SPLIT_PART(unformattedDate,' ',1) as "unformattedDate2"

查询结果：

但执行带日期转换的查询时失败：

Select unformattedDate,
SPLIT_PART(unformattedDate,' ',1) as "unformattedDate2",
CAST(SPLIT_PART(unformattedDate,' ',1) as Date) "unformattedDate3"

报错信息：

SQL Error [100071] [HY000]: [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. INVALID_CAST_ARGUMENT: Value cannot be cast to date:  [Execution ID: 80c26833-daac-44e8-8441-3464d9757a6d]

问题原因 #

Athena的CAST函数直接转换日期时，仅支持**ISO标准格式（yyyy-MM-dd）**及少数特定格式，而你数据中的M/d/yyyy（或MM/dd/yyyy）美式日期格式不在默认识别范围内，因此转换失败。

解决方案 #

使用date_parse函数指定日期格式进行转换，该函数支持自定义格式字符串，适配非标准日期格式：

Select unformattedDate,
SPLIT_PART(unformattedDate,' ',1) as "unformattedDate2",
date_parse(SPLIT_PART(unformattedDate,' ',1), '%m/%d/%Y') as "unformattedDate3"

格式说明：

• %m：匹配1-2位月份（单数字月份也能识别）
• %d：匹配1-2位日期
• %Y：匹配4位年份

额外容错处理 #

如果数据中存在空值或不符合格式的异常记录，可添加try函数避免查询报错，转换失败时返回NULL：

Select unformattedDate,
SPLIT_PART(unformattedDate,' ',1) as "unformattedDate2",
try(date_parse(SPLIT_PART(unformattedDate,' ',1), '%m/%d/%Y')) as "unformattedDate3"

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