Specify multiple delimiters for Redshift copy command

Question

Is there a way to specify multiple delimiters to Redshift copy command while loading data.

I have a data file having the following format:-

1 | ab | cd | ef

2 | gh | ij | kl

I am using a command like this:-

COPY MY_TBL
  FROM 's3://s3-file-path' 
  iam_role 'arn:aws:iam::ddfjhgkjdfk'
  manifest
  IGNOREHEADER 1
gzip delimiter '|';

Fields are separated by | and records are separated using newline. How do I copy this data into Redshift. Because my query above gives me a delimiter not found error

Answer 1

No, delimiters are single characters.

From Data Format Parameters:

Specifies the single ASCII character that is used to separate fields in the input file, such as a pipe character ( | ), a comma ( , ), or a tab ( \t ).

You could import it with a pipe delimiter, then perform an UPDATE command to STRIP() off the spaces.

Answer 2

Your error above suggests that something in your data is causing the COPY command to fail. This could be a number of things, from file encoding, to some funky data in there. I've struggled with the "delimiter not found" error recently, which turned out to be the ESCAPE parameter combined with trailing backslashes in my data which prevented my delimiter (\t) from being picked up.

Fortunately, there are a few steps you can take to help you narrow down the issue:

stl_load_errors - This system table contains details on any error logged by Redshift during the COPY operation. This should be able to identify the row number in your data file that is causing the problem.

NOLOAD - will allow you to run your copy command without actually loading any data to Redshift. This performs the COPY ANALYZE operation and will highlight any errors in the stl_load_errors table.

FILLRECORD - This allows Redshift to "fill" any columns that it sees as missing in the input data. This is essentially to deal with any ragged-right data files, but can be useful in helping to diagnose issues that can lead to the "delimiter not found" error. This will let you load your data to Redshift and then query in database to see where your columns start being out of place.

From the sample you've posted, your setup looks good, but obviously this isn't the entire picture. The options above should help you narrow down the offending row(s) to help resolve the issue.