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.

asked a year ago227 views
1 Answer
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
SUPPORT ENGINEER
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions