How to copy csv data file to Amazon RedShift?

mysql csv amazon-redshift
ciphor picture ciphor · Mar 7, 2013 · Viewed 45.3k times · Source

I'm trying to migrating some MySQL tables to Amazon Redshift, but met some problems.

The steps are simple: 1. Dump the MySQL table to a csv file 2. Upload the csv file to S3 3. Copy the data file to RedShift

Error occurs in step 3:

The SQL command is:

copy TABLE_A from 's3://ciphor/TABLE_A.csv' CREDENTIALS 'aws_access_key_id=xxxx;aws_secret_access_key=xxxx' delimiter ',' csv;

The error info:

An error occurred when executing the SQL command: copy TABLE_A from 's3://ciphor/TABLE_A.csv' CREDENTIALS 'aws_access_key_id=xxxx;aws_secret_access_key=xxxx ERROR: COPY CSV is not supported [SQL State=0A000] Execution time: 0.53s 1 statement(s) failed.

I don't know if there's any limitations on the format of the csv file, say the delimiters and quotes, I cannot find it in documents.

Any one can help?

Answer

ciphor picture ciphor · Mar 13, 2013

The problem is finally resolved by using:

copy TABLE_A from 's3://ciphor/TABLE_A.csv' CREDENTIALS 'aws_access_key_id=xxxx;aws_secret_access_key=xxxx' delimiter ',' removequotes;

More information can be found here http://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html

Related questions

How to output MySQL query results in CSV format?

Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format? Here's what I'm doing now: mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/ /,/g' | …

Read More
How do I import CSV file into a MySQL table?

I have an unnormalized events-diary CSV from a client that I'm trying to load into a MySQL table so that I can refactor into a sane format. I created a table called 'CSVImport' that has one field for every column …

Read More
MYSQL import data from csv using LOAD DATA INFILE

I am importing some data of 20000 rows from a CSV file into Mysql. Columns in the CSV are in a different order than MySQL table's columns. How to automatically assign columns corresponding to Mysql table columns? When I execute LOAD …

Read More