How do I remove Header Row when migrating from S3 to Redshift DB?

Question

I have a MySQL table that I'm migrating over to Redshift. The steps are pretty straightforward.

Export MySQL table to CSV
Place CSV into Amazon S3
Create table in Redshift with exact specifications as MySQL table
Copy CSV export into Redshift

I'm having a problem with the last step. I have headers in my MySQL CSV export. I can't currently recreate it, so I'm stuck with the CSV file. Step 4 is giving me an error because of the headers.

Instead of changing the CSV, I would love to add a line to account for headers. I've searched through AWS's documentation for copying tables which is pretty extensive, but nothing to account for headers. Looking for something like header = TRUE to add into the query below.

My COPY statement into Redshift right now looks like:

COPY apples FROM
's3://buckets/apples.csv'  
CREDENTIALS 'aws_access_key_id=abc;aws_secret_access_key=def'
csv
;

mlh351 mlh351 · Accepted Answer · 2016-07-06T21:37:58

Found the IGNOREHEADER function, but still couldn't figure out where to write it.

Pretty obvious now, but just add IGNOREHEADER at the bottom. The 1 represents the number of rows you want to skip for headers, aka my CSV had one row of headers.

COPY apples FROM
's3://buckets/apples.csv'  
CREDENTIALS 'aws_access_key_id=abc;aws_secret_access_key=def'
csv
IGNOREHEADER 1
;

How do I remove Header Row when migrating from S3 to Redshift DB?

2 Answers