Clever Geek Handbook

How to import PostgreSQL data file into Amazon redshift

I am trying to import a PostgreSQL data file in Amazon redshift using my command line. I have imported the schema file, but I cannot import the data file. It looks like the data insertion at Amazon's redshift is slightly different.

I want to know all kinds of data file import in redshift using command line.

UPDATE

My data file looks like this:

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 the 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 there was an error like:

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

+3


source to share


2 answers


Dump your table using CSV format:

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

Upload it to S3 and read it with redshift using:



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

Source: Importing Data to Redshift from MySQL and Postgres

+3


source


You cannot use pg_dump: dump all your data to s3 and use the copy command to upload to Redshift. This is a common mistake.



+1


source






More articles:


All Articles