Copy S3 to Redshift: String length exceeds DDL length

1
votes

I've checked the file manually to make sure nothing exceeds the length. That was all fine, but I doubled the length of every varchar anyway.

I added the TRUNCATECOLUMNS parameter:

TRUNCATECOLUMNS

Truncates data in columns to the appropriate number of characters so that it fits the column specification. Applies only to columns with a VARCHAR or CHAR data type, and rows 4 MB or less in size.

Still getting this error: Copy s3 to redshift: String length exceeds DDL length

COPY [table name] 
FROM [s3 path]
iam_role [iam role]
FORMAT CSV
IGNOREHEADER 1
region 'us-west-2'
BLANKSASNULL 
TRIMBLANKS
TRUNCATECOLUMNS
1
amazon-web-servicesamazon-s3importamazon-redshift
If the string contains multi-byte characters, then more storage space is required than the actual number of characters. Have you tried changing the field to TEXT instead of a specific VARCHAR[n] length? Redshift (being Postgres) can handle TEXT nicely. - John Rotenstein
As John says you should check for multi-byte (non-ascii) characters in your input. Check that your rows aren't over 4MB is size as this negates the TRUNCATECOLUMNS option. It may be informative to post the row from stl_load_errors as this often has more information on exactly which field is causing the issue - sometimes the issue isn't where you think it is. - Bill Weiner
Oh and I don't think that type TEXT will help you as it is an alias to VARCHAR(256) in Redshift. So unless your sizes are less than this ... docs.aws.amazon.com/redshift/latest/dg/… - Bill Weiner

1 Answers

2
votes

It turns out that wasnt the actual problem. This error blankets a number of other things, including text in the date column and strings in a number column.. which was the actual issue.