blocks|key|2612355|text|不幸的是，雅典娜并不完全支持所有的Presto功能，它有limitations，技术上比Presto落后几个版本。有一些尝试让雅典娜与AWS胶元存储紧密集成，虽然它基于蜂巢的元存储，但有一些不一致。我希望Spark，Hive，Glue，Athena，Presto等人可以使用相同的metastore，这会让生活变得更容易，但回到你的问题上：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2612356|这个document+about+an+older+teradata+fork+of+Presto在presto中提到了一些关于时间戳的问题：|2612357|sql用于声明带/不带时区的时间戳的方法不是|2612358|标准。在Presto中，两者都是使用单词TIMESTAMP声明的，例如，TIMESTAMP+'2003-12-10+10:32:02.1212‘或TIMESTAMP+'2003-12-10+10:32:02.1212+UTC’。根据时间戳末尾是否包含时区，确定时间戳是带时区的还是不带时区的。在其它系统中，时间戳被显式声明为带时区的时间戳或不带时区的时间戳|blockquote|2612359|2612360|2612361|雅典娜的派生版本Presto确实支持timestamp和timestamp+with+timezone，但有teradata文档中提到的那个怪癖，这不应该是一个问题。真正的问题是Athena+does+not+support+timestamp+with+timezone。|style|CODE|2612362|您链接的presto文档显示该函数返回一个不受支持的timestamp+with+timezone类型的值，因此您需要将其转换为其他受支持的类型。这是一个疏忽，雅典娜允许函数和类型转换为不受支持的数据类型，希望这将得到补救，但现在您必须解决这个问题。|2612363|您需要做的是在该函数调用周围使用CAST()函数，这会将类型从timestamp+with+time+zone更改为timestamp|2612364|不幸的是，您可能不能将字符串直接转换为时间戳，尽管这取决于字符串的格式。你也不能使用在字符串之前写timestamp的类型，例如，由于我将在下面解释的原因，不能做timestamp+'2018-01-01+15:00:00'。|2612365|由from_iso1601_timestamp()函数返回的类型|2612366|SELECT+typeof("real_date")+AS+real_date_type
FROM
(
SELECT+From_iso8601_timestamp('2018-01-01T15:00:00Z')+as+"real_date"
)|code-block|syntax|javascript|2612367|带有时区的|2612368|时间戳|2612369|2612370|2612371|这不管用|2612372|SELECT+typeof("real_date")+AS+real_date_type
FROM
(
SELECT+CAST('2018-01-01T15:00:00Z'+AS+timestamp)+as+"real_date"
)|2612373|2612374|+SQL错误失败:+INVALID_CAST_ARGUMENT:值不能转换为时间戳|2612375|2612376|2612377|这种类型的转换还会返回带有时区的时间戳:(|2612378|请注意，这段代码的SELECT部分是有效的，它表明它是一个timestamp，但是由于某些内部不一致的原因，您无法创建视图，并且会得到一个错误。|2612379|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"
)|2612380|2612381|+SQL错误失败:无法初始化类com.facebook.presto.util.DateTimeZoneIndex|2612382|2612383|2612384|不管出于什么原因，创建一个视图需要该java类，而解析select中的值却不需要，这是一个需要解决的bug。|2612385|这个不错。|2612386|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"
)|2612387|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.aws.amazon.com/athena/latest/ug/other-notable-limitations.html|1|https://teradata.github.io/presto/docs/141t/release/unsupported.html|2|https://docs.aws.amazon.com/athena/latest/ug/data-types.html^0|S|B|0|0|2|1B|1|0|0|0|0|0|I|9|S|N|2H|1B|2|0|Q|N|0|G|6|V|O|1M|9|0|1D|9|29|V|0|1|O|0|0|0|0|0|0|0|0|0|0|0|0|0|T|9|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|2B|8|@]|9|@$A|2C|B|2D|1|2E]]|C|$]]|$1|D|3|E|5|6|7|2F|8|@]|9|@$A|2G|B|2H|1|2I]]|C|$]]|$1|F|3|G|5|6|7|2J|8|@]|9|@]|C|$]]|$1|H|3|I|5|J|7|2K|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|2L|8|@]|9|@]|C|$]]|$1|L|3|-4|5|6|7|2M|8|@]|9|@]|C|$]]|$1|M|3|N|5|6|7|2N|8|@$A|2O|B|2P|O|P]|$A|2Q|B|2R|O|P]]|9|@$A|2S|B|2T|1|2U]]|C|$]]|$1|Q|3|R|5|6|7|2V|8|@$A|2W|B|2X|O|P]]|9|@]|C|$]]|$1|S|3|T|5|6|7|2Y|8|@$A|2Z|B|30|O|P]|$A|31|B|32|O|P]|$A|33|B|34|O|P]]|9|@]|C|$]]|$1|U|3|V|5|6|7|35|8|@$A|36|B|37|O|P]|$A|38|B|39|O|P]]|9|@]|C|$]]|$1|W|3|X|5|6|7|3A|8|@$A|3B|B|3C|O|P]]|9|@]|C|$]]|$1|Y|3|Z|5|10|7|3D|8|@]|9|@]|C|$11|12]]|$1|13|3|14|5|6|7|3E|8|@]|9|@]|C|$]]|$1|15|3|16|5|J|7|3F|8|@]|9|@]|C|$]]|$1|17|3|-4|5|6|7|3G|8|@]|9|@]|C|$]]|$1|18|3|-4|5|6|7|3H|8|@]|9|@]|C|$]]|$1|19|3|1A|5|6|7|3I|8|@]|9|@]|C|$]]|$1|1B|3|1C|5|10|7|3J|8|@]|9|@]|C|$11|12]]|$1|1D|3|-4|5|6|7|3K|8|@]|9|@]|C|$]]|$1|1E|3|1F|5|J|7|3L|8|@]|9|@]|C|$]]|$1|1G|3|-4|5|6|7|3M|8|@]|9|@]|C|$]]|$1|1H|3|-4|5|6|7|3N|8|@]|9|@]|C|$]]|$1|1I|3|1J|5|6|7|3O|8|@]|9|@]|C|$]]|$1|1K|3|1L|5|6|7|3P|8|@$A|3Q|B|3R|O|P]]|9|@]|C|$]]|$1|1M|3|1N|5|10|7|3S|8|@]|9|@]|C|$11|12]]|$1|1O|3|-4|5|6|7|3T|8|@]|9|@]|C|$]]|$1|1P|3|1Q|5|J|7|3U|8|@]|9|@]|C|$]]|$1|1R|3|-4|5|6|7|3V|8|@]|9|@]|C|$]]|$1|1S|3|-4|5|6|7|3W|8|@]|9|@]|C|$]]|$1|1T|3|1U|5|6|7|3X|8|@]|9|@]|C|$]]|$1|1V|3|1W|5|6|7|3Y|8|@]|9|@]|C|$]]|$1|1X|3|1Y|5|10|7|3Z|8|@]|9|@]|C|$11|12]]|$1|1Z|3|-4|5|6|7|40|8|@]|9|@]|C|$]]]|20|$21|$5|22|23|24|C|$25|26]]|27|$5|22|23|24|C|$25|28]]|29|$5|22|23|24|C|$25|2A]]]]

Unfortunately Athena doesn't fully support all Presto features, it has <a href="https://docs.aws.amazon.com/athena/latest/ug/other-notable-limitations.html" rel="noreferrer">limitations</a> 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:

This <a href="https://teradata.github.io/presto/docs/141t/release/unsupported.html" rel="noreferrer">document about an older teradata fork of Presto</a> mentions some issues with timestamp in presto:

<blockquote>
 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
</blockquote>

The version of Presto that Athena is forked from does support both <code>timestamp</code> and <code>timestamp with timezone</code> but with that quirk as mentioned in the teradata docs which shouldn't be an issue. The real issue is that <a href="https://docs.aws.amazon.com/athena/latest/ug/data-types.html" rel="noreferrer">Athena does not support timestamp with timezone</a>.

The presto docs you've linked to show that the function returns a value of that unsupported type <code>timestamp with timezone</code>, 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.

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

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 <code>timestamp</code> before the string e.g. can't do <code>timestamp '2018-01-01 15:00:00'</code> for reasons I will explain below.

<h1>Type returned by the <code>from_iso1601_timestamp()</code> function</h1>

<pre class="lang-sql prettyprint-override"><code>SELECT typeof("real_date") AS real_date_type
FROM
(
SELECT From_iso8601_timestamp('2018-01-01T15:00:00Z') as "real_date"
)
</code></pre>

