Invalid Date Format - trying to parse a value listed under a different key

For engineers Troubleshooting
1

I’m having difficulty getting the StorageLoader to process unstructured events from an s3://<company_name>/shredded/good directory into Redshift.

The error in the terminal reads ERROR: Load into table '<table_name>' failed. Check 'stl_load_errors' system table for details.

The err_reason given is Invalid Date Format - length must be 10 or more.

The event in question is as follows:
{"schema":{"vendor":"com.<company>","name":"job","format":"jsonschema","version":"1-0-0"},"data":{"jobSlug":"<name-of-job>","publisherSlug":"<name of published>","location":"national","sector":"hospitality","jobType":"full-time","publishFrom":"2016-07-27","expiryDate":"2016-08-30"},"hierarchy":{"rootId":"00efed37-6002-4648-8b95-0b24ed8a1cdc","rootTstamp":"2016-07-27 13:46:04.000","refRoot":"events","refTree":["events","job"],"refParent":"events"}}
The column name that the error occurs on is publishFrom, and the raw field value is given as hospitality, rather than 2016-07-27 as expected.

Does anybody have any ideas on what may have caused the incorrect value to be associated with a key?

Thanks

2

Hi @samf89,

JSONPaths file dictates which event value goes to which Redshift column. The order and number of paths in JSONPaths file have to match that of the corresponding Redshift table columns.

Please, read the following post explaining the concept and role of JSONPaths files: JSONPaths files demystified.

Regards,
Ihor

2 Likes
3

Yep it looks like that was the issue. Informative post, thanks very much!