在 Athena 上将时间戳转换为带有时区的时间戳失败了。

Question

我正在尝试创建以下视图：

CREATE OR REPLACE VIEW view_events AS 
(
   SELECT
     "rank"() OVER (PARTITION BY "tb1"."innerid" ORDER BY "tb1"."date" ASC) "r"
   , "tb2"."opcode"
   , "tb1"."innerid"
   , "tb1"."date"
   , From_iso8601_timestamp(tb1.date) as "real_date"
   , "tb2"."eventtype"
   , "tb1"."fuelused"
   , "tb1"."mileage"
   , "tb1"."latitude"
   , "tb1"."longitude"
   FROM
     rt_message_header tb1
   , rt_messages tb2
   WHERE ((("tb1"."uuid" = "tb2"."header_uuid") AND ("tb2"."opcode" = '39')) AND ("tb2"."type" = 'event'))
   ORDER BY "tb1"."innerid" ASC, "tb1"."date" ASC
)

我遇到了以下错误：

您的查询存在以下错误： 不支持的Hive类型：带有时区的时间戳

但是当我单独运行查询时，它可以正常工作，并且From_iso8601_timestamp被提到这里作为有效的日期函数。

是否有人能告诉我我做错了什么？

- Gal Itzhak

Answer 1

简要概述：

SELECT CAST(From_iso8601_timestamp('2018-01-01T15:00:00Z') AS timestamp) as "real_date"

全文:

不幸的是，Athena并不完全支持所有Presto功能，它有限制并且在技术上比Presto落后了几个版本。虽然有一些尝试让Athena与AWS Glue Metastore密切集成，但由于基于Hive的metastore存在一些不一致性，因此存在问题。我希望Spark、Hive、Glue、Athena、Presto等都能使用相同的metastore，这将使生活变得更加容易，但回到您的问题：

这篇关于较旧版本的teradata Presto分支的文档提到了Presto中时间戳的一些问题:

Presto声明具有/不具有时区的时间戳的方法不符合SQL标准。在Presto中，两者都使用单词TIMESTAMP声明，例如TIMESTAMP '2003-12-10 10:32:02.1212'或TIMESTAMP '2003-12-10 10:32:02.1212 UTC'。根据是否在时间戳的末尾包含时区来确定时间戳是否具有时区。在其他系统中，时间戳显式声明为带时区的时间戳或不带时区的时间戳。

从Athena分叉出来的Presto版本支持timestamp和timestamp with timezone，但是由于teradata文档提到的这个怪癖应该不是问题。真正的问题在于Athena不支持具有时区的时间戳。

您链接的presto文档显示该函数返回不支持的类型timestamp with timezone，因此您需要将其转换为其他受支持的类型。Athena允许使用不支持的函数和转换到不支持的数据类型是一个疏忽，希望能够解决，但目前必须绕过它。

您需要做的是在该函数调用周围使用CAST()函数，将类型从timestamp with time zone更改为timestamp

不幸的是，您可能无法直接将字符串转换为时间戳，尽管这取决于字符串的格式。您也不能使用以timestamp编写字符串的转换样式，例如无法执行timestamp '2018-01-01 15:00:00'，原因如下所述。

from_iso1601_timestamp()函数返回的类型

SELECT typeof("real_date") AS real_date_type
FROM
(
SELECT From_iso8601_timestamp('2018-01-01T15:00:00Z') as "real_date"
)

带有时区的时间戳

这样不行

SELECT typeof("real_date") AS real_date_type
FROM
(
SELECT CAST('2018-01-01T15:00:00Z' AS timestamp) as "real_date"
)

SQL错误[FAILED]: INVALID_CAST_ARGUMENT: 无法将值转换为时间戳

这种类型的转换也会返回带有时区的时间戳 :(

请注意，此SELECT部分有效，并显示它是timestamp，但出于某些内部不一致原因，您无法创建视图，您将收到一个错误。

CREATE OR replace VIEW test 
AS 
SELECT typeof( "real_date" ) AS real_date_type
FROM
(
SELECT  timestamp '2018-01-01 15:00:00' as "real_date"
)

SQL错误[FAILED]：无法初始化类com.facebook.presto.util.DateTimeZoneIndex

由于某些原因，在解析select中的值时创建视图需要java类，但是解析值不需要。这是一个应该解决的bug。

这可行！

CREATE OR REPLACE VIEW test
AS
SELECT typeof("real_date") AS real_date_type
FROM
(
SELECT CAST(From_iso8601_timestamp('2018-01-01T15:00:00Z') AS timestamp) as "real_date"
)

- Davos

Answer 2

你可以在 Athena 中使用以下语法处理时间戳数据类型 (dt)：

SELECT id,dt,dt AT TIME ZONE 'America/New_York' as dateTimeNY FROM Table

- Maor Aharon

Answer 3

最近我在处理一个类似的问题时遇到了麻烦。AWS支持团队向我推荐了Davos方案，但它并没有解决我的情况。最终对我有用的解决方案是：

create or replace view db_name.vw_name AS
select
    from_unixtime(cast(to_unixtime(current_timestamp) AS bigint)) as field_name
from db_name.tbl_name

这将把输出格式从timestamp with time zone转换为timestamp

如果您想验证字段的数据类型，可以使用以下方法:

select typeof(field_name) from db_name.vw_name

希望能对你有所帮助！

- Andrew Madsen

---

谢谢你的补充，这似乎是一个有用的替代方案。 - Davos

Answer 4

select 
cast(replace(cast(at_timezone(<YOUR_DATE_FIELD> , 'US/Eastern') as varchar) ,'America/New_York', '') as timestamp),
typeof(cast(replace(cast(at_timezone(<YOUR_DATE_FIELD> , 'US/Eastern') as varchar) ,'America/New_York', '') as timestamp))
from 
<YOUR_TABLE>

假设 <YOUR_DATE_FIELD> 是以 UTC 格式表示的。第一个 at_timezone 函数将把日期转换为 EST 格式，但会附加额外的文字 'US/Eastern'。

下一步是将其转换为 varchar 类型，以删除文字。但是，当转换为 varchar 时，它会变成 'America/New_York'，这就需要用 '' 替换。最后将其转换为 timestamp 类型。

- Koustav

---

请解释一下你的答案。为什么它有效？ - Rohit Gupta

Answer 5

像 Koustav 在我的情况下所回答的那样进行强制转换是获取实时 paris_without_tz 的唯一解决方案，并且它适用于 athena 的 select 和 ctas。

CREATE TABLE test WITH (format = 'PARQUET', write_compression = 'SNAPPY') AS 
select timestamp '2022-11-30 23:00:00.000' as utc
, at_timezone(timestamp '2022-11-30 23:00:00.000', 'Europe/Paris') as tz_paris
, CAST(at_timezone(timestamp '2022-11-30 23:00:00.000', 'Europe/Paris') as timestamp) AS remains_utc
, CAST(replace(CAST(at_timezone(timestamp '2022-11-30 23:00:00.000', 'Europe/Paris') as varchar), ' Europe/Paris', '') as timestamp) AS paris_without_tz

- Sebastien