elt update table Redshift

Four Stars

elt update table Redshift

Hi,

 

I created some jobs in TOS in order to implement Insert-Update between tables from different schemas on Redshift.

For both cases, I used ELT components, using "Mapping Redshift". No problem for "Insert" task.

Actually, I am not able to implement UPDATE. Use case: One table on a schema that has to update another table on a different schema. This is what I've done:

  • Set correctly keys on both tables;
  • "Action on data" : Update (tELTJDBCOutput);
  • Correct mapping on ELTMap. No joins required (one input table that has to update output table). I tried also to inner join input table with a copy (in input) of the output table on key fields.
  • WHERE Clauses : ""

The error that the job returns is "single-row subquery returns more than one row". 

 

I tried to use also tRedshiftOutput component and tRedshiftRow for the update, and in this case everything is ok. I have to use ELT component because is much faster with lots of data to move.

 

I wish you can help me! 

 

Really thank you!

Forteen Stars

Re: elt update table Redshift

Hi 

 

without design it not possible to tell what wrong

first of all you could try to check in the Code tab - what SQL query generated and try to run it manually on Redshift

 

but in case of simple UPDATE you could always use tRedshiftRow and write your update manually, it will work same as tELT - run all code on the server without transfer data

 

regards, Vlad

-----------
Highlighted
Four Stars

Re: elt update table Redshift

Really tahnk you for your reply.

 

As I said, I am able to update table with ordinary talend components, as tDBOutput.

 

I am not able to get update with tELTOutput component, mapping Redshift. So, can you tell me how to set this component to get a correct update?

 

Here, you can see my use-case. The error is "single-row subquery returns more than one row" (table A that update table B. I also set correct keys).

 

Please, provide me a solution for this problem.

 

Thanks

Employee

Re: elt update table Redshift

Hi @rosemath91 ,

 

Could you please check the keys that you have set for updation have unique values in both tables. Normally this error is thrown when the keys which are used to join are not unique.

 

Regards,

Pratheek Manjunath

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch 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