代码之家 › 专栏 › 技术社区 › Data_101

在Amazon Athena中创建表时转换时间戳

amazon-athena

Data_101 · 技术社区 · 7 年前

我一直在使用下面的查询在Athena中创建一个表,

CREATE EXTERNAL TABLE IF NOT EXISTS test.test_table (
      `converteddate` string,
      `userid` string,
    )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'serialization.format' = ',',
  'field.delim' = ','
) LOCATION 's3:XXXX'
TBLPROPERTIES ('has_encrypted_data'='false',"skip.header.line.count"="1")

这将返回我:

converteddate        | userid
-------------------------------------
2017-11-29T05:00:00  | 00001
2017-11-27T04:00:00  | 00002
2017-11-26T03:00:00  | 00003
2017-11-25T02:00:00  | 00004
2017-11-24T01:00:00  | 00005

我想返回:

converteddate        | userid
-------------------------------------
2017-11-29 05:00:00  | 00001
2017-11-27 04:00:00  | 00002
2017-11-26 03:00:00  | 00003
2017-11-25 02:00:00  | 00004
2017-11-24 01:00:00  | 00005

并且有 转换日期 作为一个 日期时间 而不是一串 .

2 回复 | 直到 6 年前

Dhaval 7 年前

创建表时无法转换数据。但您可以在查询时获取数据。您可以使用date\u parse(字符串,格式)->时间戳函数。这里提到了更多细节。

对于您的用例,您可以执行如下操作

select date_parse(converteddate, '%y-%m-%dT%H:%i:%s') as converted_timestamp, userid
from test_table

注意:根据字符串的类型,您必须为月份(始终为两位数或非两位数)、天、小时(12或24小时格式)等选择合适的说明符

Alexandre 5 年前

(我的回答有一个前提:您使用的是OpenCSVSerDe。例如,它不适用于LazySimpleSerDe。)

如果可以选择更改输入CSV文件的格式,则应将时间戳转换为 UNIX Epoch Time . 这是OpenCSVSerDe所期望的格式。

例如,您的示例CSV如下所示:

"converteddate","userid"    
"2017-11-29T05:00:00","00001"
"2017-11-27T04:00:00","00002"
"2017-11-26T03:00:00","00003"
"2017-11-25T02:00:00","00004"
"2017-11-24T01:00:00","00005"

它应该是:

"converteddate","userid"
"1511931600000","00001"
"1511755200000","00002"
"1511665200000","00003"
"1511575200000","00004"
"1511485200000","00005"

这些整数是自1970年1月1日午夜以来,每个原始日期的毫秒数。

然后,您可以运行 CREATE TABLE 声明:

CREATE EXTERNAL TABLE IF NOT EXISTS test.test_table (
      converteddate timestamp,
      userid string
    )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
LOCATION 's3:XXXX'
TBLPROPERTIES ("skip.header.line.count"="1");

如果您使用 select * from test_table ,结果如下:

       converteddate        userid  
 ------------------------- -------- 
  2017-11-29 05:00:00.000    00001  
  2017-11-27 04:00:00.000    00002  
  2017-11-26 03:00:00.000    00003  
  2017-11-25 02:00:00.000    00004  
  2017-11-24 01:00:00.000    00005

如您所见,在Athena上键入TIMESTAMP包括毫秒。

我写了一篇关于在OpenCSVSerDe中使用类型TIMESTAMP和DATE的更全面的解释。你可以读它 here .