JSON timestamp.formats'='yyyy-MM-dd'T'HH:mm:ss.SSS'Z' returns HIVE_BAD_DATA: Error Parsing a column in the table: Cannot create timestamp, parsing error

When creating your table in Athena, you should define the column as a STRING type initially. This is because the format you're using includes the 'T' and 'Z' characters, which are not part of the standard timestamp format that Athena recognizes automatically. (Refer - https://docs.aws.amazon.com/athena/latest/ug/data-types-timestamps.html)

Once the time column shows up as STRING, use can use date_parse() function to read it as a timestamp.

Below is an example of Athena SQL and it's output using the typeof() function

SELECT typeof('2024-08-23T03:01:49.186Z');
OUTPUT: varchar(24)

SELECT typeof(date_parse('2024-08-23T03:01:49.186Z', '%Y-%m-%dT%H:%i:%s.%fZ')) 
OUTPUT: timestamp(3)

SELECT date_parse('2024-08-23T03:01:49.186Z', '%Y-%m-%dT%H:%i:%s.%fZ') AS parsed_timestamp
OUTPUT: 	
2024-08-23 03:01:49.186