AWS Athena Epoch转timestamp建表后数据错误如何解决

根本原因 #

Athena 处理 Parquet 文件中的 timestamp 类型字段时，默认将读取到的整数值识别为 毫秒级 Unix 时间戳，而你存储的 test_date 是秒级 Unix 时间戳，1467936000 毫秒仅相当于 1467936 秒，换算后自然对应 1970 年的时间。

解决方案 #

可根据你的使用场景选择以下任意一种方案：

• 方案1：新增SERDE参数指定时间单位（无需修改源文件、无需每次查询转换，推荐）
  建表时在 SERDE 配置中新增参数指定时间戳为秒级即可，建表语句示例：

CREATE EXTERNAL TABLE `test_table`(
  `test_date` timestamp)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
WITH SERDEPROPERTIES (
  "parquet.timestamp.time.unit" = "SECONDS"
)
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://xxxxxxxxx/'
TBLPROPERTIES (
  'has_encrypted_data'='false')

修改后直接查询 test_date 字段即可得到正确时间。

• 方案2：调整表结构+查询时转换（无需修改源文件）
  建表时将test_date字段类型设为BIGINT，查询时用from_unixtime()函数转换为时间格式，建表语句示例：

CREATE EXTERNAL TABLE `test_table`(
  `test_date` BIGINT)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://xxxxxxxxx/'
TBLPROPERTIES (
  'has_encrypted_data'='false')

查询语句示例：

SELECT from_unixtime(test_date) AS test_date FROM test_table

• 方案3：调整源文件时间精度（无需调整建表逻辑）
  生成 Parquet 文件时，将秒级时间戳乘以 1000 转换为毫秒级整数再存储，原来的建表语句（test_date设为timestamp类型）即可直接返回正确时间。

内容的提问来源于stack exchange，提问作者Jeeva Bharathi