hadoop - HIVE中的TIMESTAMP格式问题

Question

我有从JSON文件创建的Hive表。

CREATE external TABLE logan_test.t1 (
   name string,
   start_time timestamp
   )
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
WITH SERDEPROPERTIES (
  "timestamp.formats" = "yyyy-MM-dd'T'HH:mm:ss.SSSSSS"
)
LOCATION 's3://t1/';

我的时间戳数据采用 yyyy-MM-dd'T'HH:mm:ss.SSSSSS的格式。

我为页面中指定的时间戳格式指定了SERDEPROPERTIES。
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-TimestampstimestampTimestamps

创建语句成功执行，但是 select *失败，并出现以下错误。

HIVE_BAD_DATA: Error parsing field value '2017-06-01T17:51:15.180400' for field 1: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

Answer 1

在介绍中引入了timestamp.formats的Jira HIVE-9298在说明中说，它适用于LazySimpleSerDe。我没有在文档中找到其他SerDe所做的其他提及。
解决方案是将时间戳定义为STRING并在选择中进行转换。yyyy-MM-dd'T'HH:mm:ss.SSSSSS格式的示例:

select timestamp(regexp_replace(start_time, '^(.+?)T(.+?)','$1 $2'))

如果数据文件中同时存在两种格式，则这对于 yyyy-MM-dd'T'HH:mm:ss.SSSSSS和 yyyy-MM-dd HH:mm:ss.SSSSSS(正常时间戳记)均适用。

timestamp(regexp_replace(start_time, '^(.+?)[T ](.+?)','$1 $2'))

正则表达式功能强大，您可以使用相同的模式解析不同的字符串格式。