AWS Datapipeline RedShiftCopyActivity - how to specify "columns"

Question

I am trying to copy a bunch of csv files from S3 to Redshift using the RedShiftCopyActivity and a datapipeline.

This works fine as long as the csv structure matches the table structure. In my case the csv has less columns than the table and then the RedShiftCopyActivity failes with a "Delimiter not found" error in stl_load_errors.

I would like to use the redshift copy command "columns" option. That way I can make it work, but the columns part of the redshift copy command does not seem to be available in the RedShiftCopyActivity.

Anyone any suggestions?

All tips warmly welcomed.

Many thanks upfront.

Peter

Answer 1

I know this is an old question but now you can specify a list of columns to the Redshift COPY command.

COPY tablename (column1 [,column2, ...]) 

When loading data from S3, the column order needs to match the order of the source data. Check out the docs here: Amazon Redshift Column Mapping Options.

Radu