AWS Glue: crawler misinterprets timestamps as strings. GLUE ETL meant to convert strings to timestamps makes them NULL

amazon-web-services amazon-s3 amazon-athena aws-glue
Balajee Addanki picture Balajee Addanki · Aug 27, 2018 · Viewed 8.4k times · Source

I have been playing around with AWS Glue for some quick analytics by following the tutorial here

While I have been able to successfully create crawlers and discover data in Athena, I've had issues with the data types created by the crawler. The date and timestamp data types get read as string data types.

I followed this up by creating an ETL job in GLUE using the data source created by the crawler as the input and a target table in Amazon S3

As part of the mapping transformation, I converted the data types of the date and timestamp as string to timestamp but unfortunately the ETL converted these column types to NULLS. I have contemplated using classifiers with GROK expressions but then decided transform them as part of ETL in GLUE.

The timestamp format is as 1/08/2010 6:15:00 PM

Answer

j.b.gorski picture j.b.gorski · Aug 29, 2018

I don't know what the format of timestamp you use therefore I assumed that the format is the following:

2018-05-30T12:22:07.000Z

In this case you can set the serde parameter in the table definition as below:

'timestamp.formats'='yyyy-MM-dd\'T\'HH:mm:ss.SSSZ'

Related questions

How to create AWS Glue table where partitions have different columns? ('HIVE_PARTITION_SCHEMA_MISMATCH')

As per this AWS Forum Thread, does anyone know how to use AWS Glue to create an AWS Athena table whose partitions contain different schemas (in this case different subsets of columns from the table schema)? At the moment, when …

Read More
Add a partition on glue table via API on AWS?

I have an S3 bucket which is constantly being filled with new data, I am using Athena and Glue to query that data, the thing is if glue doesn't know that a new partition is created it doesn't search that …

Read More
What is difference between s3 select and athena

I am trying to understand what is difference between aws athena service and newly released s3 select (still in preview). And how usecases different for both of those? It seems both helps in selecting partial data from s3.

Read More