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.

demandé il y a un an244 vues
1 réponse
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
INGÉNIEUR EN ASSISTANCE TECHNIQUE
répondu il y a un an

Vous n'êtes pas connecté. Se connecter pour publier une réponse.

Une bonne réponse répond clairement à la question, contient des commentaires constructifs et encourage le développement professionnel de la personne qui pose la question.

Instructions pour répondre aux questions