<blockquote>
 timestamp with time zone
</blockquote>

<h1>This doesn't work</h1>

<pre class="lang-sql prettyprint-override"><code>SELECT typeof("real_date") AS real_date_type
FROM
(
SELECT CAST('2018-01-01T15:00:00Z' AS timestamp) as "real_date"
)
</code></pre>

<blockquote>
 SQL Error [FAILED]: INVALID_CAST_ARGUMENT: Value cannot be cast to
 timestamp
</blockquote>

<h1>This style of Casting also returns timestamp with timezone :(</h1>

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

<pre class="lang-sql prettyprint-override"><code>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"
)
</code></pre>

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

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.

<h1>This works yay</h1>

<pre class="lang-sql prettyprint-override"><code>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"
)
</code></pre>

blocks|key|2824670|text|在我最近正在做的事情上遇到了类似的事情。AWS支持将我引向了达沃斯解决方案，但它最终并不适用于我的情况。从我那里得到的解决方案是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2824671|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|code-block|syntax|javascript|2824672|这会将timestamp+with+time+zone为current_timestamp的输出转换为timestamp|offset|length|style|CODE|2824673|如果要验证字段的数据类型，可以使用：|2824674|select+typeof(field_name)+from+db_name.vw_name|2824675|希望这能有所帮助！|2824676|entityMap^0|0|0|3|O|S|H|1F|9|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]|$I|Z|J|10|K|L]|$I|11|J|12|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|14|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|T|$]]

Ran into something similar on something that I was working on recently. AWS Support pointed me to Davos solution but it didn't end up working for my case. The solution that ended up working from me was:

<pre><code>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
</code></pre>

This will convert the output of <code>current_timestamp</code> which is <code>timestamp with time zone</code> to <code>timestamp</code>

If you want to verify the data type of the field, you can use:

<pre><code>select typeof(field_name) from db_name.vw_name
</code></pre>

Hope that helps!

blocks|key|91137|text|在Athena中，您可以在时间戳数据类型(Dt)上使用以下语法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|91138|SELECT+id,dt,dt+AT+TIME+ZONE+'America/New_York'+as+dateTimeNY+FROM+Table|code-block|syntax|javascript|91139|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can use the following syntax in Athena over Timestamp data type (dt):

<pre><code>SELECT id,dt,dt AT TIME ZONE 'America/New_York' as dateTimeNY FROM Table
</code></pre>

I'm trying to create to following view:

<pre><code>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
)
</code></pre>

and it gives me the following error:

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

however when I run the query on it's own it works fine, and the From_iso8601_timestamp is mentioned <a href="https://prestodb.io/docs/current/functions/datetime.html" rel="noreferrer">here</a> as a valid date function.

can anyone tell me what I'm doing wrong?

converting to timestamp with time zone failed on Athena

Presto

Spark 

Hive

我正在尝试创建以下视图：CREATE OR REPLACE VIEW view_events AS (   SELECT     "rank"() OVER (PARTITION BY "tb1"."innerid" ORDER BY "tb1"."date" ASC) "r"   , "tb2"."opcode"  ...

问在Athena上转换为带有时区的时间戳失败
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Athena上转换为带有时区的时间戳失败EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Athena上转换为带有时区的时间戳失败
EN