Athena无法使用OpenCSVSerde解析日期

Question

Athena无法使用OpenCSVSerde解析日期

13

我在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 在文本文件中解析过日期; 我相信是使用了 LazySimpleSerDe
明显看起来像是 OpenCSVSerde 的问题

文档明确表示支持此功能。寻找遇到此问题或任何建议的人。

- Kirk Broadhurst

2个回答

3

一个解决方案是将d列声明为字符串，然后在SELECT查询中使用DATE（d）或date_parse将值解析为日期数据类型。

- Tanveer Uddin

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Alexandre · Accepted Answer

实际上，你提到的文档存在问题。你可能指的是这段内容：

[OpenCSVSerDe] 识别 UNIX 格式下的 DATE 类型，比如 YYYY-MM-DD，作为 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] 仅会识别指定为 UNIX 格式（例如 yyyy-mm-dd hh:mm:ss[.f...]）的 TIMESTAMP 类型，将其解释为 LONG 类型。

这似乎表明我们应该将 TIMESTAMP 格式化为 yyyy-mm-dd hh:mm:ss[.f...]。实际上并不是这样。事实上，我们需要再次使用 UNIX Epoch 时间，并且这一次使用从 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