topic Re: How to insert data to Amazon RDS(PostgreSQL) from local flat file? in Talend Studio

How to insert data to Amazon RDS(PostgreSQL) from local flat file?

Anonymous — Sat, 16 Nov 2024 11:25:51 GMT

I would like to insert large number of rows from flat file on my local drive to AWS RDS database (PostgreSQL).
I used tFileInput and tPostgresqlOutputBulkExec but the latter component gave me following errors.
Is there any component that can insert data into AWS RDS and doesn't require superuser priviledge (You can't have it in RDS) ?
------------------------------------------------------------------------------
Exception in component tPostgresqlOutputBulkExec_1_tPBE
org.postgresql.util.PSQLException: ERROR: must be superuser to COPY to or from a file

Re: How to insert data to Amazon RDS(PostgreSQL) from local flat file?

Anonymous — Mon, 10 Nov 2014 13:35:29 GMT

How many number of rows you have in a source file, can you insert using tPostgresqlOutput component?
Vaibhav

Re: How to insert data to Amazon RDS(PostgreSQL) from local flat file?

Anonymous — Tue, 11 Nov 2014 03:00:21 GMT

Thank you for the suggestion. It's abount 20,000,000 rows (2.5GB) in total, devided into equally sized 5 files. I had thought it wasn't an option to insert this much of rows.
But anyway, I tried it with one file and found out that although it takes an hour or two for all the data at hand, it can work. I still prefer bulk copy without superuser ( According to Amazon documentation "\copy" command instead of "copy" command ), insert can be an alternative if I run the job over night.

Re: How to insert data to Amazon RDS(PostgreSQL) from local flat file?

Anonymous — Tue, 11 Nov 2014 07:48:02 GMT

You can even think of optimizing the record insert by playing with memory settings, commit size, batch size etc...
Vaibhav

Re: How to insert data to Amazon RDS(PostgreSQL) from local flat file?

Anonymous — Fri, 14 Nov 2014 02:02:03 GMT

In the end, it turns out that row insert into Amazon RDS(MySQL) using specialized to AWS compornent is an option for over night process (about 1500-2000 lines / second) but insert into Amazon RDS (PostgreSQL) is not (60 lines / second ).
I decided to leave the talend just with fixed to delimited file conversion and use pgadminIII to do /copy.