Athena 无法使用 OpenCSVSerde 解析日期

我在 S3 上有一个非常简单的 csv 文件

"i","d","f","s"
"1","2018-01-01","1.001","something great!"
"2","2018-01-02","2.002","something terrible!"
"3","2018-01-03","3.003","I'm an oil man"

我正在尝试使用以下命令创建一个表

CREATE EXTERNAL TABLE test (i int, d date, f  float, s string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
LOCATION 's3://mybucket/test/'
TBLPROPERTIES ("skip.header.line.count"="1");

当我查询表时（select * from test）我收到这样的错误：

HIVE_BAD_DATA：
解析字段 1 的字段值“2018-01-01”时出错：对于输入字符串：“2018-01-01”

更多信息：

• 如果我改变d列改为字符串查询将成功
• 我之前使用 Athena 解析过文本文件中的日期；我相信使用惰性简单串行器 https://docs.aws.amazon.com/athena/latest/ug/lazy-simple-serde.html
• 确实seems就像 OpenCSVSerde 的问题

The 文档 https://docs.aws.amazon.com/athena/latest/ug/csv.html明确表示这是受支持的。求有遇到过这种情况的人，或者有什么建议。

事实上，这是一个问题文档 https://docs.aws.amazon.com/athena/latest/ug/csv.html你提到的。您可能指的是这段摘录：

[OpenCSVSerDe] 识别 DATE 类型（如果在 UNIX 中指定） 格式，例如 YYYY-MM-DD，作为 LONG 类型。

可以理解的是，您将日期格式设置为 YYYY-MM-DD。然而，文档中的这句话具有很大的误导性。当提到UNIX格式时，它实际上有UNIX 纪元时间 https://en.wikipedia.org/wiki/Unix_time心里。

根据 UNIX Epoch 的定义，您的日期应该是整数（因此在文档中引用了 LONG 类型）。您的日期应该是自 1970 年 1 月 1 日以来经过的天数。

例如，您的示例 CSV 应如下所示：

"i","d","f","s"
"1","17532","1.001","something great!"
"2","17533","2.002","something terrible!"
"3","17534","3.003","I'm an oil man"

然后你可以运行完全相同的命令：

CREATE EXTERNAL TABLE test (i int, d date, f  float, s string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
LOCATION 's3://mybucket/test/'
TBLPROPERTIES ("skip.header.line.count"="1");

如果您使用以下命令查询 Athena 表select * from test， 你会得到：

  i       d          f              s           
 --- ------------ ------- --------------------- 
  1   2018-01-01   1.001   something great!     
  2   2018-01-02   2.002   something terrible!  
  3   2018-01-03   3.003   I'm an oil man    

类似的问题也损害了上述文档中对 TIMESTAMP 的解释：

[OpenCSVSerDe] 识别 TIMESTAMP 类型（如果在 UNIX 格式，例如yyyy-mm-dd hh:mm:ss[.f...]，作为 LONG 类型。

这似乎表明我们应该将 TIMESTAMP 格式化为yyyy-mm-dd hh:mm:ss[.f...]。并不真地。事实上，我们需要再次使用 UNIX Epoch Time，但这次使用的是自 1970 年 1 月 1 日午夜以来经过的毫秒数。

例如，考虑以下示例 CSV：

"i","d","f","s","t"
"1","17532","1.001","something great!","1564286638027"
"2","17533","2.002","something terrible!","1564486638027"
"3","17534","3.003","I'm an oil man","1563486638012"

以及以下 CREATE TABLE 语句：

CREATE EXTERNAL TABLE test (i int, d date, f  float, s string, t timestamp)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
LOCATION 's3://mybucket/test/'
TBLPROPERTIES ("skip.header.line.count"="1");

这将是结果集select * from test:

  i       d          f              s                       t             
 --- ------------ ------- --------------------- ------------------------- 
  1   2018-01-01   1.001   something great!      2019-07-28 04:03:58.027  
  2   2018-01-02   2.002   something terrible!   2019-07-30 11:37:18.027  
  3   2018-01-03   3.003   I'm an oil man        2019-07-18 21:50:38.012