Timestamp not be accepted as partition key

0

Hi could anyone help on these? we are facing this when we migrating to glue catalog, it cannot show timestamp in the glue athena which cause this table unselectable. HIVE_INVALID_PARTITION_VALUE: Invalid partition value ‘2022-08-09 23%3A59%3A59’ for TIMESTAMP partition key: xxx_timestamp=2022-08-09 23%253A59%253A59

  • I would suggest you take the chance of the migration to change that, even if you can fix it, using a timestamp as a partition sounds problematic, first because it has too much granularity and also because it's going to reduce the compatibility with client tools and cause you difficulties. You can do the same thing with a string.

已提問 1 年前檢視次數 243 次
1 個回答
0

Hello,

According to below documentation

https://docs.aws.amazon.com/athena/latest/ug/data-types.html

https://docs.aws.amazon.com/athena/latest/ug/data-types.html#data-types-timestamps-writing-to-s3-objects

The timestamp compatible format in Athena is YYYY-MM-DD HH:MM:SS.SSS for example, timestamp '2008-09-15 03:04:05.324'.

So, I would recommend you to run "SHOW PARTITIONS <table_name> and check if the partitions are in the correct format. If you find any partitions not in the right format, please drop the particular partition and reload the partition in the correct format. Use the following query to drop partition:

ALTER TABLE <table_name> DROP PARTITION (xxx_timestamp = " ")

Or you can change the datatype of the this column to string from Glue console and try running the query again.

Tables-> click table to update -> Actions -> Edit Schema -> Select field -> Edit -> Data type:String

Viewing and Editing Table Details - https://docs.aws.amazon.com/glue/latest/dg/console-tables.html

profile pictureAWS
支援工程師
已回答 1 年前

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南