在雅典娜上转换为带有时区的时间戳失败

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
)

它给了我以下错误：

您的查询有以下错误： 不支持的配置单元类型：带有时区的时间戳

但是，当我自己运行查询时，它工作正常，并且此处提到的From_iso8601_timestamp是一个有效的日期函数。

谁能告诉我我做错了什么？

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的元存储，但存在一些不一致之处。我希望Spark，Hive，Glue，Athena，Presto等人可以使用相同的元存储，这将使生活更轻松，但回到您的问题：

这篇关于 Presto 的旧 teradata 分支的文档提到了 presto 中时间戳的一些问题：

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

Athena 分叉的 Presto 版本确实支持timestamp和timestamp with timezone，但正如 teradata 文档中提到的，这个怪癖应该不是问题。真正的问题是雅典娜不支持时区时间戳。

您链接的 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 错误 [失败]： 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 错误 [失败]：无法初始化类 com.facebook.presto.util.DateTimeZoneIndex

无论出于何种原因，创建视图都需要该 java 类，而解析选择中的值则不需要。这是一个应该解决的错误。

这行得通

耶

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"
)

Answer 2

您可以在 Athena over Timestamp 数据类型 (dt( 中使用以下语法：

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

Answer 3

在我最近正在做的事情上遇到了类似的事情。AWS Support 向我推荐了达沃斯解决方案，但它最终并不适合我的情况。最终从我身上工作的解决方案是：

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 zonecurrent_timestamp的输出转换为timestamp

如果要验证字段的数据类型，可以使用：

select typeof(field_name) from db_name.vw_name

希望对您有所帮助！

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>

假设是UTC格式。第一个at_timezone函数会将日期更改为 EST 格式，但会附加额外的文字"美国/东部"。

下一步是将其转换为 varchar 以删除文字。然而，当转换为varchar时，它会变成'America/New_York'，这是需要用''替换的。最后将其转换为时间戳

Answer 5

在我的情况下，像库斯塔夫答案这样的选角是获得实时paris_without_tz的唯一解决方案，它适用于雅典娜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