csv - Athena 无法使用 OpenCSVSerde 解析日期

Question

我在 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:
Error parsing field value '2018-01-01' for field 1: For input string: "2018-01-01"

更多信息:

• 如果我将 d 列更改为字符串，查询将会成功
• 我之前曾使用 Athena 解析过文本文件中的日期；我相信使用 LazySimpleSerDe
• 绝对看起来像是 OpenCSVSerde 的问题

documentation明确表示这是受支持的。寻找遇到过此问题的人或任何建议。

Answer 1

其实就是documentation的问题你提到的。您可能指的是这段摘录:

[OpenCSVSerDe] recognizes the DATE type if it is specified in the UNIX format, such as YYYY-MM-DD, as the type LONG.

可以理解的是，您将日期格式设置为 YYYY-MM-DD。然而，文档中的这句话具有很大的误导性。当提到UNIX格式时，实际上有UNIX Epoch 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");

如果您使用 select * from test 查询 Athena 表，您将得到:

  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] recognizes the TIMESTAMP type if it is specified in the UNIX format, such as yyyy-mm-dd hh:mm:ss[.f...], as the type 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