Executing \COPY command against Amazon RDS Postgres

Six Stars

Executing \COPY command against Amazon RDS Postgres

Hello,

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?

 

Forteen Stars

Re: Executing \COPY command against Amazon RDS Postgres

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 ';'";
-----------
Six Stars

Re: Executing \COPY command against Amazon RDS Postgres

Tried that, I still get ERROR: syntax error at or near "\"

Highlighted
Forteen Stars

Re: Executing \COPY command against Amazon RDS Postgres

on AWS I have only Redshift, but on Azure I use just COPY

 

Screen Shot 2018-02-15 at 9.49.13 AM.png

 

test it without \

 

-----------
Six Stars

Re: Executing \COPY command against Amazon RDS Postgres

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.

Forteen Stars

Re: Executing \COPY command against Amazon RDS Postgres

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%...

-----------

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog