How to import a PostgreSQL data file into Amazon redshift

Question

I am trying to import a PostgreSQL data file into Amazon redshift using my command line. I did import the schema file but can not import data file. It seems that data insertion in amazon redshift is a bit different.

I want to know all kinds of way of importing data file into redshift using command line.

UPDATE

My data file looks like :

COPY actor (actor_id, first_name, last_name, last_update) FROM stdin; 
0 Chad Murazik 2014-12-03 10:54:44
1 Nelle Sauer 2014-12-03 10:54:44
2 Damien Ritchie 2014-12-03 10:54:44
3 Casimer Wiza 2014-12-03 10:54:44
4 Dana Crist 2014-12-03 10:54:44
....

I typed the following command from CLI:

PGPASSWORD=**** psql -h testredshift.cudmvpnjzyyy.us-west-2.redshift.amazonaws.com -p 5439 -U abcd -d pagila -f /home/jamy/Desktop/pag_data.sql`

And then got error like :

ERROR: LOAD source is not supported. (Hint: only S3 or DynamoDB or EMR based load is allowed

What do you mean by "data file"? Is that a file with SQL INSERT statements? CSV? Output from pg_dump? — Patrick
– Patrick, Commented May 15, 2015 at 6:35
output from pg_dump for example: COPY actor (actor_id, first_name, last_name, last_update) FROM stdin; 0 Chad Murazik 2014-12-03 10:54:44 1 Nelle Sauer 2014-12-03 10:54:44 2 Damien Ritchie 2014-12-03 10:54:44 3 Casimer Wiza 2014-12-03 10:54:44 4 Dana Crist 2014-12-03 10:54:44 — Sahidul Islam
– Sahidul Islam, Commented May 15, 2015 at 6:37
"all kind of way[s]"? There are endless ways. But I think it sounds like you want to just feed the dump into psql. Other options include pg_restore, the COPY command, or using an ETL tool like Talend Studio, Pentaho Kettle or CloverETL. Anyway, what's the error message? — Craig Ringer
– Craig Ringer, Commented May 15, 2015 at 8:32
command: PGPASSWORD=**** psql -h testredshift.cudmvpnjzyyy.us-west-2.redshift.amazonaws.com -p 5439 -U abcd -d pagila -f /home/jamy/Desktop/pag_data.sql ERROR: LOAD source is not supported. (Hint: only S3 or DynamoDB or EMR based load is allowed) @CraigRinger — Sahidul Islam
– Sahidul Islam, Commented May 15, 2015 at 12:35

Adam Matan · Accepted Answer · 2015-05-19 11:16:05Z

3

Dump your table using a CSV format:

\copy <your_table_name> TO 'dump_fulename.csv' csv header NULL AS '\N'

Upload it to S3, and read it from redshift using:

COPY schema.table FROM 's3:/...' WITH CREDENTIALS '...' CSV;

Source: Importing Data into Redshift from MySQL and Postgres

answered May 19, 2015 at 11:16

Adam Matan

138k155 gold badges414 silver badges584 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yuri Levinsky · Accepted Answer · 2015-05-19 08:02:49Z

1

You can't use pg_dump: unload all your data to s3 and use a copy command to load it into Redshift. This is a common mistake.

answered May 19, 2015 at 8:02

Yuri Levinsky

1,5952 gold badges14 silver badges27 bronze badges

2 Comments

Sahidul Islam Over a year ago

Is there any other way?? it looks like little bit complex

Yuri Levinsky Over a year ago

This one is most efficient. You can use postgresql dblink or write your appp.

Collectives™ on Stack Overflow

How to import a PostgreSQL data file into Amazon redshift

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related