CODEWITHSUNDEEP
amazon-web-servicesamazon-s3amazon-redshift
Asish
Asish

Reputation: 409

Redshift COPY from S3 fails when timestamp is not correct

While loading data into Redshift from S3 via the COPY command, if any record in the file contains an incorrect timestamp, then the copy fails. I have passed maxerror as 1000 to the COPY command, but still it fails.

However, upon subsequent retries, the same command works. Though it fails to load the corrupted records.

This is the error I am getting:

ERROR:  Assert
DETAIL:  
  -----------------------------------------------
  error:  Assert
  code:      1000
  context:   status == 0 - timestamp: '-6585881136298398395'
  query:     30903
  location:  cg_util.cpp:1063
  process:   query1_69 [pid=25674]
  -----------------------------------------------

AWS cli version : aws-cli/1.10.56 Python/2.7.12 Linux/4.4.19-29.55.amzn1.x86_64 botocore/1.4.46

Is there anyone who faced the same issue? How did you resolve it?

Upvotes: 1

Views: 2704

Answers (1)

Yusuf Hassan
Yusuf Hassan

Reputation: 2013

Append

ACCEPTANYDATE dateformat 'auto'

in your copy statement.

ACCEPTANYDATE dateformat (AWS Documentation)

This'll atleast try to enforce that your copy statements don't fail. Still, some of the unsupported format might be null (as you mentioned,I am fine with the corrupt record(record containing wrong timestamp) not getting loaded to redshift. But other records should be loaded)

Upvotes: 2

Related Questions