I am trying to use the "\COPY" command bulk load a CSV file into Amazon RDS Postgres instance.
Here is the command I am using:
\copy nch.bulk_test from 'out.csv' with DELIMITER ';'
When I execute this use a psql client, I had no issues. I was able to load 10,000,000 records very quickly.
However, when I try to execute this command using the tPostgresqlRow component, I get a "ERROR: syntax error at or near "\""
And I have escaped the back slash and the single quotes using the tJava component as follows:
String copy_command = "\\copy bulk_test from \'out.csv\' with DELIMITER \';\'"; context.copy_command = copy_command;
I am simply calling context.copy_command using the tPostgresRow component.
Any suggestions on what I am doing wrong?
You need escape only \
context.copy_command = "\\copy nch.bulk_test from 'out.csv' with DELIMITER ';'";
or just in tPostgreSQLRow
"\\copy nch.bulk_test from 'out.csv' with DELIMITER ';'";
COPY works well for Redshift and I don't have experience with Azure.
COPY doesn't work for Postgres RDS since it's expecting the csv to be local on the database server which is not possible with RDS. That's why Amazon recommends \COPY instead.
and this why it can not be executed:
COPY - it is SQL command
\copy - it is psql command
in this case - You need use tSystem and call psql rather then tPostgeSQLRow
and You can test tRDSPostgresqlBulkExe component from community - https://exchange.talend.com/#marketplaceproductoverview:marketplace=marketplace%252F1&p=marketplace%...
Try Talend Cloud free for 30 days.
Introduction to Talend Open Studio for Data Integration.
Practical steps to developing your data integration strategy.
Create systems and workflow to manage clean data ingestion and data transformation.