在雅典娜上转换为带时区的时间戳失败 [英] converting to timestamp with time zone failed on Athena

问题描述

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

I'm trying to create to following view:

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
)

，它给我以下错误：

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

Your query has the following error(s): Unsupported Hive type: timestamp with time zone

但是当我在上面运行查询时它本身就可以正常工作，并且在此处提到了From_iso8601_timestamp作为有效的日期函数

however when I run the query on it's own it works fine, and the From_iso8601_timestamp is mentioned here as a valid date function.

有人可以告诉我我在做什么错吗？

can anyone tell me what I'm doing wrong?

推荐答案

不幸的是，雅典娜不完全支持Presto的所有功能，它具有限制，从技术上讲，它比Presto落后几个版本。有一些尝试使Athena与AWS Glue Metastore紧密集成，后者虽然基于Hive的Metastore也存在一些不一致之处。我希望Spark，Hive，Glue，Athena，Presto等能够与同一个metastore一起工作，使生活更轻松，但是回到您的问题：

Unfortunately Athena doesn't fully support all Presto features, it has limitations and is technically a few versions behind Presto. There's some attempt to make Athena integrate closely with the AWS Glue Metastore, which while based on Hive's metastore has some inconsistencies. I wish that Spark, Hive, Glue, Athena, Presto et al would just work with the same metastore, it would make life easier, but back to your issue:

有关Presto的较旧Teradata分支的文档提到了一些时间戳问题在presto中：

This document about an older teradata fork of Presto mentions some issues with timestamp in presto:

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

Presto’s method for declaring timestamps with/with out timezone is not sql standard. In Presto, both are declared using the word TIMESTAMP, e.g. TIMESTAMP '2003-12-10 10:32:02.1212' or TIMESTAMP '2003-12-10 10:32:02.1212 UTC'. The timestamp is determined to be with or without timezone depending on whether you include a time zone at the end of the timestamp. In other systems, timestamps are explicitly declared as TIMESTAMP WITH TIME ZONE or TIMESTAMP WITHOUT TIME ZONE

Presto的版本Athena的分叉版本同时支持 timestamp 和 timestamp带时区，但具有teradata文档中提到的怪癖，不会成为问题。真正的问题是雅典娜不支持带时区的时间戳。

The version of Presto that Athena is forked from does support both timestamp and timestamp with timezone but with that quirk as mentioned in the teradata docs which shouldn't be an issue. The real issue is that Athena does not support timestamp with timezone.

您已链接以显示的最新文档显示，该函数返回的值不受支持，类型为 timestamp，带有时区，因此您需要将其转换为其他受支持的内容。疏忽大意的是，Athena允许函数和强制转换为不支持的数据类型，并希望可以进行补救，但现在您必须解决此问题。

The presto docs you've linked to show that the function returns a value of that unsupported type timestamp with timezone, so you need to cast it as something else that is supported. It's an oversight that Athena allows functions and casting to a datatype that is then not supported, and hopefully that will be remedied, but for now you have to work around it.

您需要做的是在该函数调用周围使用 CAST（）函数，这会将类型更改为带时区的时间戳转换为 timestamp

What you need to do is use the CAST() function around that function call, which will change the type from timestamp with time zone into timestamp

不幸的是，您可能无法将字符串直接转换为时间戳，尽管这取决于关于字符串的格式。您也不能使用强制转换的样式，例如在字符串前写 timestamp 。不能执行 timestamp'2018-01-01 15:00:00'的原因，我将在下面解释。

Unfortunately you probably can't cast the string directly to a timestamp, although it depends on how the string is formatted. You also can't use the style of casting where you write timestamp before the string e.g. can't do timestamp '2018-01-01 15:00:00' for reasons I will explain below.

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

带有时区的时间戳

timestamp with time zone

这不起作用

This doesn't work

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：值不能转换为
timestamp

SQL Error [FAILED]: INVALID_CAST_ARGUMENT: Value cannot be cast to timestamp

这种类型的Casting还会返回带有时区的时间戳：（

请注意，SELECT p它的工作原理是，它说这是一个时间戳，但是由于某些内部不一致的原因，您无法创建视图，并且会收到错误消息。

This style of Casting also returns timestamp with timezone :(

Note that the SELECT part of this works, and it says that it is a timestamp, but for some internal inconsistency reason you can't create a view and you'll get an error.

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

SQL Error [FAILED]: Could not initialize class com.facebook.presto.util.DateTimeZoneIndex

无论出于何种原因，创建视图都需要Java类，而在select中解析值时却不需要。

For whatever reason, creating a view requires that java class while parsing the value in the select doesn't. It's a bug that should be addressed.

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

这篇关于在雅典娜上转换为带时区的时间戳失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！